[PHP] 在Windows環境中與SQL Server(MsSQL)的串接設定

此文章同步刊登於我的部落格

環境建置

一、 安裝 ODBC Driver for SQL Server

1. 到https://docs.microsoft.com/zh-tw/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15下載驅動
2. 開啟下載下來的msodbcsql.msi，並一路安裝完成
   

二、 引用dll檔案

1. 將以下檔案放到/php/ext/底下
2. • php_pdo_sqlsrv_74_nts_x64.dll
   • php_pdo_sqlsrv_74_ts_x64.dll
   • php_sqlsrv_74_nts_x64.dll
   • php_sqlsrv_74_ts_x64.dll
2. 編輯php.ini，加入以下內容

   extension=sqlsrv_74_ts_x64
   extension=sqlsrv_74_nts_x64
   extension=pdo_sqlsrv_74_ts_x64
   extension=pdo_sqlsrv_74_nts_x64INI

3. 儲存php.ini後，重新啟動Apache

PHP串接

一、連線設定(CodeIgniter 3.1.16)

$config = array(
    'hostname' => $host . ',' . $port,
    'username' => $user,
    'password' => $password,
    'database' => $dbname,
    'dbdriver' => 'sqlsrv',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => FALSE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => $this->char_set,
    'dbcollat' => $this->dbcollat,
    'swap_pre' => '',
    'encrypt'  => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => $this->save_queries
);

try {
    $db = @$this->load->database($config, true);
    if(empty($db->conn_id)) {
        // 連線失敗
        echo $db->error()['message'];
    } else {
        // 連線成功
    }
} catch (Exception $e) {
    // 出現例外
    echo $e->getMessage();
}

環境

• Windows 10
• PHP 7.4.9
• CodeIgniter 3.1.16
• Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production