<?PHP
/**
*
* MSSQL interface module
*
* @copyright CYap_Mssql.php is part of Yap project {@link http://www.andrioli.com/en/yap.html} and it is LGPL
* @author Andrioli Darvin <darvin (inside) andrioli (dot) com>
* @version $Header: d:\cvs/classistd/yap/CYap_Mssql.php,v 1.8 2008/03/10 12:15:46 darvin Exp $
*/
/*
* +-------------------------------------------------------------------------+
* | Yap |
* +-------------------------------------------------------------------------+
* | Copyright (c) 2003-2008 Andrioli Darvin |
* | Email <darvin (inside) andrioli (dot) com> |
* | Web http://www.andrioli.com/en/yap.html |
* | Download http://www.phpclasses.org/browse.html/package/1391.html |
* | |
* +-------------------------------------------------------------------------+
* | This library is free software; you can redistribute it and/or modify |
* | it under the terms of the GNU Lesser General Public License as |
* | published by the Free Software Foundation; either version 2 of the |
* | License, or (at your option) any later version. |
* | |
* | This library is distributed in the hope that it will be useful, but |
* | WITHOUT ANY WARRANTY; without even the implied warranty of |
* | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
* | Lesser General Public License for more details. |
* | |
* | You should have received a copy of the GNU Lesser General Public |
* | License along with this library; if not, write to the Free Software |
* | Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA |
* +-------------------------------------------------------------------------+
*/
/**
* @package CYap
*/
class CYap_MsSQL extends CYapDB {
/**
* Tracks if the last query is a query with limit. Used to mimic the mysql's limit
* statement
* @var boolean
*/
var $UseLimit;
/**
* If UseLimit set to true, this var counts down how many rows left to
* the limit end
* @var integer
*/
var $LimitToEnd;
/**
* If UseLimit set to true, this var 'remember' the start limit
* @var integer
*/
var $LimitStart;
/**
* If UseLimit set to true, this var 'remember' the end limit
* @var integer
*/
var $LimitEnd;
/**
* Memorize the last row retruned for each result.
* Used to track the columns type and number for query
* @var array
*/
var $LastRow;
/**
*
* @see SelectDb()
*/
var $SaveUser;
/**
*
* @see SelectDb()
*/
var $SavePasswd;
function CYap_MsSQL()
{
CYapDB::CYapDB();
$this->UseLimit=false;
$this->LimitStart=0;
$this->LastRow=array();
$this->CurrentRow=array();
}
/**
* Open the database connection
* @param string $server database server
* @param string $User username
* @param string $Password User password
* @return bool true on success, false on failure
*/
function OpenDb($DBSystem,$DBUser="",$DBPasswd="")
{
if($this->Db_Open)
{
$this->txtError='Database already open';
return(false);
}
$this->SaveUser=$DBUser;
$this->SavePasswd=$DBPasswd;
if(($this->DbConn=mssql_connect($DBSystem,$DBUser,$DBPasswd))===FALSE)
{
// Errore di select del db
$this->txtError="Error during connection to the server ".$DBSystem;
return(false);
}
$this->Db_Open=true;
return(true);
}
/**
* database selection. Always true, the ODBC function don't allow to
* select any database
* @param string $DBname database name
* @return bool true on success, false on failure
*/
function SelectDb($DBname)
{
if(!$this->Db_Open)
{
$this->txtError='No database open';
return(false);
}
if(!mssql_select_db($DBname,$this->DbConn))
{
// Errore di select del db
$this->txtError="I cannot select the db ".$DBname;
return(false);
}
return(true);
}
/**
* Execute the given query
* @param string $sql sql statement
* @return mixed the result id, false on failure
*/
function DbExecSql($sql)
{
$this->UseLimit=FALSE;
$this->LimitStart=0;
if(!$this->Db_Open)
{
$this->txtError='No database open';
return(false);
}
if(($this->DbResult=mssql_query($sql,$this->DbConn))===false)
{
// Errore di select del db
$this->txtError="Error into sql statement. Sql string: ".$sql.".Err:". mssql_get_last_message();
return(false);
}
$this->CurrentRow[$this->DbResult]=0;
$this->LastQuery[$this->DbResult]=$sql;
return($this->DbResult);
}
/**
* Perform a select statement using range to limits the
* rows to return
* @param string $select select statement
* @param integer $start no. rows to start
* @param integer $many how many rows sholud be returned
* @return bool true on success, false on failure
*/
function QueryLimit($select,$start=0,$many=-1)
{
// If I got an error, exit. The error text is set by DbExecSql
// and the caller should know what to do.
if(($res=$this->DbExecSql($select))===FALSE)
return($res);
$this->UseLimit=TRUE;
// yap start limi is zero based, the odbc start from 1
$this->LimitStart=$start;
if($many!=-1)
{
$this->LimitToEnd=$many;
}
else
$this->LimitToEnd=999999; // I hope that no one perform a select that returns 1000000 rows!
$this->LimitEnd=$this->LimitToEnd;
$this->CurrentRow[$res]=$this->LimitStart;
return($res);
}
/**
* Retrieve the number of rows retrieved by the given result
* @param integer $result
* @return integer
*/
function DbGetNumRow($result)
{
$nRows=mssql_num_rows($result);
if($this->UseLimit)
{
$newRows=$nRows-$this->LimitStart;
$nRows=($newRows>$this->LimitEnd)?$this->LimitEnd:$newRows;
}
// var_dump($nRows);
return($nRows);
}
/**
* Retrieve the number of fields retrieved by the given result
* @param integer $result
* @return integer
*/
function DbGetNumFields($result)
{
return(mssql_num_fields($result));
}
/**
* Get column information from a result and return as an object
*
* @param integer $result
* @param integer $FieldNo
* @return object DbMeta
*/
function DbFetchField($result,$FieldNo)
{
$meta=new DbMeta();
$meta->name=mssql_field_name($result,$FieldNo);
$type=mssql_field_type($result,$FieldNo);
$meta->max_length=mssql_field_length($result,$FieldNo);
switch($type)
{
case 'LONGBINARY': // Ole object
$meta->type='IGNORE'; // Ignore this field
$meta->numeric=0;
break;
case 'DATETIME': // Date format
case 'LONGCHAR': // memo fields
case 'CHAR' :
case 'VARCHAR' :
$meta->type=$type;
$meta->numeric=0;
break;
case 'BIT': // Boolean
$meta->type='BOOL';
$meta->numeric=0;
break;
case 'BYTE' :
$meta->type='BIT';
$meta->numeric=0;
break;
case 'CURRENCY' :
$meta->type='DOUBLE';
$meta->numeric=1;
break;
case 'SMALLINT' :
case 'INTEGER' :
case 'DOUBLE' :
$meta->type=$type;
$meta->numeric=1;
break;
}
$meta->table='';
return($meta);
}
/**
* Get the flags associated with the specified field in a result
* @param resource $result
* @param integer $FieldNo
* @return string
*/
function DbFieldFlags($result,$FieldNo)
{
$type=mssql_field_type($result,$FieldNo);
if($type=='COUNTER')
$txt='auto_increment';
else
$txt='';
return($txt);
}
/**
* Free the given result
* @param resource $result
* @return bool true on success, false on failure
*/
function DbFreeResult($result)
{
if(!($es=mssql_free_result($result)))
$this->txtError=mssql_get_last_message();
$this->LastRow[$result]=array();
return($es);
}
/**
* Return the current row as associative array
* @param resource $result
* @return array row data or false on failure
*/
function DbGetValue($result)
{
if(!$this->Db_Open)
{
$this->txtError='No database open';
return(false);
}
// var_dump($this->UseLimit);
// var_dump($this->LimitToEnd);
if($this->UseLimit&&!$this->LimitToEnd)
return(FALSE);
if($this->UseLimit)
$this->LimitToEnd--;
// var_dump($this->CurrentRow[$result]);
if(@mssql_data_seek($result,$this->CurrentRow[$result]))
{
$this->LastRow[$result]=mssql_fetch_array($result);
$this->CurrentRow[$result]++;
}
else
$this->LastRow[$result]=false;
return($this->LastRow[$result]);
}
/**
* Close the connection to the database
* @return bool true
*/
function Db_Close()
{
if(!$this->Db_Open)
{
$this->txtError='No database open';
return(false);
}
mssql_close($this->DbConn);
$this->Db_Open=false;
return(true);
}
/**
* From an array of values, make the sql for adding the row
*
* @param array array of values. The key is the field name
* @param array type of each fields
* @param string name of the key field
* @access public
*/
function DbaddSql($row,$fieldsType,$KeyField)
{
$Count=0;
$fName='';
$fValue='';
foreach($row as $Key => $Value)
{
// I Can't update the row key
if($Key!=$KeyField)
{
if($Count)
{
$fName.=', ';
$fValue.=', ';
}
$fName.='['.$Key.']';
$apice=$fieldsType[$Key]['apice'];
$fValue.=$apice.$this->DBEscape($Value).$apice;
$Count++;
}
}
$OutText='('.$fName.') VALUES ('.$fValue.') ';
return($OutText);
}
/**
* From an array of values, made the sql for modify the row
*
* @param array array of values. The key is the field name
* @param array type of each fields
* @access public
*/
function DbmodifySql($row,$fieldsType)
{
$OutText=' set ';
$c=0;
foreach($row as $Key => $Value)
{
if($c) $OutText.=', ';
$apice=$fieldsType[$Key]['apice'];
$OutText.='['.$Key.'] = '.$apice.$this->DBEscape($Value).$apice.' ';
$c++;
}
return($OutText);
}
/**
* Build the condition string for the $FieldName using the pattern matching
* syntax
* @access public
* @param string
* @param string
* @return string
*/
function DbRegexCond($FieldName,$pattern)
{
return($FieldName.' like \'%'.$pattern.'%\'');
}
/**
* Escape the string using the db functions, if provided
* It don't apply the escape string if magic_quotes_gpc is enabled
*
* @param string $value sql statement
* @access public
* @return string the escaped string
*/
function DbEscape($value)
{
$quote=(bool)ini_get('magic_quotes_gpc');
if(!$quote)
$ret=str_replace("'","''",$value);
else
$ret=str_replace("\\","'",$value);
return($ret);
}
/**
* Build the condition statement for $FieldName using the 'between' expression
*
* @param string $FieldName
* @param mixed $ValueFrom
* @param mixed $ValueTo
* @param string $ValueType
* @return string
*/
function DbBetweenCond($FieldName,$ValueFrom,$ValueTo,$ValueType)
{
if($ValueType=='DB_NUMBER')
return($FieldName.' between '.$ValueFrom.' and '.$ValueTo);
else
return($FieldName.' between \''.$ValueFrom.'\' and \''.$ValueTo.'\'');
}
}
?>