PHPのお勉強！

PHP TOP
PHP 8.4.2 Released!

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

    はじめに

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

    この拡張モジュールは、Windows 版では使えません。

    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 ももうサポートしていません。

    目次

    • PDO_DBLIB DSN — Microsoft SQL Server および Sybase データベースに接続する

    Found A Problem?

    Learn How To Improve This Page • Submit a Pull Request • Report a Bug
    ＋add a note

    User Contributed Notes 7 notes

    up
    47
    Johankasselman at live dot com
    9 years ago
    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";
    }

    }

    }

    ?>
    up
    4
    Fabian G
    6 years ago
    Instead of using Mssql or DBLib extension you should use the official extensions from Microsoft from here: https://github.com/Microsoft/msphpsql
    up
    10
    graham1 dot simpson at hsbcib dot com
    19 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.
    up
    3
    support at converters dot ru
    18 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.
    up
    2
    Vic L
    7 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!
    up
    1
    fleduc dot perso at gmail dot com
    7 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
    up
    1
    ian at helastel dot com
    12 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);
    ...
    ＋add a note
    To Top