Skip to content

Features

Jump to bottom
Jenny Tam edited this page Dec 10, 2018 · 104 revisions

Contents

Azure Active Directory

Azure Active Directory (Azure AD) is a central user ID management technology that operates as an alternative to SQL Server authentication. Azure AD allows connections to Microsoft Azure SQL Database and SQL Data Warehouse with federated identities in Azure AD using a username and password or using Windows Integrated Authentication. For details please check the online docs for Azure AD.

Support for Azure AD access token is introduced in 5.4.0-preview, and ODBC Driver version 17+ is required in Linux / macOS. Users can connect using a new connection option AccessToken with a valid access token byte string, but the connection string must not contain keywords for user ID, password, and Authentication. For more technical details, please read Authenticating with an Access Token.

The examples below assume the PHP variable $accToken contains the valid access token byte string in the format extracted from an OAuth JSON response:

PDO_SQLSRV

// Using an access token to connect: do not pass in $uid or $pwd
$connectionInfo = "Database = $azureAdDatabase; AccessToken = $accToken;";
$conn = new PDO("sqlsrv:server = $azureAdServer; $connectionInfo");

SQLSRV

// Using an access token to connect: do not use UID or PWD connection options 
$connectionInfo = array("Database"=>$azureAdDatabase, "AccessToken"=>$accToken);
$conn = sqlsrv_connect($azureAdServer, $connectionInfo);

Emulate prepare in PDO_SQLSRV driver

When PDO::ATTR_EMULATE_PREPARES is on for a prepared statement, the PDO_SQLSRV driver internally replaces the placeholders in a SQL query prepared by PDO::prepare() with parameters that are bounded with PDOStatement::bindParam(), and a complete SQL string with no placeholders left is sent to the database.

Please check the online docs for details and an example of how to use Emulate Prepare

Idle Connection Resiliency

Connection resiliency is the idea that a connection that is broken can be reestablished, within certain constraints. If a connection to Microsoft SQL Server fails, connection resiliency allows the client to automatically attempt to reestablish the connection. Connection resiliency is a property of the data source; only SQL Server 2014 and later and Azure SQL Database support connection resiliency.

Please check the online docs for Connection resiliency.

Querying sql_variant columns

sql_variant is a data type that stores values of various SQL Server-supported data types. A column of type sql_variant may contain rows of different data types. For instance, a column defined as sql_variant can store int, binary, and char values.

For an example for SQLSRV driver, please check sqlsrv_query. Likewise, the PDO_SQLSRV example can be found in PDO::query.

Connection pooling in Linux and Mac

A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all connections are being used, a new connection is made and added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

For more details and examples about connection pooling, please check online docs Connection Pooling

Transparent NetworkIP Resolution (TNIR)

Transparent Network IP Resolution is a revision of the existing MultiSubnetFailover feature that affects the connection sequence of the driver in the case where the first resolved IP of the hostname does not respond and there are multiple IPs associated with the hostname. It interacts with MultiSubnetFailover to provide different connection sequences.

For details, please check online docs about Transparent Network IP Resolution (TNIR)

lastInsertID

Version 5.0 of the Microsoft PHP Drivers for SQL Server changes the behaviour of PDO::lastInsertID to be consistent with the behaviour outlined in the PDO documentation. PDO::lastInsertID returns the ID of the last inserted sequence or row, and accepts an optional string $name as its only parameter:

public string PDO::lastInsertId ([ string $name = NULL ] )

The $name argument is the identifier of the sequence object for which the ID is returned.

Before version 5.0 of the drivers, the behaviour was as follows. The $name argument was treated as a table name and not a sequence name. Therefore, passing a sequence object to lastInsertID produced no output. Passing a table name produced the last inserted ID for that table.

For more information and an example, please check PDO::lastInsertId

Using Always Encrypted with the PHP driver

Always Encrypted allows client application to encrypt data and never reveal the encryption keys or data to the SQL Server. To understand it or for more background information, please see Always Encrypted.

Prerequisites

  • Configure Always Encrypted in your database. Follow the directions in Getting Started with Always Encrypted to configure Always Encrypted in your database.
  • A keystore that houses the master and column encryption keys is required. Currently the PHP drivers support the Windows Certificate Store or Azure Key Vault. Always Encrypted is supported on Windows, Linux, and macOS through Azure Key Vault.
  • MSODBC driver 17, available here.

For details of how to use the Always Encrypted feature, please check the online docs Using Always Encrypted with the PHP Drivers for SQL Server.

Always Encrypted: Limitations

For the lists of limitations in SQL Server and the SQLSRV and PDO_SQLSRV drivers regarding Always Encrypted, please check online docs here.

Optional "Driver" connection option

SQLSRV and PDO_SQLSRV rely on Microsoft ODBC Driver for SQL Server to communicate with SQL Server. If you want to specify which ODBC Driver to use, you can set the "Driver" option with these possible values are:

ODBC Driver 17 for SQL Server ODBC Driver 13 for SQL Server ODBC Driver 11 for SQL Server (Windows only).

By default, when the Driver keyword is not specified, the Microsoft Drivers for PHP for SQL Server attempt to find the supported Microsoft ODBC driver(s) in the system, starting with the latest version of ODBC and so on. If none found, the exception "This extension requires the Microsoft ODBC Driver for SQL Server." is thrown.

SQLSRV:

$connectionOptions = array("Database"=>$database,"UID"=>$userName, "PWD"=>$userPassword, "Driver"=>"ODBC Driver 17 for SQL Server");
$conn = sqlsrv_connect($server, $connectionOptions);

PDO_SQLSRV:

 $connectionOptions = "Driver = ODBC Driver 17 for SQL Server";
 $conn = new PDO("sqlsrv:server = $server; $connectionOptions", $uid, $pwd);

Binding Decimals as Parameters

Both SQLSRV and PDO_SQLSRV support binding decimals as parameters (see the examples in sqlsrv_query and PDOStatement::bindParam).

However, PHP has warned that floating point numbers have limited precision. To illustrate, see the following scenario:

<?php

$number = 999999999999.99999;
printf("%f", $number);
echo PHP_EOL;

$number = 9223372036854.80000;
printf("%f", $number);
echo PHP_EOL;
?>

The output is

1000000000000.000000
9223372036854.800781

Therefore, to ensure that the accuracy is preserved when binding decimals as parameters, it is recommended to use strings as inputs. Below are some examples:

PDO_SQLSRV

$input = 9223372036854.80000;
$stmt = $conn->prepare("INSERT INTO $tbname (col) VALUES (?)");
// by default it is PDO::PARAM_STR, rounding of the input value 
// occurs if PDO::PARAM_INT is specified
$stmt->bindParam(1, $input);    
$stmt->execute();

SQLSRV

$input = "9223372036854.80000";
$params = array($input);
$stmt = sqlsrv_prepare($conn, "INSERT INTO $tbname (col) VALUES (?)", $params);
sqlsrv_execute($stmt);

The above suggestion applies to decimal and numeric data types in SQL Server.

Using Azure Key Vault for Column Encryption

You can use Azure Key Vault (AKV) to store the master and column encryption keys required for Always Encrypted functionality to work. With AKV, Always Encrypted is supported on Windows, Linux, and macOS. To get started with AKV, see the documentation. To connect to AKV from a PHP script, you can use the credentials for an Azure account consisting of either an Azure Active Directory username and password, or an application client ID and client secret. The encryption keys can be easily created in SQL Server Management Studio or using PowerShell; details here. Support for AKV is provided by the underlying ODBC Driver (version 17 is required), as detailed here.

To use AKV from a PHP script, please check the online docs Using Azure Key Vault.

Retrieving DateTime values as strings or PHP DateTime objects

When using SQLSRV, by default, datetime, Date, Time, DateTime2, and DateTimeOffset types are returned as PHP DateTime types. To retrieve these data types as strings, set the connection option ReturnDatesAsStrings to true.

Starting with 5.4.0-preview, the option ReturnDatesAsStrings is added to the statement level, which always override the corresponding connection option. For example,

<?php  
$serverName = 'MyServer';  
$connectionInfo = array('Database' => 'MyDatabase', 'ReturnDatesAsStrings' => false);  
$conn = sqlsrv_connect($serverName, $connectionInfo);  
// Here should check if $conn is valid, but let us assume the connection is successful

$options = array('ReturnDatesAsStrings' => true);
$query = "SELECT DateTimeCol FROM $tableName";
$stmt = sqlsrv_prepare($conn, $query, array(), $options);
// Again, assume $stmt is valid
sqlsrv_execute($stmt);

// Expect the fetched value to be a string
$field = sqlsrv_get_field($stmt, 0); 
echo $field . PHP_EOL;

sqlsrv_close( $conn);  
?>

On the other hand, when using PDO_SQLSRV, datetime, Date, Time, DateTime2, and DateTimeOffset types are by default returned as strings. Starting with 5.4.0-preview, the new flag PDO_STMT_OPTION_FETCHES_DATETIME_TYPE is introduced such that these types can be retrieved as PHP DateTime objects. This flag can be set as a connection attribute or statement attribute, and the latter always overrides the corresponding connection attribute.

Note that for both drivers, retrieving DateTime as objects are only allowed for regular fetching, not as output parameters. In addition, neither PDO::ATTR_STRINGIFY_FETCHES nor PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE affects whether DateTime data types are returned as strings or not.

For example, assuming the connection is successful and $conn a valid PHP resource object:

// To set the connection attribute 
$conn->setAttribute(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE, true);
$query = "SELECT DateTimeCol FROM $tableName";
$stmt = $conn->prepare($query);
$stmt->execute();

// Expect a DateTimeCol value as a PHP DateTime type
$row = $stmt->fetch(PDO::FETCH_ASSOC);  
var_dump($row);

Likewise, this example shows how to set the statement attribute:

$query = "SELECT DateTimeCol FROM $tableName";
$stmt = $conn->prepare($query);
$stmt->setAttribute(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE, true);
$stmt->execute();

// Expect a DateTimeCol value as a PHP DateTime type
$row = $stmt->fetch(PDO::FETCH_NUM);  
var_dump($row);

Alternatively, the user can set the statement attribute as an option:

$dateObj = null;
$query = "SELECT DateTimeCol FROM $tableName";
$options = array(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE => true);
$stmt = $conn->prepare($query, $options);
$stmt->execute();
$stmt->bindColumn(1, $dateObj, PDO::PARAM_LOB);
$row = $stmt->fetch(PDO::FETCH_BOUND);
var_dump($dateObj);

The following example shows how to achieve the opposite:

$dateStr = null;
$query = "SELECT DateTimeCol FROM $tableName";
$options = array(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE => false);
$stmt = $conn->prepare($query, $options);
$stmt->execute();
$stmt->bindColumn(1, $dateStr);
$row = $stmt->fetch(PDO::FETCH_BOUND);
echo $dateStr . PHP_EOL;

Configurable options to format fetched decimal or numeric values

Unlike integers and floats, which can be retrieved as numbers, in order to preserve the accuracy of decimal or numeric types, their values are always fetched as strings with exact precisions. However, if the values are less than 1, the leading zeroes are missing. Note that the same applies to money and smallmoney fields because they are a subset of decimal fields with fixed precisions and scales.

Starting with 5.5.0-preview, the option FormatDecimals is added to sqlsrv connection and statement levels, which allows the user to format decimal strings. This option expects a boolean value (i.e. true or false) and only affects the decimal / numeric data of fetched results. In other words, the FormatDecimals option has no effect on other operations like insertion or update.

By default, FormatDecimals is false. If set to true, the leading zeroes to decimal strings will be added, if missing. In addition, another option is provided at connection and statement levels, DecimalPlaces, for users to configure the number of decimal places when displaying money and smallmoney data. It accepts integer values in the range of [0, 4]. The underlying data remains the same, but rounding may occur when shown. For this reason, it is not recommended to use the formatted results as inputs to any calculations.

Note that DecimalPlaces option only affects money data, and FormatDecimals must be set to true for it to take effect. In other words, if FormatDecimals is set to false, formatting will be turned off, regardless of DecimalPlaces value. Also, since money or smallmoney fields have scale 4, setting DecimalPlaces value to any negative number or any value larger than 4 will be ignored.

For output params, leading zeroes will be added for any decimal fields if FormatDecimals is true, but only if either SQLSRV_SQLTYPE_DECIMAL or SQLSRV_SQLTYPE_NUMERIC is set correctly to match the data fields.

The equivalent connection and statement attributes in pdo_sqlsrv are PDO::SQLSRV_ATTR_FORMAT_DECIMALS and PDO::SQLSRV_ATTR_DECIMAL_PLACES, but we do not support formatting output params in pdo_sqlsrv.

For both drivers, the statement attributes always override the corresponding connection attributes.

The following example shows a simple fetch. Note that error handling is omitted to make things simple.

$connectionInfo = array("UID" => $username, "PWD" => $password, "Database"=>"myDB", "FormatDecimals" => true);  
$server = "myServer";  // IP address also works
$conn = sqlsrv_connect( $server, $connectionInfo);  

$numDigits = 2;
$query = "SELECT money1 FROM $tableName";
$options = array("DecimalPlaces" => $numDigits);
$stmt = sqlsrv_prepare($conn, $query, array(), $options);
sqlsrv_execute($stmt);

if (sqlsrv_fetch($stmt)) {
    $field = sqlsrv_get_field($stmt, 0);  
    echo $field;   // expect a numeric value string with 2 decimal places
}

This example shows how to format the output param of a stored procedure that returns a decimal(8,4) value.

$outString = '';
$numDigits = 2;
$outSql = '{CALL myStoredProc(?)}';
$stmt = sqlsrv_prepare($conn, 
                       $outSql, 
                       array(array(&$outString, SQLSRV_PARAM_OUT, null, SQLSRV_SQLTYPE_DECIMAL(8, 4))),
                       array('FormatDecimals' => true));

if (sqlsrv_execute($stmt)) {
    echo $outString;  // expect a numeric value string with no missing leading zero
}

This pdo_sqlsrv example shows how to set the connection attributes.

$attrs = array(PDO::SQLSRV_ATTR_FORMAT_DECIMALS => true,
                  PDO::SQLSRV_ATTR_DECIMAL_PLACES => 2);
 
$conn = new PDO("sqlsrv:Server=myServer; Database = myDB", $username, $password, $attrs);

Alternatively, the user can set the attributes after connection is successful.

$conn = new PDO("sqlsrv:Server=myServer; Database = myDB", $username, $password);
$conn->setAttribute(PDO::SQLSRV_ATTR_FORMAT_DECIMALS, true);
$conn->setAttribute(PDO::SQLSRV_ATTR_DECIMAL_PLACES, 2);

A simple fetch that uses bindColumn looks like this one below:

$numDigits = 3;
$query = "SELECT smallmoney1 FROM $tableName";
$options = array(PDO::SQLSRV_ATTR_DECIMAL_PLACES => $numDigits);
$stmt = $conn->prepare($query, $options);
$stmt->execute();

$stmt->bindColumn('smallmoney1', $field);
$result = $stmt->fetch(PDO::FETCH_BOUND);  
echo $field;   // expect a number string with 3 decimal places, overriding the corresponding connection attribute

If the user does not want formatting at all, the statement attribute can be set to override the connection attribute, like this:

$query = "SELECT smallmoney1 FROM $tableName";
$options = array(PDO::SQLSRV_ATTR_FORMAT_DECIMALS => false);
$stmt = $conn->prepare($query, $options);
$stmt->execute();

$stmt->bindColumn('smallmoney1', $field);
$result = $stmt->fetch(PDO::FETCH_BOUND);  
echo $field;   // expect a number string showing the original scale -- 4 decimal places
Clone this wiki locally