2013/06/06

Codeigniter 2.1.3 連結 MS SQL

之前也是為了Codeigniter(以下簡稱CI) 連接SQL 2008找了網路上的解決方案
文章在這裡 CodeIgniter 連接 MS SQL 2008 並解決UTF-8問題 


那時用的還是CI 1.7.2,目前最新版本為2.1.3,也因為MS SQL很難搞所以完全不想升級
沒想到升成2.1.3,SQL SERVER還是很難搞= =
目前測試 Active Record可以用了!! 
當然前提SQL Server Native Client 跟 PHP 的 Driver 要先裝好

Microsoft Drivers 3.0 for PHP for SQL Server 
Microsoft® SQL Server® 2012 Native Client 安裝指示內有連結 (X86) (X64)
    
<<config 設定>>
application\config\database.php
$db['default']['dbdriver'] = 'sqlsrv';

<<修改核心>> 因為是修改到核心,會動到全部的DB操作,請小心測試

system\database\DB_Driver.php : Line 446
function simple_query($sql)
{
    if ( ! $this->conn_id)
    {
        $this->initialize();
    }

    return $this->_execute($sql);
}
改成
function simple_query($sql, $binds=null)
{
    if ( ! $this->conn_id)
    {
        $this->initialize();
    }

    return $this->_execute($sql, $binds);
}
※參數多一個binds參數對應sqlsrv_query()第二個參數

system\database\DB_Driver.php : Line 250
function query($sql, $binds = FALSE, $return_object = TRUE)
{
    ...
    // Run the Query
    if (FALSE === ($this->result_id = $this->simple_query($sql)))
    {
     ...
}
改成
function query($sql, $binds = NULL, $return_object = TRUE)
{
    ...
    // Run the Query
    if (FALSE === ($this->result_id = $this->simple_query($sql,$binds)))
    {
     ...
}
※傳入參數由FALSE改為NULL, 並多傳一個參數

system\database\DB_active_rec.php : Line 1163
function insert($table = '', $set = NULL)
{
 ...
 $sql = $this->_insert($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->ar_set), array_values($this->ar_set));

 $this->_reset_write();
 return $this->query($sql);
}
改成
function insert($table = '', $set = NULL)
{
 ...
 //1020606 modify for sqlsrv will return array( 'sql'=>(string)sql statement, 'params' => (array)params );
 $tmp_return = $this->_insert($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->ar_set), array_values($this->ar_set));
 if( is_array($tmp_return) ){
  $sql = $tmp_return['sql'];
  $params = $tmp_return['params'];
 }else{
  $sql = $tmp_return; //other driver still use plain text sql statement
  $params = null;
 }
 $this->_reset_write();  
 return $this->query($sql,$params);
}
※判別使用的DB方法,如為mssql則分成sql與資料

system\database\drivers\sqlsrv\sqlsrv_driver.php : Line 87
function db_pconnect()
{
    $this->db_connect(TRUE);
}
改成
function db_pconnect()
{
    return $this->db_connect(TRUE);
}
※N年BUG 沒有return

system\database\drivers\sqlsrv\sqlsrv_driver.php : Line 494
function _insert($table, $keys, $values)
{ 
 return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
}
改成
function _insert($table, $keys, $values)
{ 
 //convert values to array(value) for SQL Server query params
 //and sql value statement change to ?, ? ,?... format
 $params = array();
 $sql_values = array();
 foreach( $values as $value ){
  $params[] = array(str_replace("'", '', $value));     //DB_Driver escape() 會多加''
  $sql_values[] = '?';
 }
 $sql = "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $sql_values).")";
 return array('sql'=>$sql, 'params'=>$params);
}
※整段重改,將新增的資料轉為陣列,並將SQL語法VALUES值改為?

system\database\drivers\sqlsrv\sqlsrv_driver.php : Line 146
function _execute($sql)
{ 
 $sql = $this->_prep_query($sql);
 return sqlsrv_query($this->conn_id, $sql, null, array(
  'Scrollable'    => SQLSRV_CURSOR_STATIC,
  'SendStreamParamsAtExec' => true
 ));
}
改成
function _execute($sql, $binds=NULL)
{ 
 $sql = $this->_prep_query($sql);
 return sqlsrv_query($this->conn_id, $sql, $binds);
}
※null改成$binds, 其他連線參數值改成sqlsrv_query預設值

system\database\drivers\sqlsrv\sqlsrv_driver.php : 最底下新增兩個方法
已覆蓋 system\database\DB_driver的同名方法
//原本的方法會做一堆檢查跟處理,用MSSQL免
function compile_binds($sql, $binds)
{
 return $sql; //just don't do anything
}

system\database\drivers\sqlsrv\sqlsrv_result.php : Line 37
function num_rows()
{
  return @sqlsrv_num_rows($this->result_id);
}
改成
function num_rows()
{
  $num_rows = isset($num_rows) ? $num_rows : 0;
  return !$num_rows = sqlsrv_num_rows($this->result_id) ? 0 : $num_rows;
  //return @sqlsrv_num_rows($this->result_id);
}
※sqlsrv_query的cursor預設值會無法使用sqlsrv_num_rows直接取得列數,result會抓不到東西, 所以改用另一種方式取得num_rows

收工!

沒有留言:

張貼留言