Mssql+php Database Operations Class

Mssql+php Database Operations Class

Class Dbqueryformssql {
/**
* Maximum number of records returned by the Select method
*/
Const MAX_ROW_NUM = 100000;

/**
* Data query result set object
* @var Object $dataSet
*/
Public $dataSet = NULL;

/**
* Data Source objects
* @var Object $ds
*/
Public $ds = NULL;

/**
* SQL statement for query
* @var String $sql
*/
Public $sql = ';

Public $transCnt = 0;

/**
* The mode of executing the query, the value is oci_commit_on_success or Oci_default
* @var String $excuteMode
*/
Public $executeMode = oci_commit_on_success;

/**
* Constructor
* @param Object $ds Database
* @param string $sql The SQL statement to initialize the query
*/
function __construct ($ds =null, $sql =null) {
if (! $ds) {
$this->error (dbexception::D b_unconnected, ' database also

Not connected. ');
} else {
$this->ds = $ds;
if ($sql) {
$this->open ($sql);
}
}
}

/**
* Free up the memory occupied
* @param object $dataSet The result set of the resource that needs to be freed
* @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);
}
/**
* Database encryption for $pass, returns the value after encryption
* @param string $pass strings to encrypt
* @return String
* @access Public
*/
Public Function Encodepassword ($pass) {
return MD5 ($PASS);
}

/**
* Get error message and error code
* @param integer $queryResult query results
* @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 (mysql_select_db ($this->ds->name ">! @mysql_select_db ($this->ds->name)) {
throw new Dbexception (' database does not exist ',

Dbexception::D b_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;
}

/**
* Error Information Processing
* @param string $errorId Error ID
* @param string $errorMessage error message
* @access protected
*/
protected function Error ($ERRORID, $errorMessage) {
throw new Dbexception ($errorMessage, $errorId);
}

/**
* Execute SQL statement
* @param string $sql SQL statement
* @return Object
* @param int $rowFrom start line number, starting from 1
* @param int $rowTo end line number, and a value of 0 indicates
* @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 (! @mysql_select_db ($this->ds->name)) {
throw new Dbexception (' database does not exist ',

Dbexception::D b_open_failed);
}*/
$dataSet = @mssql_query ($sql, $this->ds->connect);
echo $sql. ' <br/><br/><br/><br/> ';
if (! $dataSet && $error) {
$sqlError = $this->errorinfo ();
$errorMessage = ' Execute [<b><font color= ' #FF0000 ' > '.

$sql
. ' </font></b>] An error! <br> <font

color= #FF0000 > ['
. $sqlError [' Code ']. ']: '
. $sqlError [' message ']. ' </font> ';
$this->error (dbexception::D b_query_error,

$errorMessage);
  }
 
  if ($offset) {
    $offset = $offset -1;//var_dump ($dataSet); Echo ' abc ';
    $resultNum = mssql_num_rows ($dataSet);
   if ($resultNum < $offset) {
     @mssql_data_seek ($dataSet, $ RESULTNUM-1);
   } else {
     @mssql_data_seek ($dataSet, $offset);
   &NBSP}
  
  return $dataSet;
 
 
 /**
  * Execute SQL statements, The result set is saved to the property $dataset
  * @param string $sql SQL statement
  * @param int $rowFrom start line number, line number starting from 1
  * @param i NT $rowTo end line number with a value of 0 for
  * @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;
}

/**
* Split a row of field values into an array
* @param object $dataSet result set
* @param integer $resultType return type, OCI_ASSOC, Oci_num, or

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

/**
* Get the number of fields
* @param object $dataSet result set
* @return Integer
*/
Public Function GetFieldCount ($dataSet = NULL) {

Return Mssql_num_fields ($dataSet)? $dataSet: $this-

>dataset);
}

/**
* Get the next record. Returns the record number, or False if the end of the record is returned
* @return Integer
* @access Public
* @see Getprior ()
*/
Public function next () {
return $this->fetchrecord ();
}

/**
* Get the current database time, the format is: 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 ');
}

/**
* Take the data from the datasheet according to the SQL statement, take the value of the first record only,
* If there is only one field in the record, only the field value is returned.
* Return FALSE not found
*
* @param string $sql SQL statement
* @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) {//if using

MSSQL_ASSOC, and only one column, you need to know the column name of the first column.
$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;
  
 }
 
 /**
  * Fetch ID self increment
  *
  * @return int
  * @access public
  */&NBSP
 publ IC function Getinsertid () {
  return $this->getvalue (' SELECT @ @identity ');
 }
 
 /**
  * Fetch sequence
  * @param $seq sequence name
  * @return int
  * @access public
  */ &NBSP
 public function getseq ($seq = ') {
   $this->execute (' BEGIN TRANSACTION adodbseq '); br>    $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;
}
/**
* Whether the table exists, returns true
* @param string $tableName the name of the table to query
* @return BOOL
* @access Public
*/
Public Function tableisexists ($tableName) {
return false;
}

/**
* Start a transaction
* @access Public
*/
Public function begin () {
$this->transcnt + 1;
$this->execute (' BEGIN TRAN ');
return true;
}

/**
* Submit a transaction
* @access Public
*/
Public Function commit () {
if ($this->transcnt) {
$this->transcnt-= 1;
}
$this->execute (' COMMIT TRAN ');
return true;
}

/**
* Roll Back transaction
* @access Public
*/
Public Function rollback () {
if ($this->transcnt) {
$this->transcnt-= 1;
}
$this->execute (' ROLLBACK TRAN ');
return true;
}

/**
* Insert a record
* @param string $tableName table name
* @param array $fieldArray field
* @param string $whereForUnique uniqueness Condition
* @return int
* @access Public
*/
Public Function Insert ($tableName, $fieldArray, $whereForUnique =

NULL) {
if (! $tableName | |! $fieldArray | |!is_array ($fieldArray)) {
throw new Exception (' parameter $tableName or $fieldArray

The value is not legal! ');
}
if ($whereForUnique) {
$where = ' where '. $whereForUnique;
$isExisted = $this->getvalue (' SELECT COUNT (*) from '

. $tableName. $where);
if ($isExisted) {
throw new Dbexception (' record already exists! ',

Dbexception::D b_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);
}

/**
* Update a record
* @param string $tableName table name
* @param array $fieldArray field
* @param string $whereForUpdate query criteria
* @param string $whereForUnique uniqueness Condition
* @return int
* @access Public
*/
Public Function Update ($tableName, $fieldArray,

$whereForUpdate =null, $whereForUnique =null) {
if (! $tableName | |! $fieldArray | |!is_array ($fieldArray)) {
throw new Exception (' parameter $tableName or $fieldArray

The value is not legal! ');
}
if ($whereForUnique) {
$where = ' where '. $whereForUnique;
$isExisted = $this->getvalue (' SELECT COUNT (*) from '

. $tableName. $where);
if ($isExisted) {
throw new Dbexception (' record already exists! ',

Dbexception::D b_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;
}

/**
* Select a record
* @param string $sql SQL statement
* @param string $dataFormat Returns the data format, the value

There are "array", "HashMap", "Hashmap_str", "DataSet"
* @param int $rowFrom start line number, starting from 1
* @param int $rowTo end line number, and a value of 0 indicates
* @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 '://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 '://array, there is a bug, which needs to be indexed with column name! When only

There is a column sometimes
$result = Array ();
$isMultiField = ($this->getfieldcount ($dataSet) >

1);
$i = 0;
while ($data = $this->fetchrecord

($dataSet, MSSQL_ASSOC)) {
$idx = 0;
if (! $isMultiField) {//Only one column
if ($dataFormat = = MSSQL_ASSOC) {//With

MSSQL_ASSOC, and only one column, you need to know the column name of the first column.
$firstColumnInfo = Mssql_fetch_field

($dataSet, 0);
$idx = $firstColumnInfo-

>name;//column Name
}
}
$result [$i] = ($isMultiField)? $data:

$data [$IDX];
$i + +;
}
$this->close ($dataSet);
Break

Case ' HashMap '://Hash list
$result = Array ();
while ($data = $this->fetchrecord ($dataSet)) {
$result [$data [0]] = $data [1];
}
$this->close ($dataSet);
Break

Case ' hashmap_str '://Hash List string
$result = Array ();
while ($data = $this->fetchrecord ($dataSet,

Oci_num)) {
$result [] = $data [0]. '=' . $data [1];
}
$result = implode (' | ', $result);
$this->close ($dataSet);
Break

Default://dataset DataSet, when the return data format is a dataset, select

Method has the same functionality as the Execute method
$result = $dataSet;
}
return $result;
}

/**
* Returns the maximum value
* @param string $tableName table name
* @param string $idField field name
* @param string $where query criteria
* @return int
* @access Public
*/
Public Function Getmax ($tableName, $idField, $where = NULL) {
$where = ($where)? (' WHERE '. $where): ';
return $this->getvalue (' SELECT MAX ('. $idField. ') from '

. $tableName. $where);
}
}