Location: PHPKode > scripts > RC4PHP class > rc4php-class/rc4php_db_mssql.php
<?PHP
/**
*
* RC4PHP :  Raul's Classes For PHP <http://rc4php.sourceforge.net/>
* Copyright (c) 2006, Raul IONESCU
*                     Bucharest, ROMANIA
*
* Licensed under The MIT License
* Redistributions of files must retain the above copyright notice.
*
* @package      RC4PHP
* @copyright 	Copyright (c) 2006, Raul IONESCU.
* @author 	Raul IONESCU <hide@address.com>
* @license      http://www.opensource.org/licenses/mit-license.php The MIT License
* @version 	0.6.3 (development)
* @category 	MSSQL implementation
* @access 	public
*
* PHP versions 5.1 or greater
*/
//////////////////////////////////////////////////////////////////
require_once('rc4php_autoload.php');
//////////////////////////////////////////////////////////////////
ini_set('mssql.connect_timeout','36000');
ini_set('mssql.timeout','36000');
ini_set('mssql.textsize','-1');
ini_set('mssql.textlimit','-1');
ini_set('mssql.textsize','-1');
ini_set('mssql.batchsize','0');
ini_set('mssql.datetimeconvert','1');
ini_set('mssql.max_procs','256');
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
/**
* class RC4PHP_DB_MSSQL
*
* It's the RC4PHP_DB_MSSQL SQL class.
* 
* GET PROPERTIES
* ---------------------------------------------------------------------------
* (Note: get properties  marked with [], can also be accessed as array keys.)
* ---------------------------------------------------------------------------
* type				= object's type: 'MSSQL'	        []
* version			= server version			[]
* database			= database's name			[]
* server 			= server's name				[]
* user				= user's name				[]
* password			= user's password			[]
* connection		        = sql connection resource
* databases			= databases from server (array)
* tables			= database's tables (array)
* views				= current database's views (array)
* 
* SET PROPERTIES
* ---------------------------------------------------------------------------
* (Note: set properties  marked with [], can also be accessed as array keys.)
* ---------------------------------------------------------------------------
* database			= database's name			[]
* server			= server's name				[]
* user				= user's name				[]
* password			= user's passwor			[]
* 
* PUBLIC METHODS
* ----------------------------
* escapeString($string);                    -> returns escaped string of $string.
* &escapeBinaryString(&$binaryString);      -> returns escaped string for binary data.
* &unescapeSQLBinaryString(&$binaryString); -> returns binary string from SQL server.
* isConnected();                            -> returns state of connection: boolean true for connected else it returns false.
* connect();                                -> try to connects to SQL server.
* disconnect();                             -> disconnect from SQL server.
* query($sql);                              -> send a query to the SQL server; it returns an SQL Result object.
* getDatabases();                           -> get array of databases
* getTables($database='');                  -> get array of tables from $database; if $database is empty then it will use current database.
* getViews($database='');                   -> get array of views from $database; if $database is empty then it will use current database.
* DATE2MIDAS($date);                        -> convert date into MIDAS date.
* MIDAS2DATE($MIDASdate);                   -> convert MIDAS date into date.
* 
* @access public
*/
class RC4PHP_DB_MSSQL extends RC4PHP_DB_SQL
{
protected $RC4PHP_DB_QUOTE_PATTERN='[%s]';
//////////////////////////////////////////////////////////////////
/**
* Constructing RC4PHP_DB_MSSQL object
*
* Initialize object's properties.
* It doesn't establish connection to the SQL server. The connection
* it's established only when it's needed.
*
* @param string $dbSQLDBName
* @param string $dbSQLServer
* @param string $dbSQLUser
* @param string $dbSQLPassword
* @return RC4PHP_DB_MSSQL
*/
public function __construct($dbSQLDBName='',$dbSQLServer='localhost',$dbSQLUser='',$dbSQLPassword='')
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
self::_checkPHPExtension('mssql');
parent::__construct($this->quoteName($dbSQLDBName),$dbSQLServer,$dbSQLUser,$dbSQLPassword);
}
//////////////////////////////////////////////////////////////////
/**
* Destroing RC4PHP_DB_MSSQL object
*
* When object it's destroyed, the database connection will be closed.
*
* @return void
*/
public function __destruct()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
parent::__destruct();
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns a string of SQL server's type: 'MSSQL'.
* 
* @access protected
* @return string
*/
protected function _getType()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
return 'MSSQL';
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns SQL server's version. 
*
* @access protected
* @return string | boolean
*/
protected function _getVersion()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
$this->connect();
if($rs=@mssql_query('SELECT @@VERSION',$this->dbSQLConnection))
	{
	 $SQLversion=@mssql_result($rs,0,0);
	 @mssql_free_result($rs);
	 return $SQLversion;
	}
else throw new Exception('Can not determine server version.',-1);
}
//////////////////////////////////////////////////////////////////
/**
*
*
* The method returns escaped version of string $string.
*
* @access public
* @param string $string
* @return string
*/
public function escapeString($string)
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
static $search = array('\'',';');
static $replace = array('\'\'');

return str_ireplace($search,$replace,str_ireplace('\'\'','\'',strip_tags($string)));
}
//////////////////////////////////////////////////////////////////
/**
*
*
* The method returns escaped version of binary string $binaryString.
*
* @access public
* @param string $binartString
* @return string
*/
public function &escapeBinaryString(&$binaryString)
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
$escapedBinaryString='0x'.bin2hex($binaryString); 
return $escapedBinaryString;
}
//////////////////////////////////////////////////////////////////
/**
*
*
* The method returns unescaped version of binary string $binaryString
* returned by the SQL server.
*
* @access public
* @param string $binaryString
* @return string
*/
public function &unescapeSQLBinaryString(&$binaryString)
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
return $binaryString;
}
//////////////////////////////////////////////////////////////////
/**
*
*
* The method returned boolean TRUE if connection to SQL server is 
* established, otherwise it returns FALSE.
*
* @access public
* @return bool
*/
public function isConnected()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
static $isValidConnection=false;
if($isValidConnection==false) $isValidConnection=@is_resource($this->dbSQLConnection);
return $isValidConnection;
}
//////////////////////////////////////////////////////////////////
/**
*
*
* If the connection wasn't made yet, then establish connection to SQL server.
*
* @access public
* @return bool
*/
public function connect()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
if($this->isConnected()==false)
	{
	 if(($this->dbSQLConnection=@mssql_connect($this->dbSQLServer,$this->dbSQLUser,$this->dbSQLPassword))==false) throw new RC4PHP_DB_MSSQL_Exception($this->dbSQLConnection,"SQL server connect failure.",-1);
	 if((@mssql_select_db($this->dbSQLDBName,$this->dbSQLConnection))==false) throw new RC4PHP_DB_MSSQL_Exception($this->dbSQLConnection);
	}
}
//////////////////////////////////////////////////////////////////
/**
*
*
* If the connection was made, then disconnects object from SQL server.
*
* @access public
* @return bool
*/
public function disconnect()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
if($this->isConnected())
	{
	 $this->dbSQLConnection=false;	
	 return @mssql_close($this->dbSQLConnection);
	} 
return false;
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Sends a query to be executed by the SQL server and returns an result
* object. If the SQL instruction it's not generating a result that would
* be used , then it's mandatory to execute recorset's method execute().
*
* @access public
* @param string $sql
* @return RC4PHP_DB_MSSQL_Result
*/
public function query($sql)
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
try{ return new RC4PHP_DB_MSSQL_Result($this,$sql);	}
catch(Exception $e){ $this->disconnect(); throw $e; }
}
//////////////////////////////////////////////////////////////////
/**
*
*
* @access public
* Returns an array with databases from SQL server.
* @return array
*/
public function getDatabases()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{ return $this->_get('SELECT DISTINCT [name] FROM [master]..[sysdatabases] ORDER BY [name]'); }
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns an array with tables from specified database. 
* If database it's not set, then it's used current database.
*
* @access public
* @param string $database
* @return array
*/
public function getTables($database='')
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
if(@empty($database)) $database=$this->dbSQLDBName;
return $this->_get("SELECT DISTINCT [name] FROM $database..[sysobjects] WHERE ([xtype]='U') ORDER BY [name]");
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns an array with views from specified database. 
* If database it's not set, then it's used current database.
*
* @access public
* @param string $database
* @return array
*/
public function getViews($database='')
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
if(@empty($database)) $database=$this->dbSQLDBName;
return $this->_get("SELECT DISTINCT [name] FROM [$database]..[sysobjects] WHERE ([xtype]='V') ORDER BY [name]");
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Creates the SQL function DATE2MIDAS(ISODATE date). 
* If database it's not set, then it's used current database.
*
* @access public
* @param string $database
* @return boolean
*/
public function createSQLFunctionDATE2MIDAS()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
$this->connect();
return @mssql_query('CREATE FUNCTION [dbo].[DATE2MIDAS] (@DATE AS smalldatetime)  
				  RETURNS int AS  
				  BEGIN 
					RETURN DATEDIFF(dd,\'1971-12-31\',@DATE);
				  END',$this->dbSQLConnection);
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Creates the SQL function MIDAS2DATE(MIDASDATE int). 
* If database it's not set, then it's used current database.
*
* @access public
* @param string $database
* @return boolean
*/
public function createSQLFunctionMIDAS2DATE()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{
$this->connect();
return @mssql_query('CREATE FUNCTION [dbo].[MIDAS2DATE](@MIDASDate AS int)  
					 RETURNS smalldatetime AS  
					 BEGIN 
						RETURN DATEADD(dd,@MIDASDate,\'1971-12-31\');
					 END',$this->dbSQLConnection);
}
//////////////////////////////////////////////////////////////////
}
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
/**
* class RC4PHP_DB_MSSQL_Result
*
* It's the base result class who will be extended by other SQL result classes.
* 
* ------------------------------------------------------------------------------
* (Note: the result's rows and columns can also be accessed as array elements
* 		Example: $result[0][0] 		-> first columnn from 1st row
* 			 $result[1]['Foo']	-> column 'Foo' from 2nd row)
* ------------------------------------------------------------------------------
* 
* GET PROPERTIES
* -------------------------------------------------------------
* type			= object's type: 'MSSQLResult'
* maxPageSize		= limit maximum page size
* pageSize		= number of rows for page
* pages			= number of pages
* page			= current page number (starting from 1)
* row			= current row number (from current page, if pageSize is set) (starting from 0)
* rows			= number of rows from current page or total number of rows if pagination is not used
* totalRows             = total number of rows, no matter pagination is set or not
* fields		= number of fields
* affectedRows		= number of affected rows
* lastInsertedID	= last inserted ID
* executionTime         = execution time for the SQL query in msec
* 
* SET PROPERTIES
* -------------------------------------------------------------
* maxPageSize           = limit maximum page size
* pageSize              = number of rows for page
* page                  = current page number (starting from 1)
* row                   = current row number (from current page, if pageSize is set) (starting from 0)
* 
* PUBLIC METHODS
* -------------------------------------------------------------
* execute();			    	-> execute the sql querry.
* fetchField($fieldOffset=NULL);
* fetchRow();			    	-> fetch row from current row position as an array. each result column is stored in an array offset, starting at offset 0.
* fetchRowAssociative();    	        -> fetch row from current row position as an associative array of column's names.
* fetchRowArray();		    	-> fetch row from current row position as an array. the array offsets can be colum's name or column's number (starting from 0).
* fetchObject();		    	-> fetch row from current row position as an object with properties that correspond to the fetched row.
* fetchAllRows();		    	-> fetch all rows from current row position as a bidimensional array. first dimension is row number (starting from 0) and the 2nd dimension is column (starting from 0).
* fetchAllRowsAssociative();	        -> fetch all rows from current row position as a bidimensional array. first dimension is row number (starting from 0) and the 2nd dimension is column's name.
* fetchAllRowsArray();			-> fetch all rows from current row position as a bidimensional array. first dimension is row number (starting from 0) and the 2nd dimension can be colum's name or column's number (starting from 0).
* fetchAllObjectsArray();		-> fetch all rows from current row position as an  array of objects. the array offsets are row numbers (starting from 0).
* nextResult();
* freeResult();                         -> free all memory associated with the result.
* 
* @access public
*/
class RC4PHP_DB_MSSQL_Result extends RC4PHP_DB_SQL_Result
{
protected $dbSQLResultCurrentPageStartingRow=0;
protected $dbSQLOriginalCommand='';
protected $AdvanceToThePageResult=false;
//////////////////////////////////////////////////////////////////
/**
* Constructing RC4PHP_DB_MSSQL_Result object
*
* Initialize object's properties.
* It doesn't query SQL server until execute() method or a property 
* of the result object (such as fetchRow() method or rows property,
* for example) it's called.
*
* @param RC4PHP_DB_MSSQL $RC4PHP_DB_MSSQLObject
* @param string $dbSQLCommand
* @return RC4PHP_DB_MSSQL_Result
*/
public function __construct(RC4PHP_DB_MSSQL &$RC4PHP_DB_MSSQLObject,&$dbSQLCommand)
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
$this->dbSQLOriginalCommand=preg_replace('/\s\s+/',' ',trim($dbSQLCommand));
parent::__construct($RC4PHP_DB_MSSQLObject,$this->dbSQLOriginalCommand);
}
//////////////////////////////////////////////////////////////////
/**
* Destroing RC4PHP_DB_MSSQL_Result object
*
* When object it's destroyed, the associated memory with the result
* it's freed.
*
* @return void
*/
public function __destruct()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
parent::__destruct();
}
//////////////////////////////////////////////////////////////////
/**
* Clonning RC4PHP_DB_MSSQL_Result object
*
* When object it's cloned, the internal $dbSQLObject it's clonned
* and the result it's cleared. When needed, a new connection to
* the SQL server will be established and a new result will be 
* retrived.
*
* @return RC4PHP_DB_MSSQLResult
*/
public function __clone()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
parent::__clone();
$this->dbSQLResultCurrentPageStartingRow=$that->dbSQLResultCurrentPageStartingRow;
$this->dbSQLOriginalCommand=$that->dbSQLOriginalCommand;
$this->AdvanceToThePageResult=false;
}
//////////////////////////////////////////////////////////////////
public function __sleep()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
$sleepArray=parent::__sleep();
$sleepArray[]='dbSQLResultCurrentPageStartingRow';
$sleepArray[]='dbSQLOriginalCommand';
$sleepArray[]='AdvanceToThePageResult';
return $sleepArray;
}
//////////////////////////////////////////////////////////////////
/**
* Getting RC4PHP_DB_MSSQL_Result properties
*
* type			= object's type: 'MySQLResult'
* pageSize		= number of rows for page
* pages			= number of pages
* page			= current page number (starting from 1)
* row			= current row number (from current page, if pageSize is set) (starting from 0)
* rows			= number of rows from current page or total number of rows if pagination is not used
* totalRows             = total number of rows, no matter pagination is set or not
* fields		= number of fields
* affectedRows          = number of affected rows
* lastInsertedID        = last inserted ID
* executionTime         = execution time for the SQL query in msec
*
* @param string $varname
* @return mixed
*/
public function __get($varname)
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
$varname=@strtoupper($varname);
switch($varname)
	{
	 case 'TYPE': return 'MSSQL.Result';
	 default:     return parent::__get($varname);
	}
}
//////////////////////////////////////////////////////////////////
/**
* Setting RC4PHP_DB_MSSQL_Result properties
*
* pageSize	                = number of rows for page
* page		                = current page number (starting from 1)
* row		                = current row number (from current page, if pageSize is set) (starting from 0)
*
* @param string $varname
* @param string $value
*/
public function __set($varname,$value)
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try
	{
	 $varname=@strtoupper($varname);
	 $value=@intval($value);
	 parent::__set($varname,$value);
	 switch($varname)
		{
			case 'PAGE':
				$this->dbSQLResultCurrentPageStartingRow=($this->dbSQLResultCurrentPage==1)?(0):(($this->dbSQLResultCurrentPage*$this->dbSQLResultPageSize)-$this->dbSQLResultPageSize);
				//change sql limit
				$this->dbSQLCommand=$this->_getSQLQueryString();
				return true;		

			case 'PAGESIZE':
				$this->dbSQLCommand=$this->_getSQLQueryString();
				$this->dbSQLResultTotalRows=0;
				return true;
				
			case 'ROW':
				$this->_checkConnectionAndResult();
                $value=($value<0)?(0):($value>($this->rows-1)?($this->rows-1):($value));
                $this->dbSQLResultCurrentRow=$value;
				return @mssql_data_seek($this->dbSQLResult,$this->dbSQLResultCurrentRow);
		}
	}
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }		
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns a string of SQL server's type: 'MSSQL.Result'.
* 
* @access protected
* @return string
*/
protected function _getType()
///class RC4PHP_DB_MSSQL//////////////////////////////////////////
{ return 'MSSQL.Result'; }
//////////////////////////////////////////////////////////////////
/**
*
*
* It checks if the connection was already established and the SQL command
* was sended to the server. If not, try to connect to the SQL server and
* sends command to server.
*
* @access protected
* @return void
*/
protected function _checkConnectionAndResult($acceptRC4PHP_DB_SQL_ResultAsBooleanTrue=false)
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
$this->_checkConnection();
if(($this->dbSQLResult==false) || ($this->dbSQLResultPreviousPage!=$this->dbSQLResultCurrentPage))
	{
	 $startTime=microtime(true);
	 $this->dbSQLResultPreviousPage=$this->dbSQLResultCurrentPage;
	 $this->freeResult(); 
	 if(($this->dbSQLResult=@mssql_query($this->dbSQLCommand,$this->_getConnection()))===false){ throw new RC4PHP_DB_MSSQL_Exception($this->_getConnection()); }
	 if(($this->AdvanceToThePageResult==false)&&($this->dbSQLResultPageSize)){ $this->AdvanceToThePageResult=true; @mssql_next_result($this->dbSQLResult); } 
 	 $this->dbSQLQueryTime=microtime(true)-$startTime;
 	 //at this point I have to recheck if it's a resource result
 	 $this->_isResourceResult(true);
	}	
//at this point	dbSQLResult can be only a resource or boolean 'true'
if($acceptRC4PHP_DB_SQL_ResultAsBooleanTrue) { if(($this->dbSQLResult!==true) && ($this->_isResourceResult()==false)) { throw new RC4PHP_DB_SQL_Exception('Invalid result.',-1); } }	
else { if($this->_isResourceResult()==false) { throw new RC4PHP_DB_SQL_Exception('Invalid result.',-1); } }	
}
//////////////////////////////////////////////////////////////////
protected function _isResultGeneratingCommand(&$sql)
///class RC4PHP_DB_SQL_Result///////////////////////////////////////////
{ return !(((stripos($sql,'SELECT ')===false) && (stripos($sql,'EXEC ')===false)&& (stripos($sql,'EXECUTE ')===false)) || stripos($sql,'INTO ') || stripos($sql,'COMPUTE ') || stripos($sql,'COMPUTE BY') || stripos($sql,'FOR BROWSE') || stripos($sql,'INTO')); }
//////////////////////////////////////////////////////////////////
/*
UNUSED OBSOLETE FUNCTION
protected function _addTOP100PERCENT2SQLcommand($sql)
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{ return str_ireplace(array('SELECT ','SELECT_ALL','SELECT_DISTINCT'),array('SELECT TOP 100 PERCENT ','SELECT ALL TOP 100 PERCENT','SELECT DISTINCT TOP 100 PERCENT'),str_ireplace(array('SELECT ALL','SELECT DISTINCT',';'),array('SELECT_ALL ','SELECT_DISTINCT '),trim($sql))); }
*/
//////////////////////////////////////////////////////////////////
protected function _getSQLQueryString()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
/*
static $MSSQLversion=0;
*/
if(!$this->dbSQLResultPageSize) return $this->dbSQLCommand;
/*
if($MSSQLversion==0)
	{
	 $this->_checkConnection();
	 if($rs=@mssql_query('DECLARE @versionStart int, @versionLength int; SET @versionStart=CHARINDEX (' - ', @@VERSION)+3; SET @versionLength=CHARINDEX ('.', @@VERSION)-@versionStart;  SELECT CAST(SUBSTRING(@@VERSION,@versionStart,@versionLength) AS int) AS MSSQLversion;',$this->_getConnection()))
	 	{
		 $MSSQLversion=@intval(@mssql_result($rs,0,0));
		 @mssql_free_result($rs);
		}
	 else throw new RC4PHP_DB_MSSQL_Exception($this->_getConnection());
	}
*/	
/*
if($MSSQLversion<9)//8 = MSSQL 2000, 9 = MSSQL 2005
	{
*/
//remainig: SELECT ALL SELECT DISTINCT
	 $spName='RC4PHPpagination_'.md5(strtolower($this->dbSQLOriginalCommand));
	 $SQL=$this->dbSQLObject->escapeString($this->dbSQLOriginalCommand);
	 $sqlcmd="
		DECLARE @offset AS bigint, @limit AS bigint,@SQL AS varchar(8000),@SQLCOMMAND AS varchar(8000),@sp_name AS varchar(128);
		SELECT @offset=".$this->dbSQLResultCurrentPageStartingRow.", @limit=".$this->dbSQLResultPageSize.", @sp_name='".$spName."', @SQL='".$SQL."';".
		/* NOTE: THE SQL COMMAND MUST BE ESCAPED BEFORE */
		/* IF UPPER(LEFT(@SQL,4))='EXEC'
			BEGIN
				/* SQL COMMAND IT'S A PROCEDURE CALL */ 
				/*SET @SQLCOMMAND=
				'
				DECLARE @cursor int;
				EXEC sp_cursoropen @cursor=@cursor output,@stmt=N' + CHAR(39)+REPLACE(@SQL,CHAR(39),CHAR(39)+CHAR(39))+CHAR(39)+',@scrollopt=8,@ccopt=8193;
				EXEC sp_cursorfetch @cursor=@cursor,@fetchtype=16,@rownum='+CAST((@offset+1) AS varchar(512))+',@nrows='+CAST(@limit AS varchar(512))+';
				EXEC sp_cursorclose @cursor=@cursor;';
			END
		ELSE
			BEGIN */
				/* SQL COMMAND IT'S NOT A PROCEDURE CALL */
				"SET @SQLCOMMAND=
				'
				IF NOT EXISTS(SELECT * FROM sysobjects WHERE ((type=''P'') and (name='+CHAR(39)+@sp_name+CHAR(39)+')))
					EXEC(''CREATE PROCEDURE '+@sp_name+' AS \r\n'+REPLACE(@SQL,CHAR(39),CHAR(39)+CHAR(39))+''');
				IF EXISTS(SELECT * FROM sysobjects WHERE ((type=''P'') and (name='+CHAR(39)+@sp_name+CHAR(39)+')))
					BEGIN
						DECLARE @cursor int;
						EXEC sp_cursoropen @cursor=@cursor output,@stmt=N' + CHAR(39) + 'EXEC ' + @sp_name  + CHAR(39)+',@scrollopt=8,@ccopt=8193;
						EXEC sp_cursorfetch @cursor=@cursor,@fetchtype=16,@rownum='+CAST((@offset+1) AS varchar(512))+',@nrows='+CAST(@limit AS varchar(512))+';
						EXEC sp_cursorclose @cursor=@cursor;
					END
				ELSE RAISERROR (''Error occured while creating SP \"'+@sp_name+'\".'',18, 1);';
			/* END */
		EXEC(@SQLCOMMAND);";
	 $this->AdvanceToThePageResult=false;
	 //print $sqlcmd;
	 return $sqlcmd;
/*
	}
else
	{//MSSQL is 2005 or greater
	 
	
	}	
*/	
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns the number of total rows not just the number of rows from
* current page for result.
*
* @access protected
* @return integer
*/
protected function _getTotalRows()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
if($this->dbSQLResultTotalRows==0)
	{
	 if($this->dbSQLResult===true) { $this->dbSQLResultTotalRows=0; return 0; }
	 if($this->dbSQLResultPageSize==0) { $this->dbSQLResultTotalRows=$this->rows; }
	 else
	 	{
		 $this->_checkConnection();
		 if($rs=@mssql_query('SELECT COUNT(*) FROM ('.$this->dbSQLOriginalCommand.') AS __rc4php__',$this->_getConnection())){ $this->dbSQLResultTotalRows=@mssql_result($rs,0,0); @mssql_free_result($rs); }
		 else 
		 	{ /* in this last case COUNT also failed and I am forced to run directly the SQL query in order to get number of rows */
			 if($rs=@mssql_query($this->dbSQLOriginalCommand,$this->_getConnection())){ $this->dbSQLResultTotalRows=@mssql_num_rows($rs); @mssql_free_result($rs); }
			 else { return 0; }
			}
		}	
	}
return $this->dbSQLResultTotalRows;		
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns the number of rows from result.
*
* @access protected
* @return integer | boolean
*/
protected function _getRows()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try {
     if($this->dbSQLResultRows==0){ $this->_checkConnectionAndResult(); $this->dbSQLResultRows=@mssql_num_rows($this->dbSQLResult); } 
     return $this->dbSQLResultRows;
    }
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }	
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns the number of affected rows by the SQL command.
*
* @access protected
* @return integer | boolean
*/
protected function _getAffectedRows()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try {
     $this->_checkConnectionAndResult(true);
     return @mssql_rows_affected($this->_getConnection());
    }
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }	
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns the last inserted ID.
*
* @access protected
* @return integer | boolean
*/
protected function _getLastInsertedID()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try {
     $this->_checkConnectionAndResult(true);
     if($rs=@mssql_query('SELECT SCOPE_IDENTITY() AS LastInsertedID',$this->_getConnection()))
        {
         $lastInsertedID=@mssql_result($rs,0,0);
         @mssql_free_result($rs);
         return $lastInsertedID;
	}
     else throw new RC4PHP_DB_MSSQL_Exception($this->_getConnection());
    }
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) $this->dbSQLObject->disconnect(); throw $e; }	
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Returns the number of fields(columns) from result.
*
* @access protected
* @return integer | boolean
*/
protected function _getFields()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try {
     $this->_checkConnectionAndResult();
     return @mssql_num_fields($this->dbSQLResult);
    }
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }	
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Send the SQL command to the SQL server.
*
* @access public
* @return boolean
*/
public function execute()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try { 
     $this->_checkConnectionAndResult(true); 
     return true;
    } 
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }	
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Fetch field or returns FALSE.
*
* @access public
* @return mixed | boolean
*/
public function fetchField($fieldOffset=NULL)
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try {
     $this->_checkConnectionAndResult();
     return (($fieldOffset!==NULL)?(@mssql_fetch_field($this->dbSQLResult,$fieldOffset)):(@mssql_fetch_field($this->dbSQLResult)));
    }
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }	
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Fetch and returns one row as an array. The method returns FALSE 
* when no more rows are available.
*
* @access public
* @return array | boolean
*/
public function fetchRow()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try { 
     $this->_checkConnectionAndResult();
     ++$this->dbSQLResultCurrentRow;
     return @mssql_fetch_row($this->dbSQLResult);	
    }
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }	 
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Fetch and returns one row as an associative array. The method 
* returns FALSE when no more rows are available.
*
* @access public
* @return array | boolean
*/
public function fetchRowAssociative()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try {
     $this->_checkConnectionAndResult();
     ++$this->dbSQLResultCurrentRow;
     return @mssql_fetch_assoc($this->dbSQLResult);	
    }
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }	 
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Fetch and returns one row as an associative and numerical array.
* The method returns FALSE when no more rows are available.
*
* @access public
* @return array | boolean
*/
public function fetchRowArray()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try {
     $this->_checkConnectionAndResult();
     ++$this->dbSQLResultCurrentRow;
     return @mssql_fetch_array($this->dbSQLResult);	
    }
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }	 
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Fetch and returns one row as object.
* The method returns FALSE when no more rows are available.
*
* @access public
* @return object | boolean
*/
public function fetchObject()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
try {
     $this->_checkConnectionAndResult();
     ++$this->dbSQLResultCurrentRow;
     return @mssql_fetch_object($this->dbSQLResult);	
    }
catch(Exception $e){ $this->freeResult(); if($this->dbSQLObject instanceof RC4PHP_DB_SQL) { $this->dbSQLObject->disconnect(); } throw $e; }	 
}
//////////////////////////////////////////////////////////////////
/**
*
*
* When sending more than one SQL statement to the server or executing
* a stored procedure with multiple results, it will cause the server 
* to return multiple result sets. This function will test for additional
* results available form the server. If an additional result set exists 
* it will free the existing result set and prepare to fetch the rows from
* the new result set. The function will return TRUE if an additional result
* set was available or FALSE otherwise. 
*
* @access public
* @return boolean
*/
public function nextResult()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
$this->dbSQLResultRows=0;
$this->dbSQLResultCurrentRow=0;
$this->dbSQLResultCurrentPage=1;
$this->dbSQLResultPreviousPage=1;
$this->dbSQLResultCurrentPageStartingRow=0;
return @mssql_next_result($this->dbSQLResult);
}
//////////////////////////////////////////////////////////////////
/**
*
*
* Frees the associated result memory.
*
* @access public
* @return boolean
*/
public function freeResult()
///class RC4PHP_DB_MSSQL_Result///////////////////////////////////
{
if($this->_isResourceResult()) @mssql_free_result($this->dbSQLResult);
$this->dbSQLResult=false;	
}
//////////////////////////////////////////////////////////////////
}
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
/**
* class RC4PHP_DB_MSSQL_DATA_DICTIONARY
*
* It's a MSSQL dictionary class.
* 
* GET PROPERTIES
* ---------------------------------------------------------------------------
* DATA TYPES
* ==========
* boolean
* binary
* char	
* unicodeChar
* varChar
* unicodeVarChar
* float			
* integer
* numeric
* date
* 
* PUBLIC METHODS
* ---------------------------------------------------------------------------
* DATA TYPES
* ==========
* char($size=self::DB_DEFAULT_CHAR_SIZE)
* unicodeChar($size=self::DB_DEFAULT_CHAR_SIZE);
* varchar($size=self::DB_DEFAULT_VARCHAR_SIZE);
* unicodeVarChar($size=self::DB_DEFAULT_VARCHAR_SIZE);
* float($size=self::DB_DEFAULT_FLOAT_SIZE);
* integer($size=self::DB_DEFAULT_INTEGER_SIZE);
* numeric($fieldDefinition=array(self::DB_DEFAULT_NUMERIC_PRECISION,self::DB_DEFAULT_NUMERIC_SCALE),$scale=self::DB_DEFAULT_NUMERIC_SCALE);
* 
* @access public
*/
class RC4PHP_DB_MSSQL_DATA_DICTIONARY extends RC4PHP_DB_SQL_DATA_DICTIONARY
{
//////////////////////////////////////////////////////////////////
public function __construct($dbSQLcommand='')
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{ parent::__construct($dbSQLcommand); }
//////////////////////////////////////////////////////////////////
protected function _boolean()
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{ return 'bit'; }
//////////////////////////////////////////////////////////////////
protected function _char($size=self::DB_DEFAULT_CHAR_SIZE)
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{ 
if(($size=(is_array($size))?((isset($size[0])?(abs(intval($size[0]))):(self::DB_DEFAULT_VARCHAR_SIZE))):(abs(intval($size))))>8000) $size=8000;
return "char($size)"; 
}
//////////////////////////////////////////////////////////////////
protected function _unicodeChar($size=self::DB_DEFAULT_UNICODE_CHAR_SIZE)
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{ 
if(($size=(is_array($size))?((isset($size[0])?(abs(intval($size[0]))):(self::DB_DEFAULT_UNICODE_CHAR_SIZE))):(abs(intval($size))))>4000) $size=4000;
return "nchar($size)"; 
}
//////////////////////////////////////////////////////////////////
protected function _varChar($size=self::DB_DEFAULT_VARCHAR_SIZE) 
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{ 
if(($size=(is_array($size))?((isset($size[0])?(abs(intval($size[0]))):(self::DB_DEFAULT_VARCHAR_SIZE))):(abs(intval($size))))>8000) $size=8000;
return "varchar($size)"; 
}
//////////////////////////////////////////////////////////////////
protected function _unicodeVarChar($size=self::DB_DEFAULT_UNICODE_VARCHAR_SIZE)
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{ 
if(($size=(is_array($size))?((isset($size[0])?(abs(intval($size[0]))):(self::DB_DEFAULT_UNICODE_VARCHAR_SIZE))):(abs(intval($size))))>4000) $size=4000;
return "nvarchar($size)"; 
}
//////////////////////////////////////////////////////////////////
protected function _binary()
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{ return 'image'; }
//////////////////////////////////////////////////////////////////
protected function _float($size=self::DB_DEFAULT_FLOAT_SIZE)
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{
if(($size=(is_array($size))?((isset($size[0])?(abs(intval($size[0]))):(self::DB_DEFAULT_FLOAT_SIZE))):(abs(intval($size))))>53) $size=53;
return "float($size)";
}
//////////////////////////////////////////////////////////////////
protected function _integer($size=self::DB_DEFAULT_INTEGER_SIZE)
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{
if(($size=(is_array($size))?((isset($size[0])?(abs(intval($size[0]))):(self::DB_DEFAULT_INTEGER_SIZE))):(abs(intval($size))))>8) $size=8;
switch($size)
	{
	 case 1:  return 'tinyint';
	 case 2:  return 'smallint';
	 case 4:  return 'int';
	 case 8:  return 'bigint';
	 default: return '';
	}
}
//////////////////////////////////////////////////////////////////
protected function _numeric($fieldDefinition=array(self::DB_DEFAULT_NUMERIC_PRECISION,self::DB_DEFAULT_NUMERIC_SCALE),$optionalScale=self::DB_DEFAULT_NUMERIC_SCALE)
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{
if(($precision=(is_array($fieldDefinition))?((isset($fieldDefinition[0]))?(abs(intval($fieldDefinition[0]))):(self::DB_DEFAULT_NUMERIC_PRECISION)):(abs(intval($fieldDefinition))))>38) $precision=38;
if(($scale=(is_array($fieldDefinition))?((isset($fieldDefinition[1]))?(abs(intval($fieldDefinition[1]))):(self::DB_DEFAULT_NUMERIC_SCALE)):((empty($optionalScale)?(self::DB_DEFAULT_NUMERIC_SCALE):(abs(intval($optionalScale))))))>$precision) $scale=$precision;
return "numeric($precision,$scale)";
}
//////////////////////////////////////////////////////////////////
protected function _dateTime()
///class RC4PHP_DB_MSSQL_DATA_DICTIONARY//////////////////////////
{ return 'datetime'; }
//////////////////////////////////////////////////////////////////
}
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
/**
* class RC4PHP_DB_MSSQL_Exception
*
* It's the MSSQL exception class.
* 
* PUBLIC METHODS
* -------------------------------------------------------------
* getMessage();         -> returns message of exception 
* getCode();            -> returns code of exception
* getFile();            -> returns source filename
* getLine();            -> returns source line
* getTrace();           -> returns an array of the backtrace()
* getTraceAsString();   -> returns formated string of trace
* getBacktrace();	-> returns an array of the backtrace
* 
* @access public
*/
class RC4PHP_DB_MSSQL_Exception extends RC4PHP_DB_SQL_Exception
{
//////////////////////////////////////////////////////////////////
/**
* Constructing RC4PHP_DB_MSSQL_Exception object
*
* Initialize object's properties.
*
* @param resource $dbSQLConnection
* @param string $message
* @param integer $code
* @return RC4PHP_DB_SQL_Exception
*/
public function __construct(&$dbSQLConnection=false,$SQLErrorMessage=self::_ExceptionDefaultMessage_,$SQLErrorCode=self::_ExceptionDefaultCode_)
///class RC4PHP_DB_MSSQL_Exception////////////////////////////////
{
parent::__construct($SQLErrorMessage, $SQLErrorCode);
if($errorMessage=@mssql_get_last_message()) $this->message=@trim($SQLErrorMessage.' '.RC4PHP_DB_SQL_Result::_ServerErrorMessageHeader_.$errorMessage.RC4PHP_DB_SQL_Result::_ServerErrorMessageFooter_);
else $this->message=@trim($SQLErrorMessage);
if(!$SQLErrorCode){ if($rs=@mssql_query('SELECT @@ERROR AS ErrorCode',$dbSQLConnection)){ $this->code=@mssql_result($rs,0,0); @mssql_free_result($rs); } }
}
//////////////////////////////////////////////////////////////////
}
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
?>
Return current item: RC4PHP class