2 /*****************************************************************
4 Description : Routines to access MySQL, SQL Server, Oracle, & ODBC databases
6 Author : Matt Brown (dowdybrown@yahoo.com)
7 Copyright (C) 2006-2008 Matt Brown
9 Rico is licensed under the Apache License, Version 2.0 (the "License"); you may not use this
10 file except in compliance with the License. You may obtain a copy of the License at
11 http://www.apache.org/licenses/LICENSE-2.0
12 ******************************************************************/
15 //********************************************************************************************************
16 // Parse a SQL statement
17 //********************************************************************************************************
28 // -------------------------------------------------------------
29 // Rebuilds a SQL select statement that was parsed by ParseSelect
30 // -------------------------------------------------------------
34 if ($this->IsDistinct) $sqltext.="DISTINCT ";
35 $sqltext.=$this->UnparseColumnList()." FROM ".$this->FromClause;
36 if (!empty($this->WhereClause)) {
37 $sqltext.=" WHERE ".$this->WhereClause;
39 if (is_array($this->arGroupBy)) {
40 if (count($this->arGroupBy) > 0) {
41 $sqltext.=" GROUP BY ".implode(",",$this->arGroupBy);
44 if (!empty($this->HavingClause)) {
45 $sqltext.=" HAVING ".$this->HavingClause;
47 if (is_array($this->arOrderBy)) {
48 if (count($this->arOrderBy) > 0) {
49 $sqltext.=" ORDER BY ".implode(",",$this->arOrderBy);
55 function UnparseSelect() {
56 return $this->Unparse();
59 function UnparseSelectDistinct() {
60 $this->IsDistinct=true;
61 return $this->Unparse();
64 function UnparseDistinctColumn($colnum) {
65 $sqltext="SELECT DISTINCT ".$this->UnparseColumn($colnum)." FROM ".$this->FromClause;
66 if (!empty($this->WhereClause)) {
67 $sqltext.=" WHERE ".$this->WhereClause;
69 $sqltext.=" ORDER BY ".$this->UnparseColumn($colnum);
73 function UnparseColumn($i) {
74 $s=$this->arSelList[$i];
75 if (!empty($this->arSelListAs[$i])) {
76 $s.=" AS ".$this->arSelListAs[$i];
81 function UnparseColumnList() {
82 if (empty($this->arSelList)) return "";
83 $sqltext=$this->UnparseColumn(0);
84 for ($i=1; $i<count($this->arSelList); $i++) {
85 $sqltext.=",".$this->UnparseColumn($i);
90 function DebugPrint() {
91 echo "<p>Parse Result:";
92 echo "<table border='1'>";
93 if ($this->IsDistinct) echo "<tr valign='top'><td>DISTINCT<td> ";
94 echo "<tr valign='top'><td>COLUMNS:<td><ol>";
95 if (!empty($this->arSelList)) {
96 for ($i=0; $i<count($this->arSelList); $i++) {
97 echo "<li>".$this->UnparseColumn($i);
100 echo "</ol><tr valign='top'><td>FROM:<td>" . $this->FromClause;
101 if (!empty($this->WhereClause)) {
102 echo "<tr valign='top'><td>WHERE:<td>".$this->WhereClause;
104 if (is_array($this->arGroupBy)) {
105 if (count($this->arGroupBy) > 0) {
106 echo "<tr valign='top'><td>GROUP BY:<td>".implode("<br>",$this->arGroupBy);
109 if (!empty($this->HavingClause)) {
110 echo "<tr valign='top'><td>HAVING:<td>".$this->HavingClause;
112 if (is_array($this->arOrderBy)) {
113 if (count($this->arOrderBy) > 0) {
114 echo "<tr valign='top'><td>ORDER BY:<td>".implode("<br>",$this->arOrderBy);
121 $this->arSelList=array();
122 $this->arSelListAs=array();
123 $this->arGroupBy=array();
124 $this->arOrderBy=array();
125 $this->FromClause=NULL;
126 $this->WhereClause=NULL;
127 $this->HavingClause=NULL;
128 $this->IsDistinct=false;
130 // -------------------------------------------------------------
131 // Parse a SQL select statement into its major components
135 // 3) more than one space between "group" and "by", or "order" and "by"
136 // If distinct is specified, it will be part of the first item in arSelList
137 // -------------------------------------------------------------
139 function ParseSelect($sqltext) {
143 $sqltext=str_replace("\n"," ",$sqltext);
144 $sqltext=" ".str_replace("\r"," ",$sqltext)." SELECT ";
145 // SELECT suffix forces last curfield to be saved
150 for ($i=0; $i<$l; $i++) {
151 $ch=substr($sqltext,$i,1);
153 if ($ch == $endquote) {
154 if ($endquote=="'" && substr($sqltext,$i,2) == "''") {
164 elseif ($ch == "'" || $ch == "\"" || $ch == "`") {
169 elseif ($ch == "[") {
174 elseif ($ch == "(") {
178 elseif ($ch == ")") {
179 if ($parencnt == 0) {
181 // sql statement has a syntax error
186 elseif ($parencnt > 0) {
189 elseif ($ch == ",") {
192 array_push($this->arSelList, $curfield);
193 array_push($this->arSelListAs, NULL);
197 $this->arSelListAs[count($this->arSelList)-1]=$curfield;
202 array_push($this->arGroupBy, $curfield);
206 array_push($this->arOrderBy, $curfield);
214 elseif ($ch == " ") {
215 $j=strpos($sqltext," ",$i+1);
222 if (strtoupper(substr($sqltext,$j+1,3))=="BY ") $j+=3;
223 $nexttoken=strtoupper(substr($sqltext,$i+1,$j-$i-1));
224 //echo '<br>'.$nexttoken;
225 switch ($nexttoken) {
236 $this->AddColumn($curfield, NULL);
240 $this->arSelListAs[count($this->arSelList)-1]=$curfield;
243 case "FROM": $this->FromClause=$curfield; $curfield=''; break;
244 case "WHERE": $this->WhereClause=$curfield; $curfield=''; break;
245 case "GROUP BY": array_push($this->arGroupBy, $curfield); $curfield=''; break;
246 case "HAVING": $this->HavingClause=$curfield; $curfield=''; break;
247 case "ORDER BY": array_push($this->arOrderBy, $curfield); $curfield=''; break;
254 if ($clause=="SELECT") {
255 $this->AddColumn($curfield, NULL);
259 } else if ($curfield != "") {
265 if ($clause=="SELECT") {
266 $this->IsDistinct=true;
269 } else if ($curfield != "") {
275 if ($curfield != "") {
289 // -------------------------------------------------------------
290 // Add column to select list
291 // -------------------------------------------------------------
292 function AddColumn($ColumnSql, $ColumnName) {
293 array_push($this->arSelList, $ColumnSql);
294 array_push($this->arSelListAs, $ColumnName);
297 // -------------------------------------------------------------
298 // Add a join to the from clause
299 // -------------------------------------------------------------
300 function AddJoin($JoinClause) {
301 if (preg_match("/ join /i",$this->FromClause)) {
302 $this->FromClause="(".$this->FromClause.")";
303 // required by Access
305 $this->FromClause.=" ".$JoinClause;
308 function SplitSortSpec($sortspec, &$sortcol, &$sortdir) {
309 $sortspec=strtoupper($sortspec);
310 if (substr($sortspec,-3) == "ASC") {
311 $sortcol=trim(substr($sortspec,0,-3));
314 elseif (substr($sortspec,-4) == "DESC") {
315 $sortcol=trim(substr($sortspec,0,-4));
319 $sortcol=trim($sortspec);
324 function FindSortColumn($sortspec) {
325 $this->SplitSortSpec($sortspec, $findcol, $finddir);
326 for ($i=0; $i<=count($this->arOrderBy)-1; $i++) {
327 $this->SplitSortSpec($this->arOrderBy[$i], $sortcol, $sortdir);
328 if ($sortcol == $findcol) {
335 // -------------------------------------------------------------
336 // Add sort criteria to the beginning of the order by clause
337 // -------------------------------------------------------------
338 function AddSort($NewSort) {
339 $colidx=$this->FindSortColumn($NewSort);
341 for ($i=$colidx; $i>0; $i--) {
342 $this->arOrderBy[$i]=$this->arOrderBy[$i-1];
344 $this->arOrderBy[0]=$NewSort;
347 array_unshift($this->arOrderBy, $NewSort);
351 // -------------------------------------------------------------
352 // Append sort criteria to the order by clause
353 // -------------------------------------------------------------
354 function AppendSort($NewSort) {
355 array_push($this->arOrderBy, $NewSort);
358 // -------------------------------------------------------------
359 // Add a condition to the where clause
360 // -------------------------------------------------------------
361 function AddWhereCondition($NewCondition) {
362 $this->AddCondition($this->WhereClause, $NewCondition);
365 // -------------------------------------------------------------
366 // Add a condition to the having clause
367 // -------------------------------------------------------------
368 function AddHavingCondition($NewCondition) {
369 $this->AddCondition($this->HavingClause, $NewCondition);
372 function AddCondition(&$Clause, $NewCondition) {
373 if (empty($NewCondition)) {
376 if (empty($Clause)) {
377 $Clause="(".$NewCondition.")";
380 $Clause.=" AND (".$NewCondition.")";
386 // -------------------------------------------------------------
387 // created by dbClass.GetColumnInfo()
388 // -------------------------------------------------------------
391 var $ColName,$Nullable,$ColType,$ColLength,$Writeable,$IsPKey;
394 // tested ok with MySQL 4 & 5
398 function dbClass_mysql($conn) { $this->conn=$conn; }
399 function HasError() { return mysql_errno()!=0; }
400 function ErrorMsg() { return mysql_error(); }
401 function Close() { return mysql_close($this->conn); }
402 function FreeResult($rsLookUp) { return mysql_free_result($rsLookUp); }
403 function RunQuery($sqltext) { $this->lastQuery=$sqltext; return mysql_query($sqltext,$this->conn); }
404 function NumFields($rsMain) { return mysql_num_fields($rsMain); }
405 function NumRows($rsMain) { return mysql_num_rows($rsMain); }
406 function FieldType($rsMain,$i) { return mysql_field_type($rsMain,$i); }
407 function FetchRow($rsMain,&$result) { $result=mysql_fetch_row($rsMain); return ($result==false) ? false : true; }
408 function FetchAssoc($rsMain,&$result) { $result=mysql_fetch_assoc($rsMain); return ($result==false) ? false : true; }
409 function FetchArray($rsMain,&$result) { $result=mysql_fetch_array($rsMain,MYSQL_NUM); return ($result==false) ? false : true; }
410 function AffectedRows($rsMain) { return mysql_affected_rows($this->conn); }
411 function Seek($rsMain,$offset) { return mysql_data_seek($rsMain,$offset); }
412 function RunParamQuery($query, $phs = array()) {
413 foreach ($phs as $ph) { // from php.net
415 $ph = "'" . mysql_real_escape_string($ph) . "'";
419 $query = substr_replace($query, $ph, strpos($query, '?'), 1);
421 $this->lastQuery=$query;
422 return mysql_query($query,$this->conn);
424 function GetColumnInfo($TableName) {
425 $rsMain=$this->RunQuery("SHOW COLUMNS FROM ".$TableName);
426 if (!$rsMain) return null;
428 while($this->FetchAssoc($rsMain,$row)) {
429 $colinfo=new dbColumn;
430 $colinfo->IsPKey=($row["Key"]=="PRI");
431 $colinfo->ColName=$row["Field"];
432 $colinfo->ColType=$row["Type"];
433 $colinfo->Nullable=($row["Null"]=="YES");
434 if (preg_match("/\((\d+)\)/", $row["Type"], $matches))
435 $colinfo->ColLength=$matches[1];
437 $colinfo->ColLength=0;
438 $colinfo->Writeable=($row["Extra"] != 'auto_increment');
439 array_push($arColumns, $colinfo);
441 $this->FreeResult($rsMain);
444 function GetTableList($TableType) {
445 if ($TableType!='VIEW') $TableType='BASE TABLE';
446 $rsMain=$this->RunQuery("SHOW FULL TABLES WHERE Table_type='".$TableType."'");
447 if (!$rsMain) return null;
449 while($this->FetchRow($rsMain,$row)) {
450 array_push($arTables, $row[0]);
452 $this->FreeResult($rsMain);
455 function Concat($arStrings) {
456 return "concat(".implode(",",$arStrings).")";
458 function Convert2Char($s) {
459 return $s; // implicit conversion
461 function SqlDay($s) {
462 return "dayofmonth(".$s.")";
464 function SqlMonth($s) {
465 return "month(".$s.")";
467 function SqlYear($s) {
468 return "year(".$s.")";
470 function CurrentTime() {
471 return "LOCALTIMESTAMP";
476 // tested ok with Oracle XE
480 function dbClass_oci($conn) { $this->conn=$conn; }
481 function HasError() { return is_array(ocierror()); }
482 function ErrorMsg() { $e=ocierror(); return is_array($e) ? $e['message'] : ''; }
483 function Close() { return ocilogoff($this->conn); }
484 function FreeResult($rsLookUp) { return ocifreestatement($rsLookUp); }
485 function RunQuery($sqltext) { $this->lastQuery=$sqltext; $stmt=ociparse($this->conn, $sqltext); ociexecute($stmt); return $stmt; }
486 function NumFields($rsMain) { return ocinumcols($rsMain); }
487 function NumRows($rsMain) { return -1; }
488 function FieldType($rsMain,$i) { return ocicolumntype($rsMain,$i+1); }
489 function FetchRow($rsMain,&$result) { return ocifetchinto($rsMain,$result,OCI_NUM); }
490 function FetchAssoc($rsMain,&$result) { return ocifetchinto($rsMain,$result,OCI_ASSOC); }
491 function FetchArray($rsMain,&$result) { return ocifetchinto($rsMain,$result,OCI_NUM); }
492 function AffectedRows($rsMain) { return (is_resource($rsMain) ? ocirowcount($rsMain) : false); }
493 function Seek($rsMain,$offset) {
494 for($i=0; $i<$offset; $i++) ocifetchrow($rsMain);
496 function RunParamQuery($query, $phs = array()) {
497 foreach ($phs as $ph) { // from php.net
498 $ph = isset($ph) ? "'" . str_replace("'","''",$ph) . "'" : "NULL";
499 $query = substr_replace($query, $ph, strpos($query, '?'), 1);
501 $this->lastQuery=$query;
502 return $this->RunQuery($query);
504 function GetColumnInfo($TableName) {
505 $TableName=strtoupper($TableName);
506 $rsMain=$this->RunQuery("select * from col WHERE tname='$TableName' order by colno");
507 if (!$rsMain) return null;
509 while($this->FetchAssoc($rsMain,$row)) {
510 $colinfo=new dbColumn;
511 $colinfo->IsPKey=false;
512 $colinfo->ColName=$row["CNAME"];
513 $colinfo->ColType=$row["COLTYPE"];
514 $colinfo->Nullable=($row["NULLS"]=="NULL");
515 $colinfo->ColLength=$row["WIDTH"];
516 $colinfo->Writeable=true; // need to figure out where to find this
517 array_push($arColumns, $colinfo);
518 //echo "<p>GetColumnInfo: ".$row["CNAME"].' - '.$row["COLTYPE"]."</p>";
520 $this->FreeResult($rsMain);
521 $sql = "SELECT b.column_name FROM USER_CONSTRAINTS a, USER_CONS_COLUMNS b WHERE (b.table_name='$TableName') AND (a.table_name='$TableName') AND (a.constraint_type = 'P') AND (a.constraint_name = b.constraint_name)";
522 $rsMain = $this->RunQuery($sql);
524 while($this->FetchRow($rsMain,$row)) {
526 //echo "<p>GetColumnInfo pk: ".$colname."</p>";
527 for($i=0; $i<count($arColumns); $i++) {
528 if ($arColumns[$i]->ColName==$colname) {
529 $arColumns[$i]->IsPKey=true;
534 $this->FreeResult($rsMain);
538 function Concat($arStrings) {
539 return implode(" || ",$arStrings);
541 function Convert2Char($s) {
542 return "cast(".$s." as varchar2(20))";
544 function SqlDay($s) {
545 return "to_char(".$s.",'DD')";
547 function SqlMonth($s) {
548 return "to_char(".$s.",'MM')";
550 function SqlYear($s) {
551 return "to_char(".$s.",'YYYY')";
553 function CurrentTime() {
554 return "LOCALTIMESTAMP";
559 // tested ok with MS SQL Server & MS Access
560 // Oracle works ok except for GetColumnInfo
564 function dbClass_odbc(&$dbc) { $this->dbc=$dbc; }
565 function HasError() { return odbc_error()!=''; }
566 function ErrorMsg() { return @odbc_error() . ' ' . @odbc_errormsg(); }
567 function Close() { return odbc_close(); }
568 function FreeResult($rsLookUp) { return odbc_free_result($rsLookUp); }
569 function RunQuery($sqltext) { $this->lastQuery=$sqltext; return odbc_exec($this->dbc->dbMain,$sqltext); }
570 function NumFields($rsMain) { return odbc_num_fields($rsMain); }
571 function NumRows($rsMain) { return odbc_num_rows($rsMain); }
572 function FieldType($rsMain,$i) { return odbc_field_type($rsMain,$i+1); }
573 function FetchRow($rsMain,&$result) { $rc=odbc_fetch_into($rsMain,$result); return ($rc==false) ? false : true; }
574 function FetchAssoc($rsMain,&$result) { $result=odbc_fetch_array($rsMain); return ($result==false) ? false : true; }
575 function FetchArray($rsMain,&$result) { $rc=odbc_fetch_into($rsMain,$result); return ($rc==false) ? false : true; }
576 function AffectedRows($rsMain) { return odbc_num_rows($rsMain); }
577 function Seek($rsMain,$offset) {
578 for($i=0; $i<$offset; $i++) odbc_fetch_row($rsMain);
580 function RunParamQuery($query, $phs = array()) {
581 // odbc_prepare/odbc_execute chokes on this: SELECT * FROM (SELECT * FROM orders WHERE ShipCountry=?) AS rico_Main
582 // so that approach cannot be used
583 foreach ($phs as $ph) {
585 if (preg_match("/^\d\d\d\d-?\d\d-?\d\d(?:[T ](\d\d)(?::?(\d\d)(?::?(\d\d)(?:\.(\d+))?)?)?(Z|(?:([-+])(\d\d)(?::?(\d\d))?)?)?)?$/",$ph)) {
586 $ph = "{ts '".$ph."'}";
588 $ph = "'" . str_replace("'","''",$ph) . "'";
593 $query = substr_replace($query, $ph, strpos($query, '?'), 1);
595 $this->lastQuery=$query;
596 return odbc_exec($this->dbc->dbMain,$query);
598 function GetColumnInfo($TableName) {
599 switch ($this->dbc->Dialect) {
601 $qualifier=$this->dbc->dbDefault;
605 $qualifier=$this->dbc->dsn;
611 //echo "<p>GetColumnInfo: ".$qualifier.".".$schema.".".$TableName."</p>";
612 $rsMain=odbc_columns($this->dbc->dbMain, $qualifier, $schema, $TableName);
613 //odbc_result_all($rsMain);
614 if (!$rsMain) return null;
616 while($this->FetchAssoc($rsMain,$row)) {
617 if ($row["TABLE_NAME"]!=$TableName) continue;
618 $colinfo=new dbColumn;
619 //echo "<p>GetColumnInfo: ".$row["COLUMN_NAME"].':'.$row["TYPE_NAME"]."</p>";
620 $colinfo->ColName=$row["COLUMN_NAME"];
621 $colinfo->ColType=$row["TYPE_NAME"];
622 if (array_key_exists("PRECISION",$row)) {
623 $colinfo->ColLength=$row["PRECISION"];
624 } else if (array_key_exists("COLUMN_SIZE",$row)) {
625 $colinfo->ColLength=$row["COLUMN_SIZE"];
627 $colinfo->Nullable=($row["NULLABLE"]=="YES");
628 $colinfo->IsPKey=false;
629 $colinfo->Writeable=($row["TYPE_NAME"] != 'int identity');
630 array_push($arColumns, $colinfo);
632 $this->FreeResult($rsMain);
633 //$rsMain=odbc_columnprivileges($this->dbc->dbMain, $qualifier, $schema, $TableName,"%");
634 //odbc_result_all($rsMain);
635 //$this->FreeResult($rsMain);
636 $rsMain=odbc_primarykeys($this->dbc->dbMain, $qualifier, $schema, $TableName);
638 while($this->FetchAssoc($rsMain,$row)) {
639 $colname=$row["COLUMN_NAME"];
640 //echo "<p>GetColumnInfo pk: ".$colname."</p>";
641 for($i=0; $i<count($arColumns); $i++) {
642 if ($arColumns[$i]->ColName==$colname) {
643 $arColumns[$i]->IsPKey=true;
648 $this->FreeResult($rsMain);
652 function Concat($arStrings) {
653 $cnt=count($arStrings);
656 case 1: return $arStrings[0];
658 $result="{fn concat(".$arStrings[0].",".$arStrings[1].")}";
659 for ($i=2; $i<$cnt; $i++) {
660 $result="{fn concat(".$result.",".$arStrings[$i].")}";
665 function Convert2Char($s) {
666 return "{fn CONVERT(" . $s . ",SQL_VARCHAR)}";
668 function SqlDay($s) {
669 return "{fn DAYOFMONTH(" . $s . ")}";
671 function SqlMonth($s) {
672 return "{fn MONTH(" . $s . ")}";
674 function SqlYear($s) {
675 return "{fn YEAR(" . $s . ")}";
677 function CurrentTime() {
678 return "{fn CURDATE()}";
686 function dbClass_mssql($conn) { $this->conn=$conn; }
687 function HasError() { return mssql_get_last_message()!=0; }
688 function ErrorMsg() { return mssql_get_last_message(); }
689 function Close() { return mssql_close($this->conn); }
690 function FreeResult($rsLookUp) { return mssql_free_result($rsLookUp); }
691 function RunQuery($sqltext) { $this->lastQuery=$sqltext; return mssql_query($sqltext,$this->conn); }
692 function NumFields($rsMain) { return mssql_num_fields($rsMain); }
693 function NumRows($rsMain) { return mssql_num_rows($rsMain); }
694 function FieldType($rsMain,$i) { return mssql_field_type($rsMain,$i); }
695 function FetchRow($rsMain,&$result) { $result=mssql_fetch_row($rsMain); return ($result==false) ? false : true; }
696 function FetchAssoc($rsMain,&$result) { $result=mssql_fetch_assoc($rsMain); return ($result==false) ? false : true; }
697 function FetchArray($rsMain,&$result) { $result=mssql_fetch_array($rsMain,MSSQL_NUM); return ($result==false) ? false : true; }
698 function AffectedRows($rsMain) { return mssql_rows_affected($rsMain); }
699 function Seek($rsMain,$offset) { return mssql_data_seek($rsMain,$offset); }
700 function RunParamQuery($query, $phs = array()) {
701 foreach ($phs as $ph) {
703 $ph = "'" . str_replace("'","''",$ph) . "'";
707 $query = substr_replace($query, $ph, strpos($query, '?'), 1);
709 $this->lastQuery=$query;
710 return mssql_query($query,$this->conn);
713 function GetColumnInfo($TableName) {
714 $TableName=strtoupper($TableName);
715 $rsMain=$this->RunQuery("exec sp_columns '$TableName'");
716 if (!$rsMain) return null;
719 while($this->FetchAssoc($rsMain,$row)) {
720 $colinfo=new dbColumn;
721 $colinfo->ColName=$row["COLUMN_NAME"];
722 $colinfo->ColType=$row["TYPE_NAME"];
723 if (array_key_exists("PRECISION",$row)) {
724 $colinfo->ColLength=$row["PRECISION"];
725 } else if (array_key_exists("LENGTH",$row)) {
726 $colinfo->ColLength=$row["LENGTH"];
728 $colinfo->Nullable=($row["NULLABLE"]==1);
729 $colinfo->IsPKey=false;
730 $colinfo->Writeable=(strtoupper($row["TYPE_NAME"]) != "INT IDENTITY");
731 array_push($arColumns, $colinfo);
732 $tableid = $row["Id_table"];
734 $this->FreeResult($rsMain);
736 $rsMain=$this->RunQuery("exec sp_pkeys '$TableName'");
738 while($this->FetchAssoc($rsMain,$row)) {
739 $colname=$row["COLUMN_NAME"];
740 for($i=0; $i<count($arColumns); $i++) {
741 if ($arColumns[$i]->ColName==$colname) {
742 $arColumns[$i]->IsPKey=true;
747 $this->FreeResult($rsMain);
752 function Concat($arStrings) {
753 return implode("+",$arStrings);
755 function Convert2Char($s) {
756 return "CAST(" . $s . " AS VARCHAR)";
758 function SqlDay($s) {
759 return "DAY(" . $s . ")";
761 function SqlMonth($s) {
762 return "MONTH(" . $s . ")";
764 function SqlYear($s) {
765 return "YEAR(" . $s . ")";
767 function CurrentTime() {
768 return "CURRENT_TIMESTAMP";
777 var $debug,$ConnTimeout,$CmdTimeout,$LockTimeout,$Provider;
783 // these are private:
784 var $dbMain,$DisplayFunc,$dbDefault;
786 // -------------------------------------------------------------
788 // -------------------------------------------------------------
789 function dbClassRico()
791 $this->Provider="localhost";
793 $this->ConnTimeout=30; // seconds
794 $this->LockTimeout=5000; // milliseconds
795 $this->DisplayErrors=true;
796 $this->CmdTimeout=120; // 2 minutes
797 $this->ErrMsgFmt="HTML";
798 $this->DisplayFunc="echo";
799 $this->Dialect="MySQL";
803 // -------------------------------------------------------------
804 // Class Destructor (only called if php5)
805 // -------------------------------------------------------------
806 function __destruct()
813 return $this->dbDefault;
816 //********************************************************************************************************
817 // If the database is down, then an explanation can be placed here
818 //********************************************************************************************************
819 function MaintenanceMsg()
824 function DisplayMsg($msg)
826 if (!empty($this->DisplayFunc))
828 if ($this->ErrMsgFmt=="HTML" && substr($msg,0,1)!="<")
830 $msg="<p>".htmlspecialchars(str_replace("\n","<br>",$msg))."</p>";
834 $msg=str_replace("\n"," ",$msg);
836 eval($this->DisplayFunc."(\"".$msg."\");");
840 function HandleError($msg)
842 if ($this->DisplayErrors)
844 $this->DisplayMsg($this->LastErrorMsg);
848 //********************************************************************************************************
849 // Checks if an error has occurred, and if so, displays a message & returns true
850 //********************************************************************************************************
851 function CheckForError($msg)
853 if (!$this->db->HasError()) return false;
854 $this->LastErrorMsg=$msg;
855 if (empty($this->ErrMsgFmt)) return true;
856 $this->HandleError($this->FormatErrorMsg($msg));
860 //********************************************************************************************************
861 // Attempts to connect to the Database. Returns true on success.
862 //********************************************************************************************************
863 function MySqlLogon($DefDB,$userid,$pw)
865 $this->Dialect="MySQL";
866 $this->dbDefault = $DefDB;
867 $this->dbMain = mysql_connect($this->Provider,$userid,$pw);
868 mysql_select_db($DefDB,$this->dbMain);
869 $this->db = new dbClass_mysql($this->dbMain);
870 if ($this->CheckForError("opening connection")) return false;
874 //********************************************************************************************************
875 // Attempts to connect to the Database. Returns true on success.
876 //********************************************************************************************************
877 function OracleLogon($sim,$user,$pw)
879 $this->Dialect="Oracle";
880 $this->dbDefault = $user;
881 $this->dbMain = ocilogon($user,$pw,$sim);
882 $this->db = new dbClass_oci($this->dbMain);
883 if ($this->CheckForError("opening connection")) return false;
884 $this->RunActionQuery("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'");
888 //********************************************************************************************************
889 // Attempts to connect to the Database. Returns true on success.
890 //********************************************************************************************************
891 function MSSqlLogon($servername,$DefDB,$user,$pw)
893 $this->Dialect="TSQL";
894 $this->dbDefault = $DefDB;
895 ini_set("mssql.datetimeconvert","Off");
896 $this->dbMain = mssql_connect($servername,$user,$pw);
897 if (!is_resource($this->dbMain)) {
898 $this->LastErrorMsg="Error while connecting to ".$servername;
901 $this->db = new dbClass_mssql($this->dbMain);
902 mssql_select_db($DefDB,$this->dbMain);
906 //********************************************************************************************************
907 // Attempts to connect to the Database. Returns true on success.
908 //********************************************************************************************************
909 function OdbcLogon($dsn,$DefDB,$userid,$pw)
912 $this->dbDefault = $DefDB;
913 $this->dbMain = odbc_connect($dsn,$userid,$pw,SQL_CUR_USE_ODBC);
914 if (!is_resource($this->dbMain)) {
915 $this->LastErrorMsg="Error while opening ODBC connection: " . odbc_error();
918 $this->db = new dbClass_odbc($this);
923 //********************************************************************************************************
924 // Close database connection
925 //********************************************************************************************************
927 if (is_resource($this->dbMain)) $this->db->Close();
928 $this->dbMain = NULL;
932 function CurrentTime() {
933 return $this->db->CurrentTime();
936 function Convert2Char($s) {
937 return $this->db->Convert2Char($s);
940 // returns SQL that converts a datetime value to a date in YYYY-MM-DD format
941 function SqlDate($s) {
942 return $this->Concat(array($this->SqlYear($s),"'-'",$this->SqlMonth($s),"'-'",$this->SqlDay($s)),false);
945 // returns SQL that converts a datetime value to the day-of-month
946 function SqlDay($s) {
947 return $this->db->SqlDay($s);
950 // returns SQL that converts a datetime value to the month number
951 function SqlMonth($s) {
952 return $this->db->SqlMonth($s);
955 // returns SQL that converts a datetime value to the year
956 function SqlYear($s) {
957 return $this->db->SqlYear($s);
960 // requires an active db connection when using MySQL
961 function addQuotes($s) {
962 if (get_magic_quotes_gpc())
963 $s = stripslashes($s);
965 switch ($this->Dialect) {
966 case "MySQL": return "'" . mysql_real_escape_string($s) . "'";
967 default: return "'".str_replace("'","''",$s)."'";
971 // returns SQL that concatenates an array of strings
972 function Concat($arStrings, $addQuotes) {
974 for ($i=0; $i<count($arStrings); $i++)
975 $arStrings[$i]=$this->addQuotes($arStrings[$i]);
977 return $this->db->Concat($arStrings);
980 //********************************************************************************************************
981 // Return a string containing an error message
982 // String format is based on ErrMsgFmt
983 //********************************************************************************************************
984 function FormatErrorMsg($ContextMsg)
986 switch ($this->ErrMsgFmt)
989 $function_ret="<p class=dberror id=dbError>Error! " . $this->db->ErrorMsg() ."</p>".
990 "<p class=dberror id=dbErrorDetail><u>Operation that caused the error:</u><br>".$ContextMsg."</p>";
993 $function_ret="Error! " . $this->db->ErrorMsg() ."\n\nOperation that caused the error:\n".$ContextMsg;
996 $function_ret="Error! " . $this->db->ErrorMsg() ." (".$ContextMsg.")";
999 return $function_ret;
1002 //********************************************************************************************************
1003 // Runs a query and moves to the first record.
1004 // Use only for queries that return records (no updates or deletes).
1005 // If the query generated an error then Nothing is returned, otherwise it returns a new recordset object.
1006 //********************************************************************************************************
1007 function RunQuery($sqltext) {
1008 $rsLookUp=$this->db->RunQuery($sqltext);
1009 if ($this->CheckForError($sqltext)) return null;
1010 if ($this->debug) $this->DisplayMsg($sqltext);
1015 //********************************************************************************************************
1016 // Runs a parameterized query (put ? in $sqltext to indicate where parameters should be inserted)
1017 // Use only for queries that return records (no updates or deletes).
1018 // If the query generated an error then null is returned, otherwise it returns a new recordset object.
1019 //********************************************************************************************************
1020 function RunParamQuery($sqltext, $arParams) {
1021 $rsLookUp=$this->db->RunParamQuery($sqltext, $arParams);
1022 if ($this->CheckForError($sqltext)) return null;
1023 if ($this->debug) $this->DisplayMsg($sqltext);
1028 //********************************************************************************************************
1029 // Safely close a recordset
1030 //********************************************************************************************************
1031 function rsClose($rsLookUp) {
1032 if (is_resource($rsLookUp)) $this->db->FreeResult($rsLookUp);
1036 //********************************************************************************************************
1037 // Runs a query and returns results from the first record in arData.
1038 // Returns true if arData is modified (ie. a record exists).
1039 // If the query generates an error then arData is left unchanged
1040 // returns arData as an array, fields indexed numerically
1041 //********************************************************************************************************
1042 function SingleRecordQuery($sqltext,&$arData)
1044 $rsMain=$this->RunQuery($sqltext);
1045 if (!$rsMain) return false;
1046 $success=$this->db->FetchArray($rsMain,$arData);
1047 $this->rsClose($rsMain);
1052 //********************************************************************************************************
1053 // Runs a query where no result set is expected (updates, deletes, etc)
1054 // - returns the number of records affected by the action query
1055 //********************************************************************************************************
1056 function RunActionQuery($sqltext)
1058 $rsMain=$this->db->RunQuery($sqltext);
1059 if ($this->CheckForError($sqltext)) {
1062 else if ($this->debug) {
1063 $this->DisplayMsg($sqltext);
1065 return $this->db->AffectedRows($rsMain);
1069 //********************************************************************************************************
1070 // Runs a query where no result set is expected (updates, deletes, etc)
1071 // - if an error occurs, then the message is returned in errmsg
1072 //********************************************************************************************************
1073 function RunActionQueryReturnMsg($sqltext,&$errmsg)
1075 $tmpDisplayErrors=$this->DisplayErrors;
1076 $this->DisplayErrors=false;
1077 $this->LastErrorMsg="";
1078 $function_ret=$this->RunActionQuery($sqltext);
1079 if (!empty($this->LastErrorMsg))
1081 $errmsg=$this->LastErrorMsg;
1083 $this->DisplayErrors=$tmpDisplayErrors;
1084 return $function_ret;
1088 //********************************************************************************************************
1089 // Takes a sql create (table or view) statement and performs:
1090 // 1) a conditional drop (if it already exists)
1092 // 3) grants select access to public (if not a temp table)
1094 // for views, all actions must occur on the default database for the connection
1095 //********************************************************************************************************
1096 function DropCreate($sqlcreate)
1098 $parsed=explode(" ",$sqlcreate);
1099 if (count($parsed) < 3) return; // error
1100 $sqltext="DROP ".$parsed[1]." ".$parsed[2];
1101 $this->RunActionQueryReturnMsg($sqltext,$dropmsg);
1102 $this->RunActionQuery($sqlcreate);
1105 //********************************************************************************************************
1106 // Returns a comma-separated list of column names that make up the primary key
1107 // Returns empty string if no primary key has been defined
1108 //********************************************************************************************************
1109 function PrimaryKey($TableName) {
1111 $arColumns=$this->GetColumnInfo($TableName);
1112 if (!is_array($arColumns)) return '';
1113 foreach ($arColumns as $colinfo) {
1114 if ($colinfo->IsPKey) {
1115 if ($keys!='') $keys.=',';
1116 $keys.=$colinfo->ColName;
1123 //********************************************************************************************************
1124 // Returns array of column info - one entry for each column in $TableName
1125 //********************************************************************************************************
1126 function GetColumnInfo($TableName) {
1127 return $this->db->GetColumnInfo($TableName);
1131 //********************************************************************************************************
1132 // Returns array of table/view names
1133 // $ObjectType is one of 'VIEW' or 'TABLE'
1134 //********************************************************************************************************
1135 function GetTableList($ObjectType) {
1136 return $this->db->GetTableList($ObjectType);
1140 //********************************************************************************************************
1141 // Add a condition to a where or having clause
1142 //********************************************************************************************************
1143 function AddCondition(&$WhereClause,$NewCondition)
1145 if (empty($WhereClause))
1146 $WhereClause="(".$NewCondition.")";
1148 $WhereClause.=" AND (".$NewCondition.")";