David's blog

adodb 实在太庞大了，况且也不需要那么多的功能，就自己写了一个 PHP 操作 SQL Server 的类。带有强烈的 discuz 风格。

// sample
$db = new dbstuff;
$db->connect($dbserver, $dbuser, $dbpwd, $dbname, $codepage);

class dbstuff {
  ///////////////////
  // Attributes
  ///////////////////

  var $conn;
  var $exectimeout = 180;
  var $codepage;
  // Connection status: 1=>opened, 0=>closed
  var $state = 0;
  var $querynum = 0;
  var $recordcount = 0;
  // save excuted sql
  var $sqls = array(); 

  ///////////////////
  // Methods
  ///////////////////

  // Open a new connection to the MSSQL server
  function connect($dbserver, $dbuser, $dbpwd, $dbname, $codepage = 936) {
    $conn = & $this->conn;
    $this->codepage = $codepage;
    $conn = @new COM("ADODB.Connection", NULL, $codepage);
    if(!$conn) $this->halt('Can not connect to MSSQL Server');
    $conn->CommandTimeout = $this->exectimeout;
    @$conn->Open("Provider=SQLOLEDB; Data Source=$dbserver; UID=$dbuser; PWD=$dbpwd;Initial Catalog=$dbname");
    if(!$conn->State){
      $this->halt('Can not connect to Database Server');
    }
    $this->state = $conn->State;
  }

  // Open a new connection to another MSSQL server
  function rconnect($dbserver, $dbuser, $dbpwd, $dbname, $codepage) {
    $this->close();
    $this->connect($dbserver, $dbuser, $dbpwd, $dbname, $codepage);
  }

  // Select ms sql database
  function select_db($dbname) {
    return $this->query("USE [$dbname]");
  }

  // Performs a query on the database
  function query($sql){
    $conn = & $this->conn;
    $query = @$conn->Execute($sql);
    if ($conn->Errors->Count > 0) $this->halt('Database Query Error', $sql);
    $this->sqls[] = $sql;
    if(!$query) return FALSE;
    /* NO RECORDSET RETURNED */
    /* INSERT, DELETE, NO RETURN(rs, v) PROC AND SO ON */
    if ($query->State == 0) {
      $this->recordcount = 0;
      $this->querynum++;
      return TRUE;
    }
    $this->recordcount = -1;
    $this->querynum++;
    return $query;
  }

  // Fetch a result row as an associative array
  /* FETCH ONE RESULT ROW ONLY BY DEFAULT*/
  function fetch_array(&$rs, $freeresult = TRUE) {
    if($rs->BOF && $rs->EOF){
      $this->free_result($rs);
      return NULL;
    } else {
      if(!$rs->EOF){
        $arr = array();
        $colums = $rs->Fields->Count;
        for($i=0; $i < $colums; $i++){
          $fld = $rs->Fields($i);
          $arr[strtolower($fld->Name)] = $this->datetime_convert($fld->Value, $fld->Type);
        }
        if($freeresult){
          $this->free_result($rs);
        }else{
          $rs->MoveNext();
        }
        return $arr;
      } else {
        $this->free_result($rs);
        return NULL;
      }
    }
  }

  // Fetch a result row as enumerated array
  /* FETCH ONE RESULT ROW ONLY BY DEFAULT*/
  function fetch_row(&$rs, $freeresult = TRUE) {
    if($rs->BOF && $rs->EOF){
      $this->free_result($rs);
      return NULL;
    } else {
      if(!$rs->EOF){
        $arr = array();
        $colums = $rs->Fields->Count;
        for($i=0; $i < $colums; $i++){
          $fld = $rs->Fields($i);
          $arr[$i] = $this->datetime_convert($fld->Value, $fld->Type);
        }
        if($freeresult){
          $this->free_result($rs);
        }else{
          $rs->MoveNext();
        }
        return $arr;
      } else {
        $this->free_result($rs);
        return NULL;
      }
    }
  }

  // Fetch all result rows as associative array 
  function fetch_all(&$rs) {
    if($rs->BOF && $rs->EOF){
      return null;
    } else {
      $arr = array();
      $colums = $rs->Fields->Count;
      $rows = 0;
      while(!$rs->EOF){
        for($i=0; $i < $colums; $i++){
          $fld = $rs->Fields($i);
          $arr[$rows][strtolower($fld->Name)] = $this->datetime_convert($fld->Value, $fld->Type);
        }
        $rows++;
        $rs->MoveNext();
      }
      $this->free_result($rs);
      return $arr;
    }
  }

  // Open a recordset
  function recordset($sql, $cursortype = 3, $locktype = 1){
    $conn = & $this->conn;
    $rs = @new COM("ADODB.RecordSet", NULL, $this->codepage);
    $rs->CursorLocation = 3;
    @$rs->Open($sql, $conn, $cursortype, $locktype);
    if ($conn->Errors->Count > 0) $this->halt('Database Query Error', $sql);
    $this->querynum++;
    $this->sqls[] = $sql;
    return $rs;
  }
  
  // Fetch all result rows in one page as associative array 
  function fetch_page($sql, $pagesize = 20, $curpage = 1, $cursortype = 3, $locktype = 1){
    $conn = & $this->conn;
    $rs = @new COM("ADODB.RecordSet", NULL, $this->codepage);
    $rs->CursorLocation = 3;
    $rs->CacheSize = $pagesize < 100 ? $pagesize : 100;
    @$rs->Open($sql, $conn, $cursortype, $locktype);
    if ($conn->Errors->Count > 0) $this->halt('Database Query Error', $sql);
    $this->sqls[] = $sql;
    $this->querynum++;
    if($rs->BOF && $rs->EOF){
      $this->recordcount = 0;
      return NULL;
    } else {
      $this->recordcount = $rs->RecordCount;
      if(!$pagesize) $pagesize = 20;
      $rs->PageSize = $pagesize;
      if(!$curpage)
        $curpage = 1;
      elseif($curpage > $rs->PageCount)
        $curpage = $rs->PageCount;
      $rs->AbsolutePage = $curpage;
      $arr = array();
      $colums = $rs->Fields->Count;
      for($i=0; $i< $pagesize; $i++){
        for($j=0; $j < $colums; $j++){
          $fld = $rs->Fields($j);
          $arr[$i][strtolower($fld->Name)] = $this->datetime_convert($fld->Value, $fld->Type);
        }
        $rs->MoveNext();
        if($rs->EOF) break;
      }
      $this->free_result($rs);
      return $arr;
    }
  }

  // Fetch one field value of a result row
  /* SHOULD BE CALL BEFORE fetch_array, fetch_row, fetch_all */
  function result(&$rs, $row = 0, $mixfield = 0, $freeresult = TRUE) {
    if($rs->BOF && $rs->EOF){
      $this->free_result($rs);
      return NULL;
    }
    if($row) $rs->Move($row);
    $value = $this->datetime_convert($rs->fields[$mixfield]->Value, $rs->fields[$mixfield]->Type);
    if($freeresult) $this->free_result($rs);
    return $value;
  }

  // Check record existence
  function exists($table, $cond='') {
    $query = $this->query("SELECT COUNT(*) FROM [$table]".($cond?" WHERE $cond":NULL));
    return $this->result($query);
  }

  // Convert timestamp to mssql datetime style as YYYY-MM-DD hh:mm:ss
  function datetime_convert($fldv, $fldt){
    if($fldt == 135 && $fldv) {
      return date("Y-m-d H:i:s", $fldv);
    }
    return $fldv;
  }
  
  // Get the number of rows in a result
  function num_rows() {
    $count = $this->recordcount;
    /* RS MAY RETURNED BY EXECUTE, NOT USE OPEN RECORDSET METHOD */
    /* BUT THIS MAY NOT WORK WELL 
    if($count == -1) $count = $this->affected_rows();*/
    return $count;
  }

  // Get the number of fields in a result 
  function num_fields(&$rs) {
    return $rs->Fields->Count;
  }

  // Get the auto generated id used in the last query
  function insert_id(){
    $query = $this->query("SELECT @@IDENTITY");
    return $this->result($query);
  }

  // Get the number of affected rows in a previous SQL operation (UPDATE, DELETE)
  /* IT SHOULD BE CALLED IMMEDIATELY AFTER A SQL OPERATION */
  /* IF 'SET NOCOUNT ON' HAD BEEN SET BEFORE THE SQL OPERATION, IT WILL NOT WORK */
  function affected_rows(){
    $query = $this->query("SELECT @@ROWCOUNT");
    return $this->result($query);
  }
  
  // Get the mssql object id, use this function to check object exists
  function object_id($obj) {
    $query = $this->query("SELECT OBJECT_ID('$obj')");
    return $this->result($query);
  }

  // Get the mssql version
  function version() {
    $query = $this->query("SELECT @@VERSION");
    return $this->result($query);
  }

  // Release resource
  function free_result(&$rs) {
    if($rs->State == 1){
      $rs->Close();
      $rs->Release();
    }
    $rs = null;
  }

  // Close db connection
  function close() {
    if($this->state == 1){
      $this->conn->Close();
      $this->conn->Release();
      $this->conn = null;
    }
    $this->state = 0;
  }
  
  // Get error details 
  /* SHOULD BE CALLED BEFORE errno() */
  function error(){
    $errors = $this->conn->Errors;
    if ($errors->Count == 0) return '';
    $err = $errors->Item($errors->Count-1);
    return $err->Description;
  }
  
  // Get error number
  function errno(){
    $errors = $this->conn->Errors;
    if ($errors->Count == 0) return 0;
    $err = $errors->Item($errors->Count-1);
    return $err->NativeError;
  }

  // halt script processing, show error message
  function halt($message = '', $sql = '') {
    $errno = $this->errno();
    if ($errno == 5701) return;
    $error = $this->error();
    $script = '
PHP: '.$GLOBALS['_SERVER']['PHP_SELF'];
    $sql = $sql ? '
SQL: '.nl2br(htmlspecialchars($sql)) : '';
    if ($GLOBALS['debug'] && $GLOBALS['userid'] && !defined("AJAX")){
      if ($GLOBALS['adminid']) {
        $output = $message.$script.$sql.'
ERR: '.$error.'
NUM: '.$errno;
      } else {
        $output = $message.$script;
      }
    } else {
      $output = $message;
    }
    $output = ''.$output.'';
    die($output);
  }
}