PHP Classes

File: tbssql_odbc.php

Recommend this page to a friend!
  Classes of Skrol29   TbsSQL   tbssql_odbc.php   Download  
File: tbssql_odbc.php
Role: Class source
Content type: text/plain
Description: Class for ODBC generic databases
Class: TbsSQL
easy and powerfull tool for working with SQL
Author: By
Last change: Version 3.1, 2010-10-11

- Support Oracle databases.
- New methode ConfInfo() displays configuration information at any time.
- Fixed bug: clear and restore the console window when the parent window is actualized.
- Fixed bug: function _SqlDateFrmDb() may causes a PHP error "Notice: Undefined variable: x in ...tbssql_sqlserver_odbc.php on line 461".
- Enhanced configuration information displayed in trace mode.
- New constants: TBSSQL_1MINUTE, TBSSQL_DISABLED, TBSSQL_ALWAYS.
Date: 13 years ago
Size: 31,806 bytes
 

Contents

Class file image Download
<?php // TbsSql Engine // Version 3.1, 2010-10-11, Skrol29 // www.tinybutstrong.com if ( (version_compare(PHP_VERSION,'5')<0) && (!function_exists('clone')) ) { eval('function clone($object) {return $object;}'); // eval is needed because the syntax function clone() is refused in PHP 5 } define('TBSSQL_SILENT', 0); define('TBSSQL_NORMAL', 1); define('TBSSQL_DEBUG', 2); define('TBSSQL_TRACE', 4); define('TBSSQL_GRID', 8); define('TBSSQL_CONSOLE', 16); define('TBSSQL_1MINUTE', 1); define('TBSSQL_1HOUR', 60); define('TBSSQL_1DAY', 24*60); define('TBSSQL_1WEEK', 7*24*60); define('TBSSQL_NOCACHE', -1); define('TBSSQL_DISABLED', false); define('TBSSQL_ALWAYS', 0); define('TBSSQL_ARRAY', 'array'); define('TBSSQL_OBJECT', 'object'); class clsTbsSql { function __construct($srv='',$uid='',$pwd='',$db='',$drv='',$Mode=TBSSQL_NORMAL) { // Default values (defined here to be compatible with both PHP 4 & 5) $this->Version = '3.1'; $this->Id = false; $this->SqlNull = 'NULL'; // can be modified by user $this->DefaultRowType = TBSSQL_ARRAY; $this->CacheDir = '.'; $this->CacheTimeout = false; // in minutes $this->TempCacheTimeout = false; $this->CacheAutoClear = TBSSQL_1WEEK; // 1 week in minutes $this->CacheSuffix = ''; $this->InitMsg = true; $this->InitCsl = true; $this->_CacheCurrSql = false; // is different from false if data as to be saved if ($srv==='') { $this->Mode = $Mode; } else { $this->Connect($srv,$uid,$pwd,$db,$drv,$Mode); // Try to connect when instance is created } $this->_Dbs_Prepare(); $this->SetTbsKey(); } // Compatibility for PHP 4 function clsTbsSql($srv='',$uid='',$pwd='',$db='',$drv='',$Mode=TBSSQL_NORMAL) { $this->__construct($srv,$uid,$pwd,$db,$drv,$Mode); } // Public methods function Connect($srv,$uid='',$pwd='',$db='',$drv='',$Mode=false) { if ($Mode!==false) $this->Mode = $Mode; $auto = (($srv!='') and ($uid.$pwd.$db.$drv=='')); if ($auto) { // Connection by a global variable $var = $srv; if (!isset($GLOBALS[$var])) return $this->_Message('[Error] Automatic Connection failed because the global variable \''.$var.'\' is not found.'); if (!$this->_TakeVar($var,'srv',$srv)) return false; if (!$this->_TakeVar($var,'uid',$uid)) return false; if (!$this->_TakeVar($var,'pwd',$pwd)) return false; if (!$this->_TakeVar($var,'db' ,$db)) return false; $this->_TakeVar($var,'drv',$drv,false); unset($GLOBALS[$var]); } $this->_Dbs_Connect($srv,$uid,$pwd,$db,$drv); if (is_null($this->Id)) $this->Id = false; if ($this->Id===false) return $this->_SqlError(false); return true; } function Close() { if ($this->Id!==false) $this->_Dbs_Close(); } function Execute($Sql) { // we do not use cache here $ArgLst = func_get_args(); $Sql = $this->_SqlProtect($ArgLst); $RsId = $this->_Dbs_RsOpen($Sql, true); if ($RsId===false) return $this->_SqlError($this->Id); $this->_Dbs_RsClose($RsId); return true; } function GetVal($Sql) { $ArgLst = func_get_args(); $Sql = $this->_SqlProtect($ArgLst); $Data = array(); if (!$this->_CacheTryRetrieve($Sql,$Data)) { if (!$this->_GetDataFromDb($Sql, $Data, ($this->_CacheCurrSql===false))) return false; // if CacheSql is false, then we do not need to retrieve all the records from the db if ($this->_CacheCurrSql!==false) $this->_CacheUpdate($Data); } if (count($Data)==0) { return false; } else { return reset($Data[0]); } } function GetRow($Arg0) { $ArgLst = func_get_args(); $ObjConv = $this->_ObjCheck($Arg0, $ArgLst); $SqlPos = ($ObjConv) ? 1 : 0; $Sql = $this->_SqlProtect($ArgLst, $SqlPos); $Data = array(); if (!$this->_CacheTryRetrieve($Sql,$Data)) { if (!$this->_GetDataFromDb($Sql, $Data, ($this->_CacheCurrSql===false))) return false; // if CacheSql is false, then we do not need to retrieve all the records from the db if ($this->_CacheCurrSql!==false) $this->_CacheUpdate($Data); } if (count($Data)==0) return false; $Data = array($Data[0]); // conversion to object if ($ObjConv) { $this->_ObjConversion($Data, $Arg0); } elseif ($this->DefaultRowType!==TBSSQL_ARRAY) { $this->_ObjConversion($Data, $this->DefaultRowType); } return $Data[0]; } function GetRows($Arg0) { $ArgLst = func_get_args(); $ObjConv = $this->_ObjCheck($Arg0, $ArgLst); $SqlPos = ($ObjConv) ? 1 : 0; $Sql = $this->_SqlProtect($ArgLst, $SqlPos); $Data = array(); if (!$this->_CacheTryRetrieve($Sql,$Data)) { if (!$this->_GetDataFromDb($Sql, $Data)) return false; if ($this->_CacheCurrSql!==false) $this->_CacheUpdate($Data); } // conversion to object if ($ObjConv) { $this->_ObjConversion($Data, $Arg0); } elseif ($this->DefaultRowType!==TBSSQL_ARRAY) { $this->_ObjConversion($Data, $this->DefaultRowType); } return $Data; } function GetList($Sql) { $ArgLst = func_get_args(); $Sql = $this->_SqlProtect($ArgLst); $Data = array(); $col1 = false; $col2 = false; if (!$this->_CacheTryRetrieve($Sql,$Data)) { if ($this->_CacheCurrSql===false) { // no cache $RsId = $this->_Dbs_RsOpen($Sql); if ($RsId===false) return $this->_SqlError($this->Id); $r = $this->_Dbs_RsFetch($RsId); // First row if ($r===false) return $Data; $this->_ItemFoundCol($r, $col1, $col2); $this->_ItemAdd($Data, $r, $col1, $col2); while ($r = $this->_Dbs_RsFetch($RsId)) $this->_ItemAdd($Data, $r, $col1, $col2); // other rows $this->_Dbs_RsClose($RsId); return $Data; } else { if (!$this->_GetDataFromDb($Sql, $Data)) return false; $this->_CacheUpdate($Data); } } // At this point, $Data contain bold data. $Data2 = array(); if (!isset($Data[0])) return $Data2; $this->_ItemFoundCol($Data[0], $col1, $col2); foreach ($Data as $r) $this->_ItemAdd($Data2, $r, $col1, $col2); return $Data2; } function GetSql($Sql) { $ArgLst = func_get_args(); return $this->_SqlProtect($ArgLst); } function AffectedRows() { return $this->_Dbs_AffectedRows(); } function LastRowId($Info=false) { return $this->_Dbs_LastRowId($Info); } function SetTbsKey($Key='') { // Define the key (or a new key) for the TinyButStrong Template Engine global $_TBS_UserFctLst; if ($Key=='') { // Search for an free key $Key = 'tbssql'; // default key $i = 1; while (isset($_TBS_UserFctLst['k:'.$Key])) { $i++; $Key = 'tbssql'.$i; } } $this->TbsKey = $Key; $this->_Tbs_FetchFct = '_Tbs_RsFetch_Default'; // saved in a property in order to be passed by reference and then be modified when cache is enabled $_TBS_UserFctLst['k:'.$Key] = array('type'=>4,'open'=>array(&$this,'_Tbs_RsOpen'),'fetch'=>array(&$this,&$this->_Tbs_FetchFct),'close'=>array(&$this,'_Tbs_RsClose')); } function CacheTimestamp($Sql) { // return the timestamp of the cache file $x = $this->_CacheFilePath($Sql); if (file_exists($x)) { return filemtime($x); } else { return false; } } function CacheDelete($Sql) { // delete the cache file $x = $this->_CacheFilePath($Sql); if (file_exists($x)) { $ok = unlink($x); if ($ok) { if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('[Cache]: CacheDelete() has deleted the cache file: '.$x, '#060'); } else { $this->_Message('[Error]: CacheDelete() unexpected failure. Check permissions for deleting cache file '.$x); } return $ok; } else { if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('[Cache]: CacheDelete() has not found the cache file '.$x, '#060'); return false; } } function CacheFile($Sql) { // return the full path of the cache file $x = $this->_CacheFilePath($Sql); if (file_exists($x)) { return $x; } else { return false; } } function ConfInfo() { $this->InitMsg = false; // prevent from displaying the info twice // version $this->_Message('[Configuration]: TbsSql version '.$this->Version, '#060'); // mode $x = $this->_ModeDecompose($this->Mode, array(TBSSQL_SILENT=>'TBSSQL_SILENT', TBSSQL_NORMAL=>'TBSSQL_NORMAL', TBSSQL_DEBUG=>'TBSSQL_DEBUG', TBSSQL_TRACE=>'TBSSQL_TRACE', TBSSQL_GRID=>'TBSSQL_GRID', TBSSQL_CONSOLE=>'TBSSQL_CONSOLE')); $this->_Message('[Configuration]: property Mode = '.$x, '#060'); // DefaultRowType $x = $this->_ModeDecompose($this->DefaultRowType, array(TBSSQL_ARRAY=>'TBSSQL_ARRAY', TBSSQL_OBJECT=>'TBSSQL_OBJECT')); $this->_Message('[Configuration]: property DefaultRowType = '.$x, '#060'); // CacheTimeout $x = $this->_ModeDecompose($this->CacheTimeout, false); $this->_Message('[Configuration]: property CacheTimeout = '.$x, '#060'); // TempCacheTimeout $x = $this->_ModeDecompose($this->TempCacheTimeout, false); $this->_Message('[Configuration]: property TempCacheTimeout = '.$x, '#060'); // CacheAutoClear $x = $this->_ModeDecompose($this->CacheAutoClear, false); $this->_Message('[Configuration]: property CacheAutoClear = '.$x, '#060'); // CacheDir and CacheSuffix $this->_Message('[Configuration]: property CacheDir = '.var_export($this->CacheDir,true), '#060'); $this->_Message('[Configuration]: property CacheSuffix = '.var_export($this->CacheSuffix,true), '#060'); } // Private methods // Special for TinyButStrong // ------------------------- function _Tbs_RsOpen($Src,$Sql) { $Sql = $this->_SqlProtect(array($Sql)); // just in order to manage the Mode when it is TRACE or DEBUG $this->_CacheTbsData = array(); // default value in case of data to be saved if ($this->_CacheTbsOk=$this->_CacheTryRetrieve($Sql,$this->_CacheTbsData)) { // Data is retrieved from the cache $this->_CacheTbsEnd = count($this->_CacheTbsData); $this->_CacheTbsCurr = -1; $this->_Tbs_FetchFct = '_Tbs_RsFetch_FromCache'; // from cache $RecSet = true; } else { // No cache $RecSet = $this->_Dbs_RsOpen($Sql); if ($RecSet===false) $this->_SqlError(false); if ($this->_CacheCurrSql===false) { $this->_Tbs_FetchFct = '_Tbs_RsFetch_Default'; // no cache } else { $this->_Tbs_FetchFct = '_Tbs_RsFetch_ToBeSaved'; // cache to be saved } } return $RecSet; } function _Tbs_RsFetch_Default(&$RsId) { // no cache return $this->_Dbs_RsFetch($RsId); } function _Tbs_RsFetch_FromCache(&$RsId) { // read data from cache $this->_CacheTbsCurr++; if ($this->_CacheTbsCurr>=$this->_CacheTbsEnd) { return false; } else { return $this->_CacheTbsData[$this->_CacheTbsCurr]; } } function _Tbs_RsFetch_ToBeSaved(&$RsId) { // data to be saved $Rec = $this->_Dbs_RsFetch($RsId); if ($Rec!==false) $this->_CacheTbsData[] = $Rec; return $Rec; } function _Tbs_RsClose(&$RsId) { if ($this->_CacheTbsOk) { unset($this->_CacheTbsData); // free memory return true; } else { if ($this->_CacheCurrSql!==false) $this->_CacheUpdate($this->_CacheTbsData); return $this->_Dbs_RsClose($RsId); } } // Other private methods function _SqlError($ObjId) { if ($this->Mode===TBSSQL_SILENT) return; $x = 'Database error message: '.$this->_Dbs_Error($ObjId); if ($this->_ModeHas(TBSSQL_DEBUG)) $x .= "\r\nSQL = ".$this->LastSql; $this->_Message($x); return false; } function _Message($Txt, $Color='#FF0000') { // display a message right now in the Html page or in the console. Must return false. Default color is red, which means error. if ($this->Mode===TBSSQL_SILENT) return false; if ($this->InitMsg) { // display the TbsSql version the very first time and continue $this->ConfInfo(); } if (is_array($Txt)) { $Html = $this->_HtmlGrid($Txt); } else { $Html = '<div style="color: '.$Color.';">[TbsSql]'.nl2br(htmlentities($Txt)).'</div>'."\r\n"; } if ($this->_ModeHas(TBSSQL_CONSOLE)) { $Html = addslashes($Html); // apply to ('), ("), (\) and (null) $Html = str_replace(array("\n","\r","\t"),array('\n','\r','\t'),$Html); if ($this->InitCsl) { $this->InitCsl = false; $m1 = 100; $Html = ' <script type="text/javascript"> /* code generated by TbsSql */ var TbsSqlConsole = window.open("","TbsSql_Console","top='.$m1.',left="+(screen.width-screen.height+'.$m1.')+",width="+(screen.height-'.(2*$m1).')+",height="+(screen.height-'.(2*$m1).')+",resizable=yes,scrollbars=yes,location=no,status=no,toolbar=no,menubar=no,directories=no"); TbsSqlConsole.document.body.innerHTML = ""; // clear the console when the parent window is actualized TbsSqlConsole.document.write(\'<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>TbsSQL Console</title></head><body>'.$Html.'</body></html>\'); TbsSqlConsole.focus(); </script> '; } else { $Html = '<script type="text/javascript">/* code generated by TbsSql */ TbsSqlConsole.document.body.innerHTML += "'.$Html.'";</script>'; } } echo $Html; flush(); return false; } function _ModeHas($Option) { return (($this->Mode & $Option)===$Option); } function _ModeDecompose($Mode, $Options ) { // decompose the mode in options // string mode if (is_string($Mode)) { if (isset($Options[$Mode])) { return $Options[$Mode]; } else { return $Mode; } } // duration if ($Options===false) { $Options = array('TBSSQL_1WEEK'=>TBSSQL_1WEEK, 'TBSSQL_1DAY'=>TBSSQL_1DAY, 'TBSSQL_1HOUR'=>TBSSQL_1HOUR, 'TBSSQL_1MINUTE'=>TBSSQL_1MINUTE, 'TBSSQL_ALWAYS'=>TBSSQL_ALWAYS, 'TBSSQL_NOCACHE'=>TBSSQL_NOCACHE, 'TBSSQL_DISABLED'=>TBSSQL_DISABLED); // order must be respected if (in_array($Mode, $Options, true)) return array_search($Mode, $Options, true); $x = array(); $remain = $Mode; foreach ($Options as $name=>$duration) { if ( ($duration!==false) && ($duration>0) && ($remain>=$duration) ) { $r = ($remain % $duration); $n = intval(($remain-$r)/$duration); $remain = $remain - ($n*$duration); $x[] = ($n==1) ? $name : $n.'*'.$name; } } if (count($x)==0) return $Mode.' (minutes)'; if ($remain>0) $x[] = $remain.' (minutes)'; return implode(' + ', $x); } // numeric $x = array(); $remain = $Mode; foreach ($Options as $opt=>$name) { if ( ($opt!=0) && (($Mode & $opt)===$opt) ) { $x[] = $name; $remain = $remain - $opt; } } if ($remain!=0) $x[] = $remain; if (count($x)==0) { if (isset($Options[0])) { $x[] = $Options[0]; } else { $x[] = 0; } } return implode(' + ', $x); } function _TakeVar($VarName,$Key,&$Target,$MustBe=true) { // Read gloabl variables for automatic connexion if (isset($GLOBALS[$VarName][$Key])) { $Target = $GLOBALS[$VarName][$Key]; } elseif ($MustBe) { $this->_Message('[Error]: Automatic Connection failed because the item \''.$Key.'\' is not found in the global variable \''.$VarName.'\'.'); return false; } return true; } function _ObjCheck($Arg0, $ArgLst) { // Check if an object conversion is asked if (is_string($Arg0)) { if( (strpos($Arg0,' ')===false) && (strpos($Arg0,"\r")===false) && (strpos($Arg0,"\n")===false) && (count($ArgLst)>1) ) return true; } elseif (is_object($Arg0)) { return true; } return false; } function _ObjConversion(&$Data, $ObjRef) { // Convert the records from arrays to objects // Note: foreach() is twice faster than array_walk() for both record and column loops. if (is_object($ObjRef)) { foreach ($Data as $idx => $rec) { $obj = clone($ObjRef); foreach ($rec as $col => $val) {$obj->$col = $val;} $Data[$idx] = $obj; } } else { if ($ObjRef==='array') return; if (($ObjRef==='') || ($ObjRef==='object')) $ObjRef = 'stdClass'; foreach ($Data as $idx => $rec) { $obj = new $ObjRef; // the class name foreach ($rec as $col => $val) {$obj->$col = $val;} $Data[$idx] = $obj; } } } function _SqlDateFrmDb($Date,$FrmMode) { // Prepare the date item before to merge it in an SQL query if (is_string($Date)) { if (!isset($this->_SqlDateParseOk)) $this->_SqlDateParseOk = function_exists('date_parse'); // date_parse() is available since PHP 5.2 if ($this->_SqlDateParseOk) { $x = date_parse($Date); if (($x===false) || ($x['warning_count']>0) || ($x['error_count']>0)) { $this->_Message('[Error]: the date argument (string) \''.$Date.'\' is not recognized as a valide date. It will be merged as is in the SQL query.'); $Mode = 0; // The date will be passed as is to the SQL $x = $Date; } else { $x = array( 'y'=>$x['year'] , 'm'=>$x['month'] , 'd'=>$x['day'] , 'h'=>intval($x['hour']) , 'i'=>intval($x['minute']) , 's'=>intval($x['second']), 'f'=>intval($x['fraction'])); // intval() helps to force false to 0 } } else { $x = strtotime($Date); if (($x===-1) || ($x===false)) { $this->_Message('[Error]: the date argument (string) \''.$Date.'\' is not recognized as a valide date. This can happens on 32bit systems for dates over 2038-01-19. TbsSQL can workaround this date limit if you use PHP>=5.2'); $Mode = 0; // The date will be passed as is to the SQL $x = $Date; } else { $x = $this->_SqlDateInfo($x); } } } elseif (is_int($Date) || is_float($Date)) { // It's a timestamp $x = $this->_SqlDateInfo($Date); } elseif (is_array($Date)) { // It's an array supported by TbsSQL $x = $Date; if (!isset($x['y'])) { $this->_Message('[Error]: the date argument is an array but has no key \'y\'. The value will be replaced with the current date-time.'); $x = $this->_SqlDateInfo(time()); } if (!isset($x['m'])) $x['m'] = 1; if (!isset($x['d'])) $x['d'] = 1; if (!isset($x['h'])) $x['h'] = 0; if (!isset($x['i'])) $x['i'] = 0; if (!isset($x['s'])) $x['s'] = 0; } else { $x = ''; // avoid a PHP error notice $this->_Message('[Error]: the date argument can not be recognized as date: '.var_export($Date,true)); } return $this->_Dbs_Date($x,$FrmMode); } function _SqlDateInfo($Timestamp) { // return a date info array from a timestamp $x = getdate($Timestamp); return array( 'y'=>$x['year'] , 'm'=>$x['mon'] , 'd'=>$x['mday'] , 'h'=>$x['hours'] , 'i'=>$x['minutes'] , 's'=>$x['seconds'], 'f'=>0.0); } function _SqlDateFrmStd($d, $s_date, $s_ent=false, $s_time=false, $s_frac=false, $s_frac_nbr=3) { // useful function for common date formating, to be used in method _Dbs_Date(); $x = $d['y'].$s_date.str_pad($d['m'],2,'0',STR_PAD_LEFT).$s_date.str_pad($d['d'],2,'0',STR_PAD_LEFT); if ($s_ent!==false) { // time is added to the date $x .= $s_ent.str_pad($d['h'],2,'0',STR_PAD_LEFT).$s_time.str_pad($d['i'],2,'0',STR_PAD_LEFT).$s_time.str_pad($d['s'],2,'0',STR_PAD_LEFT); if ($s_frac!==false) $x .= $s_frac.substr(number_format($d['f'],$s_frac_nbr,'.',''),2); // fraction of seconds is added to the date-time } return $x; } function _SqlProtect($ArgLst, $SqlPos=0, $Normal=true) { // Replace items (%i% , @i@ , #i#, ~i~) with the corresponding protected values $Sql = $ArgLst[$SqlPos]; $IdxMax = count($ArgLst) - 1; $ChrLst = array('%','@','#','~'); for ($i=$SqlPos+1;$i<=$IdxMax;$i++) { for ($t=0;$t<=1;$t++) { if ($t==0) { // Scann normal tags $ValIsNull = false; $iz = ''.$i; } else { // Scann nullable tags $ValIsNull = ( is_null($ArgLst[$i]) or ($ArgLst[$i]===false) ); $iz = '['.$i.']'; } foreach ($ChrLst As $Chr) { $tag = $Chr.$iz.$Chr; if (strpos($Sql,$tag)!==false) { if ($ValIsNull) { $x = $this->SqlNull; } elseif ($Chr==='%') { $x = $this->_Dbs_ProtectStr(''.$ArgLst[$i]); // Simple value } elseif ($Chr==='@') { $x = $x = '\''.$this->_Dbs_ProtectStr(''.$ArgLst[$i]).'\''; // String value } elseif ($Chr==='#') { $x = $x = $this->_SqlDateFrmDb($ArgLst[$i],1); // Date value } elseif ($Chr==='~') { $x = $x = $this->_SqlDateFrmDb($ArgLst[$i],2); // Date and time value } $Sql = str_replace($tag,$x,$Sql) ; } } } } if ($Normal) { if ($this->_ModeHas(TBSSQL_DEBUG)) { $this->LastSql = $Sql; } elseif ($this->_ModeHas(TBSSQL_TRACE)) { $this->_Message('[SQL]: '.$Sql,'#663399'); } } return $Sql; } function _GetDataFromDb($Sql, &$Data, $OnlyFirstRow=false) { $RsId = $this->_Dbs_RsOpen($Sql); if ($RsId===false) return $this->_SqlError($this->Id); $Data = array(); if ($OnlyFirstRow) { $r = $this->_Dbs_RsFetch($RsId); if ($r!==false) $Data[] = $r; } else { while ($r = $this->_Dbs_RsFetch($RsId)) { $Data[] = $r; } } $this->_Dbs_RsClose($RsId); if ($this->_ModeHas(TBSSQL_GRID)) $this->_Message($Data); return true; } function _ItemFoundCol($Row, &$col1, &$col2) { if ($Row===false) return false; $col_lst = array_keys($Row); $col1 = $col_lst[0]; $col2 = isset($col_lst[1]) ? $col_lst[1] : false; return true; } function _ItemAdd(&$Data, $Row, $col1, $col2) { if ($col2===false) { $Data[] = $Row[$col1]; } else { $Data[$Row[$col1]] = $Row[$col2]; } } // trace functions // --------------- function _HtmlGrid($Data) { $nl = "\n"; $nbsp = '&nbsp;'; $firstrow = true; $row_bg = array(0=>'#faebd7', 1=>'#fff8dc'); $row_idx = 0; $html = $nl.'<table border="1" cellspacing="0" cellpadding="2">'; foreach ($Data as $id => $row) { if ($firstrow) { $html .= $nl.' <tr bgcolor="#999999"><td>#</td>'; foreach ($row as $col => $val) $html .= '<td>'.str_replace(' ',$nbsp,$col).'</td>'; $html .= '</tr>'; $firstrow = false; } $html .= $nl.' <tr bgcolor="'.$row_bg[$row_idx].'"><td>'.$id.'</td>'; foreach ($row as $col => $val) { $x = str_replace(' ',$nbsp,strval($val)); if ($x==='') $x = $nbsp; $html .= '<td>'.$x.'</td>'; } $html .= '</tr>'; $row_idx++; if ($row_idx>1) $row_idx=0; } if ($firstrow) { $html .= $nl.' <tr bgcolor="'.$row_bg[0].'"><td>No data</td></tr>'; } $html .= $nl.'</table>'; return $html; } function _TraceGetTime() { // return the current timer in milli-seconds $x = microtime(); $p = strpos($x,' '); if ($p===false) return (float)0; $x = substr($x,$p+1).substr($x,1,$p); return (float)$x; } // cache functions // --------------- function _CacheFilePath($Sql) { return $this->CacheDir.'/cache_tbssql_'.md5($Sql).$this->CacheSuffix.'.php'; // we save it as a PHP file in order to hide the contents from web users } function _CacheTryRetrieve($Sql, &$Data) { // Try to retrieve data from the cache file. Return true if $Data contains the data from the cache. // at this point $this->_CacheCurrSql is always false $this->_CacheCurrSql = false; // for security // check if cache is enabled if ($this->TempCacheTimeout===false) { if (($this->CacheTimeout===false) || ($this->CacheTimeout===TBSSQL_NOCACHE)) return false; $timeout = $this->CacheTimeout; } else { $timeout = $this->TempCacheTimeout; $this->TempCacheTimeout = false; if ($timeout===TBSSQL_NOCACHE) return false; } // $this->_CacheCurrFile = $this->_CacheFilePath($Sql); $now = time(); //echo 'debug CacheTryRetrieve : timout= '.$timeout.', cache file='.date('Y-m-d h:i:s',@filemtime($this->_CacheCurrFile)).' , limit='.date('Y-m-d h:i:s',@filemtime($this->_CacheCurrFile)+60*$timeout).' , now='.date('Y-m-d h:i:s',$now)."<br>\r\n"; if ( file_exists($this->_CacheCurrFile) && ($now<=(filemtime($this->_CacheCurrFile)+60*$timeout)) ) { // retrieve the data // echo 'debug CacheTryRetrieve : cache still current'."<br>\r\n"; include($this->_CacheCurrFile); // set $CacheSql and $Data if ($Sql===$CacheSql) { if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('[Cache]: Data retrieved from cache file '.$this->_CacheCurrFile,'#060'); if ($this->_ModeHas(TBSSQL_GRID)) $this->_Message($Data); return true; } else { // It can happens very rarely that two different SQL queries have the same md5, with this chech we are sure to have to good result if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('[Cache]: Data not retrieved from cache file '.$this->_CacheCurrFile.' because SQL is different.','#060'); return false; } } //echo 'debug CacheTryRetrieve : cache to be updated.'."<br>\r\n"; $this->_CacheCurrSql = $Sql; return false; } function _CacheUpdate($Data) { // Update the cache $fid = @fopen($this->_CacheCurrFile, 'w'); if ($fid===false) { $this->_Message('[Error]: The cache file '.$this->_CacheCurrFile.' cannot be saved.'); $ok = false; } else { flock($fid,2); // acquire an exlusive lock fwrite($fid,'<?php $CacheSql='.var_export($this->_CacheCurrSql,true).'; '); fwrite($fid,'$Data='.var_export($Data,true).';'); flock($fid,3); // release the lock fclose($fid); //echo 'debug CacheTryUpdate : cache file='.date('Y-m-d h:i:s',filemtime($this->_CacheCurrFile)).' end='.date('Y-m-d h:i:s',$cache_end).' , now='.date('Y-m-d h:i:s',time()).' , ok='.var_export($ok,true)."<br>\r\n"; if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('[Cache]: Data saved in cache file '.$this->_CacheCurrFile,'#060'); $ok = true; } $this->_CacheCurrSql = false; if ($this->CacheAutoClear!==false) { $this->_CacheTryClearDir(); $this->CacheAutoClear = false; // only one try per script call is enought, no need to check for each SQL query } return $ok; } function _CacheTryClearDir() { // Try to delete too old cache files $check_file = $this->CacheDir.'/cache_tbssql_info.php'; // if the check file does not exist yet, we create it if (!file_exists($check_file)) { touch($check_file); return; } // if the check file is too young, we do not clear $limit = time()-60*$this->CacheAutoClear; if (filemtime($check_file)>$limit) return; // clear the directory $lst = array(); $dir = opendir($this->CacheDir); $pref = 'cache_tbssql_'; $suff = $this->CacheSuffix.'.php'; $pref_len = strlen($pref); $suff_len = strlen($suff); $file_len = $pref_len+strlen(md5(''))+$suff_len; while ($file = readdir($dir)) { if ( (strlen($file)==$file_len) && (substr($file,0,$pref_len)===$pref) && (substr($file,-$suff_len)===$suff) ) { $fullpath = $this->CacheDir.'/'.$file; if (filemtime($fullpath)<=$limit) $lst[] = $fullpath; } } closedir($dir); foreach ($lst as $fullpath) { unlink($fullpath); } touch($check_file); if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('[Cache]: CacheAutoClear has deleted '.count($lst).' old cache files from directory '.$this->CacheDir,'#060'); } // ------------------------------- // Specific to the Database System // ------------------------------- // Database Engine: Windows or Linux ODBC // Version 1.03, 2010-06-10, Skrol29 function _Dbs_Prepare() { $this->Version .= ' for OBDC (Windows or Linux)'; } function _Dbs_Connect($srv,$uid,$pwd,$db,$drv) { // Should set $this->Id, value false means connection failed. // Retrieve the list of parameters given in the connection string if ( (strpos($srv,';')===false) and (strpos($srv,'=')===false) ) { // A sigle name is given as server name $str = 'SERVER='.$srv; } else { // several parameters ar given as server name => it taken as a string connection $str = trim($srv); } $prm_lst = array(); $z = explode(';',$str); foreach ($z as $x) { $x = trim($x); $p = strpos($x,'='); if ($p===false) { $prm_lst[strtoupper($x)] = ''; } else { $prm_lst[strtoupper(substr($x,0,$p))] = substr($x,$p+1); } } unset($z); // Complete the connextion string. /* if (!isset($prm_lst['DRIVER'])) { // Parameter DRIVER must exist if parameter DSN is not defined if ( ($drv=='') and (!isset($prm_lst['DSN'])) ) { if (strtolower(substr(PHP_OS,0,3))=='win') { $drv = '{SQL Server}'; // Windows OS } else { $drv = 'FreeTDS'; // Linux OS: There are only two SQL Server drivers for UnixODBC: Easysoft (driver='Easysoft ODBC-SQL Server') and FreeTDS } } if ($drv!=='') $str .= ';DRIVER='.$drv; // Optional if a DSN is defined. } */ if ( (!isset($prm_lst['DATABASE'])) and ($db!=='') ) $str .= ';DATABASE='.$db; // Parameter DATABASE is a priority over the DSN parameters. if ((count($prm_lst)==1) and (substr($str,-1,1)!=';') ) $str .= ';'; // If it is a connextion string then at least on semilicon must be present (tested with for 'Easysoft ODBC-SQL Server' driver) // Information, must be the same for any database type if ($this->Mode==TBSSQL_DEBUG) $this->LastSql = 'Connection String='.$str; if ($this->Mode==TBSSQL_TRACE) $this->_Message('Trace Connection String: '.$str,'#663399'); $this->Id = odbc_connect($str,$uid,$pwd); // do not use @ before the function because the non installation of the extension on Linux makes a fatal error. } function _Dbs_Close() { odbc_close($this->Id); } function _Dbs_Error($ObjId) { if ($ObjId===false) { return @odbc_errormsg(); } else { return @odbc_errormsg($ObjId); } } function _Dbs_RsOpen($Sql, $Exec=false) { $RsId = @odbc_exec($this->Id,$Sql); if ($RsId!==false) { $this->ColumnLst = array(); $iMax = odbc_num_fields($RsId); for ($i=1;$i<=$iMax;$i++) { $this->ColumnLst[$i] = ''.odbc_field_name($RsId,$i); } } return $RsId; } function _Dbs_RsFetch(&$RsId) { if (odbc_fetch_row($RsId)) { $Rec = array(); foreach ($this->ColumnLst as $col_id=>$col_name) { $Rec[$col_name] = odbc_result($RsId,$col_id); } } else { $Rec = false; } return $Rec; } function _Dbs_RsClose(&$RsId) { return @odbc_free_result($RsId); } function _Dbs_ProtectStr($Txt) { return str_replace('\'','\'\'',$Txt); } function _Dbs_Date($DateInfo,$FrmMode) { switch ($FrmMode) { case 1: // Date only return '{d \''.$this->_SqlDateFrmStd($DateInfo, '-').'\'}'; // Canonical ODBC date, works both for stored-procedures and functions case 2: // Date and time return '{ts \''.$this->_SqlDateFrmStd($DateInfo, '-', ' ', ':', '.', 3).'\'}'; // only 3 digits for the milliseconds for ODBC case 0: // Value is a string return '\''.$this->_Dbs_ProtectStr($DateInfo).'\''; default: // Error in date recognization return '\'0000-00-00\''; } } function _Dbs_LastRowId($Info=false) { $this->_Message("This version of TbsSQL for ODBC (any database support) doesn't allow to use the method LastRowId() because there is no ODBC canonical function to perform this feature. You have to use the SQL function of the database instead."); } function _Dbs_AffectedRows() { $this->_Message("This version of TbsSQL for ODBC (any database support) doesn't allow to use the method AffectedRows() because there is no ODBC canonical function to perform this feature. You have to use the SQL function of the database instead."); $this->_Message(""); } }