PHP - Manual: MS SQL Server (PDO)

2024-11-15

Microsoft SQL Server and Sybase Functions (PDO_DBLIB)

简介

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.

This extension is not available anymore on Windows.

On Windows, you should use SqlSrv, an alternative driver for MS SQL is available from Microsoft: » http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx .

If it is not possible to use SqlSrv, you can use the PDO_ODBC driver to connect to Microsoft SQL Server and Sybase databases, as the native Windows DB-LIB is ancient, thread un-safe and no longer supported by Microsoft.


Hi All, I'wrote a class to connect to MSSQL/Azure databases with Transaction support.

Hope this can help anyone!

<?php
/**
*    @author     Johan Kasselman <johankasselman@live.com>
*    @since         2015-09-28    V1
*
*/

class pdo_dblib_mssql{

    private $db;
       private $cTransID;
       private $childTrans = array();

    public function __construct($hostname, $port, $dbname, $username, $pwd){

        $this->hostname = $hostname;
        $this->port = $port;
        $this->dbname = $dbname;
        $this->username = $username;
        $this->pwd = $pwd;

        $this->connect();
        
    }

    public function beginTransaction(){

        $cAlphanum = "AaBbCc0Dd1EeF2fG3gH4hI5iJ6jK7kLlM8mN9nOoPpQqRrSsTtUuVvWwXxYyZz";
        $this->cTransID = "T".substr(str_shuffle($cAlphanum), 0, 7);

        array_unshift($this->childTrans, $this->cTransID);

        $stmt = $this->db->prepare("BEGIN TRAN [$this->cTransID];");
        return $stmt->execute();

    }

    public function rollBack(){
        
        while(count($this->childTrans) > 0){
            $cTmp = array_shift($this->childTrans);
            $stmt = $this->db->prepare("ROLLBACK TRAN [$cTmp];");
            $stmt->execute();
        }

        return $stmt;
    }

    public function commit(){

        while(count($this->childTrans) > 0){
            $cTmp = array_shift($this->childTrans);
            $stmt = $this->db->prepare("COMMIT TRAN [$cTmp];");
            $stmt->execute();
        }

        return  $stmt;
    }

    public function close(){
        $this->db = null;
    }

    public function connect(){

        try {
            $this->db = new PDO ("dblib:host=$this->hostname:$this->port;dbname=$this->dbname", "$this->username", "$this->pwd");

           

        } catch (PDOException $e) {
            $this->logsys .= "Failed to get DB handle: " . $e->getMessage() . "\n";
        }

    }

}

?>

down

fleduc dot perso at gmail dot com ¶

4 years 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

down

graham1 dot simpson at hsbcib dot com ¶

16 years ago


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:

<?php
  try {
    $hostname = "myhost";
    $port = 10060;
    $dbname = "tempdb";
    $username = "dbuser";
    $pw = "password";
    $dbh = new PDO ("dblib:host=$hostname:$port;dbname=$dbname","$username","$pw");
  } catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
  }
  $stmt = $dbh->prepare("select name from master..sysdatabases where name = db_name()");
  $stmt->execute();
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
  unset($dbh); unset($stmt);
?>

Hope this helps.

down

mat at typomania dot co dot uk ¶

4 years ago


Have lost over a day trying to work out why a connection to an sql server was failing, I thought I'd share the following:

This fails:
new PDO ( "dblib:dbname=[dbname]; host=[host];", [user], [password]);

This works:
new PDO ( "dblib:dbname = [dbname]; host=[host];", [user], [password]);

It's always the little things that are the hardest to find. In this case the missing spaces were triggering the error "SQLSTATE[HY000] General SQL Server error: Check messages from the SQL Server (severity 16)". The spaces don't cause problems when using the same syntax to connect to mysql, so I'm guessing it's a Microsoft thing. I only I could bill them for the time I've wasted...

down

support at converters dot ru ¶

16 years ago


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 <=5.1.2).
If your MSSQL connection use strings in OEM encoding (cp866 for russian charset) 

1. Run Microsoft Server/Client Network Utility on work PC and UNCHECK "DBLibrary options"/"Automatic ANSI to OEM conversion"

2. Restart Web server if needed.

down

Vic L ¶

5 years ago


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!

down

Fabian G ¶

3 years ago


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

down

angrom ¶

4 years ago


PDO dblib (pdo_dblib.so) was installed in ubuntu 16.04 with php 7.2
apt-get install php7.2-sybase
*You need FreeTDS driver

down

ian at helastel dot com ¶

9 years ago


For people with issues inserting UTF-8 / Unicode data using DBLIB, you can't do this natively - but you can workaround the problem by converting the data first.

e.g. inserting into a nvarchar column with collation Latin1_General_CI_AS

...
$res = $db->prepare($sql);
$res->bindValue(':value', iconv('UTF-8', 'ISO8859-1', $value);
...

down

meltir at meltir dot com ¶

11 years ago


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

down

-2

xwisdom at gmail dot com ¶

11 years ago


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


<?php

$return = null;

$param1 = 'value1';

$param2 = 'value2';


$ds = $db->prepare('{? = call ?,?}');

$ds->bindParam(1, $return, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT,4);

$ds->bindParam(2, $param1, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT,4);

$ds->bindParam(3, $param2, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT,4);


$ds->execute();

?>

down

-1

Gavin H ¶

6 years ago


When using dblib to connect to azure sql in linux, the connecting user must have at least access rights to the master database (no read/write necessary), as you cannot change the default database on a user in azure like you can with sql server. It would appear that even though you specify a database in the DSN, freetds is making a connection to the default database (master), then changing to the specified database. jTDS (used by tools like DBeaver) build a connection string that specifies the connection database, and thus doesn't need access to master. I also had to specify the server name in the user (e.g. - "username@servername" in PHP, though not in tsql (which also uses the freetds library).

down

-4

twilson at tridiumtech dot com ¶

12 years ago


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:
<?php
$mssql = new PDO('dblib:host=<host>;dbname=<dbname>','<user>','<password>');
?>

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

down

-4

dpassey at everettsd dot org ¶

13 years ago


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.

down

-6

Bryan ¶

13 years ago


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

down

-6

tris+php at tfconsulting dot com dot au ¶

13 years ago


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.

<?php
class mssql {
    private static $statement=null;

    private static $typemap=array(
        'ntext' => 'text',
        'bigint' => 'real',
        'decimal' => 'real',
        'float' => 'real',
        'numeric' => 'real'
    );
    
    public static function all_fields($table) {
        if(self::$statement==null) {
            $db=pdodb::instance(); // or however you get your global instance
            $query="SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?";
            self::$statement=$db->prepare($query);
        }
        
        self::$statement->execute(array($table));
        
        $fields=array();
        $need_cast=false;
        while($field=self::$statement->fetch()) {
            $field_quoted=self::quote_field($field['COLUMN_NAME']);
            if(isset(self::$typemap[$field['DATA_TYPE']])) {
                $fields[]='CAST('.$field_quoted.' AS '.self::$typemap[$field['DATA_TYPE']].') AS '.$field_quoted;
                $need_cast=true;
            } else $fields[]=$field_quoted;
        }
        return ($need_cast) ? implode(', ',$fields) : '*';
    }
    
    public static function quote_field($field) {
        $pos=strpos($field,' ');
        return ($pos===false) ? $field : '['.$field.']';
    }
}

$db=pdodb:instance(); // singleton PDO instance 
$stmt=$db->prepare('SELECT '.mssql::all_fields('My_Table').' FROM My_Table');
$stmt->execute();
...
?>

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'

down

-7

Steve H ¶

16 years ago


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.

down

-8

TomF ¶

10 years ago


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

add a note

官方地址：https://www.php.net/manual/en/ref.pdo-dblib.php

有任何技术问题请点击这里网站运营推广招聘

IT PHP 编程语言 Linux 开发编程科技 Elasticsearch HTML/CSS/XML 网络 JAVA NoSQL 数据库面试 C/C++ Golang Git 算法操作系统正则表达式 Redis 互联网 MySql JavaScript 运维软件国际架构设计 Mac OS TCP/IP Excel Vim Windows Socket Oracle VR MongoDB Python 运营 MemCache 商业硬件电子娱乐设计 nginx 摄影游戏 WordPress HTTP 团建数码电器

php7 安装fileinfo扩展 preg_split — 通过一个正则表达式分隔字符串 php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题 [PHP] inet_pton/inet_ntop IP地址转换函数 array_multisort() 多字段排序 PHPMailer设置utf8 PHPMailer字符集CharSet ADORecordSet对象 adodb手册 PHP 8.1 [鸟哥]PHP_INT_MIN 和 -9223372036854775808 opcache预加载 Composer的Packagist资源 mysql面试题 PHP7添加redis扩展 [鸟哥]PHP FFI详解 - 一种全新的PHP扩展方式 Packagist / Composer 中国全量镜像 PHP获取指定函数定义在哪个文件中及行号 PHP mkdir()写出来的权限与mode值不符合 php设置cookie为httponly防止xss攻击常用的php ADODB使用方法集锦

略微加速

PHP官方手册 - 互联网笔记

Microsoft SQL Server and Sybase Functions (PDO_DBLIB)

简介

目录

User Contributed Notes 18 notes