2 /*****************************************************************
\r
4 Description : Routines to access MySQL, SQL Server, Oracle, & ODBC databases
\r
6 Author : Matt Brown (dowdybrown@yahoo.com)
\r
7 Copyright (C) 2006-2008 Matt Brown
\r
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 ******************************************************************/
\r
15 //********************************************************************************************************
\r
16 // Parse a SQL statement
\r
17 //********************************************************************************************************
\r
29 function ToArray() {
\r
30 return array($this->arSelList,$this->arSelListAs,$this->FromClause,$this->WhereClause,$this->arGroupBy,$this->HavingClause,$this->arOrderBy,$this->IsDistinct);
\r
33 function LoadArray($a) {
\r
34 $this->arSelList=$a[0];
\r
35 $this->arSelListAs=$a[1];
\r
36 $this->FromClause=$a[2];
\r
37 $this->WhereClause=$a[3];
\r
38 $this->arGroupBy=$a[4];
\r
39 $this->HavingClause=$a[5];
\r
40 $this->arOrderBy=$a[6];
\r
41 $this->IsDistinct=$a[7];
\r
44 // -------------------------------------------------------------
\r
45 // Rebuilds a SQL select statement that was parsed by ParseSelect
\r
46 // -------------------------------------------------------------
\r
47 function Unparse($arSkipCols) {
\r
49 if ($this->IsDistinct) $sqltext.="DISTINCT ";
\r
50 $sqltext.=$this->UnparseColumnListSkip($arSkipCols)." FROM ".$this->FromClause;
\r
51 if (!empty($this->WhereClause)) {
\r
52 $sqltext.=" WHERE ".$this->WhereClause;
\r
54 if (is_array($this->arGroupBy)) {
\r
55 if (count($this->arGroupBy) > 0) {
\r
56 $sqltext.=" GROUP BY ".implode(",",$this->arGroupBy);
\r
59 if (!empty($this->HavingClause)) {
\r
60 $sqltext.=" HAVING ".$this->HavingClause;
\r
62 if (is_array($this->arOrderBy)) {
\r
63 if (count($this->arOrderBy) > 0) {
\r
64 $sqltext.=" ORDER BY ".implode(",",$this->arOrderBy);
\r
70 function UnparseSelect() {
\r
71 return $this->Unparse(array());
\r
74 function UnparseSelectSkip($arSkipCols) {
\r
75 return $this->Unparse($arSkipCols);
\r
78 function UnparseSelectDistinct() {
\r
79 $this->IsDistinct=true;
\r
80 return $this->Unparse(array());
\r
83 function UnparseDistinctColumn($colnum) {
\r
84 $sqltext="SELECT DISTINCT ".$this->UnparseColumn($colnum)." FROM ".$this->FromClause;
\r
85 if (!empty($this->WhereClause)) {
\r
86 $sqltext.=" WHERE ".$this->WhereClause;
\r
88 $sqltext.=" ORDER BY ".$this->arSelList[$colnum];
\r
92 function UnparseColumn($i) {
\r
93 $s=$this->arSelList[$i];
\r
94 if (!empty($this->arSelListAs[$i])) {
\r
95 $s.=" AS ".$this->arSelListAs[$i];
\r
100 function UnparseColumnList() {
\r
101 if (empty($this->arSelList)) return "";
\r
102 $sqltext=$this->UnparseColumn(0);
\r
103 for ($i=1; $i<count($this->arSelList); $i++) {
\r
104 $sqltext.=",".$this->UnparseColumn($i);
\r
109 function UnparseColumnListSkip($arSkipCols) {
\r
110 for ($i=0,$SkipIdx=0; $i<count($this->arSelList); $i++) {
\r
112 if ($SkipIdx < count($arSkipCols)) {
\r
113 $skip=($arSkipCols[$SkipIdx]==$i);
\r
114 if ($skip) $SkipIdx++;
\r
117 if (empty($sqltext)) {
\r
118 $sqltext=$this->UnparseColumn($i);
\r
120 $sqltext.=",".$this->UnparseColumn($i);
\r
127 function DebugPrint() {
\r
128 echo "<p>Parse Result:";
\r
129 echo "<table border='1'>";
\r
130 if ($this->IsDistinct) echo "<tr valign='top'><td>DISTINCT<td> ";
\r
131 echo "<tr valign='top'><td>COLUMNS:<td><ol>";
\r
132 if (!empty($this->arSelList)) {
\r
133 for ($i=0; $i<count($this->arSelList); $i++) {
\r
134 echo "<li>".$this->UnparseColumn($i);
\r
137 echo "</ol><tr valign='top'><td>FROM:<td>" . $this->FromClause;
\r
138 if (!empty($this->WhereClause)) {
\r
139 echo "<tr valign='top'><td>WHERE:<td>".$this->WhereClause;
\r
141 if (is_array($this->arGroupBy)) {
\r
142 if (count($this->arGroupBy) > 0) {
\r
143 echo "<tr valign='top'><td>GROUP BY:<td>".implode("<br>",$this->arGroupBy);
\r
146 if (!empty($this->HavingClause)) {
\r
147 echo "<tr valign='top'><td>HAVING:<td>".$this->HavingClause;
\r
149 if (is_array($this->arOrderBy)) {
\r
150 if (count($this->arOrderBy) > 0) {
\r
151 echo "<tr valign='top'><td>ORDER BY:<td>".implode("<br>",$this->arOrderBy);
\r
158 $this->arSelList=array();
\r
159 $this->arSelListAs=array();
\r
160 $this->arGroupBy=array();
\r
161 $this->arOrderBy=array();
\r
162 $this->FromClause=NULL;
\r
163 $this->WhereClause=NULL;
\r
164 $this->HavingClause=NULL;
\r
165 $this->IsDistinct=false;
\r
167 // -------------------------------------------------------------
\r
168 // Parse a SQL select statement into its major components
\r
169 // Does not handle:
\r
170 // 1) union queries
\r
172 // 3) more than one space between "group" and "by", or "order" and "by"
\r
173 // If distinct is specified, it will be part of the first item in arSelList
\r
174 // -------------------------------------------------------------
\r
176 function ParseSelect($sqltext) {
\r
180 $sqltext=str_replace("\n"," ",$sqltext);
\r
181 $sqltext=" ".str_replace("\r"," ",$sqltext)." SELECT ";
\r
182 // SELECT suffix forces last curfield to be saved
\r
183 $l=strlen($sqltext);
\r
187 for ($i=0; $i<$l; $i++) {
\r
188 $ch=substr($sqltext,$i,1);
\r
190 if ($ch == $endquote) {
\r
191 if ($endquote=="'" && substr($sqltext,$i,2) == "''") {
\r
201 elseif ($ch == "'" || $ch == "\"" || $ch == "`") {
\r
206 elseif ($ch == "[") {
\r
211 elseif ($ch == "(") {
\r
215 elseif ($ch == ")") {
\r
216 if ($parencnt == 0) {
\r
218 // sql statement has a syntax error
\r
223 elseif ($parencnt > 0) {
\r
226 elseif ($ch == ",") {
\r
229 array_push($this->arSelList, $curfield);
\r
230 array_push($this->arSelListAs, NULL);
\r
234 $this->arSelListAs[count($this->arSelList)-1]=$curfield;
\r
239 array_push($this->arGroupBy, $curfield);
\r
243 array_push($this->arOrderBy, $curfield);
\r
251 elseif ($ch == " ") {
\r
252 $j=strpos($sqltext," ",$i+1);
\r
259 if (strtoupper(substr($sqltext,$j+1,3))=="BY ") $j+=3;
\r
260 $nexttoken=strtoupper(substr($sqltext,$i+1,$j-$i-1));
\r
261 //echo '<br>'.$nexttoken;
\r
262 switch ($nexttoken) {
\r
273 $this->AddColumn($curfield, NULL);
\r
277 $this->arSelListAs[count($this->arSelList)-1]=$curfield;
\r
280 case "FROM": $this->FromClause=$curfield; $curfield=''; break;
\r
281 case "WHERE": $this->WhereClause=$curfield; $curfield=''; break;
\r
282 case "GROUP BY": array_push($this->arGroupBy, $curfield); $curfield=''; break;
\r
283 case "HAVING": $this->HavingClause=$curfield; $curfield=''; break;
\r
284 case "ORDER BY": array_push($this->arOrderBy, $curfield); $curfield=''; break;
\r
286 $clause=$nexttoken;
\r
291 if ($clause=="SELECT") {
\r
292 $this->AddColumn($curfield, NULL);
\r
294 $clause=$nexttoken;
\r
296 } else if ($curfield != "") {
\r
302 if ($clause=="SELECT") {
\r
303 $this->IsDistinct=true;
\r
306 } else if ($curfield != "") {
\r
312 if ($curfield != "") {
\r
326 // -------------------------------------------------------------
\r
327 // Add column to select list
\r
328 // -------------------------------------------------------------
\r
329 function AddColumn($ColumnSql, $ColumnName) {
\r
330 array_push($this->arSelList, $ColumnSql);
\r
331 array_push($this->arSelListAs, $ColumnName);
\r
334 // -------------------------------------------------------------
\r
335 // Add a join to the from clause
\r
336 // -------------------------------------------------------------
\r
337 function AddJoin($JoinClause) {
\r
338 if (preg_match("/ join /i",$this->FromClause)) {
\r
339 $this->FromClause="(".$this->FromClause.")";
\r
340 // required by Access
\r
342 $this->FromClause.=" ".$JoinClause;
\r
345 function SplitSortSpec($sortspec, &$sortcol, &$sortdir) {
\r
346 $sortspec=strtoupper($sortspec);
\r
347 if (substr($sortspec,-3) == "ASC") {
\r
348 $sortcol=trim(substr($sortspec,0,-3));
\r
351 elseif (substr($sortspec,-4) == "DESC") {
\r
352 $sortcol=trim(substr($sortspec,0,-4));
\r
356 $sortcol=trim($sortspec);
\r
361 function FindSortColumn($sortspec) {
\r
362 $this->SplitSortSpec($sortspec, $findcol, $finddir);
\r
363 for ($i=0; $i<=count($this->arOrderBy)-1; $i++) {
\r
364 $this->SplitSortSpec($this->arOrderBy[$i], $sortcol, $sortdir);
\r
365 if ($sortcol == $findcol) {
\r
372 // -------------------------------------------------------------
\r
373 // Add sort criteria to the beginning of the order by clause
\r
374 // -------------------------------------------------------------
\r
375 function AddSort($NewSort) {
\r
376 $colidx=$this->FindSortColumn($NewSort);
\r
377 if ($colidx >= 0) {
\r
378 for ($i=$colidx; $i>0; $i--) {
\r
379 $this->arOrderBy[$i]=$this->arOrderBy[$i-1];
\r
381 $this->arOrderBy[0]=$NewSort;
\r
384 array_unshift($this->arOrderBy, $NewSort);
\r
388 // -------------------------------------------------------------
\r
389 // Append sort criteria to the order by clause
\r
390 // -------------------------------------------------------------
\r
391 function AppendSort($NewSort) {
\r
392 array_push($this->arOrderBy, $NewSort);
\r
395 // -------------------------------------------------------------
\r
396 // Add a condition to the where clause
\r
397 // -------------------------------------------------------------
\r
398 function AddWhereCondition($NewCondition) {
\r
399 $this->AddCondition($this->WhereClause, $NewCondition);
\r
402 // -------------------------------------------------------------
\r
403 // Add a condition to the having clause
\r
404 // -------------------------------------------------------------
\r
405 function AddHavingCondition($NewCondition) {
\r
406 $this->AddCondition($this->HavingClause, $NewCondition);
\r
409 function AddCondition(&$Clause, $NewCondition) {
\r
410 if (empty($NewCondition)) {
\r
413 if (empty($Clause)) {
\r
414 $Clause="(".$NewCondition.")";
\r
417 $Clause.=" AND (".$NewCondition.")";
\r
423 // -------------------------------------------------------------
\r
424 // created by dbClass.GetColumnInfo()
\r
425 // -------------------------------------------------------------
\r
428 var $ColName,$Nullable,$ColType,$ColLength,$Writeable,$IsPKey;
\r
431 // tested ok with MySQL 4 & 5
\r
432 class dbClass_mysql
\r
435 function dbClass_mysql($conn) { $this->conn=$conn; }
\r
436 function HasError() { return mysql_errno()!=0; }
\r
437 function ErrorMsg() { return mysql_error(); }
\r
438 function Close() { return mysql_close($this->conn); }
\r
439 function FreeResult($rsLookUp) { return mysql_free_result($rsLookUp); }
\r
440 function RunQuery($sqltext) { $this->lastQuery=$sqltext; return mysql_query($sqltext,$this->conn); }
\r
441 function NumFields($rsMain) { return mysql_num_fields($rsMain); }
\r
442 function NumRows($rsMain) { return mysql_num_rows($rsMain); }
\r
443 function FieldType($rsMain,$i) { return mysql_field_type($rsMain,$i); }
\r
444 function FieldName($rsMain,$i) { return mysql_field_name($rsMain,$i); }
\r
445 function FetchRow($rsMain,&$result) { $result=mysql_fetch_row($rsMain); return ($result==false) ? false : true; }
\r
446 function FetchAssoc($rsMain,&$result) { $result=mysql_fetch_assoc($rsMain); return ($result==false) ? false : true; }
\r
447 function FetchArray($rsMain,&$result) { $result=mysql_fetch_array($rsMain,MYSQL_NUM); return ($result==false) ? false : true; }
\r
448 function AffectedRows($rsMain) { return mysql_affected_rows($this->conn); }
\r
449 function Seek($rsMain,$offset) { return mysql_data_seek($rsMain,$offset); }
\r
450 function RunParamQuery($query, $phs = array()) {
\r
451 foreach ($phs as $ph) { // from php.net
\r
452 if ( isset($ph) ) {
\r
453 $ph = "'" . mysql_real_escape_string($ph) . "'";
\r
457 $query = substr_replace($query, $ph, strpos($query, '?'), 1);
\r
459 $this->lastQuery=$query;
\r
460 return mysql_query($query,$this->conn);
\r
462 function GetColumnInfo($TableName) {
\r
463 $rsMain=$this->RunQuery("SHOW COLUMNS FROM ".$TableName);
\r
464 if (!$rsMain) return null;
\r
465 $arColumns=array();
\r
466 while($this->FetchAssoc($rsMain,$row)) {
\r
467 $colinfo=new dbColumn;
\r
468 $colinfo->IsPKey=($row["Key"]=="PRI");
\r
469 $colinfo->ColName=$row["Field"];
\r
470 $colinfo->ColType=$row["Type"];
\r
471 $colinfo->Nullable=($row["Null"]=="YES");
\r
472 if (preg_match("/\((\d+)\)/", $row["Type"], $matches))
\r
473 $colinfo->ColLength=$matches[1];
\r
475 $colinfo->ColLength=0;
\r
476 $colinfo->Writeable=($row["Extra"] != 'auto_increment');
\r
477 array_push($arColumns, $colinfo);
\r
479 $this->FreeResult($rsMain);
\r
482 function GetTableList($TableType) {
\r
483 if ($TableType!='VIEW') $TableType='BASE TABLE';
\r
484 $rsMain=$this->RunQuery("SHOW FULL TABLES WHERE Table_type='".$TableType."'");
\r
485 if (!$rsMain) return null;
\r
487 while($this->FetchRow($rsMain,$row)) {
\r
488 array_push($arTables, $row[0]);
\r
490 $this->FreeResult($rsMain);
\r
493 function Concat($arStrings) {
\r
494 return "concat(".implode(",",$arStrings).")";
\r
496 function Convert2Char($s) {
\r
497 return $s; // implicit conversion
\r
499 function SqlDay($s) {
\r
500 return "dayofmonth(".$s.")";
\r
502 function SqlMonth($s) {
\r
503 return "month(".$s.")";
\r
505 function SqlYear($s) {
\r
506 return "year(".$s.")";
\r
508 function CurrentTime() {
\r
509 return "LOCALTIMESTAMP";
\r
514 // tested ok with Oracle XE
\r
518 function dbClass_oci($conn) { $this->conn=$conn; }
\r
519 function HasError() { return is_array(ocierror()); }
\r
520 function ErrorMsg() { $e=ocierror(); return is_array($e) ? $e['message'] : ''; }
\r
521 function Close() { return ocilogoff($this->conn); }
\r
522 function FreeResult($rsLookUp) { return ocifreestatement($rsLookUp); }
\r
523 function RunQuery($sqltext) { $this->lastQuery=$sqltext; $stmt=ociparse($this->conn, $sqltext); ociexecute($stmt); return $stmt; }
\r
524 function NumFields($rsMain) { return ocinumcols($rsMain); }
\r
525 function NumRows($rsMain) { return -1; }
\r
526 function FieldType($rsMain,$i) { return ocicolumntype($rsMain,$i+1); }
\r
527 function FieldName($rsMain,$i) { return oci_field_name($rsMain,$i); }
\r
528 function FetchRow($rsMain,&$result) { return ocifetchinto($rsMain,$result,OCI_NUM); }
\r
529 function FetchAssoc($rsMain,&$result) { return ocifetchinto($rsMain,$result,OCI_ASSOC); }
\r
530 function FetchArray($rsMain,&$result) { return ocifetchinto($rsMain,$result,OCI_NUM); }
\r
531 function AffectedRows($rsMain) { return (is_resource($rsMain) ? ocirowcount($rsMain) : false); }
\r
532 function Seek($rsMain,$offset) {
\r
533 for($i=0; $i<$offset; $i++) ocifetchrow($rsMain);
\r
535 function RunParamQuery($query, $phs = array()) {
\r
536 foreach ($phs as $ph) { // from php.net
\r
537 $ph = isset($ph) ? "'" . str_replace("'","''",$ph) . "'" : "NULL";
\r
538 $query = substr_replace($query, $ph, strpos($query, '?'), 1);
\r
540 $this->lastQuery=$query;
\r
541 return $this->RunQuery($query);
\r
543 function GetColumnInfo($TableName) {
\r
544 $TableName=strtoupper($TableName);
\r
545 $rsMain=$this->RunQuery("select * from col WHERE tname='$TableName' order by colno");
\r
546 if (!$rsMain) return null;
\r
547 $arColumns=array();
\r
548 while($this->FetchAssoc($rsMain,$row)) {
\r
549 $colinfo=new dbColumn;
\r
550 $colinfo->IsPKey=false;
\r
551 $colinfo->ColName=$row["CNAME"];
\r
552 $colinfo->ColType=$row["COLTYPE"];
\r
553 $colinfo->Nullable=($row["NULLS"]=="NULL");
\r
554 $colinfo->ColLength=$row["WIDTH"];
\r
555 $colinfo->Writeable=true; // need to figure out where to find this
\r
556 array_push($arColumns, $colinfo);
\r
557 //echo "<p>GetColumnInfo: ".$row["CNAME"].' - '.$row["COLTYPE"]."</p>";
\r
559 $this->FreeResult($rsMain);
\r
560 $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)";
561 $rsMain = $this->RunQuery($sql);
563 while($this->FetchRow($rsMain,$row)) {
\r
565 //echo "<p>GetColumnInfo pk: ".$colname."</p>";
\r
566 for($i=0; $i<count($arColumns); $i++) {
\r
567 if ($arColumns[$i]->ColName==$colname) {
\r
568 $arColumns[$i]->IsPKey=true;
\r
573 $this->FreeResult($rsMain);
\r
577 function Concat($arStrings) {
\r
578 return implode(" || ",$arStrings);
\r
580 function Convert2Char($s) {
\r
581 return "cast(".$s." as varchar2(20))";
\r
583 function SqlDay($s) {
\r
584 return "to_char(".$s.",'DD')";
\r
586 function SqlMonth($s) {
\r
587 return "to_char(".$s.",'MM')";
\r
589 function SqlYear($s) {
\r
590 return "to_char(".$s.",'YYYY')";
\r
592 function CurrentTime() {
\r
593 return "LOCALTIMESTAMP";
\r
595 function GetTableList($TableType) {
\r
596 if ($TableType!='VIEW'){
\r
597 $TableType='TABLE';
\r
598 $tableFiltre="and exists (select 'X' from USER_TABLES where TABLE_NAME = OBJECT_NAME) ";
\r
600 $rsMain=$this->RunQuery("SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = '".$TableType."' and OWNER = UPPER('".$GLOBALS['appName']."') ".$tableFiltre."order by 1");
\r
601 if (!$rsMain) return null;
\r
603 while($this->FetchRow($rsMain,$row)) {
\r
604 array_push($arTables, $row[0]);
\r
606 $this->FreeResult($rsMain);
\r
612 // tested ok with MS SQL Server & MS Access
\r
613 // Oracle works ok except for GetColumnInfo
\r
616 var $lastQuery,$dbc;
\r
617 function dbClass_odbc(&$dbc) { $this->dbc=$dbc; }
\r
618 function HasError() { return odbc_error()!=''; }
\r
619 function ErrorMsg() { return @odbc_error() . ' ' . @odbc_errormsg(); }
\r
620 function Close() { return odbc_close(); }
\r
621 function FreeResult($rsLookUp) { return odbc_free_result($rsLookUp); }
\r
622 function RunQuery($sqltext) { $this->lastQuery=$sqltext; return odbc_exec($this->dbc->dbMain,$sqltext); }
\r
623 function NumFields($rsMain) { return odbc_num_fields($rsMain); }
\r
624 function NumRows($rsMain) { return odbc_num_rows($rsMain); }
\r
625 function FieldType($rsMain,$i) { return odbc_field_type($rsMain,$i+1); }
\r
626 function FieldName($rsMain,$i) { return odbc_field_name($rsMain,$i); }
\r
627 function FetchRow($rsMain,&$result) { $rc=odbc_fetch_into($rsMain,$result); return ($rc==false) ? false : true; }
\r
628 function FetchAssoc($rsMain,&$result) { $result=odbc_fetch_array($rsMain); return ($result==false) ? false : true; }
\r
629 function FetchArray($rsMain,&$result) { $rc=odbc_fetch_into($rsMain,$result); return ($rc==false) ? false : true; }
\r
630 function AffectedRows($rsMain) { return odbc_num_rows($rsMain); }
\r
631 function Seek($rsMain,$offset) {
\r
632 for($i=0; $i<$offset; $i++) odbc_fetch_row($rsMain);
\r
634 function RunParamQuery($query, $phs = array()) {
\r
635 // odbc_prepare/odbc_execute chokes on this: SELECT * FROM (SELECT * FROM orders WHERE ShipCountry=?) AS rico_Main
\r
636 // so that approach cannot be used
\r
637 foreach ($phs as $ph) {
\r
638 if ( isset($ph) ) {
\r
639 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)) {
\r
640 $ph = "{ts '".$ph."'}";
\r
642 $ph = "'" . str_replace("'","''",$ph) . "'";
\r
647 $query = substr_replace($query, $ph, strpos($query, '?'), 1);
\r
649 $this->lastQuery=$query;
\r
650 return odbc_exec($this->dbc->dbMain,$query);
\r
652 function GetColumnInfo($TableName) {
\r
653 switch ($this->dbc->Dialect) {
\r
655 $qualifier=$this->dbc->dbDefault;
\r
659 $qualifier=$this->dbc->dsn;
\r
665 //echo "<p>GetColumnInfo: ".$qualifier.".".$schema.".".$TableName."</p>";
\r
666 $rsMain=odbc_columns($this->dbc->dbMain, $qualifier, $schema, $TableName);
\r
667 //odbc_result_all($rsMain);
\r
668 if (!$rsMain) return null;
\r
669 $arColumns=array();
\r
670 while($this->FetchAssoc($rsMain,$row)) {
\r
671 if ($row["TABLE_NAME"]!=$TableName) continue;
\r
672 $colinfo=new dbColumn;
\r
673 //echo "<p>GetColumnInfo: ".$row["COLUMN_NAME"].':'.$row["TYPE_NAME"]."</p>";
\r
674 $colinfo->ColName=$row["COLUMN_NAME"];
\r
675 $colinfo->ColType=$row["TYPE_NAME"];
\r
676 if (array_key_exists("PRECISION",$row)) {
\r
677 $colinfo->ColLength=$row["PRECISION"];
\r
678 } else if (array_key_exists("COLUMN_SIZE",$row)) {
\r
679 $colinfo->ColLength=$row["COLUMN_SIZE"];
\r
681 $colinfo->Nullable=($row["NULLABLE"]=="YES");
\r
682 $colinfo->IsPKey=false;
\r
683 $colinfo->Writeable=($row["TYPE_NAME"] != 'int identity');
\r
684 array_push($arColumns, $colinfo);
\r
686 $this->FreeResult($rsMain);
\r
687 //$rsMain=odbc_columnprivileges($this->dbc->dbMain, $qualifier, $schema, $TableName,"%");
\r
688 //odbc_result_all($rsMain);
\r
689 //$this->FreeResult($rsMain);
\r
690 $rsMain=odbc_primarykeys($this->dbc->dbMain, $qualifier, $schema, $TableName);
\r
692 while($this->FetchAssoc($rsMain,$row)) {
\r
693 $colname=$row["COLUMN_NAME"];
\r
694 //echo "<p>GetColumnInfo pk: ".$colname."</p>";
\r
695 for($i=0; $i<count($arColumns); $i++) {
\r
696 if ($arColumns[$i]->ColName==$colname) {
\r
697 $arColumns[$i]->IsPKey=true;
\r
702 $this->FreeResult($rsMain);
\r
706 function Concat($arStrings) {
\r
707 $cnt=count($arStrings);
\r
710 case 1: return $arStrings[0];
\r
712 $result="{fn concat(".$arStrings[0].",".$arStrings[1].")}";
\r
713 for ($i=2; $i<$cnt; $i++) {
\r
714 $result="{fn concat(".$result.",".$arStrings[$i].")}";
\r
719 function Convert2Char($s) {
\r
720 return "{fn CONVERT(" . $s . ",SQL_VARCHAR)}";
\r
722 function SqlDay($s) {
\r
723 return "{fn DAYOFMONTH(" . $s . ")}";
\r
725 function SqlMonth($s) {
\r
726 return "{fn MONTH(" . $s . ")}";
\r
728 function SqlYear($s) {
\r
729 return "{fn YEAR(" . $s . ")}";
\r
731 function CurrentTime() {
\r
732 return "{fn CURDATE()}";
\r
736 // For MS SQL Server
\r
737 class dbClass_mssql
\r
739 var $lastQuery,$dbc;
\r
740 function dbClass_mssql($conn) { $this->conn=$conn; }
\r
741 function HasError() { return mssql_get_last_message()!=0; }
\r
742 function ErrorMsg() { return mssql_get_last_message(); }
\r
743 function Close() { return mssql_close($this->conn); }
\r
744 function FreeResult($rsLookUp) { return mssql_free_result($rsLookUp); }
\r
745 function RunQuery($sqltext) { $this->lastQuery=$sqltext; return mssql_query($sqltext,$this->conn); }
\r
746 function NumFields($rsMain) { return mssql_num_fields($rsMain); }
\r
747 function NumRows($rsMain) { return mssql_num_rows($rsMain); }
\r
748 function FieldType($rsMain,$i) { return mssql_field_type($rsMain,$i); }
\r
749 function FieldName($rsMain,$i) { return mssql_field_name($rsMain,$i); }
\r
750 function FetchRow($rsMain,&$result) { $result=mssql_fetch_row($rsMain); return ($result==false) ? false : true; }
\r
751 function FetchAssoc($rsMain,&$result) { $result=mssql_fetch_assoc($rsMain); return ($result==false) ? false : true; }
\r
752 function FetchArray($rsMain,&$result) { $result=mssql_fetch_array($rsMain,MSSQL_NUM); return ($result==false) ? false : true; }
\r
753 function AffectedRows($rsMain) { return mssql_rows_affected($rsMain); }
\r
754 function Seek($rsMain,$offset) { return mssql_data_seek($rsMain,$offset); }
\r
755 function RunParamQuery($query, $phs = array()) {
\r
756 foreach ($phs as $ph) {
\r
757 if ( isset($ph) ) {
\r
758 $ph = "'" . str_replace("'","''",$ph) . "'";
\r
762 $query = substr_replace($query, $ph, strpos($query, '?'), 1);
\r
764 $this->lastQuery=$query;
\r
765 return mssql_query($query,$this->conn);
\r
768 function GetColumnInfo($TableName) {
\r
769 $TableName=strtoupper($TableName);
\r
770 $rsMain=$this->RunQuery("exec sp_columns '$TableName'");
\r
771 if (!$rsMain) return null;
\r
772 $arColumns=array();
\r
774 while($this->FetchAssoc($rsMain,$row)) {
\r
775 if (strtoupper($row["TABLE_NAME"]) != $TableName) continue; // sp_columns treats "Order_Details" and "Order Details" as the same
\r
776 $colinfo=new dbColumn;
\r
777 $colinfo->ColName=$row["COLUMN_NAME"];
\r
778 $colinfo->ColType=$row["TYPE_NAME"];
\r
779 if (array_key_exists("PRECISION",$row)) {
\r
780 $colinfo->ColLength=$row["PRECISION"];
\r
781 } else if (array_key_exists("LENGTH",$row)) {
\r
782 $colinfo->ColLength=$row["LENGTH"];
\r
784 $colinfo->Nullable=($row["NULLABLE"]==1);
\r
785 $colinfo->IsPKey=false;
\r
786 $colinfo->Writeable=(strtoupper($row["TYPE_NAME"]) != "INT IDENTITY");
\r
787 array_push($arColumns, $colinfo);
\r
789 $this->FreeResult($rsMain);
\r
790 // Get Primary Keys
\r
791 $rsMain=$this->RunQuery("exec sp_pkeys '$TableName'");
\r
793 while($this->FetchAssoc($rsMain,$row)) {
\r
794 $colname=$row["COLUMN_NAME"];
\r
795 for($i=0; $i<count($arColumns); $i++) {
\r
796 if ($arColumns[$i]->ColName==$colname) {
\r
797 $arColumns[$i]->IsPKey=true;
\r
802 $this->FreeResult($rsMain);
\r
807 function GetTableList($TableType) {
\r
808 $typecode=$TableType!='VIEW' ? 'U' : 'V';
\r
809 $rsMain=$this->RunQuery("select name from sysobjects where type='".$typecode."' order by name");
\r
810 if (!$rsMain) return null;
\r
811 $arTables=array();
\r
812 while($this->FetchRow($rsMain,$row)) {
\r
813 array_push($arTables, $row[0]);
\r
815 $this->FreeResult($rsMain);
\r
819 function Concat($arStrings) {
\r
820 return implode("+",$arStrings);
\r
822 function Convert2Char($s) {
\r
823 return "CAST(" . $s . " AS VARCHAR)";
\r
825 function SqlDay($s) {
\r
826 return "DAY(" . $s . ")";
\r
828 function SqlMonth($s) {
\r
829 return "MONTH(" . $s . ")";
\r
831 function SqlYear($s) {
\r
832 return "YEAR(" . $s . ")";
\r
834 function CurrentTime() {
\r
835 return "CURRENT_TIMESTAMP";
\r
844 var $debug,$ConnTimeout,$CmdTimeout,$LockTimeout,$Provider;
\r
846 var $DisplayErrors;
\r
850 // these are private:
\r
851 var $dbMain,$DisplayFunc,$dbDefault;
\r
853 // -------------------------------------------------------------
\r
854 // Class Constructor
\r
855 // -------------------------------------------------------------
\r
858 $this->Provider="127.0.0.1";
\r
859 $this->debug=false;
\r
860 $this->ConnTimeout=30; // seconds
\r
861 $this->LockTimeout=5000; // milliseconds
\r
862 $this->DisplayErrors=true;
\r
863 $this->CmdTimeout=120; // 2 minutes
\r
864 $this->ErrMsgFmt="HTML";
\r
865 $this->DisplayFunc="echo";
\r
866 $this->Dialect="MySQL";
\r
867 $this->Wildcard="%";
\r
870 // -------------------------------------------------------------
\r
871 // Class Destructor (only called if php5)
\r
872 // -------------------------------------------------------------
\r
873 function __destruct()
\r
878 function DefaultDB()
\r
880 return $this->dbDefault;
\r
883 //********************************************************************************************************
\r
884 // If the database is down, then an explanation can be placed here
\r
885 //********************************************************************************************************
\r
886 function MaintenanceMsg()
\r
891 function DisplayMsg($msg)
\r
893 if (!empty($this->DisplayFunc))
\r
895 if ($this->ErrMsgFmt=="HTML" && substr($msg,0,1)!="<")
\r
897 $msg="<p>".htmlspecialchars(str_replace("\n","<br>",$msg))."</p>";
\r
901 $msg=str_replace("\n"," ",$msg);
\r
903 eval($this->DisplayFunc."(\"".$msg."\");");
\r
907 function HandleError($msg)
\r
909 if ($this->DisplayErrors)
\r
911 $this->DisplayMsg($this->LastErrorMsg);
\r
915 //********************************************************************************************************
\r
916 // Checks if an error has occurred, and if so, displays a message & returns true
\r
917 //********************************************************************************************************
\r
918 function CheckForError($msg)
\r
920 if (!$this->db->HasError()) return false;
\r
921 $this->LastErrorMsg=$msg;
\r
922 if (empty($this->ErrMsgFmt)) return true;
\r
923 $this->HandleError($this->FormatErrorMsg($msg));
\r
927 //********************************************************************************************************
\r
928 // Attempts to connect to the Database. Returns true on success.
\r
929 //********************************************************************************************************
\r
930 function MySqlLogon($DefDB,$userid,$pw)
\r
932 $this->Dialect="MySQL";
\r
933 $this->dbDefault = $DefDB;
\r
934 $this->dbMain = mysql_connect($this->Provider,$userid,$pw);
\r
935 $this->db =& new dbClass_mysql($this->dbMain);
\r
936 if ($this->CheckForError("opening connection to " . $this->Provider)) return false;
\r
937 mysql_select_db($DefDB,$this->dbMain);
\r
938 if ($this->CheckForError("selecting database " . $DefDB)) return false;
\r
942 //********************************************************************************************************
\r
943 // Attempts to connect to the Database. Returns true on success.
\r
944 //********************************************************************************************************
\r
945 function OracleLogon($sim,$user,$pw)
\r
947 $this->Dialect="Oracle";
\r
948 $this->dbDefault = $user;
\r
949 $this->dbMain = ocilogon($user,$pw,$sim);
\r
950 $this->db =& new dbClass_oci($this->dbMain);
\r
951 if ($this->CheckForError("opening connection")) return false;
\r
952 $this->RunActionQuery("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'");
\r
956 //********************************************************************************************************
\r
957 // Attempts to connect to the Database. Returns true on success.
\r
958 //********************************************************************************************************
\r
959 function MSSqlLogon($servername,$DefDB,$user,$pw)
\r
961 $this->Dialect="TSQL";
\r
962 $this->dbDefault = $DefDB;
\r
963 ini_set("mssql.datetimeconvert","Off");
\r
964 $this->dbMain = mssql_connect($servername,$user,$pw);
\r
965 if (!is_resource($this->dbMain)) {
\r
966 $this->LastErrorMsg="Error while connecting to ".$servername;
\r
969 $this->db =& new dbClass_mssql($this->dbMain);
\r
970 mssql_select_db($DefDB,$this->dbMain);
\r
974 //********************************************************************************************************
\r
975 // Attempts to connect to the Database. Returns true on success.
\r
976 //********************************************************************************************************
\r
977 function OdbcLogon($dsn,$DefDB,$userid,$pw)
\r
980 $this->dbDefault = $DefDB;
\r
981 $this->dbMain = odbc_connect($dsn,$userid,$pw,SQL_CUR_USE_ODBC);
\r
982 if (!is_resource($this->dbMain)) {
\r
983 $this->LastErrorMsg="Error while opening ODBC connection: " . odbc_error();
\r
986 $this->db = new dbClass_odbc($this);
\r
991 //********************************************************************************************************
\r
992 // Close database connection
\r
993 //********************************************************************************************************
\r
994 function dbClose() {
\r
995 if (is_resource($this->dbMain)) $this->db->Close();
\r
996 $this->dbMain = NULL;
\r
1000 function CurrentTime() {
\r
1001 return $this->db->CurrentTime();
\r
1004 function Convert2Char($s) {
\r
1005 return $this->db->Convert2Char($s);
\r
1008 // returns SQL that converts a datetime value to a date in YYYY-MM-DD format
\r
1009 function SqlDate($s) {
\r
1010 return $this->Concat(array($this->SqlYear($s),"'-'",$this->SqlMonth($s),"'-'",$this->SqlDay($s)),false);
\r
1013 // returns SQL that converts a datetime value to the day-of-month
\r
1014 function SqlDay($s) {
\r
1015 return $this->db->SqlDay($s);
\r
1018 // returns SQL that converts a datetime value to the month number
\r
1019 function SqlMonth($s) {
\r
1020 return $this->db->SqlMonth($s);
\r
1023 // returns SQL that converts a datetime value to the year
\r
1024 function SqlYear($s) {
\r
1025 return $this->db->SqlYear($s);
\r
1028 // requires an active db connection when using MySQL
\r
1029 function addQuotes($s) {
\r
1030 if (get_magic_quotes_gpc())
\r
1031 $s = stripslashes($s);
\r
1033 switch ($this->Dialect) {
\r
1034 case "MySQL": return "'" . mysql_real_escape_string($s) . "'";
\r
1035 default: return "'".str_replace("'","''",$s)."'";
\r
1039 // returns SQL that concatenates an array of strings
\r
1040 function Concat($arStrings, $addQuotes) {
\r
1042 for ($i=0; $i<count($arStrings); $i++)
\r
1043 $arStrings[$i]=$this->addQuotes($arStrings[$i]);
\r
1045 return $this->db->Concat($arStrings);
\r
1048 //********************************************************************************************************
\r
1049 // Return a string containing an error message
\r
1050 // String format is based on ErrMsgFmt
\r
1051 //********************************************************************************************************
\r
1052 function FormatErrorMsg($ContextMsg)
\r
1054 switch ($this->ErrMsgFmt)
\r
1057 $function_ret="<p class=dberror id=dbError>Error! " . $this->db->ErrorMsg() ."</p>".
\r
1058 "<p class=dberror id=dbErrorDetail><u>Operation that caused the error:</u><br>".$ContextMsg."</p>";
\r
1061 $function_ret="Error! " . $this->db->ErrorMsg() ."\n\nOperation that caused the error:\n".$ContextMsg;
\r
1064 $function_ret="Error! " . $this->db->ErrorMsg() ." (".$ContextMsg.")";
\r
1067 return $function_ret;
\r
1070 //********************************************************************************************************
\r
1071 // Runs a query and moves to the first record.
\r
1072 // Use only for queries that return records (no updates or deletes).
\r
1073 // If the query generated an error then Nothing is returned, otherwise it returns a new recordset object.
\r
1074 //********************************************************************************************************
\r
1075 function RunQuery($sqltext) {
\r
1076 $rsLookUp=$this->db->RunQuery($sqltext);
\r
1077 if ($this->CheckForError($sqltext)) return null;
\r
1078 if ($this->debug) $this->DisplayMsg($sqltext);
\r
1083 //********************************************************************************************************
\r
1084 // Runs a parameterized query (put ? in $sqltext to indicate where parameters should be inserted)
\r
1085 // Use only for queries that return records (no updates or deletes).
\r
1086 // If the query generated an error then null is returned, otherwise it returns a new recordset object.
\r
1087 //********************************************************************************************************
\r
1088 function RunParamQuery($sqltext, $arParams) {
\r
1089 $rsLookUp=$this->db->RunParamQuery($sqltext, $arParams);
\r
1090 if ($this->CheckForError($sqltext)) return null;
\r
1091 if ($this->debug) $this->DisplayMsg($sqltext);
\r
1096 //********************************************************************************************************
\r
1097 // Safely close a recordset
\r
1098 //********************************************************************************************************
\r
1099 function rsClose($rsLookUp) {
\r
1100 if (is_resource($rsLookUp)) $this->db->FreeResult($rsLookUp);
\r
1104 //********************************************************************************************************
\r
1105 // Runs a query and returns results from the first record in arData.
\r
1106 // Returns true if arData is modified (ie. a record exists).
\r
1107 // If the query generates an error then arData is left unchanged
\r
1108 // returns arData as an array, fields indexed numerically
\r
1109 //********************************************************************************************************
\r
1110 function SingleRecordQuery($sqltext,&$arData)
\r
1112 $rsMain=$this->RunQuery($sqltext);
\r
1113 if (!$rsMain) return false;
\r
1114 $success=$this->db->FetchArray($rsMain,$arData);
\r
1115 $this->rsClose($rsMain);
\r
1120 //********************************************************************************************************
\r
1121 // Runs a query where no result set is expected (updates, deletes, etc)
\r
1122 // - returns the number of records affected by the action query
\r
1123 //********************************************************************************************************
\r
1124 function RunActionQuery($sqltext)
\r
1126 $rsMain=$this->db->RunQuery($sqltext);
\r
1127 if ($this->CheckForError($sqltext)) {
\r
1130 else if ($this->debug) {
\r
1131 $this->DisplayMsg($sqltext);
\r
1133 return $this->db->AffectedRows($rsMain);
\r
1137 //********************************************************************************************************
\r
1138 // Runs a query where no result set is expected (updates, deletes, etc)
\r
1139 // - if an error occurs, then the message is returned in errmsg
\r
1140 //********************************************************************************************************
\r
1141 function RunActionQueryReturnMsg($sqltext,&$errmsg)
\r
1143 $tmpDisplayErrors=$this->DisplayErrors;
\r
1144 $this->DisplayErrors=false;
\r
1145 $this->LastErrorMsg="";
\r
1146 $function_ret=$this->RunActionQuery($sqltext);
\r
1147 if (!empty($this->LastErrorMsg))
\r
1149 $errmsg=$this->LastErrorMsg;
\r
1151 $this->DisplayErrors=$tmpDisplayErrors;
\r
1152 return $function_ret;
\r
1156 //********************************************************************************************************
\r
1157 // Takes a sql create (table or view) statement and performs:
\r
1158 // 1) a conditional drop (if it already exists)
\r
1160 // 3) grants select access to public (if not a temp table)
\r
1162 // for views, all actions must occur on the default database for the connection
\r
1163 //********************************************************************************************************
\r
1164 function DropCreate($sqlcreate)
\r
1166 $parsed=explode(" ",$sqlcreate);
\r
1167 if (count($parsed) < 3) return; // error
\r
1168 $sqltext="DROP ".$parsed[1]." ".$parsed[2];
\r
1169 $this->RunActionQueryReturnMsg($sqltext,$dropmsg);
\r
1170 $this->RunActionQuery($sqlcreate);
\r
1173 //********************************************************************************************************
\r
1174 // Returns a comma-separated list of column names that make up the primary key
\r
1175 // Returns empty string if no primary key has been defined
\r
1176 //********************************************************************************************************
\r
1177 function PrimaryKey($TableName) {
\r
1179 $arColumns=$this->GetColumnInfo($TableName);
\r
1180 if (!is_array($arColumns)) return '';
\r
1181 foreach ($arColumns as $colinfo) {
\r
1182 if ($colinfo->IsPKey) {
\r
1183 if ($keys!='') $keys.=',';
\r
1184 $keys.=$colinfo->ColName;
\r
1191 //********************************************************************************************************
\r
1192 // Returns array of column info - one entry for each column in $TableName
\r
1193 //********************************************************************************************************
\r
1194 function GetColumnInfo($TableName) {
\r
1195 return $this->db->GetColumnInfo($TableName);
\r
1199 //********************************************************************************************************
\r
1200 // Returns array of table/view names
\r
1201 // $ObjectType is one of 'VIEW' or 'TABLE'
\r
1202 //********************************************************************************************************
\r
1203 function GetTableList($ObjectType) {
\r
1204 return $this->db->GetTableList($ObjectType);
\r
1208 //********************************************************************************************************
\r
1209 // Add a condition to a where or having clause
\r
1210 //********************************************************************************************************
\r
1211 function AddCondition(&$WhereClause,$NewCondition)
\r
1213 if (empty($WhereClause))
\r
1214 $WhereClause="(".$NewCondition.")";
\r
1216 $WhereClause.=" AND (".$NewCondition.")";
\r