Search results matching tags 'PHP' and 'Design'

Functions to return row count and field count

Anonymous — Thu, 06 Mar 2008 00:50:00 GMT

It’s great to see that people are downloading our the February CTP build of our PHP driver and that we’re getting feedback on our new features. We look forward to more of your feedback and we’re in the process of setting up a new forum specifically for questions and feedback for the PHP driver.

A lot of the feedback has centered around requests for a num_rows function for the PHP driver. In our PHP driver, we’re using the most performant way to retrieve the results of a SQL Server query, a forward-only read-only stream of data. With this approach there’s no way for the driver to know how the total number of rows that the query will return until you’ve finished processing the results of the query. We could offer a num_rows function but without providing buffered resultsets, that num_rows function would only return the number of rows returned so far. While that appears to be a fairly standard behavior for drivers that don’t support buffered resultsets, it doesn’t strike me as adding a great deal of value. With that said, I’m curious as to whether there’s real interest in a num_rows function without adding support for buffered resultsets?

We are planning on adding a function to return the number of fields in a resultset later in this release. There’s a simple workaround in the meantime – use the count function in conjunction with sqlsrv_field_metadata:

$fieldCount = count(sqlsrv_field_metadata($stmt));

The main benefit in adding a function to return this information is that returning the number of fields without generating the field metadata would be more performant.

David Sceppa

Program Manager, SQL Server Driver for PHP

Breaking API changes in the February '08 CTP

Anonymous — Wed, 20 Feb 2008 03:10:00 GMT

Thanks so much for the feedback on our first Technical Preview. It's great to see people engaging with the bits at such a detailed level and the feedback we've gotten thus far has been spot on and extremely constructive. We've been hard at work digesting all the suggestions and turning them into a design changes. Here's the set of changes that we've come up with for our February CTP.

API Names

First, we've gotten some strong feedback that that the _stmt and _conn in the API names are not necessary, so we will be removing these to make the names more concise. This table summarizes the changes (you may notice other changes to the signatures – they're discussed in detail below):

October 07 CTP Name	February 08 CTP Name
sqlsrv_conn_client_info($conn)	sqlsrv_client_info($conn)
sqlsrv_conn_close($conn)	sqlsrv_close($conn)
sqlsrv_connect($server, $options)	sqlsrv_connect($server, $options)
sqlsrv_conn_execute($conn, $tsql [, $params [, $sqlTypes]])	sqlsrv_query($conn, $tsql [, $params])
sqlsrv_conn_prepare($conn, $tsql [, $sqlTypes])	sqlsrv_prepare($conn, $tsql [, $params])
sqlsrv_conn_server_info($conn)	sqlsrv_server_info($conn)
sqlsrv_errors()	sqlsrv_errors()
sqlsrv_stmt_cancel($stmt)	sqlsrv_cancel($stmt)
sqlsrv_stmt_close($stmt)	sqlsrv_free_stmt($stmt)
sqlsrv_stmt_execute($stmt [, $params])	sqlsrv_execute($stmt)
sqlsrv_stmt_fetch($stmt)	sqlsrv_fetch($stmt)
sqlsrv_stmt_fetch_array($stmt [, $fetchType])	sqlsrv_fetch_array($stmt [, $fetchType])
sqlsrv_stmt_field_metadata($stmt)	sqlsrv_field_metadata($stmt, $fieldIndex)
sqlsrv_stmt_get_field($stmt, $fieldIndex [, $getAs])	sqlsrv_get_field($stmt, $fieldIndex [, $getAs])
sqlsrv_stmt_rows_affected($stmt)	sqlsrv_rows_affected($stmt)
sqlsrv_stmt_send_stream_data($stmt)	sqlsrv_send_stream_data($stmt)

Working with Parameters, Part I (Inferring Data Types)

We decided to streamline the way parameterized queries are specified in the API (and use the same mechanism for both sqlsrv_query and sqlsrv_prepare) to make parameter code easier to write and maintain. The first and most obvious change is that the sqlsrv_execute method no longer takes an array of parameters. Instead, the parameter values are bound to variables at the time sqlsrv_prepare is called, and your code only needs to change the values of bound variables and call sqlsrv_execute again to re-execute with a different set of parameter values. For example, let's say that we are entering details for a new product into a database. A parameterized query to insert a new product and return the server-generated key value might look like:

$sql = 'INSERT INTO Products (ProductName, CategoryID, UnitPrice) '

.'VALUES (?, ?, ?);SELECT SCOPE_IDENTITY() AS NewProductID';

and a series of values to match those parameters like:

//each element represents a parameter value

$newProduct = array('New Product', 1, 12.34);

Assuming an open connection is available as $conn, the code for inserting the new product and retrieving the server-generated key value might look something like this:

$sql = 'INSERT INTO Products (ProductName, CategoryID, UnitPrice) '

.'VALUES (?, ?, ?);SELECT SCOPE_IDENTITY() AS NewProductID';

//each element represents a parameter

$newProduct = array('New Product', 1, 12.34);

//Execute the query

$stmt = sqlsrv_query($conn, $sql, $newProduct);

//Move to the next result to access the value from the SELECT query

sqlsrv_next_result($stmt);

sqlsrv_fetch($stmt);

echo("New ProductID: ".sqlsrv_get_field($stmt, 1)." \n");

Working with Parameters, Part II (Specifying Metadata)

In the October 2007 CTP, all parameters were treated as input parameters (i.e. it was possible to send data to the server, but not get data from the server using this mechanism).

This time around, parameters can be input only (SQLSRV_PARAM_IN), bidirectional (SQLSRV_PARAM_INOUT), or output-only (SQLSRV_PARAM_OUT). Two other pieces of metadata can be specified by the user: the data type that the parameter should be mapped to on the server (the SQLTYPE), and what data type the parameter should be retrieved as (the PHPTYPE). Whereas the metadata available in the October '07 CTP (SQLTYPEs) was a separate function argument from the values themselves, we have decided to re-factor the way parameter metadata is specified to make it easier to program the API.

With this approach, we can now change the previous query to call a stored procedure that accepts input parameters, submits the new product and returns the new server-generated key value via an output parameter.

You could create such a stored procedure by executing the following code:

$sql = "CREATE PROCEDURE CreateProduct \n"

." (@ProductName nvarchar(40), @CategoryID int, \n"

." @UnitPrice money, @ProductID int OUTPUT) \n"

."AS \n"

." SET NOCOUNT ON \n"

." INSERT INTO Products (ProductName, CategoryID, UnitPrice) \n"

." VALUES (@ProductName, @CategoryID, @UnitPrice); \n"

." SET @ProductID = SCOPE_IDENTITY(); \n";

$stmt = sqlsrv_query($conn, $sql);

We can now call that stored procedure using the canonical-call syntax, recommended for stored procedures using the SQL Server 2005 Driver for PHP:

$sql = '{? = CALL CreateProduct (?, ?, ?, ?)}';

First we initialize the variables that we'll use to store the values passed to and from the stored procedures:

//initialize the variables for the parameters

list($returnValue, $productName, $categoryId, $unitPrice, $productId)

= array(-1, 'New Product', 1, 12.34, -1);

Next, we create an array of parameters. In the initial example, the array of parameters was based on just the corresponding variables using code like:

$parameters = array(

$p1Var,

$p2Var,

…

);

The drawback to the preceding approach is that it offers minimal control over the direction and data types used. All parameters are input-only and the SQL Server data type is inferred. In the February 2008 CTP of the SQL Server PHP driver, you can provide the parameter direction, PHP data type and SQL Server data type for the parameters using code like:

$parameters = array(

array($p1Var, $p1Direction, $p1PHPTYPE, $p1SQLTYPE),

array($p2var, $p2Direction, $p2PHPTYPE, $p2SQLTYPE),

…

);

The elements of the array describing a single query parameter always follow a prescribed order:

· The variable to bind to the query parameter

· The direction for which the value is synchronized (input vs. output vs. input/output)

· What type the data should be converted into when retrieved from the server (PHPTYPE)

· What type the data should be converted into when sent to the server (SQLTYPE)

We can use this approach to building parameters using the following code:

//each element in the array represents a parameter

$newProductParameters =

= array(array($returnValue, SQLSRV_PARAM_OUT,

SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT),

array($productName, SQLSRV_PARAM_IN,

SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR),

SQLSRV_SQLTYPE_NVARCHAR(40)),

array($categoryId, SQLSRV_PARAM_IN,

SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT),

array($unitPrice, SQLSRV_PARAM_IN,

SQLSRV_PHPTYPE_FLOAT, SQLSRV_SQLTYPE_MONEY),

array($productId, SQLSRV_PARAM_OUT,

SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT)

);

Once we've constructed the parameters, we pass the array of parameters to the call to sqlsrv_query as in the previous example:

$stmt = sqlsrv_query($conn, $sql, $newProductParameters);

After the stored procedure call has completed, the value of the output parameter is available in the $productId variable specified.

echo("ProductID: $productId\n");

After the stored procedure call has completed, the value of the output parameter is available in the $productId variable specified.

Note: Since this stored procedure suppressed the message to indicate the number of rows affected by the INSERT query using SQL Server's NOCOUNT setting, the value is immediately available. When calling a stored procedure that returns the results of a SELECT query or the number of rows affected by a DML-based query, you will need to call sqlsrv_next_result until all you've processed all results before the value of the output parameter is available.

Errors and Warnings

To help simplify the coding patterns and make the API more familiar, we've streamlined the way errors and warnings are handled. We've eliminated the sqlsrv_warnings method, in favor of an argument to sqlsrv_errors, which can be used to obtain only the errors (SQLSRV_ERR_ERRORS), only the warnings (SQLSRV_ERR_WARNINGS), or both the errors and the warnings (SQLSRV_ERR_ALL).

The Driver's distinction between errors and warnings comes from ODBC. We looked for a way to let the developer leverage the distinction without requiring an unnecessarily complex programming pattern. Warnings are, by definition, unexpected conditions which do not cause the underlying operation to fail. The "Best Practices" guidance we have been issuing to our customers for many iterations of data access APIs is to check for warnings always, and update code to avoid them, if possible. However, the October '07 CTP required a customer following this guidance to make a call checking for warnings after every operation – which was too much code to write in the "most common scenario" case.

To this end, we are providing a heuristic called warnings_return_as_errors which will cause any warnings encountered to be treated as errors (i.e. return false from the API call where they occur). This heuristic is on by default to align with our guidance that unexpected conditions should be accounted for at the time of development. The following examples show what the programming pattern might look like and how the heuristic will impact the behavior of the APIs.

// for illustration only; this is on by default

sqlsrv_configure('warnings_return_as_errors', 1);

$conn = sqlsrv_connect($serverName);

// The following is designed to show the behavior with warnings specifically...

// RAISERROR(message, severity, state), where a warning = severity 10

$sql = "RAISERROR('My test warning', 10, 1); SELECT 'Done';";

$stmt = sqlsrv_query($conn, $sql);

if ($stmt === false)

foreach(sqlsrv_errors(SQLSRV_ERR_ERRORS) as $error)

echo(' Error message: '.$error['message']."\n");

sqlsrv_close($conn);

Whereas with the October '07 CTP, and with the warnings_return_as_errors heuristic turned off, an additional call is required in order to check for all unexpected conditions:

// turn off the heuristic

sqlsrv_configure('warnings_return_as_errors', 0);

$conn = sqlsrv_connect($serverName);

// The following is designed to show the behavior with warnings specifically...

// RAISERROR(message, severity, state), where a warning = severity 10

$sql = "RAISERROR('My test warning', 10, 1); SELECT 'Done';";

$stmt = sqlsrv_query($conn, $sql);

if ($stmt === false) {

foreach(sqlsrv_errors(SQLSRV_ERR_ERRORS) as $error)

echo(' Error message: '.$error['message']."\n");

} else {

$warnings = sqlsrv_errors(SQLSRV_ERR_WARNINGS);

if (count($warnings) > 0) {

foreach($warnings as $warning)

echo(' Warning message: '.$warning['message']."\n");

sqlsrv_next_result($stmt);

}

$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);

echo(' Retrieved result: '.$row[1]."\n");

}

sqlsrv_close($conn);

The new behavior should enable developers to write less code and gracefully handle unexpected conditions for most scenarios. To turn the heuristic on for all scripts, add the following line to php.ini (this is not required as the heuristic is on by default):

sqlsrv.warnings_return_as_errors = 1;

Similarly, to turn the heuristic off, add this line to php.ini:

sqlsrv.warnings_return_as_errors = 0;

Please stay tuned, as we will soon be blogging about some of the new functionality in the February '08 CTP.

--John A. Bocharov

Program Manager, SQL Server Driver for PHP

Transparency in the Design Process

Anonymous — Thu, 11 Oct 2007 18:52:00 GMT

We have been looking at various options to ensure that the design of our SQL Server Driver for PHP truly reflects the requirements of the day to day challenges that our developer community faces when building real-world web applications and services. We would like to start by being as transparent as possible in the design process. I wanted to briefly describe how we’ll go about it so you know exactly what you are looking at when reading one of our design-related posts.

How did we get here? We shipped the first SQL Server Driver for PHP this week and we are now looking for feedback from the development community.

Transparency in the design? Over the years Microsoft has been opening up the engineering processes incrementally. Long ago there were only betas, and that was the only chance to see and give feedback about a product before it shipped. Then we started to do Community Tech Previews (CTPs). CTPs enabled us to provide bits more often and gather feedback frequently. The goal with increasing the transparency of design is to take this one step further: we would like to enable folks that are interested in our SQL Server Driver for PHP to follow the design topics as we discuss them, and have the opportunity to provide feedback right during the time where we are actively discussing a certain aspect and haven’t made a decision yet.

What exactly would we make visible? In short, our design process. To be more concrete, I’m not talking about some fancy set of specifications. What I mean is that as we go through the detailed design of the various aspects of the SQL Server Driver for PHP, we would post in this blog a) the meeting notes from our design meetings and b) deeper write-ups of specific design challenges where we’d like folks to understand how we’re seeing a problem and provide a channel for deep, detailed feedback.

How transparent is transparent? I want to be completely clear about the scope of the information we are sharing. One of the things we need to learn both from the Microsoft side and from the community side is whether the model works within a practical set of restrictions. We would post as much of our discussions as it is practically possible. However, we have to make sure we don’t compromise the interests of Microsoft as a company. There are certain things that can range from ideas to specific implementation details that we could consider trade secrets, high-value Microsoft intellectual property or something along those lines. It *will* happen that in some cases we will not discuss a topic publicly, either for a certain term (e.g. until a proper IP protection mechanism is in place) or until we ship or ever. This is nothing new, but I haven’t seen folks from large companies discuss this explicitly before, so I wanted to make sure it is clear here.

About your feedback: We would love to hear your thoughts, be it comments, suggestions, ideas or anything else. However, in the end we are designing a commercial Microsoft product. So we’ll happily take your feedback but you need to understand that by providing us feedback in any form you are agreeing that we may use it to develop our product, that others may use it in connection with the product and that you will not be compensated for any of these things. We may incorporate ideas or make changes based on comments you make, or we may make changes to the product that are indirectly influenced by discussions that we have with you and other folks in the community. Again, this is nothing new, but instead of having some fancy statement written in legal lingo I wanted to be upfront about this here in this first post on the topic. Of course our legal folks looked at this, and they were cool enough to understand that the informal nature of the process is what makes it work, and they let us get away with this statement in which I think we clearly delineate what will happen with whatever feedback you send our way.

So, what do folks think? Is this a good idea? Does it sound useful/practical? We will start posting design notes and challenges soon and tweak the process as we go.

John Bocharov
Program Manager
SQL Server Driver for PHP