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