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 } 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);
}
}