[edit] Last updated: Fri, 10 Feb 2012

Microsoft SQL Server および Sybase 関数 (PDO_DBLIB)

導入

警告

この拡張モジュールは、 実験的 なものです。この拡張モジュールの動作・関数名・その他ドキュメントに書かれている事項は、予告なく、将来的な PHP のリリースにおいて変更される可能性があります。このモジュールは自己責任で使用してください。

PDO_DBLIB は PHP Data Objects (PDO) インターフェイスを実装したドライバで、 PHP から Microsoft SQL Server および Sybase データベースに対して FreeTDS ライブラリを使用したアクセスが可能となります。

この拡張モジュールは、Windows 版では PHP 5.3 以降使うことができません。

Windows では、Microsoft が » http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx で公開しているもうひとつの MS SQL 用ドライバである SqlSrv を使わなければなりません。

SqlSrv を使うことができない場合は、 PDO_ODBC ドライバを使えば Microsoft SQL Server や Sybase データベースに接続することができます。 Windows ネイティブの DB-LIB は既に古くなっており、スレッドセーフでもなく Microsoft ももうサポートしていません。


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

xwisdom at gmail dot com 09-Dec-2010 08:42


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();


?>

Matej 12-Oct-2010 03:04


How to use mssql pdo on PHP 5.3 on IIS 7 (Windows Server 2008)



1. Installation of MSSQL driver for PHP:

Download Microsoft Drivers for PHP for SQL Server Microsoft Drivers for PHP for SQL Server - SQLSRV20.EXE - http://www.microsoft.com/downloads/en/details.aspx

?FamilyID=80e44913-24b4-4113-8807-caae6cf2ca05)

Copy php_sqlsrv_53_ts_vc9.dll and php_pdo_sqlsrv_53_ts_vc9.dll to php extension directory



2. configure php.ini to work with MSSQL PDO

extension=php_sqlsrv_53_ts_vc9.dll

extension=php_pdo_sqlsrv_53_ts_vc9.dll



3. configure  configuration

            'connectionString' => 'sqlsrv:server=hostname_or_ip;Database=database_name;',

twilson at tridiumtech dot com 20-Oct-2009 12:05


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

dpassey at everettsd dot org 29-May-2009 07:35


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.

tris+php at tfconsulting dot com dot au 24-Mar-2009 01:03


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'

Bryan 14-Jan-2009 08:21


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

mrshelly at hotmail dot com 28-Oct-2008 12:39


to connect MS SQL2005(DE) sample code:

notice: 

if u used MSSQL 2005 EXPRESS, must set the instence name 'SQLEXPRESS'

just like 



$hostname='127.0.0.1\SQLEXPRESS';



<?php

  try {

    $hostname = "host";            //host

    $dbname = "dbname";            //db name

    $username = "user";            // username like 'sa'

    $pw = "pass";                // password for the user



    $dbh = new PDO ("mssql:host=$hostname;dbname=$dbname","$username","$pw");

  } catch (PDOException $e) {

    echo "Failed to get DB handle: " . $e->getMessage() . "\n";

    exit;

  }

  $stmt = $dbh->prepare("SELECT * FROM table");

  $stmt->execute();

  while ($row = $stmt->fetch()) {

    print_r($row);

  }

  unset($dbh); unset($stmt);

?>

arnolem at free dot fr 11-Dec-2007 10:45


I think that PDO_DBLIB is now stable :

You can download the last source at http://pecl.php.net/package/PDO_DBLIB

Steve H 31-May-2006 03:24


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.

support at converters dot ru 24-Jan-2006 10:48


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.

graham1 dot simpson at hsbcib dot com 07-Sep-2005 06:19


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.

add a note

Microsoft SQL Server および Sybase 関数 (PDO_DBLIB)

導入

目次