Microsoft SQL Server Functions (PDO_SQLSRV)

Introducere

PDO_SQLSRV is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to MS SQL Server (starting with SQL Server 2005) and SQL Azure databases.

Instalarea

The PDO_SQLSRV extension is enabled by adding appropriate DLL file to your PHP extension directory and the corresponding entry to the php.ini file. The PDO_SQLSRV download comes 8 driver files, four of which are for PDO support. If you are running non-thread-safe PHP (PHP 5.3), use the php_pdo_sqlsrv_53_nts.dll file. (You should use a non-thread-safe version if you are using IIS as your web server). If you are running thread-safe PHP, use the php_pdo_sqlsrv_53_ts.dll file. Similarly for PHP 5.4, use the php_pdo_sqlsrv_54_nts.dll or php_pdo_sqlsrv_54_ts.dll depending on whether your PHP installation is non-thread-safe or thread-safe.

The most recent version of the driver is available for download here: » SQLSRV 3.0 download. If you need support for PHP 5.2 and/or PHP compiled with VC6, use the 2.0 release of the driver: » SQLSRV 2.0 download.

For more information about system requirements, see » SQLSRV System Requirements.

The PDO_SQLSRV extension is only compatible with PHP running on Windows. For Linux, see ODBC and » Microsoft's SQL Server ODBC Driver for Linux.

Constante predefinite

Constantele de mai jos sunt definite de acest driver și vor fi disponibile doar când această extensie a fost compilată în interiorul PHP, sau a fost încărcată dinamic în timpul rulării. În plus, aceste constante specifice driver-ului trebuie utilizate numai dacă utilizați acest driver. Utilizarea atributelor specifice unui driver cu un alt driver poate rezulta într-un comportament imprevizibil. PDO::getAttribute() poate fi utilizată pentru a obține atributul PDO_ATTR_DRIVER_NAME , pentru a verifica driver-ul în caz că codul-sursă al dumneavoastră poate rula cu mai multe driver-e.

PDO::SQLSRV_TXN_READ_UNCOMMITTED (integer)
This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Read Uncommitted.
PDO::SQLSRV_TXN_READ_COMMITTED (integer)
This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Read Committed.
PDO::SQLSRV_TXN_REPEATABLE_READ (integer)
This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Repeateable Read.
PDO::SQLSRV_TXN_SNAPSHOT (integer)
This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Snapshot.
PDO::SQLSRV_TXN_SERIALIZABLE (integer)
This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Serializable.
PDO::SQLSRV_ENCODING_BINARY (integer)
Specifies that data is sent/retrieved as a raw byte stream to/from the server without performing encoding or translation. This constant can be passed to PDOStatement::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam.
PDO::SQLSRV_ENCODING_SYSTEM (integer)
Specifies that data is sent/retrieved to/from the server as 8-bit characters as specified in the code page of the Windows locale that is set on the system. Any multi-byte characters or characters that do not map into this code page are substituted with a single byte question mark (?) character. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam.
PDO::SQLSRV_ENCODING_UTF8 (integer)
Specifies that data is sent/retrieved to/from the server in UTF-8 encoding. This is the default encoding. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam.
PDO::SQLSRV_ENCODING_DEFAULT (integer)
Specifies that data is sent/retrieved to/from the server according to PDO::SQLSRV_ENCODING_SYSTEM if specified during connection. The connection's encoding is used if specified in a prepare statement. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam.
PDO::SQLSRV_ATTR_QUERY_TIMEOUT (integer)
A non-negative integer representing the timeout period, in seconds. Zero (0) is the default and means no timeout. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, and PDO::prepare.
PDO::SQLSRV_ATTR_DIRECT_QUERY (integer)
Indicates that a query should be executed directly, without being prepared. This constant can be passed to PDO::setAttribute, and PDO::prepare. For more information, see » Direct and Prepared Statement Execution.

Cuprins

  • PDO_SQLSRV DSN — Connecting to MS SQL Server and SQL Azure databases
add a note add a note

User Contributed Notes 6 notes

up
16
ian at helastel dot com
1 year ago
An updated driver compatible with both PHP 7.0 and Linux is available on the PHP-7.0-Linux branch of https://github.com/Microsoft/msphpsql/

This is more up to date and supports some additional features over the ODBC / DBLIB drivers.

It is currently considered an 'Early Technical Preview' and hence has some limitations, but in my experience it has been stable.
up
2
php at zootal dot com
4 months ago
Disclaimer: this is how I was able to connect to a Microsoft sql server via PDO using the PECL pdo_sqlsrv extension and unixodbc. PHP 7.2. Linux – Slackware 14.2. These comments represent my understanding at the time I wrote this, what I did to get this working, and may contain errors. I am not responsible for any problems or damage etc. that might occur if you follow these instructions. YMMV. This worked for me, I hope it helps you.

This is an extension to the comment I posted yesterday that can be found here (http://php.net/manual/en/ref.pdo-sqlsrv.connection.php). This version is more complete and includes expanded instructions.

I compiled and installed PHP from source. If you use your package manager to do this, then it is up to you to figure out how to get --with-pdo-dblib --with-pdo-odbc=unixODBC working, as well as how to install the PECL pdo_sqlsrv extension.

Do NOT install freetds. pdo_sqlsrv does not require freetds.
Do NOT install the Microsoft ODBC driver. pdo_sqlsrv does not use it.
Do NOT install the PECL "sqlsrv" extension. It is not needed.

Install unixODBC. I had to download and install from source because the package manager version did not work. How you do this is up to you, but if you get compile errors when compiling PHP, your unixODBC install is a likely culprit.

Install PHP 7.2 (or whatever version you want). When you configure PHP, be sure to include:

  --with-pdo-dblib --with-pdo-odbc=unixODBC

If you get compile errors, try it without --with-pdo-odbc=unixODBC to make sure it isn't something else causing the problem.

After you get PHP installed and working, install the PECL pdo_sqlsrv extension:

pecl install pdo_sqlsrv.

This is all the setup that is required to get this to work.

This is how I successfully connected to and queried a mssql table. In this case, I used the ip address of my sql server, not host or server name. Notice the variable names $pdo_object and $pdo_statement_object. I used them to indicate what data types those variables actually represent. $dsn is simply a text string containing the ip address and default database for the connection to your mssql server. Also note that in $dsn we use “dblib:host”, NOT “sqlsrv:host”. I believe this to be a critical distinction, as many examples of how to use pdo seem to use “sqlsrv:host”, which IIAC utilizes the sqlsrv extension and the MS ODBC driver, *not* pdo. This caused me a lot of grief until I figured this out.

<?php

$dsn
= 'dblib:host=<ip address>;dbname=<database name>';
$user = 'user id';
$password = 'password';

try
{
   
$pdo_object = new PDO($dsn, $user, $password);
}
catch (
PDOException $e)
{
    echo
'Connection failed: ' . $e->getMessage();
}

$sql = "SELECT * from <some table>";
$pdo_statement_object = $pdo_object->prepare($sql);
$pdo_statement_object->execute();
// $result = $pdo_statement_object->fetch(PDO::FETCH_ASSOC);
$result = $pdo_statement_object->fetchAll();
print_r($result);
?>
up
14
John P
3 years ago
As of 12/12/2014, Microsoft has officially released Version 3.1.

Support for 5.5 has 4 drivers
php_pdo_sqlsrv_55_nts.dll
php_pdo_sqlsrv_55_ts.dll
php_sqlsrv_55_nts.dll
php_sqlsrv_55_ts.dll

Note: Version 3.1 now supports PHP 5.5 and requires Microsoft ODBC Driver 11 (or higher)

You can downlod the new driver from
http://www.microsoft.com/en-us/download/details.aspx?id=20098
up
16
Peter
3 years ago
SQLSRV 3.2 is now available from Microsoft that supports PHP 5.6

http://www.microsoft.com/en-us/download/details.aspx?id=20098
up
-1
fleduc dot perso at gmail dot com
7 months ago
Watch out!

If you use PDO SQLSRV on windows 7, using 32 bit php on XAMMP, you might encounter driver problems : "This extension requires the Microsoft ODBC Driver 11 for SQL Server to communicate with SQL Server"

The reason, Microsoft 32-bit ODBC driver doesn't install properly on 64-bit Windows 7.

Check the solution to PDO SQLSRV driver problem here in StackOverflow

https://stackoverflow.com/a/46245990/1330248
up
-52
Anonymous
3 years ago
Note that Microsoft has not been updating this driver for some time.  There are unofficial builds for PHP 5.5 based on patches against the source code head revision (from codeplex), and unconfirmed rumors that getting the source to build with PHP 5.6 is difficult.

Official versions have been released only for PHP 5.2, 5.3 and 5.4.
 
These problems probably apply to both the PDO driver (pdo_sqlsrv) and the non-PDO driver (sqlsrv extension), since they seem to be built and released together.
To Top