<?php
/* ============
* qt_lib_db.php
* ------------
* version: 4.0 build:20100210
* This is a library of public class
* ------------
* CLASS cDB
* cDB()
* Connect()
* Disconnect()
* Getrow()
* Halt()
* ------------
* NOTE:
* the function row_count have been removed because it is not supported by odbc
* ============ */
class cDB
{
var $type; // server type
var $db; // database name
var $host; // server host name
var $port; // server port number
var $dsn; // dsn name (or FALSE)
var $user; // username
var $pwd; // userpassword
var $ip; // user current ip
var $sql; // sql string
var $con; // connection id
var $qry; // query id
var $error = '';
var $debug = false;
var $stats;
/*
* Constructor of class - Initializes class and connects to the database
*
* database types are:
* mysql MySQL >=5.0 (mysql4 still valid)
* mssql Microsoft SQL Server
* ibase InterBase FireBird
* sqlite SQLite
* pg PostgreSQL
* db2 Ibm db2
* access Microsoft Access (mdb) - driver odbc
*/
function cDB($strType,$strHost,$strDb,$strUser,$strPwd,$strPort=false,$strDsn=false,$bStats=false)
{
if ( $bStats )
{
$this->stats = array('num'=>0,'start'=>(float)(vsprintf('%d.%06d', gettimeofday())),'end'=>0);
}
$this->type = strtolower($strType);
$this->host = $strHost;
$this->db = $strDb;
$this->user = $strUser;
$this->ip = $_SERVER['REMOTE_ADDR'];
$this->pwd = $strPwd;
$this->port = $strPort;
$this->dsn = $strDsn;
// check type
if ( !in_array($this->type,array('mysql','mysql4','mssql','pg','ibase','sqlite','db2','access','oci')) )
{
die('db_type ['.$this->type.'] not supported. Must be "mysql","mssql","pg","ibase","sqlite","db2","access" or "oci"');
}
// Connect
return $this->Connect();
}
/*
* This function connects the database
* @return boolean $is_connected Returns true if connection was successful otherwise false
* @desc This function connects to the database which is set in the constructor
*/
function Connect()
{
// check already connected
if ( $this->con!='' )
{
$this->error = 'Already connected to database.';
return $this->Halt(false);
}
// Selecting connection function and connecting
if ( $this->dsn )
{
$this->con = odbc_connect($this->dsn,$this->user,$this->pwd);
}
else
{
switch($this->type)
{
case 'mysql4':
$strPort = '';
if ( $this->port ) $strPort = ':'.$this->port;
$this->con = mysql_connect($this->host.$strPort,$this->user,$this->pwd);
break;
case 'mysql':
$strPort = '';
if ( $this->port ) $strPort = ':'.$this->port;
$this->con = mysql_connect($this->host.$strPort,$this->user,$this->pwd);
break;
case 'pg':
$strPort = '';
if ( $this->port ) $strPort = ' port='.$this->port;
$this->con = pg_connect('host='.$this->host.$strPort.' dbname='.$this->db.' user='.$this->user.' password='.$this->pwd);
break;
case 'ibase':
$this->con = ibase_connect($this->host.':'.$this->db,$this->user,$this->pwd);
break;
case 'sqlite':
$this->con = sqlite_open($this->db,0666,$e) or die($e);
if ( !$this->con ) echo($e);
break;
case 'mssql': $this->con = mssql_connect($this->host,$this->user,$this->pwd); break;
case 'db2': $this->con = db2_connect($this->host,$this->user,$this->pwd); break;
case 'oci': $this->con = oci_connect($this->user,$this->pwd,$this->db); break;
default: die('db_type ['.$this->type.'] not supported.');
}
}
if ( !$this->con )
{
$this->error = 'Wrong connection parameters! Cannot establish connection to host.';
return $this->Halt();
}
// selection database (if required)
if ( $this->dsn )
{
return true;
}
else
{
switch ($this->type)
{
case 'mysql': if ( mysql_select_db($this->db,$this->con) ) return true; break;
case 'mssql': if ( mssql_select_db($this->db,$this->con) ) return true; break;
case 'db2': if ( db2_select_db($this->db,$this->con) ) return true; break;
case 'mysql4': if ( mysql_select_db($this->db,$this->con) ) return true; break;
default: return true;
}
$this->error = 'Wrong database parameters! Cannot select database.';
return $this->Halt();
}
}
// ---------
function Disconnect()
{
if ( $this->dsn ) { odbc_close($this->con); return true; }
switch($this->type)
{
case 'mysql': mysql_close($this->con); break;
case 'mssql': mssql_close($this->con); break;
case 'pg': pg_close($this->con); break;
case 'ibase': ibase_close($this->con); break;
case 'sqlite': sqlite_close($this->con); break;
case 'db2': db2_close($this->con); break;
case 'oci': oci_close($this->con); break;
case 'mysql4': mysql_close($this->con); break;
default: die('db_type ['.$this->type.'] not supported.');
}
return true;
}
// ---------
function Query($strSql)
{
$this->sql = $strSql;
if ( $this->debug || isset($_SESSION['QTdebugsql']) ) printf('SQL statement: %s<br/>',$this->sql);
if ( $this->dsn || $this->type=='pg' || $this->type=='oci' ) $this->sql = str_replace('"',"'",$this->sql);
if ( $this->dsn )
{
$this->qry = odbc_exec($this->con,$this->sql);
}
else
{
switch($this->type)
{
case 'mysql': $this->qry = mysql_query($this->sql,$this->con); break;
case 'mssql': $this->qry = mssql_query($this->sql,$this->con); break;
case 'pg': $this->qry = pg_query($this->con,$this->sql); break;
case 'ibase': $this->qry = ibase_query($this->con,$this->sql); break;
case 'sqlite':$this->qry = sqlite_query($this->con,$this->sql); break;
case 'db2': $this->qry = db2_query($this->con,$this->sql); break;
case 'oci': $this->qry = oci_parse($this->con,$this->sql); oci_execute($this->qry); break;
case 'mysql4': $this->qry = mysql_query($this->sql,$this->con); break;
default: die('db_type ['.$this->type.'] not supported.');
}
}
if ( isset($this->stats) )
{
$this->stats['num']++;
}
if ( !$this->qry ) return $this->Halt();
return true;
}
// --------
function Nextid($strTable='',$strField='id',$strWhere='')
{
if ( !is_string($strTable) || empty($strTable) ) die('cDB->Nextid: argument #1 must be a string');
if ( !is_string($strField) || empty($strField) ) die('cDB->Nextid: argument #2 must be a string');
if ( !is_string($strWhere) ) die('cDB->Nextid: argument #3 must be a string');
$this->Query("SELECT max($strField)+1 as newnum FROM $strTable $strWhere");
$row = $this->Getrow();
$i = $row['newnum'];
if ( empty($i) ) $i=1;
return intval($i);
}
// --------
// attention: odbc can return fieldnames in uppercase
// attention: sqlite can return fieldname including the prefix alias (p.id)
function Getrow()
{
$row = false;
if ( $this->dsn )
{
if ( odbc_fetch_row($this->qry) )
{
for ($i=1;$i<=odbc_num_fields($this->qry);$i++)
{
$strName=strtolower(odbc_field_name($this->qry,$i));
$row[$strName]=odbc_result($this->qry,$i);
}
}
}
else
{
switch ($this->type)
{
case 'mysql': $row = mysql_fetch_assoc($this->qry); break; // php 5.0.3
case 'mssql':
$row = mssql_fetch_assoc($this->qry);
// this fix a known bug in mssql_fetch_assoc that add a space to empty string
if ( is_array($row) )
{
foreach($row as $strKey=>$oValue)
{
if ( is_string($oValue) ) {
if ( strlen($oValue)==1 ) {
$row[$strKey] = trim($oValue);
}}
}
}
break;
case 'pg': $row = pg_fetch_assoc($this->qry); break;// php 4.3.0
case 'ibase': $row = ibase_fetch_assoc($this->qry); break;// php 4.3.0
case 'sqlite':
$row = sqlite_fetch_array($this->qry,SQLITE_ASSOC);// php 5.0
if ( $row===false ) return false;
$arr = array();
foreach($row as $strKey=>$oValue)
{
if ( substr($strKey,1,1)=='.') $strKey = strtolower(substr($strKey,2));
$arr[$strKey]=$oValue;
}
$row = $arr;
break;
case 'db2': $row = db2_fetch_assoc($this->qry); break; // php unknown version
case 'oci':
$row = oci_fetch_assoc($this->qry);
if ( $row===false ) return false;
$arr = array();
foreach($row as $strKey=>$oValue)
{
$arr[strtolower($strKey)]=$oValue;
}
$row = $arr;
break;
case 'mysql4': $row = mysql_fetch_assoc($this->qry); break; // php 4.0.3
default: die('db_type ['.$this->type.'] not supported.');
}
}
return $row;
}
// --------
function Halt($bShowDbError=true,$bStop=false)
{
if ( $bShowDbError )
{
switch($this->type)
{
case 'mysql': echo 'Error ',mysql_errno(),': ',mysql_error(); break;
case 'mssql': echo 'Error: ',mssql_get_last_message(); break;
case 'pg': echo 'Error: ',pg_last_error(); break;
case 'ibase': echo 'Error: ',ibase_errmsg(); break;
case 'sqlit': echo 'Error: ',sqlite_last_error($this->con),': ',sqlite_error_string(sqlite_last_error($this->con)); break;
case 'db2': echo 'Error: ',db2_conn_errormsg(); break;
case 'oci': $e=oci_error(); echo 'Error: ',$e['message']; break;
case 'mysql4': echo 'Error ',mysql_errno(),': ',mysql_error(); break;
}
}
if ( !empty($this->error) ) echo '<br/>'.$this->error;
if ( $bStop ) exit;
return false;
}
// --------
}
?>