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