PHP 操作 SQL Server
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);
}
}
Comments
No Comments
Leave a Reply