- Azure Active Directory
- Emulate prepare in PDO_SQLSRV driver
- Idle Connection Resiliency
- Querying sql_variant columns
- Connection pooling in Linux and Mac
- Using Always Encrypted with the PHP driver
- Always Encrypted: Limitations
- Optional "Driver" Connection option
- Binding Decimals as Parameters
- Using Azure Key Vault for Column Encryption
- Retrieving DateTime values as strings or PHP DateTime objects
- Configurable options to format fetched decimal or numeric values
- Language Selection
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.
Starting with 5.6.0, the support for Azure AD access token (requires ODBC Driver version 17 or above in Linux or macOS) and managed identities for Azure resources (requires ODBC Driver version 184.108.40.206 or above) are introduced. Please check Connect Using Azure Active Directory Authentication for more details.
Emulate prepare in PDO_SQLSRV driver
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.
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)
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 ] )
$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.
- 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.
$connectionOptions = array("Database"=>$database,"UID"=>$userName, "PWD"=>$userPassword, "Driver"=>"ODBC Driver 17 for SQL Server"); $conn = sqlsrv_connect($server, $connectionOptions);
$connectionOptions = "Driver = ODBC Driver 17 for SQL Server"; $conn = new PDO("sqlsrv:server = $server; $connectionOptions", $uid, $pwd);
Binding Decimals as Parameters
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
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:
$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();
$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. The same option is added to the statement level in 5.6.0, which always override the corresponding connection option.
On the other hand, when using PDO_SQLSRV, datetime, Date, Time, DateTime2, and DateTimeOffset types are by default returned as strings. Starting with 5.6.0, 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::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE affects whether DateTime data types are returned as strings or not. For details, please read
- How to: Retrieve Date and Time Types as Strings Using the SQLSRV Driver
- How to: Retrieve Date and Time Types as PHP DateTime Objects Using the PDO_SQLSRV Driver
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.6.0, 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.
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.
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.
The equivalent connection and statement attributes in pdo_sqlsrv are
PDO::SQLSRV_ATTR_DECIMAL_PLACES. For both drivers, the statement attributes always override the corresponding connection attributes. For details, please check
- Formatting Decimal Strings and Money Values (SQLSRV Driver)
- Formatting Decimal Strings and Money Values (PDO_SQLSRV Driver)
The SQLSRV and PDO_SQLSRV drivers allow the user to specify a preferred language using the
Language connection option. The effect of this option is similar to executing
SET LANGUAGE. The available languages come from the server's syslanguages table. To see the full list of available languages, execute the following query:
SELECT name,alias FROM sys.syslanguages.
Note that this option only affects the language of messages returned from the server. It does not affect the language used by the drivers themselves, as they are currently available only in English, and it does not affect the language of the underlying ODBC driver, whose language is determined by the localised version installed on the client system. Therefore, it is possible that changing the
Language setting will result in messages being returned in different languages, depending on whether they come from the PHP driver, the ODBC driver, or SQL Server.