ThinkPHP2.0 reads MSSQL prompt Incorrect syntax near the keyword 'AS' solution

ThinkPHP2.0 reads MSSQL prompt Incorrect syntax near the keyword 'AS' solution_PHP tutorial

WBOY

Release： 2016-07-13 10:26:10

Original

844 people have browsed it

The problem code is as follows:

<&#63;php 
class IndexAction extends Action{ 
  public function index(){ 
    /* 
    $Model = new Model(); 
    $test = $Model->query('select top 10 * from f_city'); 
    dump($test); 
    */ 
    $CityModel = M('city'); 
    $CityModel->find(); 
    dump($CityModel); 
  } 
} 
&#63;>

Copy after login

The situation is that the data can be read correctly using query, but it cannot be read using the M method, and the Incorrect syntax near the keyword 'AS'. error will be reported
The reason is that there is a problem with the query statement of the DbMssql.class.php driver.

Since the MSSQL driver of TP2.0 is valid for SQL 2005, but not for the 2000 version, the reason is that there is no ROW_NUMBER function in the 2000 version. This function is only available in 2005. It seems to provide convenience and efficiency for data paging.

I hope the official can add a 2000 driver to TP2.0. The current temporary solution is to modify ThinkPHPLibThinkDbDriverDbMssql.class.php and add '//' in front of protected $selectSql in line 25
And the

of line 326

public function parseLimit($limit) { 
      if(emptyempty($limit)) $limit=1; 
  $limit    =    explode(',',$limit); 
  if(count($limit)>1) 
    $limitStr    =    '(T1.ROW_NUMBER BETWEEN '.$limit[0].' + 1 AND '.$limit[0].' + '.$limit[1].')'; 
      else 
    $limitStr = '(T1.ROW_NUMBER BETWEEN 1 AND '.$limit[0].")"; 
  return $limitStr; 
}

Copy after login

changed to:

public function parseLimit($limit) { 
  return ''; 
}

Copy after login

After this change, it can basically meet the general SQL requirements, but LIMIT cannot be used, because the LIMIT method of MSSQL 2000 is based on top N
This is achieved;

If you find it troublesome, then combine it with the Adodb class library, which has much better support for MSSQL. My method to combine the Adodb class library is as follows:

First download the Adodb class library and extract it to the Vendor directory of ThinkPHP, and rename adodb.inc.php to adodb.php
Then create a CommonAction.class.php in the project's Lib with the content

<&#63;php 
class CommonAction extends Action { 
  public $dbsql; 
  function _initialize() { 
    Vendor('adodb5.adodb'); 
    $adodb = ADONewConnection(C('DB_TYPE')); 
    $adodb->Connect(C('DB_HOST'), C('DB_USER'), C('DB_PWD'), C('DB_NAME')); 
    $adodb->SetFetchMode(ADODB_FETCH_ASSOC); 
    $this->dbsql = $adodb; 
  } 
} 
&#63;>

Copy after login

This CommonAction.class.php file must be referenced in other files of the project to use ADODB, for example:

<&#63;php 
class IndexAction extends CommonAction { 
  public function index() { 
    $query = $this->dbsql->Execute('select * from xxx'); 
    while($rows = $query->FetchRow()) { 
        echo $rows['fields']; 
     } 
  } 
} 
&#63;>

Copy after login

In this way, you can use the Thinkphp module for simple data query and Adodb for paging data query. There is really no other way. This is a stupid method. I still hope that ThinkPHP can produce a MSSQL 2000 version. Perfect driver.