Location: PHPKode > projects > OpenNitro > trunk/Nitro/NitroDB/mssql.inc.php
<?php
//
// +---------------------------------------------------------------------------+
// | Nitro :: NitroDB :: MSSQL                                                 |
// +---------------------------------------------------------------------------+
// | Copyright (c) 2007 June Systems BV                                        |
// +---------------------------------------------------------------------------+
// | 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.1 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., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA            |
// +---------------------------------------------------------------------------+
// | Authors: Siggi Oskarsson <hide@address.com>                          |
// +---------------------------------------------------------------------------+
//
// $Id: mssql.inc.php 229 2008-04-17 09:20:31Z oli $
//
// Nitro MSSQL database class
//

/**
 * This file contains the MSSQL implementation of the Nitro database layer
 *
 * @package	Nitro
 * @subpackage	DB
 * @author 		Siggi Oskarsson
 * @version 	$Revision: 1.25 $
 * @copyright	2007 June Systems BV
 */

/**
 * MSSQL database class definition
 *
 * This is the MSSQL handler implementation for the database layer
 * class. The functions in this class should not be called directly,
 * but should always be called through the main Nitro database class.
 *
 * @package	Nitro
 * @subpackage	DB
 * @see DB
 */
class DB_mssql extends DB_Nitro {
	/**
	 * @ignore
	 */
	var $DBHandlerID = 'MSSQL';
	/**
	 * @ignore
	 */
	var $Host;
	/**
	 * @ignore
	 */
	var $Port;
	/**
	 * @ignore
	 */
	var $Username;
	/**
	 * @ignore
	 */
	var $Password;
	/**
	 * @ignore
	 */
	var $Database;
	/**
	 * @ignore
	 */
	var $ConnectionID;

	/**
	 * @ignore
	 */
	var $FetchMode = MSSQL_ASSOC;
	/**
	 * @ignore
	 */
	var $FetchModes = Array(NITRODB_NUM => MSSQL_NUM,
													NITRODB_ASSOC => MSSQL_ASSOC,
													NITRODB_BOTH => MSSQL_BOTH
													);

	/**
	 * @ignore
	 */
	var $Error;

	/**
	 * @ignore
	 */
	function DB_mssql($Host, $Username, $Password, $Database, $Port)
	{
		$this->Host = $Host;
		$this->Port = $Port;
		$this->Username = $Username;
		$this->Password = $Password;
		$this->Database = $Database;
	}
	
	/**
	 * @ignore
	 */
	function Connect($NewLink = FALSE)
	{
		if ($NewLink) {
			$this->ConnectionID = @mssql_connect($this->Host, $this->Username, $this->Password, TRUE);
		} else {
			$this->ConnectionID = @mssql_connect($this->Host, $this->Username, $this->Password);
		}
		if ($this->ConnectionID) {
			if(@mssql_select_db($this->Database, $this->ConnectionID)) {
				return $this->ConnectionID;
			} else {
				$this->Error = "Failed selecting database";
				return NULL;
			}
		} else {
			$this->Error = "Failed connecting to dabase server";
			return NULL;
		}
	}
	
	/**
	 * @ignore
	 */
	function query($Query, $Limit = 0, $Offset = 0)
	{
		@mssql_select_db($this->Database, $this->ConnectionID);
		if ($Limit) $Query = $this->Limit($Query, $Limit, $Offset);
		$Result = new NitroDB_mssql_Result($Query, $this->ConnectionID);
		if (DB::isError($Result)) {
			$this->Error = __CLASS__."::".__FUNCTION__.": ".$Result->Error;
		} else {
			$this->Error = FALSE;
		}

		return $Result;
	}
	
	/**
	 * @ignore
	 */
	function listTables()
	{
		$RV = Array();

		$Result = $this->query("SHOW TABLES FROM ".$this->Database);

		if (DB::isError($Result)) {
			$this->Error = __CLASS__."::".__FUNCTION__.": ".$Result->Error;
		}

		while ($row = $Result->fetchArray(NITRODB_NUM)) {
			$RV[] = $row[0];
		}

		$Result->free();

		return $RV;
	}
	
	/**
	 * @ignore
	 */
	function listFields($Table)
	{
		$RV = Array();

		$Result = $this->query("SHOW COLUMNS FROM ".addslashes($Table));

		if (DB::isError($Result)) {
			$this->Error = __CLASS__."::".__FUNCTION__.": ".$Result->Error;
		}

		while ($row = $Result->fetchArray()) {
			$RV[$row['Field']] = $row;
		}

		$Result->free();

		return $RV;
	}
	
	/**
	 * @ignore
	 */
	function escapeString($String)
	{
		return mssql_real_escape_string($String);
	}

	/**
	 * @ignore
	 */
	function getDateTimeFunction()
	{
		return "CURRENT_TIMESTAMP";
	}

	/**
	 * @ignore
	 */
	function InsertID()
	{
		return mssql_insert_id($this->ConnectionID);
	}
	
	/**
	 * @ignore
	 */
	function PrepareQuery($Query)
	{
		$Query = str_replace('`', '"', $Query); // quote table names

		$Query = str_replace('M.File', 'M.[File]', $Query); // M.File is reserved identifier
		$Query = str_replace("FROM User\n", "FROM [User]\n", $Query); // User table in Session.inc.php
		
		return $Query;
	}

	/**
	 * @ignore
	 */
	function Limit($Query, $Limit, $Offset = 0)
	{
		$Query = ereg_replace('^SELECT ', 'SELECT TOP '.$Limit, $Query);
		
		return $Query;
	}
	
	/**
	 * @ignore
	 */
	function CheckSlave()
	{
		// TODO: make this better! This doesn't really work....
		//return @mssql_query('SHOW SLAVE STATUS', $this->ConnectionID);
		return TRUE;	
	}
}

/**
 * MSSQL Result object implementation
 *
 * <CODE>
 * $Query         = 'SELECT * FROM User';
 * $Result        = $DBConnection->query($Query);
 * $AffectedRows  = $Result->affectedRows();
 * $Row           = $Result->fetchArray();
 * $Value         = $Result->fetchResult(0,0);
 * $Row           = $Result->fetchRow();
 * $FreeResult    = $Result->free();
 * $Error         = $Result->getMessage();
 * $Value         = $Result->getResult(0,0);
 * $InsertID      = $Result->insertID();
 * $NumRows       = $Result->numRows();
 * </CODE>
 *
 * @package	Nitro
 * @subpackage	DB
 * @access	public
 */
class NitroDB_mssql_Result extends NitroDB_Nitro_Result {
	/**
	 * @ignore
	 */
	var $ConnectionID;
	/**
	 * @var	string	Query
	 */
	var $Query;
	/**
	 * @var	resource	MSSQL result resource
	 */
	var $Result;
	/**
	 * @var	string	MSSQL result error
	 */
	var $Error;
	/**
	 * @var	int	MSSQL insert id
	 */
	var $InsertID = NULL;
	/**
	 * @var	int	MSSQL affected rows
	 */
	var $AffectedRows = NULL;
	/**
	 * @var	int	Default MSSQL fetchmode (mssql_ASSOC)
	 */
	var $FetchMode = MSSQL_ASSOC;
	/**
	 * @var	int	Nitro fetchmode translation to MSSQL fetchmodes
	 */
	var $FetchModes = Array(NITRODB_NUM => MSSQL_NUM,
													NITRODB_ASSOC => MSSQL_ASSOC,
													NITRODB_BOTH => MSSQL_BOTH
													);

	/**
	 * @ignore	constructor
	 */
	function NitroDB_mssql_Result($Query, $ConnectionID)
	{
		DebugGroup("DB", "Result", "query :".$Query, __FILE__, __LINE__, DEBUG_SQL_OK);
		$this->ConnectionID = $ConnectionID;
		$Query = DB_mssql::PrepareQuery($Query);
		$this->Query = $Query;

		if ($this->Result = mssql_query($Query, $ConnectionID)) {
			Debug("DB", "Result", "Result: ".$this->Result, __FILE__, __LINE__, DEBUG_SQL_OK);

			$this->InsertID = (eregi('^[:space:]*[INSERT|REPLACE]', $Query) ? @mssql_insert_id($this->ConnectionID) : NULL);
			Debug(__CLASS__, __FUNCTION__, "Insert id {$this->InsertID}", __FILE__, __LINE__, DEBUG_SQL_OK);

			$this->AffectedRows = @mssql_rows_affected($this->ConnectionID);
			Debug(__CLASS__, __FUNCTION__, "Affected rows {$this->AffectedRows}", __FILE__, __LINE__, DEBUG_SQL_OK);

			$this->Error = FALSE;
		} else {
			echo mssql_get_last_message().'<br><br>'.$Query.'<br><br>';
			Debug("DB", "Result", "Query failed: ".mssql_get_last_message(), __FILE__, __LINE__, DEBUG_SQL_ERR);
			$this->Error = mssql_get_last_message()."\nQUERY:\n".$Query;
		}
		DebugCloseGroup(DEBUG_SQL_OK);
	}
	
	/**
	 * Get the number of rows in the result
	 *
	 * This function returns the number of rows in the result set
	 * for the query that was run or FALSE on error.
	 *
	 * @return	mixed	Number of rows or FALSE on error
	 * @access	public
	 */
	function numRows()
	{
		if ($this->Result && !$this->Error) {
			$RV = mssql_num_rows($this->Result);
			Debug(__CLASS__, __FUNCTION__, "NumRows: ".$RV, __FILE__, __LINE__, DEBUG_SQL_OK);
		} else {
			Debug(__CLASS__, __FUNCTION__, "Numrows failed, not a result or Error present", __FILE__, __LINE__, DEBUG_SQL_ERR);
			$RV = FALSE;
		}
		return $RV;
	}
	
	/**
	 * Fetch 1 row from the result
	 *
	 * This function fetches exactly one row from the result and returns
	 * it as an array. By using the FetchMode parameter the user can choose
	 * whether to return an associative or indexed array or both.
	 *
	 * @param	mixed	$FetchMode	Fetchmode to use in return array
	 * @return	mixed	Return array or FALSE on error
	 * @access	public
	 */
	function fetchArray($FetchMode = FALSE)
	{
		if ($this->Result && !$this->Error) {
			Debug(__CLASS__, __FUNCTION__, "fetching array", __FILE__, __LINE__, DEBUG_SQL_OKV);
			$RV = mssql_fetch_array($this->Result, ($FetchMode ? $this->FetchModes[$FetchMode] : $this->FetchMode));
		} else {
			Debug(__CLASS__, __FUNCTION__, "fetchArray failed, not a result or Error present", __FILE__, __LINE__, DEBUG_SQL_ERR);
			$RV = FALSE;
		}
		return $RV;
	}
	
	/**
	 * Get 1 value from result
	 *
	 * Returns exactly 1 value from the result found in the row given
	 * by $Record and column given by $Field.
	 *
	 * @param	int	$Record	Row number in result
	 * @param	int	$Field	Column number in result
	 * @return	mixed	Value from result or FALSE on error
	 * @access	public
	 */
	function fetchResult($Record, $Field)
	{
		if ($this->Result && !$this->Error) {
			Debug(__CLASS__, __FUNCTION__, "fetching result ($Record, $Field)", __FILE__, __LINE__, DEBUG_SQL_OKV);
			$RV = mssql_result($this->Result, $Record, $Field);
		} else {
			Debug(__CLASS__, __FUNCTION__, "fetchResult failed, not a result or Error present", __FILE__, __LINE__, DEBUG_SQL_ERR);
			$RV = FALSE;
		}
		return $RV;
	}
	
	/**
	 * Return number of affected rows from last query
	 *
	 * This function returns the number of rows that were affected by
	 * the last query run on the database.
	 *
	 * @return	mixed	Number of rows affected or FALSE on error
	 * @access	public
	 */
	function affectedRows()
	{
		return $this->AffectedRows;
	}
	
	/**
	 * Free result set
	 *
	 * This function should always be used when the result set
	 * created is not used anymore. This will free the result from
	 * the database memory and destroy the object itself.
	 *
	 * @return	boolean	Success or failure of freeing result
	 * @access	public
	 */
	function free()
	{
		if (is_resource($this->Result) && !$this->Error) {
			Debug("DB", "Result", "Free result: ".$this->Result, __FILE__, __LINE__, DEBUG_SQL_OK);
			@mssql_free_result($this->Result);
			//DebugCloseGroup(DEBUG_SQL_OK);
			//destroy(SELF);
			$RV = TRUE;
		} else {
			$RV = FALSE;
		}
		return $RV;
	}

	/**
	 * Get last insert id
	 *
	 * This function returns the insert id of the last insert query.
	 *
	 * @return	int	Last insert id
	 * @access	public
	 */
	function InsertID()
	{
		if (!isset($this->InsertID)) {
			$this->InsertID = @mssql_insert_id($this->ConnectionID);
		}
		
		return $this->InsertID;
	}
}

function mssql_insert_id() {
  $id = "";

  $rs = mssql_query("SELECT @@identity AS id");
  if ($row = mssql_fetch_row($rs)) {
    $id = trim($row[0]);
  }
  mssql_free_result($rs);

  return $id;
}
?>
Return current item: OpenNitro