Logo Search packages:      
Sourcecode: dcl version File versions  Download package

mssql.php

<?php
/*
 * $Id: mssql.php,v 1.3.2.1.2.13 2003/12/13 22:34:26 mdean Exp $
 *
 * Original implementation by Urmet Janes.  Many thanks!
 *
 * Double Choco Latte - Source Configuration Management System
 * Copyright (C) 1999  Michael L. Dean & Tim R. Norman
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program 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 General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 * Select License Info from the Help menu to view the terms and conditions of this license.
 */

if (!IsSet($GLOBALS['MSSQL_PHP_INCLUDED']))
{
      $GLOBALS['MSSQL_PHP_INCLUDED'] = 1;

      include_once(DCL_ROOT . 'datetime.php');

      class dclDB
      {
            var $conn;
            var $res;
            var $oid;
            var $cur;
            var $fieldArray;
            var $foreignKeys;
            var $cache;
            var $cacheEnabled;
            var $schemaVersion = '3';
            var $objDate;
            var $objTimestamp;
            var $emptyTimestamp;
            var $Record;
            var $vcur;
            var $JoinKeyword;

            function dclDB()
            {
                  $this->objDate = new DCLDate;
                  $this->objTimestamp = new DCLTimestamp;
                  $this->conn = 0;
                  $this->res = 0;
                  $this->oid = 0;
                  $this->cur = -1;
                  $this->vcur = -1;
                  $this->Record = array();

                  $this->JoinKeyword = 'INNER JOIN';
                  $this->emptyTimestamp = 'null';
            }

            function Connect($conn = '')
            {
                  global $dcl_domain_info, $dcl_domain;

                  if ($conn == '')
                  {
                        if (!IsSet($GLOBALS['__DB_CONN__']))
                        {
                              $this->conn = mssql_connect($dcl_domain_info[$dcl_domain]['dbHost'],
                                          $dcl_domain_info[$dcl_domain]['dbUser'],
                                          $dcl_domain_info[$dcl_domain]['dbPassword']);

                              if (!mssql_select_db($dcl_domain_info[$dcl_domain]['dbName'], $this->conn))
                              {
                                    // Couldn't select database - close connection and return 0
                                    mssql_close($this->conn);
                                    $this->conn = 0;
                                    return 0;
                              }

                              $GLOBALS['__DB_CONN__'] = $this->conn;
                        }
                        else
                              $this->conn = $GLOBALS['__DB_CONN__'];
                  }
                  else
                        $this->conn = $conn;

                  return $this->conn;
            }

            function CanConnectServer()
            {
                  global $dcl_domain_info, $dcl_domain;

                  $conn = mssql_connect($dcl_domain_info[$dcl_domain]['dbHost'],
                              $dcl_domain_info[$dcl_domain]['dbUser'],
                              $dcl_domain_info[$dcl_domain]['dbPassword']);

                  $bConnect = ($conn > 0);
                  mssql_close($conn);

                  return $bConnect;
            }

            function CanConnectDatabase()
            {
                  global $dcl_domain_info, $dcl_domain;

                  $conn = mssql_connect($dcl_domain_info[$dcl_domain]['dbHost'],
                              $dcl_domain_info[$dcl_domain]['dbUser'],
                              $dcl_domain_info[$dcl_domain]['dbPassword']);

                  if ($conn > 0)
                  {
                        $bRetVal = mssql_select_db($dcl_domain_info[$dcl_domain]['dbName'], $conn);
                        mssql_close($conn);

                        return $bRetVal;
                  }

                  return false;
            }

            function CreateDatabase()
            {
                  global $dcl_domain_info, $dcl_domain;

                  $conn = mssql_connect($dcl_domain_info[$dcl_domain]['dbHost'],
                              $dcl_domain_info[$dcl_domain]['dbUser'],
                              $dcl_domain_info[$dcl_domain]['dbPassword']);

                  $query = sprintf('Create Database %s', $dcl_domain_info[$dcl_domain]['dbName']);

                  return (mssql_query($query, $conn) > 0);
            }

            function TableExists($sTableName)
            {
                  return ($this->ExecuteScalar("select count(*) from sysobjects where name='$sTableName' and type='u'") > 0);
            }

            function Query($query)
            {
                  $this->res = 0;
                  $this->oid = 0;
                  $this->cur = -1;
                  $this->vcur = -1;
                  $this->Record = array();

                  if ($this->conn)
                  {
                        $this->res = mssql_query($query, $this->conn);
                        if ($this->res)
                        {
                              $this->cur = 0;
                              return $this->res;
                        }
                        else
                        {
                              print("<br>Error executing query!<br>$query");
                              return -1;
                        }
                  }
                  else
                        return -1;
            }

            function LimitQuery($query, $offset, $rows)
            {
                  $this->res = 0;
                  $this->oid = 0;
                  $this->cur = -1;
                  $this->vcur = -1;
                  $this->Record = array();

                  if ($this->conn)
                  {
                        @$this->res = mssql_query($query, $this->conn);
                        if ($this->res)
                        {
                              $this->cur = $offset;
                              // Push cursor to appropriate row in case next_record() is used
                              if ($offset > 0)
                                    @mssql_data_seek($this->res, $offset);

                              $this->vcur = $offset + $rows - 1;

                              return $this->res;
                        }
                        else
                        {
                              print("<br>Error executing query!<br>$query<br>");
                              print(mssql_get_last_message());
                              return -1;
                        }
                  }
                  else
                        return -1;
            }

            function Execute($query)
            {
                  $this->res = 0;
                  $this->oid = 0;
                  $this->cur = -1;
                  $this->vcur = -1;
                  $this->Record = array();

                  if ($this->conn)
                  {
                        mssql_query($query, $this->conn);
                        return 1;
                  }
                  else
                        return -1;
            }

            // Execute row returning query and return first row, first field
            function ExecuteScalar($sql)
            {
                  $retVal = null;

                  if (!$this->conn)
                        $this->Connect();

                  $res = mssql_query($sql, $this->conn);
                  if ($res)
                  {
                        if (mssql_num_rows($res) > 0)
                              $retVal = mssql_result($res, 0, 0);

                        mssql_free_result($res);
                  }

                  return $retVal;
            }

            function Insert($query)
            {
                  $this->res = 0;
                  $this->oid = 0;
                  $this->cur = -1;
                  $this->vcur = -1;
                  $this->Record = array();

                  if ($this->conn)
                  {
                        $this->res = mssql_query($query, $this->conn);
                        $oidRes = mssql_query('SELECT @@identity', $this->conn);
                        if ($this->res || $oidRes)
                        {
                              if ($oidRes)
                                    $this->oid = mssql_result($oidRes, 0, 0);
                              else
                                    print('<br>Could not retrieve @@identity of newly inserted record!!<br>Query: ' . $query);

                              return $this->oid;
                        }
                        else
                        {
                              print("<br>Error executing insert!<br>$query");
                              return -1;
                        }
                  }
                  else
                  {
                        print('<br>No connection!');
                        return -1;
                  }
            }

            function FreeResult()
            {
                  $this->Record = array();
                  if ($this->res != 0)
                        @mssql_free_result($this->res);

                  $this->res = 0;
            }

            function BeginTransaction()
            {
                  return $this->Execute('BEGIN TRAN');
            }

            function EndTransaction()
            {
                  return $this->Execute('COMMIT TRAN');
            }

            function RollbackTransaction()
            {
                  return $this->Execute('ROLLBACK TRAN');
            }

            function NumRows()
            {
                  if ($this->res)
                        return mssql_num_rows($this->res);
                  else
                        return -1;
            }

            function NumFields()
            {
                  if ($this->res)
                        return mssql_num_fields($this->res);
                  else
                        return -1;
            }

            // from phpGW/phpLib db classes - sort of
            function next_record()
            {
                  // bump up if just ran query
                  if ($this->cur == -1)
                        $this->cur = 0;

                  if ($this->vcur == -1 || ($this->cur++ <= $this->vcur))
                        $this->Record = @mssql_fetch_array($this->res);
                  else
                        $this->Record = NULL;

                  $stat = is_array($this->Record);
                  if (!$stat)
                        $this->FreeResult();

                  return $stat;
            }

            // from phpGW/phpLib db classes - sort of
            function f($sName)
            {
                  return $this->Record[$sName];
            }

            function GetFieldName($fieldIndex)
            {
                  if ($this->res)
                        return mssql_field_name($this->res, $fieldIndex);

                  return '';
            }

            function IsFieldNull($thisField)
            {
                  if ($this->res)
                  {
                        if (count($this->Record) > 0)
                              return $this->Record[$thisField] == NULL;

                        return ($this->GetField($thisField) == '');
                  }
                  else
                        return -1;
            }

            function FetchAllRows()
            {
                  $retVal = array();
                  $i = 0;
                  // bump up if just ran query
                  if ($this->cur == -1)
                        $this->cur = 0;

                  while ($a = @mssql_fetch_row($this->res))
                  {
                        $this->cur++;
                        $retVal[$i++] = $a;
                  }

                  return $retVal;
            }

            function InitFromArray($aSource)
            {
                  reset($this->fieldArray);
                  while (list($key, $field) = each($this->fieldArray))
                  {
                        if (IsSet($aSource[$field]))
                              $this->$field = $aSource[$field];
                  }
            }

            function InitFromGlobals()
            {
                  reset($this->fieldArray);
                  while (list($key, $field) = each($this->fieldArray))
                  {
                        if (IsSet($GLOBALS[$field])) 
                        {
                              $this->$field = $this->GPCStripSlashes($GLOBALS[$field]);
                        }
                  }
            }

            // php/eGW compatible function
            function db_addslashes($thisString)
            {
                  return $this->DBAddSlashes($thisString);
            }

            function DBAddSlashes($thisString)
            {
                  if (!IsSet($thisString) || $thisString == '')
                        return '';

                  return ereg_replace("'", "''", $thisString);
            }

            function GPCStripSlashes($thisString)
            {
                  if (get_magic_quotes_gpc() == 0)
                        return $thisString;

                  return stripslashes($thisString);
            }

            function CacheRow($key)
            {
                  if (!$this->cacheEnabled || IsSet($this->cache[$key]))
                        return -1;

                  reset($this->fieldArray);
                  while (list($dummykey, $field) = each($this->fieldArray))
                  {
                        if (IsSet($this->$field)) 
                              $this->cache[$key][$field] = $this->$field;
                  }

                  return 0;
            }

            function LoadCache($key)
            {
                  if (!$this->cacheEnabled || !IsSet($this->cache[$key]))
                        return -1;

                  reset($this->fieldArray);
                  while (list($dummykey, $field) = each($this->fieldArray))
                  {
                        if (IsSet($this->cache[$key][$field])) 
                              $this->$field = $this->cache[$key][$field];
                  }

                  return 0;
            }

            function GetNewIDSQLForTable($tableName)
            {
                  switch($tableName)
                  {
                        case 'workorders':
                              if (!$this->conn)
                                    $this->Connect();
                              $res = mssql_query('SELECT MAX(jcn)+1 FROM workorders', $this->conn);
                              if ($res)
                              {
                                    if (mssql_result($res, 0, 0) == '')  // Nothing in there yet?
                                    {
                                          return '1';
                                    }
                                    else
                                    {
                                          return mssql_result($res, 0, 0);
                                    }
                              }
                  }

                  return '';
            }

            function GetSeqSQL($jcn)
            {
                  if (!$this->conn)
                        $this->Connect();
                  $res = mssql_query("SELECT MAX(seq)+1 FROM workorders WHERE jcn=$jcn", $this->conn);
                  if ($res)
                  {
                        if (mssql_num_rows($res) > 0)
                              return mssql_result($res, 0, 0);
                  }
                  else
                        return '';
            }

            function GetDateSQL()
            {
                  return 'GetDate()';
            }

            function DisplayToSQL($thisDate)
            {
                  global $dcl_info;

                  $eregStr = str_replace('m', '([0-9]{2})', $dcl_info['DCL_DATE_FORMAT']);
                  $eregStr = str_replace('d', '([0-9]{2})', $eregStr);
                  $eregStr = str_replace('Y', '([0-9]{4})', $eregStr);
                  if(ereg('^' . $eregStr . ' ([0-9]{2}).([0-9]{2}).([0-9]{2})$', $thisDate))
                        return "'" . $this->ArrangeTimeStampForInsert($thisDate) . "'";
                  else
                        return "'" . $this->ArrangeDateForInsert($thisDate) . "'";
            }

            function ArrangeDateForInsert($thisDate)
            {
                  $this->objDate->SetFromDisplay($thisDate);
                  return $this->objDate->ToDB();
            }

            function ArrangeTimeStampForInsert($thisStamp)
            {
                  $this->objTimestamp->SetFromDisplay($thisStamp);
                  return $this->objTimestamp->ToDB();
            }

            function FormatDateForDisplay($thisDate)
            {
                  if ($thisDate != '')
                  {
                        $this->objDate->SetFromDB($thisDate);
                        return $this->objDate->ToDisplay();
                  }

                  return '';
            }

            function FormatTimeStampForDisplay($thisStamp)
            {
                  if ($thisStamp != '')
                  {
                        $this->objTimestamp->SetFromDB($thisStamp);
                        return $this->objTimestamp->ToDisplay();
                  }

                  return '';
            }

            function GetUpperSQL($text)
            {
                  return $text;
            }

            function GetRTrimSQL($text)
            {
                  return sprintf('rtrim(%s)', $text);
            }

            function GetLastInsertID($sTable)
            {
                  $res = mssql_query('SELECT @@identity', $this->conn);
                  if ($res)
                  {
                        $Record = @mssql_fetch_array($res);
                        mssql_free_result($res);
                        return $Record[0];
                  }

                  print('<br>Could not retrieve @@identity of newly inserted record!!');
                  return -1;
            }

            function HasFKRef($id)
            {
                  $bHasRef = false;
                  $oKey = new dclDB; // Don't really need anything fancy, 'spose...
                  while ((list($sTable, $sField) = each($this->foreignKeys)) && !$bHasRef)
                  {
                        if (is_array($sField)) // More than one field in here references this key
                        {
                              while ((list($sDummyKey, $sOneField) = each($sField)) && !$bHasRef)
                              {
                                    $oKey->Query("SELECT $sOneField FROM $sTable WHERE $sOneField=$id");
                                    if ($oKey->next_record())
                                          $bHasRef = true;
                              }
                        }
                        else
                        {
                              $oKey->Query("SELECT $sField FROM $sTable WHERE $sField=$id");
                              if ($oKey->next_record())
                                    $bHasRef = true;
                        }
                  }

                  return $bHasRef;
            }

            function ConvertDate($sExpression, $sField)
            {
                  return "Replace(convert(varchar(10), $sExpression, 111), '/', '-') AS $sField";
            }

            function ConvertTimestamp($sExpression, $sField)
            {
                  return "convert(varchar(20), $sExpression, 20) AS $sField";
            }

            function IsDate($vField)
            {
                  return ($this->res > 0 && mssql_field_type($this->res, $vField) == 'smalldatetime');
            }

            function IsTimestamp($vField)
            {
                  return ($this->res > 0 && mssql_field_type($this->res, $vField) == 'datetime');
            }

            function index_names()
            {
                  global $dcl_domain, $dcl_domain_info;

                  $this->query("SELECT name FROM sysobjects WHERE type = 'i' ORDER BY name");
                  $i = 0;
                  $return = array();
                  while ($this->next_record())
                  {
                        $return[$i] = array();
                        $return[$i]['index_name']           = $this->f(0);
                        $return[$i]['tablespace_name']      = $dcl_domain_info[$dcl_domain]['dbName'];
                        $return[$i++]['database']           = $dcl_domain_info[$dcl_domain]['dbName'];
                  }

                  return $return;
            }

            function FieldExists($sTable, $sField)
            {
                  return ($this->ExecuteScalar("select count(*) from syscolumns where id = object_id('$sTable') and name = '$sField'") == 1);
            }

            function GetMinutesElapsedSQL($sBeginDateSQL, $sEndDateSQL, $sAsField)
            {
                  $sRetVal = "datediff(mi, $sBeginDateSQL, $sEndDateSQL)";

                  if ($sAsField == '')
                        return $sRetVal;

                  return "$sRetVal AS $sAsField";
            }
      }
}
?>

Generated by  Doxygen 1.6.0   Back to index