Post
Ask Question

Microsoft Drivers For PHP For SQL Server

Introduction

In this article we will learn the sqlsrv API, which is a Microsoft driver for PHP for SQL Server. The names of all the related functions start with sqlsrv_.  This API helps us to use the SQL Server database in a PHP Project.

List of all functions

  • sqlsrv_begin_transaction
  • sqlsrv_cancel
  • sqlsrv_client_info
  • sqlsrv_close
  • sqlsrv_commit
  • sqlsrv_configure
  • sqlsrv_connect
  • sqlsrv_errors
  • sqlsrv_execute
  • sqlsrv_fetch
  • sqlsrv_fetch_array
  • sqlsrv_fetch_object
  • sqlsrv_free_stmt
  • sqlsrv_get_config
  • sqlsrv_get_field
  • sqlsrv_has_rows
  • sqlsrv_next_result
  • sqlsrv_num_rows
  • sqlsrv_num_fields
  • sqlsrv_prepare
  • sqlsrv_query
  • sqlsrv_rollback
  • sqlsrv_rows_affected
  • sqlsrv_send_stream_data
  • sqlsrv_server_info

sqlsrv_begin_transaction

The begin transaction function is used before rollback and commit calls. This transaction includes all statements on the specified connection that executes the sqlsrv_begin_transaction. Microsoft Drivers for PHP for SQL Server use auto-commit mode by default. Auto-commit mode specifies that all queries will be committed automatically on the success of the query.

Syntax

sqlsrv_begin_transaction ( resource $con)

$con:  This is the connection parameter, used for the transaction.
The return value is Boolean True if the transaction is successfully, otherwise FALSE.

  1. <?php  
  2. $serverName = "local";  
  3. $connectionInfo = array("UID"=>"sa","PWD"=>"urPWD","Database"=>"Practice");  
  4. $con = sqlsrv_connect( $serverName, $connectionInfo);  
  5.   if( $con === false )  
  6.   {  
  7.        echo "Could not connect.\n";  
  8.        die( print_r( sqlsrv_errors(), true ));  
  9.   }  
  10.   if ( sqlsrv_begin_transaction( $con ) === false )  
  11.   {  
  12.        echo "Could not begin transaction.\n";  
  13.        die( print_r( sqlsrv_errors(), true ));  
  14.   }  
  15. $sId = 1005; $qty = 5; $pId = 2;$price = 570;  
  16. $tsql1 = "INSERT INTO sales([salesID],[orderQty],[productID],[unitPrice]) VALUES (?, ?, ?, ?, ?)";  
  17. $params1 = array( $sId, $qty, $pId,$price);  
  18. $stmt1 = sqlsrv_query( $con, $tsql1, $params1 );  
  19. $tsql2 = "UPDATE product SET [orderQty] = ([orderQty] - ?) WHERE ProductID = ?";  
  20. $params2 = array($qty, $pId);  
  21. $stmt2 = sqlsrv_query( $con, $tsql2, $params2 );  
  22.   if( $stmt1 || $stmt2 )  
  23.   {  
  24.       sqlsrv_commit( $con );  
  25.       echo "Transaction was committed.\n";  
  26.   }  
  27.   else  
  28.   {  
  29.       sqlsrv_rollback( $con );  
  30.       echo "Transaction was rolled back.\n";  
  31.   }  
  32. //sqlsrv_free_stmt( $stmt1);  
  33. //sqlsrv_free_stmt( $stmt2);  
  34. sqlsrv_close( $con);  
  35. ?>  

In this example, we execute two queries in transaction mode. If both queries are successful then the transaction will be committed, otherwise rolled back.

Transaction

sqlsrv_cancel

The sqlsrv_cancel function cancels all the pending results, this function is not necessary to call if the result associated with the query statement is consumed. The query statement can be re-executed if the sqlsrv_cancel function is called.

Syntax

sqlsrv_cancel ( resource $statement )

$statement: The statement to be canceled.
Returns a Boolean value TRUE if the opearation is successful otherwise FALSE.

  1. <?php  
  2. $serverName = "local";  
  3. $connectionInfo = array("UID"=>"sa","PWD"=>"urPWD","Database"=>"Practice");  
  4. $con = sqlsrv_connect( $serverName, $connectionInfo);  
  5.   if( $con === false )  
  6.   {  
  7.        echo "Could not connect.\n";  
  8.        die( print_r( sqlsrv_errors(), true ));  
  9.   }  
  10. $tsql = "SELECT * FROM sales";  
  11. $stmt = sqlsrv_prepare( $con, $tsql);  
  12.   if( $stmt === false )  
  13.   {  
  14.       echo "Error in statement preparation.\n";  
  15.       die( print_r( sqlsrv_errors(), true));  
  16.   }  
  17.   if( sqlsrv_execute( $stmt ) === false)  
  18.   {  
  19.       echo "Error in statement execution.\n";  
  20.       die( print_r( sqlsrv_errors(), true));  
  21.   }  
  22. $salesTotal = 0;  
  23. $count = 0;  
  24.   while( ($row = sqlsrv_fetch_array( $stmt)) && $salesTotal <=50000)  
  25.   {  
  26.       $qty = $row[0];  
  27.       $price = $row[1];  
  28.       $salesTotal += ( $price * $qty);  
  29.       $count++;  
  30.   }  
  31. echo "$count sales accounted for the first $$salesTotal in revenue.\n";  
  32. sqlsrv_cancel( $stmt);  
  33. ?>  

 cancel

sqlsrv_client_info

This function shows the information about the connection and client.

Syntax

sqlsrv_client_info ( resource $con )

$con: Connection of the database and SQL Server.
Returns DriverDllName, DriverODBCVer, DriverVer, ExtensionVer.

sqlsrv_close

This function is used to close all connections and free all resources that are associated with that connection.

Syntax

sqlsrv_close( resources $con)

$con: The connection to be closed.
Returns a Boolean value TRUE if the opearation is successful otherwise FALSE.

  1. <?php  
  2. $serverName = "local";  
  3. $connectionInfo = array("UID"=>"sa","PWD"=>"urPWD","Database"=>"Practice");  
  4. $con = sqlsrv_connect( $serverName, $connectionInfo);  
  5.   if( $con === false )  
  6.   {  
  7.        echo "Could not connect.\n";  
  8.        die( print_r( sqlsrv_errors(), true ));  
  9.   }  
  10.    
  11. sqlsrv_close( $con);  
  12. echo "connection is closed!";  
  13. ?>  
sqlsrv_commit

This function commits the current transaction on the provided connection and returns the connection to the auto-commit mode. As we saw in our first example, we use the sqlsrv_commit function. 

Syntax

sqlsrv_commit( resource $con)

$con: Connection of transaction.
Returns a Boolean value TRUE if the opearation is successful otherwise FALSE.

sqlsrv_configure

This function helps to change the settings for error handling and logging options.

Syntax

sqlsrv_configure ( string $setting, mixed $value)

$setting: The settings value are WarningReturnAsErrors, LogSubsystems and LogSeverity.
$value: The value to be applied to the setting specified in the $setting parameter. The possible value for the parameter depends on which setting is passed.
 
sqlsrv_connect

This function is used to make a connection with the database.

Syntax

sqlsrv_connect( string $serverName [ , array $connectionInfo ])

$serverName: Name of the server that a connection is to be established with. This is the instance name of the server, it may be a name or an IP address with a port number.
$connectionInfo: This is an optional field, if we are connecting with Windows Authentication Mode then we need to only pass the database name, otherwise provide a UID and PWD with this field along with the database name.
Returns a Boolean value TRUE if the opearation is successful otherwise FALSE.

  1. <?php  
  2. $connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=>"DBName");  
  3.    
  4. $serverName = "(local/IP address)";  
  5. $connectionInfo = array( "Database"=>"DBName");  
  6. $conn = sqlsrv_connect( $serverName, $connectionInfo);  
  7.    
  8.   if( $conn )  
  9.   {  
  10.       echo "Connection established.\n";  
  11.   }  
  12.   else  
  13.   {  
  14.       echo "Connection could not be established.\n";  
  15.       die( print_r( sqlsrv_errors(), true));  
  16.   }  
  17.    
  18. sqlsrv_close( $conn);  
  19. ?>  

sqlsrv_errors

This function generates an error or warning report if any information is wrong in the function parameter passed.

 Syntax

sqlsrv_errors ( [int $errorAndWarnings ] )

$errorAndWarnings: This parameter is optional, either you can pass the integer value or something else. If we do not pass a value, this function will generate both an error and a warning message.
The return value is an array or null, each array conatins three pair values of 1) SQLSTATE 2) code and 3) message.

sqlsrv_execute

This function is used to execute the prepared statement, this can also execute the prepared statement multiple times with different parameters.

Syntax

sqlsrv_execute ( resource $statement )

$statement: This is a resource; specify the statement to be executed.
Returns a Boolean value TRUE if the opearation is successful otherwise FALSE.

  1. <?php  
  2. $serverName = "local";  
  3. $connectionInfo = array("UID"=>"sa","PWD"=>"urPWD","Database"=>"Practice");  
  4. $con = sqlsrv_connect( $serverName, $connectionInfo);  
  5.   if( $con === false )  
  6.   {  
  7.       echo "Could not connect.\n";  
  8.       die( print_r( sqlsrv_errors(), true ));  
  9.   }  
  10. /* Set up the Transact-SQL query. */  
  11. $tsql = "UPDATE product SET [orderQty] = ([orderQty] - ?) WHERE ProductID = ?";  
  12. $params = array( 5, 1);  
  13. $stmt = sqlsrv_prepare( $con, $tsql, $params);  
  14.   if( $stmt )  
  15.   {  
  16.       echo "Statement prepared.\n";  
  17.   }  
  18.   else  
  19.   {  
  20.       echo "Error in preparing statement.\n";  
  21.       die( print_r( sqlsrv_errors(), true));  
  22.   }  
  23.   if( sqlsrv_execute( $stmt))  
  24.   {  
  25.       echo "Statement executed.\n";  
  26.   }  
  27.   else  
  28.   {  
  29.       echo "Error in executing statement.\n";  
  30.       die( print_r( sqlsrv_errors(), true));  
  31.   }  
  32. sqlsrv_free_stmt( $stmt);  
  33. sqlsrv_close( $con);  
  34. ?>  

prepare

sqlsrv_fetch

This function fetches the next row of the record and sets it available for reading.

Syntax

sqlsrv_fetch ( resource  $stmt [ , row[ , ] offset ] )

$stmt: This statement is the resource corresponding to an executed statement. A statement must be executed before results can be retrieved.
row [optional]: There are some values, SQTSRV_SCROLL_NEXT, SQTSRV_SCROLL_PRIOR, SQTSRV_SCROLL_FIRST, SQTSRV_SCROLL_LAST, SQTSRV_SCROLL_ABSOLUTE, SQTSRV_SCROLL_RELATIVE.
offset: This is also optional, offset is only used when we use SQTSRV_SCROLL_ABSOLUTE, SQTSRV_SCROLL_RELATIVE to specify the row to relative. The first record in the result set is 0.

If the next row of the result set was successfully retrieved, TRUE is return, and if there is no record in the result set NULL is returned and in the case of an error FALSE is returned.

sqlsrv_fetch_array

This function fetches the next row of the data as a numerically indexed array, associative array, or both. The sqlsrv_fetch_array function always returns data depending on the default PHP type, If a field has no names, the associative key for the array element will be an empty string.

Syntax

sqlsrv_fetch_array ( resource $stmt [ , int $fetchType [ , row [ , ] offset ] ] )

$fetchType: This is an optional parameter for this function. This function has predefined constants. the values are SQLSRV_FETCH_NUMERIC, SQLSRV_FETCH_ASSOC, SQLSRV_FETCH_BOTH. 

If a row of data is retrived, an array is returned. If there are no more rows to retrieve, NULL is returned. If an error occurs, FALSE will be returned.

sqlsrv_fetch_object

This function fetches the next row of data as an object.

Syntax

sqlsrv_fetch_object ( resource $stmt [, string $className [ , array $ctorParams[ , row [ , ] offset ] ] ] )

$stmt: This is a statement resource corresponding to an executed staement.
$className: This is optional. You can specify the name of the class, if you cannot then PHP stdClass will be instiated.
$ctorParams: This is also optional. An array that contains a values passed to the constructor of the class specified with the $className parameter.
row:  This is also optional. This will specify the row to access in a result set that uses a scrollable cursor.  
offset: This is also optional, the offset is only used when we use SQTSRV_SCROLL_ABSOLUTE, SQTSRV_SCROLL_RELATIVE to specify the row to relative. The first record in the result set is 0.

Return a object with properties that corresponds to the result set field names, property value populated with the corresponding to the result set value.

sqlsrv_free_stmt

This function closes the connection and frees all resources associated with the statements.

Syntax

sqlsrv_free_stmt ( resource $stmt )

$stmt: A statement to be closed.
Returns a Boolean TRUE value when the parameter value is not incorrect, otherwise FALSE. 

sqlsrv_get_config

This function returns the value of the specified configuration settings.

Syntax

sqlsrv_get_config( string $setting)

$setting: configuration setting.
Returns a setting value that we specified in the parameter.

sqlsrv_get_field

This function retrieves the field in the current row by index. The PHP return type will be specified. 

Syntax

sqlsrv_get_field ( resource $stmt , int $fieldIndex [ , $getAsType ] )

$stmt: A statement resource corresponding to the executed statement.
$fieldIndex: index of the field to be retrived.
$getAsType: This is optional. SQLSRV_PHPTYPE_* this determine the PHP data type in the returned data.

sqlsrv_has_rows

This function finds the rows in a result set.

Syntax

sqlsrv_has_rows ( resource $stmt)

$stmt: A query statement.
The return value is Boolean, if there are any records in the result set then TRUE, otherwise FALSE.

sqlsrv_next_result

This function provides notification that the next result is available for processing.

Syntax

sqlsrv_next_result ( resource $stmt)

$stmt: A query statement that the next result is made active.
If the next result was successfully made active the Boolean value TRUE will returned, otherwise FALSE.

sqlsrv_num_fields

This function specifies the number of fields in an active result set.

Syntax

sqlsrv_num_fields( resource $stmt)

$stmt: The statement on which the target result set is active.
Returns always an integer value that will be in number of active fields, if any error occurred then FALSE will be returned.

sqlsrv_prepare

This function creates a statement resource associated with the specified connection. This function is very uselful to execute multiple queries.

Syntax

sqlsrv_prepare ( resource $con, string $tsql [ , array $prams [ , array $options ] ] )

$con: Connection resource for the query execution.
$tsql: SQL query expression that correspond with the created statement.
$params: This is an optional field, an array of values that will correspond to the query.
$options: An associative array that sets query properties, the supported keys are:

  1.  QueryTimeout: sets the query timeout in seconds, by default the driver will wait indefinitely for results.
  2.  SendStreamParamsAtExec: Configure the driver to send all stream data at execution (TRUE), or to send data in chunks.
  3.   Scrollable 
sqlsrv_query

This function  prepare and execute a  SQL Query.

Syntax

sqlsrv_query( resource $con , string $tsql [ , array $params [ , array $options ] ] )

$con: Connection for prepared statement.
$tsql: this is Transact-SQL expression that corresponds to the prepared statement.
$params: This is optional,  parameters for parameterized query.
$options: An associative array that sets query properties, the supported keys are:
  1.  QueryTimeout: sets the query timeout in seconds, by default the driver will wait indefinitely for results.
  2.  SendStreamParamsAtExec: Configure the driver to send all stream data at execution (TRUE), or to send data in chunk.
  3.  Scrollable 
sqlsrv_rollback

This function rolls back a transaction on a specified connection and returns auto-commit mode.

Syntax

sqlsrv_rollback( resource $con)

$con: Connection of transaction.
Return value TRUE if the transaction is successful, otherwise FALSE.

sqlsrv_rows_affected

This function returns the number of modified rows. This function never returns the number of rows returned by a select statement.

Syntax

sqlsrv_rows_affected ( resource $stmt )

$stmt: This will correspond to the executed statement.
Returns an integer affected/modified rows by the last executed statements. If no rows were modified, zero is return. If no information about the number of affected or modified rows ia available the (-1) negative one will be returned and in if an error occurrs when retrieving the modified rows, FALSE will be returned.

sqlsrv_send_stream_data

This function sends 8 KB (kilo bytes) of data to the server with each call. By default, all stream data is sent to the server when a query executes. if you want to change the default behavior then you should use the sqlsrv_send_stream_data.

Syntax

sqlsrv_send_stream_data ( resource $ stmt)

$stmt: This will corresponds to the executed statement.
Returns a Boolean value, TRUE if more data is to be sent, otherwise FALSE.
  1. <?php  
  2. $serverName = "local";  
  3. $connectionInfo = array("UID"=>"sa","PWD"=>"urPWD","Database"=>"Practice");  
  4. $con = sqlsrv_connect( $serverName, $connectionInfo);  
  5.   if( $con === false )  
  6.   {  
  7.       echo "Could not connect.\n";  
  8.       die( print_r( sqlsrv_errors(), true ));  
  9.   }  
  10. /* Define the query. */  
  11. $tsql = "UPDATE product SET [orderQty] = (?) WHERE ProductID = 1";  
  12. $comment = fopen( "data://text/plain,[500 ]", "r");  
  13. $params = array( &$comment);  
  14. $options = array("SendStreamParamsAtExec"=>0);  
  15. $stmt = sqlsrv_prepare( $con, $tsql, $params, $options);  
  16.    
  17. sqlsrv_execute( $stmt);  
  18.    
  19. $i = 1;  
  20.   while( sqlsrv_send_stream_data( $stmt))   
  21.   {  
  22.       echo "$i call(s) made.\n";  
  23.       $i++;  
  24.   }  
  25. sqlsrv_free_stmt( $stmt);  
  26. sqlsrv_close( $con);  
  27. ?>  

streamdata

sqlsrv_server_info


This function provides information about the server, the connection should be established before the calling the function.

Syntax

sqlsrv_server_info ( resource $con)

$con: This the parameter for the connection where the client and server are connected.
The return values of this function are:

  1. Current Database Name
  2. SQL Server Version
  3. SQL Server Name
Summary
 
 This article described how to connect with a SQL Server with PHP and what the SQL Server API is used in PHP for development, this article described a detailed view of the SQL Server API.


Similar Articles