php mssql mysql_mssql+php数据库操作类

mssql+操作类

class DbQueryForMssql {

/**

* select方法返回的最大记录数

*/

const MAX_ROW_NUM = 100000;

/**

* 数据查询结果集对象

* @var object $dataSet

*/

public $dataSet = NULL ;

/**

* 数据源对象

* @var object $ds

*/

public $ds = NULL ;

/**

* 查询的SQL语句

* @var string $sql

*/

public $sql = '' ;

public $transCnt = 0;

/**

* 执行查询的模式，值为 OCI_COMMIT_ON_SUCCESS 或 OCI_DEFAULT

* @var string $excuteMode

*/

public $executeMode = OCI_COMMIT_ON_SUCCESS ;

/**

* 构造函数

* @param object $ds 数据库

* @param string $sql 要初始化查询的SQL语句

*/

function __construct($ds=NULL , $sql=NULL) {

if (!$ds) {

$this->error(DbException::DB_UNCONNECTED, '数据库还

未连接。');

} else {

$this->ds = $ds;

if ($sql) {

$this->open($sql);

}

}

}

/**

* 释放所占用的内存

* @param object $dataSet 需要释放资源的结果集

* @access public

*/

public function close($dataSet=NULL) {

if ($dataSet) {

@mssql_free_statement($dataSet);

} else {

@mssql_free_statement($this->dataSet);

$this->eof = false ;

$this->recordCount = 0 ;

$this->recNo = -1 ;

}

}

function __destruct()

{

@mssql_free_result($this->dataSet);

@mssql_free_statement($this->dataSet);

@mssql_close($this->ds->connect);

}

/**

* 对$pass进行数据库加密,返回加密之后的值

* @param string $pass 要加密的字符串

* @return string

* @access public

*/

public function encodePassword($pass) {

return md5($pass);

}

/**

* 得到错误信息和错误代号

* @param integer $queryResult 查询结果

* @return array

* @access protected

*/

protected function errorInfo($queryResult = NULL) {

$result['message'] = mssql_get_last_message();

@mssql_select_db($this->ds->name,$this->ds->connect);

/*if (_select_db($this->ds->name">!@mysql_select_db($this->ds->name)) {

throw new DbException('数据库不存在',

DbException::DB_OPEN_FAILED);

}*/

$id = @mssql_query("select @@ERROR", $this->ds->connect);

if (!$id) {

return false;

}

$arr = mssql_fetch_array($id);

@mssql_free_result($id);

if (is_array($arr)) {

$result['code'] = $arr[0];

} else {

return $result['code'] = -1;

}

return $result;

}

/**

* 错误信息处理

* @param string $errorId 错误ID

* @param string $errorMessage 错误信息

* @access protected

*/

protected function error($errorId, $errorMessage) {

throw new DbException($errorMessage, $errorId);

}

/**

* 执行SQL语句

* @param string $sql SQL语句

* @return object

* @param int $rowFrom 启始行号，行号从1开始

* @param int $rowTo 结束行号，值为0表示

* @access public

* @see DbQuery::open

*/

public function execute($sql = '', $rowFrom = 0, $rowTo =

self::MAX_ROW_NUM, $error = true) {

//echo $this->ds->name;

if ($rowTo != self::MAX_ROW_NUM) {

$nrows = $rowTo - $rowFrom + 1;

}

$offset = $rowFrom;

if ($nrows > 0) {

$nn = $nrows + $offset - 1;

$sql = preg_replace('/(^s*selects+

(distinctrow|distinct)?)/i', '\1 top ' . $nn . ' ', $sql);

}

@mssql_select_db($this->ds->name,$this->ds->connect);

/*if ()) {

throw new DbException('数据库不存在',

DbException::DB_OPEN_FAILED);

}*/

$dataSet = @mssql_query($sql, $this->ds->connect);

//echo $sql .'

';

if (!$dataSet && $error) {

$sqlError = $this->errorInfo();

$errorMessage = '执行[' .

$sql

. ']出错！

color=#FF0000> ['

. $sqlError['code'] . ']: '

. $sqlError['message'] . '' ;

$this->error(DbException::DB_QUERY_ERROR,

$errorMessage);

}

if ($offset) {

$offset = $offset-1;//var_dump($dataSet);echo 'abc';

$resultNum = mssql_num_rows($dataSet);

if ($resultNum

@mssql_data_seek($dataSet, $resultNum-1);

} else {

@mssql_data_seek($dataSet, $offset);

}

}

return $dataSet;

}

/**

* 执行SQL语句，结果集保存到属性$dataSet中

* @param string $sql SQL语句

* @param int $rowFrom 启始行号，行号从1开始

* @param int $rowTo 结束行号，值为0表示

* @return object

* @access public

* @see DbQuery::execute

*/

public function open($sql='', $rowFrom = 0, $rowTo =

self::MAX_ROW_NUM) {

$this->dataSet = $this->execute($sql, $rowFrom, $rowTo);

$this->sql = $sql ;

return $this->dataSet;

}

/**

* 将一行的各字段值拆分到一个数组中

* @param object $dataSet 结果集

* @param integer $resultType 返回类型，OCI_ASSOC、OCI_NUM 或

OCI_BOTH

* @return array

*/

public function fetchRecord($dataSet=NULL, $resultType=MSSQL_BOTH) {

$result = @mssql_fetch_array(($dataSet) ? $dataSet : $this-

>dataSet, $resultType);

if (is_array($result)) {

foreach ($result as $key => $value) {

if (!is_numeric($key)) {

$result[strtolower($key)] = $value;

}

}

}

return $result;

}

/**

* 取得字段数量

* @param object $dataSet 结果集

* @return integer

*/

public function getFieldCount($dataSet = NULL) {

return mssql_num_fields(($dataSet) ? $dataSet : $this-

>dataSet);

}

/**

* 取得下一条记录。返回记录号，如果到了记录尾，则返回FALSE

* @return integer

* @access public

* @see getPrior()

*/

public function next() {

return $this->fetchRecord();

}

/**

* 得到当前数据库时间，格式为：yyyy-mm-dd hh:mm:ss

* @return string

* @access public

*/

public function getNow() {

return $this->getValue('SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD

HH24:MI:SS') dateOfNow FROM DUAL');

}

/**

* 根据SQL语句从数据表中取数据，只取第一条记录的值，

* 如果记录中只有一个字段，则只返回字段值。

* 未找到返回 FALSE

*

* @param string $sql SQL语句

* @return array

* @access public

*/

public function getValue($sql = '',$dataFormat=MSSQL_BOTH) {

$dataSet = $this->execute($sql, 1, 1);

if ($result = $this->fetchRecord($dataSet,$dataFormat)) {

$fieldCount = $this->getFieldCount($dataSet);

$idx = 0;

if($dataFormat == MSSQL_ASSOC){//如果使用

MSSQL_ASSOC,且只有一列时,则需要知道第一列的列名.

$firstColumnInfo = mssql_fetch_field

($dataSet ,0 );

$idx = $firstColumnInfo->name;//column name

}

$this->close($dataSet);//print_r($result);

return ($fieldCount<=1) ? $result[$idx] : $result;

} else {

return false ;

}

}

/**

* 取ID自递增值

*

* @return int

* @access public

*/

public function getInsertId() {

return $this->getValue('select @@identity');

}

/**

* 取序列

* @param $seq 序列名

* @return int

* @access public

*/

public function getSeq($seq = '') {

$this->execute('BEGIN TRANSACTION adodbseq');

$ok = $this->execute("update $seq with (tablock,holdlock)

set id = id + 1", 0, self::MAX_ROW_NUM, false);

if (!$ok) {

$this->execute("create table $seq (id float(53))");

$ok = $this->execute("insert into $seq with

(tablock,holdlock) values(1)", 0, self::MAX_ROW_NUM, false);

if (!$ok) {

$this->execute('ROLLBACK TRANSACTION

adodbseq');

return false;

}

$this->execute('COMMIT TRANSACTION adodbseq');

return 1;

}

$num = $this->getValue("select id from $seq");

$this->execute('COMMIT TRANSACTION adodbseq');

return $num;

}

/**

* 表是否存在，返回true

* @param string $tableName 要查询的表名

* @return bool

* @access public

*/

public function tableIsExists($tableName) {

return false;

}

/**

* 开始事务

* @access public

*/

public function begin() {

$this->transCnt += 1;

$this->execute('BEGIN TRAN');

return true;

}

/**

* 提交事务

* @access public

*/

public function commit() {

if ($this->transCnt) {

$this->transCnt -= 1;

}

$this->execute('COMMIT TRAN');

return true;

}

/**

* 回滚事务

* @access public

*/

public function rollback() {

if ($this->transCnt){

$this->transCnt -= 1;

}

$this->execute('ROLLBACK TRAN');

return true;

}

/**

* 插入一条记录

* @param string $tableName 表名

* @param array $fieldArray 字段数组

* @param string $whereForUnique 唯一性条件

* @return int

* @access public

*/

public function insert($tableName, $fieldArray, $whereForUnique =

NULL) {

if (!$tableName || !$fieldArray || !is_array($fieldArray)) {

throw new Exception('参数 $tableName 或 $fieldArray

的值不合法！');

}

if ($whereForUnique) {

$where = ' WHERE ' . $whereForUnique;

$isExisted = $this->getValue('SELECT COUNT(*) FROM '

. $tableName . $where);

if ($isExisted) {

throw new DbException('记录已经存在！',

DbException::DB_RECORD_IS_EXISTED);

}

}

$fieldNameList = array();

$fieldValueList = array();

foreach ($fieldArray as $fieldName => $fieldValue) {

if (!is_int($fieldName)) {

$fieldNameList[] = $fieldName;

$fieldValueList[] = ''' . $fieldValue .

''';

}

}

$fieldName = implode(',', $fieldNameList);

$fieldValue = implode(',', $fieldValueList);

$sql = 'INSERT INTO ' . $tableName . '('

. $fieldName . ') VALUES (' .

$fieldValue . ')';

//return $sql;

return $this->execute($sql);

}

/**

* 更新一条记录

* @param string $tableName 表名

* @param array $fieldArray 字段数组

* @param string $whereForUpdate 查询条件

* @param string $whereForUnique 唯一性条件

* @return int

* @access public

*/

public function update($tableName, $fieldArray,

$whereForUpdate=NULL, $whereForUnique=NULL) {

if (!$tableName || !$fieldArray || !is_array($fieldArray)) {

throw new Exception('参数 $tableName 或 $fieldArray

的值不合法！');

}

if ($whereForUnique) {

$where = ' WHERE ' . $whereForUnique;

$isExisted = $this->getValue('SELECT COUNT(*) FROM '

. $tableName . $where);

if ($isExisted) {

throw new DbException('记录已经存在！',

DbException::DB_RECORD_IS_EXISTED);

}

}

$fieldNameValueList = array();

foreach ($fieldArray as $fieldName => $fieldValue) {

if (!is_int($fieldName)) {

$fieldNameValueList[] = $fieldName . '='' .

$fieldValue . ''';

}

}

$fieldNameValue = implode(',', $fieldNameValueList);

if ($whereForUpdate) {

$whereForUpdate = ' WHERE ' . $whereForUpdate;

}

$sql = 'UPDATE ' . $tableName

. ' SET ' . $fieldNameValue .

$whereForUpdate;

return $this->execute($sql);

//return $sql;

}

/**

* 选择一条记录

* @param string $sql sql语句

* @param string $dataFormat 返回数据格式, 值

有"array","hashmap","hashmap_str","dataset"

* @param int $rowFrom 启始行号，行号从1开始

* @param int $rowTo 结束行号，值为0表示

* @result array

* @access public

*/

public function select($sql, $dataFormat = 'array', $rowFrom = 0,

$rowTo = self::MAX_ROW_NUM) {

$dataSet = $this->execute($sql, $rowFrom, $rowTo);

switch ($dataFormat) {

case 'array': //数组

$result = array();

$isMultiField = ($this->getFieldCount($dataSet) >

1);

$i = 0;

while ($data = $this->fetchRecord($dataSet)) {

$result[$i] = ($isMultiField) ? $data :

$data[0];

$i++;

}

$this->close($dataSet);

break;

case 'arrayassoc': //数组,有BUG,这里面需要用列名为索引!当只

有一列有时候

$result = array();

$isMultiField = ($this->getFieldCount($dataSet) >

1);

$i = 0;

while ($data = $this->fetchRecord

($dataSet,MSSQL_ASSOC)) {

$idx = 0;

if(!$isMultiField){//只有一列的话

if($dataFormat == MSSQL_ASSOC){//用

MSSQL_ASSOC,且只有一列时,则需要知道第一列的列名.

$firstColumnInfo = mssql_fetch_field

($dataSet ,0 );

$idx = $firstColumnInfo-

>name;//column name

}

}

$result[$i] = ($isMultiField) ? $data :

$data[$idx];

$i++;

}

$this->close($dataSet);

break;

case 'hashmap': //散列表

$result = array();

while ($data = $this->fetchRecord($dataSet)) {

$result[ $data[0] ] = $data[1];

}

$this->close($dataSet);

break;

case 'hashmap_str': //散列表字符串

$result = array();

while ($data = $this->fetchRecord($dataSet,

OCI_NUM)) {

$result[] = $data[0] . '=' . $data[1];

}

$result = implode('|', $result);

$this->close($dataSet);

break;

default: //dataset 数据集，当返回数据格式为数据集时，select

方法的功能与execute方法相同

$result = $dataSet;

}

return $result;

}

/**

* 返回最大值

* @param string $tableName 表名

* @param string $idField 字段名

* @param string $where 查询条件

* @return int

* @access public

*/

public function getMax($tableName, $idField, $where = NULL) {

$where = ($where) ? (' WHERE ' . $where) : '';

return $this->getValue('SELECT MAX(' . $idField . ') FROM '

. $tableName . $where);

}

}

本文原创发布php中文网，转载请注明出处，感谢您的尊重！