MS SQL Server(PDO)

简介

PDO_DBLIB is a driver that implements the PHP Data Objects(PDO)interface to enable access from PHP to Microsoft SQL Server and Sybase databases through the FreeTDS library.

PDO_DBLIB是一个驱动程序，该驱动程序实现PHP数据对象（PDO）接口，以允许通过FreeTDS库从PHP访问Microsoft SQL Server和Sybase数据库。

在具有PHP 5.3或更高版本的Windows上，此扩展名不再可用。

在Windows上，您应该使用SqlSrv，可以从Microsoft获得MS SQL的替代驱动程序：»http://msdn.microsoft.com/zh-cn/sqlserver/ff657782.aspx。

如果无法使用SqlSrv，则可以使用PDO_ODBC驱动程序连接到Microsoft SQL Server和Sybase数据库，因为本机Windows DB-LIB是古老的，线程不安全并且不再受Microsoft支持。

Table of Contents

• PDO_DBLIB DSN—连接到Microsoft SQL Server和Sybase数据库

Hi All, I'wrote a class to connect to MSSQL/Azure databases with Transaction support.
Hope this can help anyone! 

There is currently little sybase related PDO docs out there. The ones that I found often mention a spec for a dsn that is invalid. Here's how I am currently connecting to sybase ASE:
1. Compile up freetds http://www.freetds.org on top of open client;
2. Add the PDO and PD_DBLIB modules to php 5 as per the documentation; Note: I'm currently using the PDO-beta and PDO_DBLIB-beta;
3. Check mods installed ok using "pear list" and "php -m";
The documentation often says to use "sybase:" as your DSN, this doesn't work. Use "dblib:" instead. Here's an example:

Hope this helps.

Instead of using Mssql or DBLib extension you should use the official extensions from Microsoft from here: https://github.com/Microsoft/msphpsql

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

FYI: PDO dblib module (pdo_dblib.so) was installed when I installed php-mssql in CentOS 7. I thought php-mssql would just include the soon to be deprecated mssql PHP functions but it also contains the PDO connector. After installing this I'm able to connect to our MSSQL 2014 DB via PDO!

If You work with MSSQL Server 7.0/2000/... under Windows and use non latin Encoding then better To use PDO_MSSQL until PDO_ODBC bugs will be fixed (MSSQL ext far more stable and usabe for PHP versions prepare($sql);
$res->bindValue(':value', iconv('UTF-8', 'ISO8859-1', $value);
...

Keep in mind the limitation sql server imposes on clients using the connection methods that dblib uses:
no ntext column values can be transmitted, you will get an insightfull '4004' error when trying.
The full text of the error is:
Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.
For more info on errors that mssql might throw at you, please see:
http://msdn.microsoft.com/en-us/library/aa937595%28v=SQL.80%29.aspx

Here's how to call a stored procedure from MS SQL Server: 

Hopefully this will help someone who is having problems connecting to MSSQL. I was having a heck of a time trying to get my dev server to connect to a remote MSSQL 2005 box.
When using PDO like so:

I was receiving the message:
Adaptive Server is unavailable or does not exist
And mssql_connect() simply told me "Could not connect to server"
I tried everything, different configuration options for FreeTDS/PHP, different versions, etc. But the answer was one line in php.ini:
mssql.secure_connection = Off
This line, which defaults to Off, needs to be ON if you are using NT authentication:
mssql.secure_connection = On
Here is my build:
FreeTDS-0.82 configured like:
./configure --with-tdsver=8.0 --enable-msdblib --with-gnu-ld
PHP 5.2.11 configured like:
./configure --with-mssql=/usr/local --with-pdo-dblib=/usr/local [other options]
Running on Apache/2.2.13 (Unix / Linux)
Feel free to email me if you need help as I've spent 20+ hours reading and fiddling with this issue, so I'm very familiar with it now.
Peace,
Tom

For PDO MSSQL connection issues, ensure that you have the updated version of ntwdblib.dll (currently 8.00.194 as of this post). Overwrite the existing (old) file or place it in the Windows system32 folder. The version that ships with PHP 5.2.X does not work. This is a well known issue apparently, but I had a really hard time finding information on this issue until I was able to lock in a file name. I hope that this helps someone.

If you are struggling with 
'Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.'
when trying to do 'SELECT *' queries:
Obviously the best way to deal with this is change your unicode fields to the ascii equivalent (ntext > text), but if that is not an option here is an implementation based on the info at http://www.rochester.edu/IT/web/WebHelp/mssql/limitations.html which checks the data types of the fields and casts them as neccesary.

Essentially mssql::all_fields($table) returns '*' if it can, otherwise the fields listed with the relevant casts in place e.g. 'NoteIdent, Owner, CAST(Note AS text) AS Note'

Note that you must connect with an MS SQL user - FreeTDS does not seem to like active directory linked users.

To connect to SQL Server 2005 Express on Windows, do it like this:
$pdo = new PDO ('mssql:host=localhost,1433;dbname=[redacted]', '[redacted]', '[redacted]');
localhost
localhost\SQLEXPRESS
localhost\1433
localhost:1433
will not work on Windows.
localhost,1433
does work.
YMMV on other OS's; try each.
Also make sure your TCP/IP Properties -> IP Addresses are correct under SQL Server Configuration Manager.

To enable the mssql PDO driver under windows you need to:
1) uncomment the "extension=php_pdo_mssql.dll" line from php.ini
2) copy ntwdblib.dll into the directory where you have installed php

鹏仔微信 15129739599 鹏仔QQ344225443 鹏仔前端 pjxi.com 共享博客 sharedbk.com