00001 <?php
00002 /* 
00003 V4.90 8 June 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
00004   Released under both BSD license and Lesser GPL library license. 
00005   Whenever there is any discrepancy between the two licenses, 
00006   the BSD license will take precedence. 
00007 Set tabs to 4 for best viewing.
00008   
00009   Latest version is available at http://adodb.sourceforge.net
00010   
00011         Microsoft ADO data driver. Requires ADO. Works only on MS Windows.
00012 */
00013 
00014 // security - hide paths
00015 if (!defined('ADODB_DIR')) die();
00016         
00017 define("_ADODB_ADO_LAYER", 1 );
00018 /*--------------------------------------------------------------------------------------
00019 --------------------------------------------------------------------------------------*/
00020 
00021         
00022 class ADODB_ado extends ADOConnection {
00023         var $databaseType = "ado";      
00024         var $_bindInputArray = false;
00025         var $fmtDate = "'Y-m-d'";
00026         var $fmtTimeStamp = "'Y-m-d, h:i:sA'";
00027         var $replaceQuote = "''"; // string to use to replace quotes
00028         var $dataProvider = "ado";      
00029         var $hasAffectedRows = true;
00030         var $adoParameterType = 201; // 201 = long varchar, 203=long wide varchar, 205 = long varbinary
00031         var $_affectedRows = false;
00032         var $_thisTransactions;
00033         var $_cursor_type = 3; // 3=adOpenStatic,0=adOpenForwardOnly,1=adOpenKeyset,2=adOpenDynamic
00034         var $_cursor_location = 3; // 2=adUseServer, 3 = adUseClient;
00035         var $_lock_type = -1;
00036         var $_execute_option = -1;
00037         var $poorAffectedRows = true; 
00038         var $charPage;
00039                 
00040         function ADODB_ado() 
00041         {       
00042                 $this->_affectedRows = new VARIANT;
00043         }
00044 
00045         function ServerInfo()
00046         {
00047                 if (!empty($this->_connectionID)) $desc = $this->_connectionID->provider;
00048                 return array('description' => $desc, 'version' => '');
00049         }
00050         
00051         function _affectedrows()
00052         {
00053                 if (PHP_VERSION >= 5) return $this->_affectedRows;
00054                 
00055                 return $this->_affectedRows->value;
00056         }
00057         
00058         // you can also pass a connection string like this:
00059         //
00060         // $DB->Connect('USER ID=sa;PASSWORD=pwd;SERVER=mangrove;DATABASE=ai',false,false,'SQLOLEDB');
00061         function _connect($argHostname, $argUsername, $argPassword, $argProvider= 'MSDASQL')
00062         {
00063                 $u = 'UID';
00064                 $p = 'PWD';
00065         
00066                 if (!empty($this->charPage))
00067                         $dbc = new COM('ADODB.Connection',null,$this->charPage);
00068                 else
00069                         $dbc = new COM('ADODB.Connection');
00070                         
00071                 if (! $dbc) return false;
00072 
00073                 /* special support if provider is mssql or access */
00074                 if ($argProvider=='mssql') {
00075                         $u = 'User Id';  //User parameter name for OLEDB
00076                         $p = 'Password'; 
00077                         $argProvider = "SQLOLEDB"; // SQL Server Provider
00078                         
00079                         // not yet
00080                         //if ($argDatabasename) $argHostname .= ";Initial Catalog=$argDatabasename";
00081                         
00082                         //use trusted conection for SQL if username not specified
00083                         if (!$argUsername) $argHostname .= ";Trusted_Connection=Yes";
00084                 } else if ($argProvider=='access')
00085                         $argProvider = "Microsoft.Jet.OLEDB.4.0"; // Microsoft Jet Provider
00086                 
00087                 if ($argProvider) $dbc->Provider = $argProvider;        
00088                 
00089                 if ($argUsername) $argHostname .= ";$u=$argUsername";
00090                 if ($argPassword)$argHostname .= ";$p=$argPassword";
00091                 
00092                 if ($this->debug) ADOConnection::outp( "Host=".$argHostname."<BR>\n version=$dbc->version");
00093                 // @ added below for php 4.0.1 and earlier
00094                 @$dbc->Open((string) $argHostname);
00095                 
00096                 $this->_connectionID = $dbc;
00097                 
00098                 $dbc->CursorLocation = $this->_cursor_location;
00099                 return  $dbc->State > 0;
00100         }
00101         
00102         // returns true or false
00103         function _pconnect($argHostname, $argUsername, $argPassword, $argProvider='MSDASQL')
00104         {
00105                 return $this->_connect($argHostname,$argUsername,$argPassword,$argProvider);
00106         }       
00107         
00108 /*
00109         adSchemaCatalogs        = 1,
00110         adSchemaCharacterSets   = 2,
00111         adSchemaCollations      = 3,
00112         adSchemaColumns = 4,
00113         adSchemaCheckConstraints        = 5,
00114         adSchemaConstraintColumnUsage   = 6,
00115         adSchemaConstraintTableUsage    = 7,
00116         adSchemaKeyColumnUsage  = 8,
00117         adSchemaReferentialContraints   = 9,
00118         adSchemaTableConstraints        = 10,
00119         adSchemaColumnsDomainUsage      = 11,
00120         adSchemaIndexes = 12,
00121         adSchemaColumnPrivileges        = 13,
00122         adSchemaTablePrivileges = 14,
00123         adSchemaUsagePrivileges = 15,
00124         adSchemaProcedures      = 16,
00125         adSchemaSchemata        = 17,
00126         adSchemaSQLLanguages    = 18,
00127         adSchemaStatistics      = 19,
00128         adSchemaTables  = 20,
00129         adSchemaTranslations    = 21,
00130         adSchemaProviderTypes   = 22,
00131         adSchemaViews   = 23,
00132         adSchemaViewColumnUsage = 24,
00133         adSchemaViewTableUsage  = 25,
00134         adSchemaProcedureParameters     = 26,
00135         adSchemaForeignKeys     = 27,
00136         adSchemaPrimaryKeys     = 28,
00137         adSchemaProcedureColumns        = 29,
00138         adSchemaDBInfoKeywords  = 30,
00139         adSchemaDBInfoLiterals  = 31,
00140         adSchemaCubes   = 32,
00141         adSchemaDimensions      = 33,
00142         adSchemaHierarchies     = 34,
00143         adSchemaLevels  = 35,
00144         adSchemaMeasures        = 36,
00145         adSchemaProperties      = 37,
00146         adSchemaMembers = 38
00147 
00148 */
00149         
00150         function &MetaTables()
00151         {
00152                 $arr= array();
00153                 $dbc = $this->_connectionID;
00154                 
00155                 $adors=@$dbc->OpenSchema(20);//tables
00156                 if ($adors){
00157                         $f = $adors->Fields(2);//table/view name
00158                         $t = $adors->Fields(3);//table type
00159                         while (!$adors->EOF){
00160                                 $tt=substr($t->value,0,6);
00161                                 if ($tt!='SYSTEM' && $tt !='ACCESS')
00162                                         $arr[]=$f->value;
00163                                 //print $f->value . ' ' . $t->value.'<br>';
00164                                 $adors->MoveNext();
00165                         }
00166                         $adors->Close();
00167                 }
00168                 
00169                 return $arr;
00170         }
00171         
00172         function &MetaColumns($table)
00173         {
00174                 $table = strtoupper($table);
00175                 $arr = array();
00176                 $dbc = $this->_connectionID;
00177                 
00178                 $adors=@$dbc->OpenSchema(4);//tables
00179         
00180                 if ($adors){
00181                         $t = $adors->Fields(2);//table/view name
00182                         while (!$adors->EOF){
00183                                 
00184                                 
00185                                 if (strtoupper($t->Value) == $table) {
00186                                 
00187                                         $fld = new ADOFieldObject();
00188                                         $c = $adors->Fields(3);
00189                                         $fld->name = $c->Value;
00190                                         $fld->type = 'CHAR'; // cannot discover type in ADO!
00191                                         $fld->max_length = -1;
00192                                         $arr[strtoupper($fld->name)]=$fld;
00193                                 }
00194                 
00195                                 $adors->MoveNext();
00196                         }
00197                         $adors->Close();
00198                 }
00199                 $false = false;
00200                 return empty($arr) ? $false : $arr;
00201         }
00202         
00203 
00204 
00205         
00206         /* returns queryID or false */
00207         function &_query($sql,$inputarr=false) 
00208         {
00209                 
00210                 $dbc = $this->_connectionID;
00211                 $false = false;
00212                 
00213         //      return rs       
00214                 if ($inputarr) {
00215                         
00216                         if (!empty($this->charPage))
00217                                 $oCmd = new COM('ADODB.Command',null,$this->charPage);
00218                         else
00219                                 $oCmd = new COM('ADODB.Command');
00220                         $oCmd->ActiveConnection = $dbc;
00221                         $oCmd->CommandText = $sql;
00222                         $oCmd->CommandType = 1;
00223 
00224                         foreach($inputarr as $val) {
00225                                 // name, type, direction 1 = input, len,
00226                                 $this->adoParameterType = 130;
00227                                 $p = $oCmd->CreateParameter('name',$this->adoParameterType,1,strlen($val),$val);
00228                                 //print $p->Type.' '.$p->value;
00229                                 $oCmd->Parameters->Append($p);
00230                         }
00231                         $p = false;
00232                         $rs = $oCmd->Execute();
00233                         $e = $dbc->Errors;
00234                         if ($dbc->Errors->Count > 0) return $false;
00235                         return $rs;
00236                 }
00237                 
00238                 $rs = @$dbc->Execute($sql,$this->_affectedRows, $this->_execute_option);
00239 
00240                 if ($dbc->Errors->Count > 0) return $false;
00241                 if (! $rs) return $false;
00242                 
00243                 if ($rs->State == 0) {
00244                         $true = true;
00245                         return $true; // 0 = adStateClosed means no records returned
00246                 }
00247                 return $rs;
00248         }
00249 
00250         
00251         function BeginTrans() 
00252         { 
00253                 if ($this->transOff) return true;
00254                 
00255                 if (isset($this->_thisTransactions))
00256                         if (!$this->_thisTransactions) return false;
00257                 else {
00258                         $o = $this->_connectionID->Properties("Transaction DDL");
00259                         $this->_thisTransactions = $o ? true : false;
00260                         if (!$o) return false;
00261                 }
00262                 @$this->_connectionID->BeginTrans();
00263                 $this->transCnt += 1;
00264                 return true;
00265         }
00266         
00267         function CommitTrans($ok=true) 
00268         { 
00269                 if (!$ok) return $this->RollbackTrans();
00270                 if ($this->transOff) return true;
00271                 
00272                 @$this->_connectionID->CommitTrans();
00273                 if ($this->transCnt) @$this->transCnt -= 1;
00274                 return true;
00275         }
00276         function RollbackTrans() {
00277                 if ($this->transOff) return true;
00278                 @$this->_connectionID->RollbackTrans();
00279                 if ($this->transCnt) @$this->transCnt -= 1;
00280                 return true;
00281         }
00282         
00283         /*      Returns: the last error message from previous database operation        */      
00284 
00285         function ErrorMsg() 
00286         {
00287                 if (!$this->_connectionID) return "No connection established";
00288                 $errc = $this->_connectionID->Errors;
00289                 if (!$errc) return "No Errors object found";
00290                 if ($errc->Count == 0) return '';
00291                 $err = $errc->Item($errc->Count-1);
00292                 return $err->Description;
00293         }
00294         
00295         function ErrorNo() 
00296         {
00297                 $errc = $this->_connectionID->Errors;
00298                 if ($errc->Count == 0) return 0;
00299                 $err = $errc->Item($errc->Count-1);
00300                 return $err->NativeError;
00301         }
00302 
00303         // returns true or false
00304         function _close()
00305         {
00306                 if ($this->_connectionID) $this->_connectionID->Close();
00307                 $this->_connectionID = false;
00308                 return true;
00309         }
00310         
00311         
00312 }
00313         
00314 /*--------------------------------------------------------------------------------------
00315          Class Name: Recordset
00316 --------------------------------------------------------------------------------------*/
00317 
00318 class ADORecordSet_ado extends ADORecordSet {   
00319         
00320         var $bind = false;
00321         var $databaseType = "ado";      
00322         var $dataProvider = "ado";      
00323         var $_tarr = false; // caches the types
00324         var $_flds; // and field objects
00325         var $canSeek = true;
00326         var $hideErrors = true;
00327                   
00328         function ADORecordSet_ado($id,$mode=false)
00329         {
00330                 if ($mode === false) { 
00331                         global $ADODB_FETCH_MODE;
00332                         $mode = $ADODB_FETCH_MODE;
00333                 }
00334                 $this->fetchMode = $mode;
00335                 return $this->ADORecordSet($id,$mode);
00336         }
00337 
00338 
00339         // returns the field object
00340         function &FetchField($fieldOffset = -1) {
00341                 $off=$fieldOffset+1; // offsets begin at 1
00342                 
00343                 $o= new ADOFieldObject();
00344                 $rs = $this->_queryID;
00345                 $f = $rs->Fields($fieldOffset);
00346                 $o->name = $f->Name;
00347                 $t = $f->Type;
00348                 $o->type = $this->MetaType($t);
00349                 $o->max_length = $f->DefinedSize;
00350                 $o->ado_type = $t;      
00351 
00352                 //print "off=$off name=$o->name type=$o->type len=$o->max_length<br>";
00353                 return $o;
00354         }
00355         
00356         /* Use associative array to get fields array */
00357         function Fields($colname)
00358         {
00359                 if ($this->fetchMode & ADODB_FETCH_ASSOC) return $this->fields[$colname];
00360                 if (!$this->bind) {
00361                         $this->bind = array();
00362                         for ($i=0; $i < $this->_numOfFields; $i++) {
00363                                 $o = $this->FetchField($i);
00364                                 $this->bind[strtoupper($o->name)] = $i;
00365                         }
00366                 }
00367                 
00368                  return $this->fields[$this->bind[strtoupper($colname)]];
00369         }
00370 
00371                 
00372         function _initrs()
00373         {
00374                 $rs = $this->_queryID;
00375                 $this->_numOfRows = $rs->RecordCount;
00376                 
00377                 $f = $rs->Fields;
00378                 $this->_numOfFields = $f->Count;
00379         }
00380         
00381         
00382          // should only be used to move forward as we normally use forward-only cursors
00383         function _seek($row)
00384         {
00385            $rs = $this->_queryID; 
00386                 // absoluteposition doesn't work -- my maths is wrong ?
00387                 //      $rs->AbsolutePosition->$row-2;
00388                 //      return true;
00389                 if ($this->_currentRow > $row) return false;
00390                 @$rs->Move((integer)$row - $this->_currentRow-1); //adBookmarkFirst
00391                 return true;
00392         }
00393         
00394 /*
00395         OLEDB types
00396         
00397          enum DBTYPEENUM
00398         {       DBTYPE_EMPTY    = 0,
00399         DBTYPE_NULL     = 1,
00400         DBTYPE_I2       = 2,
00401         DBTYPE_I4       = 3,
00402         DBTYPE_R4       = 4,
00403         DBTYPE_R8       = 5,
00404         DBTYPE_CY       = 6,
00405         DBTYPE_DATE     = 7,
00406         DBTYPE_BSTR     = 8,
00407         DBTYPE_IDISPATCH        = 9,
00408         DBTYPE_ERROR    = 10,
00409         DBTYPE_BOOL     = 11,
00410         DBTYPE_VARIANT  = 12,
00411         DBTYPE_IUNKNOWN = 13,
00412         DBTYPE_DECIMAL  = 14,
00413         DBTYPE_UI1      = 17,
00414         DBTYPE_ARRAY    = 0x2000,
00415         DBTYPE_BYREF    = 0x4000,
00416         DBTYPE_I1       = 16,
00417         DBTYPE_UI2      = 18,
00418         DBTYPE_UI4      = 19,
00419         DBTYPE_I8       = 20,
00420         DBTYPE_UI8      = 21,
00421         DBTYPE_GUID     = 72,
00422         DBTYPE_VECTOR   = 0x1000,
00423         DBTYPE_RESERVED = 0x8000,
00424         DBTYPE_BYTES    = 128,
00425         DBTYPE_STR      = 129,
00426         DBTYPE_WSTR     = 130,
00427         DBTYPE_NUMERIC  = 131,
00428         DBTYPE_UDT      = 132,
00429         DBTYPE_DBDATE   = 133,
00430         DBTYPE_DBTIME   = 134,
00431         DBTYPE_DBTIMESTAMP      = 135
00432         
00433         ADO Types
00434         
00435         adEmpty = 0,
00436         adTinyInt       = 16,
00437         adSmallInt      = 2,
00438         adInteger       = 3,
00439         adBigInt        = 20,
00440         adUnsignedTinyInt       = 17,
00441         adUnsignedSmallInt      = 18,
00442         adUnsignedInt   = 19,
00443         adUnsignedBigInt        = 21,
00444         adSingle        = 4,
00445         adDouble        = 5,
00446         adCurrency      = 6,
00447         adDecimal       = 14,
00448         adNumeric       = 131,
00449         adBoolean       = 11,
00450         adError = 10,
00451         adUserDefined   = 132,
00452         adVariant       = 12,
00453         adIDispatch     = 9,
00454         adIUnknown      = 13,   
00455         adGUID  = 72,
00456         adDate  = 7,
00457         adDBDate        = 133,
00458         adDBTime        = 134,
00459         adDBTimeStamp   = 135,
00460         adBSTR  = 8,
00461         adChar  = 129,
00462         adVarChar       = 200,
00463         adLongVarChar   = 201,
00464         adWChar = 130,
00465         adVarWChar      = 202,
00466         adLongVarWChar  = 203,
00467         adBinary        = 128,
00468         adVarBinary     = 204,
00469         adLongVarBinary = 205,
00470         adChapter       = 136,
00471         adFileTime      = 64,
00472         adDBFileTime    = 137,
00473         adPropVariant   = 138,
00474         adVarNumeric    = 139
00475 */
00476         function MetaType($t,$len=-1,$fieldobj=false)
00477         {
00478                 if (is_object($t)) {
00479                         $fieldobj = $t;
00480                         $t = $fieldobj->type;
00481                         $len = $fieldobj->max_length;
00482                 }
00483                 
00484                 if (!is_numeric($t)) return $t;
00485                 
00486                 switch ($t) {
00487                 case 0:
00488                 case 12: // variant
00489                 case 8: // bstr
00490                 case 129: //char
00491                 case 130: //wc
00492                 case 200: // varc
00493                 case 202:// varWC
00494                 case 128: // bin
00495                 case 204: // varBin
00496                 case 72: // guid
00497                         if ($len <= $this->blobSize) return 'C';
00498                 
00499                 case 201:
00500                 case 203:
00501                         return 'X';
00502                 case 128:
00503                 case 204:
00504                 case 205:
00505                          return 'B';
00506                 case 7:
00507                 case 133: return 'D';
00508                 
00509                 case 134:
00510                 case 135: return 'T';
00511                 
00512                 case 11: return 'L';
00513                 
00514                 case 16://      adTinyInt       = 16,
00515                 case 2://adSmallInt     = 2,
00516                 case 3://adInteger      = 3,
00517                 case 4://adBigInt       = 20,
00518                 case 17://adUnsignedTinyInt     = 17,
00519                 case 18://adUnsignedSmallInt    = 18,
00520                 case 19://adUnsignedInt = 19,
00521                 case 20://adUnsignedBigInt      = 21,
00522                         return 'I';
00523                 default: return 'N';
00524                 }
00525         }
00526         
00527         // time stamp not supported yet
00528         function _fetch()
00529         {       
00530                 $rs = $this->_queryID;
00531                 if (!$rs or $rs->EOF) {
00532                         $this->fields = false;
00533                         return false;
00534                 }
00535                 $this->fields = array();
00536         
00537                 if (!$this->_tarr) {
00538                         $tarr = array();
00539                         $flds = array();
00540                         for ($i=0,$max = $this->_numOfFields; $i < $max; $i++) {
00541                                 $f = $rs->Fields($i);
00542                                 $flds[] = $f;
00543                                 $tarr[] = $f->Type;
00544                         }
00545                         // bind types and flds only once
00546                         $this->_tarr = $tarr; 
00547                         $this->_flds = $flds;
00548                 }
00549                 $t = reset($this->_tarr);
00550                 $f = reset($this->_flds);
00551                 
00552                 if ($this->hideErrors)  $olde = error_reporting(E_ERROR|E_CORE_ERROR);// sometimes $f->value be null
00553                 for ($i=0,$max = $this->_numOfFields; $i < $max; $i++) {
00554                         //echo "<p>",$t,' ';var_dump($f->value); echo '</p>';
00555                         switch($t) {
00556                         case 135: // timestamp
00557                                 if (!strlen((string)$f->value)) $this->fields[] = false;
00558                                 else {
00559                                         if (!is_numeric($f->value)) # $val = variant_date_to_timestamp($f->value);
00560                                                 // VT_DATE stores dates as (float) fractional days since 1899/12/30 00:00:00
00561                                                 $val=(float) variant_cast($f->value,VT_R8)*3600*24-2209161600;
00562                                         else 
00563                                                 $val = $f->value;
00564                                         $this->fields[] = adodb_date('Y-m-d H:i:s',$val);
00565                                 }
00566                                 break;                  
00567                         case 133:// A date value (yyyymmdd) 
00568                                 if ($val = $f->value) {
00569                                         $this->fields[] = substr($val,0,4).'-'.substr($val,4,2).'-'.substr($val,6,2);
00570                                 } else
00571                                         $this->fields[] = false;
00572                                 break;
00573                         case 7: // adDate
00574                                 if (!strlen((string)$f->value)) $this->fields[] = false;
00575                                 else {
00576                                         if (!is_numeric($f->value)) $val = variant_date_to_timestamp($f->value);
00577                                         else $val = $f->value;
00578                                         
00579                                         if (($val % 86400) == 0) $this->fields[] = adodb_date('Y-m-d',$val);
00580                                         else $this->fields[] = adodb_date('Y-m-d H:i:s',$val);
00581                                 }
00582                                 break;
00583                         case 1: // null
00584                                 $this->fields[] = false;
00585                                 break;
00586                         case 6: // currency is not supported properly;
00587                                 ADOConnection::outp( '<b>'.$f->Name.': currency type not supported by PHP</b>');
00588                                 $this->fields[] = (float) $f->value;
00589                                 break;
00590                         default:
00591                                 $this->fields[] = $f->value; 
00592                                 break;
00593                         }
00594                         //print " $f->value $t, ";
00595                         $f = next($this->_flds);
00596                         $t = next($this->_tarr);
00597                 } // for
00598                 if ($this->hideErrors) error_reporting($olde);
00599                 @$rs->MoveNext(); // @ needed for some versions of PHP!
00600                 
00601                 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
00602                         $this->fields = &$this->GetRowAssoc(ADODB_ASSOC_CASE);
00603                 }
00604                 return true;
00605         }
00606         
00607                 function NextRecordSet()
00608                 {
00609                         $rs = $this->_queryID;
00610                         $this->_queryID = $rs->NextRecordSet();
00611                         //$this->_queryID = $this->_QueryId->NextRecordSet();
00612                         if ($this->_queryID == null) return false;
00613                         
00614                         $this->_currentRow = -1;
00615                         $this->_currentPage = -1;
00616                         $this->bind = false;
00617                         $this->fields = false;
00618                         $this->_flds = false;
00619                         $this->_tarr = false;
00620                         
00621                         $this->_inited = false;
00622                         $this->Init();
00623                         return true;
00624                 }
00625 
00626         function _close() {
00627                 $this->_flds = false;
00628                 @$this->_queryID->Close();// by Pete Dishman (peterd@telephonetics.co.uk)
00629                 $this->_queryID = false;        
00630         }
00631 
00632 }
00633 
00634 ?>
adodb-ado.inc.php
