lib/adodb/drivers/adodb-ado5.inc.php

   1 <?php
   2 /*
   3 V4.94 23 Jan 2007  (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved.
   4   Released under both BSD license and Lesser GPL library license.
   5   Whenever there is any discrepancy between the two licenses,
   6   the BSD license will take precedence.
   7 Set tabs to 4 for best viewing.
   8
   9   Latest version is available at http://adodb.sourceforge.net
  10
  11         Microsoft ADO data driver. Requires ADO. Works only on MS Windows. PHP5 compat version.
  12 */
  13
  14 // security - hide paths
  15 if (!defined('ADODB_DIR')) die();
  16
  17 define("_ADODB_ADO_LAYER", 1 );
  18 /*--------------------------------------------------------------------------------------
  19 --------------------------------------------------------------------------------------*/
  20
  21
  22 class ADODB_ado extends ADOConnection {
  23         var $databaseType = "ado";
  24         var $_bindInputArray = false;
  25         var $fmtDate = "'Y-m-d'";
  26         var $fmtTimeStamp = "'Y-m-d, h:i:sA'";
  27         var $replaceQuote = "''"; // string to use to replace quotes
  28         var $dataProvider = "ado";
  29         var $hasAffectedRows = true;
  30         var $adoParameterType = 201; // 201 = long varchar, 203=long wide varchar, 205 = long varbinary
  31         var $_affectedRows = false;
  32         var $_thisTransactions;
  33         var $_cursor_type = 3; // 3=adOpenStatic,0=adOpenForwardOnly,1=adOpenKeyset,2=adOpenDynamic
  34         var $_cursor_location = 3; // 2=adUseServer, 3 = adUseClient;
  35         var $_lock_type = -1;
  36         var $_execute_option = -1;
  37         var $poorAffectedRows = true;
  38         var $charPage;
  39
  40         function ADODB_ado()
  41         {
  42                 $this->_affectedRows = new VARIANT;
  43         }
  44
  45         function ServerInfo()
  46         {
  47                 if (!empty($this->_connectionID)) $desc = $this->_connectionID->provider;
  48                 return array('description' => $desc, 'version' => '');
  49         }
  50
  51         function _affectedrows()
  52         {
  53                 if (PHP_VERSION >= 5) return $this->_affectedRows;
  54
  55                 return $this->_affectedRows->value;
  56         }
  57
  58         // you can also pass a connection string like this:
  59         //
  60         // $DB->Connect('USER ID=sa;PASSWORD=pwd;SERVER=mangrove;DATABASE=ai',false,false,'SQLOLEDB');
  61         function _connect($argHostname, $argUsername, $argPassword,$argDBorProvider, $argProvider= '')
  62         {
  63         // two modes
  64         //      -       if $argProvider is empty, we assume that $argDBorProvider holds provider -- this is for backward compat
  65         //      -       if $argProvider is not empty, then $argDBorProvider holds db
  66
  67
  68                  if ($argProvider) {
  69                         $argDatabasename = $argDBorProvider;
  70                  } else {
  71                         $argDatabasename = '';
  72                         if ($argDBorProvider) $argProvider = $argDBorProvider;
  73                         else $argProvider = 'MSDASQL';
  74                 }
  75
  76
  77                 try {
  78                 $u = 'UID';
  79                 $p = 'PWD';
  80
  81                 if (!empty($this->charPage))
  82                         $dbc = new COM('ADODB.Connection',null,$this->charPage);
  83                 else
  84                         $dbc = new COM('ADODB.Connection');
  85
  86                 if (! $dbc) return false;
  87
  88                 /* special support if provider is mssql or access */
  89                 if ($argProvider=='mssql') {
  90                         $u = 'User Id';  //User parameter name for OLEDB
  91                         $p = 'Password';
  92                         $argProvider = "SQLOLEDB"; // SQL Server Provider
  93
  94                         // not yet
  95                         //if ($argDatabasename) $argHostname .= ";Initial Catalog=$argDatabasename";
  96
  97                         //use trusted conection for SQL if username not specified
  98                         if (!$argUsername) $argHostname .= ";Trusted_Connection=Yes";
  99                 } else if ($argProvider=='access')
 100                         $argProvider = "Microsoft.Jet.OLEDB.4.0"; // Microsoft Jet Provider
 101
 102                 if ($argProvider) $dbc->Provider = $argProvider;
 103
 104                 if ($argDatabasename) $argHostname .= ";DATABASE=$argDatabasename";
 105                 if ($argUsername) $argHostname .= ";$u=$argUsername";
 106                 if ($argPassword)$argHostname .= ";$p=$argPassword";
 107
 108                 if ($this->debug) ADOConnection::outp( "Host=".$argHostname."<BR>\n version=$dbc->version");
 109                 // @ added below for php 4.0.1 and earlier
 110                 @$dbc->Open((string) $argHostname);
 111
 112                 $this->_connectionID = $dbc;
 113
 114                 $dbc->CursorLocation = $this->_cursor_location;
 115                 return  $dbc->State > 0;
 116                 } catch (exception $e) {
 117                 }
 118
 119                 return false;
 120         }
 121
 122         // returns true or false
 123         function _pconnect($argHostname, $argUsername, $argPassword, $argProvider='MSDASQL')
 124         {
 125                 return $this->_connect($argHostname,$argUsername,$argPassword,$argProvider);
 126         }
 127
 128 /*
 129         adSchemaCatalogs        = 1,
 130         adSchemaCharacterSets   = 2,
 131         adSchemaCollations      = 3,
 132         adSchemaColumns = 4,
 133         adSchemaCheckConstraints        = 5,
 134         adSchemaConstraintColumnUsage   = 6,
 135         adSchemaConstraintTableUsage    = 7,
 136         adSchemaKeyColumnUsage  = 8,
 137         adSchemaReferentialContraints   = 9,
 138         adSchemaTableConstraints        = 10,
 139         adSchemaColumnsDomainUsage      = 11,
 140         adSchemaIndexes = 12,
 141         adSchemaColumnPrivileges        = 13,
 142         adSchemaTablePrivileges = 14,
 143         adSchemaUsagePrivileges = 15,
 144         adSchemaProcedures      = 16,
 145         adSchemaSchemata        = 17,
 146         adSchemaSQLLanguages    = 18,
 147         adSchemaStatistics      = 19,
 148         adSchemaTables  = 20,
 149         adSchemaTranslations    = 21,
 150         adSchemaProviderTypes   = 22,
 151         adSchemaViews   = 23,
 152         adSchemaViewColumnUsage = 24,
 153         adSchemaViewTableUsage  = 25,
 154         adSchemaProcedureParameters     = 26,
 155         adSchemaForeignKeys     = 27,
 156         adSchemaPrimaryKeys     = 28,
 157         adSchemaProcedureColumns        = 29,
 158         adSchemaDBInfoKeywords  = 30,
 159         adSchemaDBInfoLiterals  = 31,
 160         adSchemaCubes   = 32,
 161         adSchemaDimensions      = 33,
 162         adSchemaHierarchies     = 34,
 163         adSchemaLevels  = 35,
 164         adSchemaMeasures        = 36,
 165         adSchemaProperties      = 37,
 166         adSchemaMembers = 38
 167
 168 */
 169
 170         function &MetaTables()
 171         {
 172                 $arr= array();
 173                 $dbc = $this->_connectionID;
 174
 175                 $adors=@$dbc->OpenSchema(20);//tables
 176                 if ($adors){
 177                         $f = $adors->Fields(2);//table/view name
 178                         $t = $adors->Fields(3);//table type
 179                         while (!$adors->EOF){
 180                                 $tt=substr($t->value,0,6);
 181                                 if ($tt!='SYSTEM' && $tt !='ACCESS')
 182                                         $arr[]=$f->value;
 183                                 //print $f->value . ' ' . $t->value.'<br>';
 184                                 $adors->MoveNext();
 185                         }
 186                         $adors->Close();
 187                 }
 188
 189                 return $arr;
 190         }
 191
 192         function &MetaColumns($table)
 193         {
 194                 $table = strtoupper($table);
 195                 $arr= array();
 196                 $dbc = $this->_connectionID;
 197
 198                 $adors=@$dbc->OpenSchema(4);//tables
 199
 200                 if ($adors){
 201                         $t = $adors->Fields(2);//table/view name
 202                         while (!$adors->EOF){
 203
 204
 205                                 if (strtoupper($t->Value) == $table) {
 206
 207                                         $fld = new ADOFieldObject();
 208                                         $c = $adors->Fields(3);
 209                                         $fld->name = $c->Value;
 210                                         $fld->type = 'CHAR'; // cannot discover type in ADO!
 211                                         $fld->max_length = -1;
 212                                         $arr[strtoupper($fld->name)]=$fld;
 213                                 }
 214
 215                                 $adors->MoveNext();
 216                         }
 217                         $adors->Close();
 218                 }
 219
 220                 return $arr;
 221         }
 222
 223         /* returns queryID or false */
 224         function &_query($sql,$inputarr=false)
 225         {
 226                 try { // In PHP5, all COM errors are exceptions, so to maintain old behaviour...
 227
 228                 $dbc = $this->_connectionID;
 229
 230         //      return rs
 231
 232                 $false = false;
 233
 234                 if ($inputarr) {
 235
 236                         if (!empty($this->charPage))
 237                                 $oCmd = new COM('ADODB.Command',null,$this->charPage);
 238                         else
 239                                 $oCmd = new COM('ADODB.Command');
 240                         $oCmd->ActiveConnection = $dbc;
 241                         $oCmd->CommandText = $sql;
 242                         $oCmd->CommandType = 1;
 243
 244                         foreach($inputarr as $val) {
 245                                 // name, type, direction 1 = input, len,
 246                                 $this->adoParameterType = 130;
 247                                 $p = $oCmd->CreateParameter('name',$this->adoParameterType,1,strlen($val),$val);
 248                                 //print $p->Type.' '.$p->value;
 249                                 $oCmd->Parameters->Append($p);
 250                         }
 251                         $p = false;
 252                         $rs = $oCmd->Execute();
 253                         $e = $dbc->Errors;
 254                         if ($dbc->Errors->Count > 0) return $false;
 255                         return $rs;
 256                 }
 257
 258                 $rs = @$dbc->Execute($sql,$this->_affectedRows, $this->_execute_option);
 259
 260                 if ($dbc->Errors->Count > 0) return $false;
 261                 if (! $rs) return $false;
 262
 263                 if ($rs->State == 0) {
 264                         $true = true;
 265                         return $true; // 0 = adStateClosed means no records returned
 266                 }
 267                 return $rs;
 268
 269                 } catch (exception $e) {
 270
 271                 }
 272                 return $false;
 273         }
 274
 275
 276         function BeginTrans()
 277         {
 278                 if ($this->transOff) return true;
 279
 280                 if (isset($this->_thisTransactions))
 281                         if (!$this->_thisTransactions) return false;
 282                 else {
 283                         $o = $this->_connectionID->Properties("Transaction DDL");
 284                         $this->_thisTransactions = $o ? true : false;
 285                         if (!$o) return false;
 286                 }
 287                 @$this->_connectionID->BeginTrans();
 288                 $this->transCnt += 1;
 289                 return true;
 290         }
 291         function CommitTrans($ok=true)
 292         {
 293                 if (!$ok) return $this->RollbackTrans();
 294                 if ($this->transOff) return true;
 295
 296                 @$this->_connectionID->CommitTrans();
 297                 if ($this->transCnt) @$this->transCnt -= 1;
 298                 return true;
 299         }
 300         function RollbackTrans() {
 301                 if ($this->transOff) return true;
 302                 @$this->_connectionID->RollbackTrans();
 303                 if ($this->transCnt) @$this->transCnt -= 1;
 304                 return true;
 305         }
 306
 307         /*      Returns: the last error message from previous database operation        */
 308
 309         function ErrorMsg()
 310         {
 311                 if (!$this->_connectionID) return "No connection established";
 312                 $errmsg = '';
 313
 314                 try {
 315                         $errc = $this->_connectionID->Errors;
 316                         if (!$errc) return "No Errors object found";
 317                         if ($errc->Count == 0) return '';
 318                         $err = $errc->Item($errc->Count-1);
 319                         $errmsg = $err->Description;
 320                 }catch(exception $e) {
 321                 }
 322                 return $errmsg;
 323         }
 324
 325         function ErrorNo()
 326         {
 327                 $errc = $this->_connectionID->Errors;
 328                 if ($errc->Count == 0) return 0;
 329                 $err = $errc->Item($errc->Count-1);
 330                 return $err->NativeError;
 331         }
 332
 333         // returns true or false
 334         function _close()
 335         {
 336                 if ($this->_connectionID) $this->_connectionID->Close();
 337                 $this->_connectionID = false;
 338                 return true;
 339         }
 340
 341
 342 }
 343
 344 /*--------------------------------------------------------------------------------------
 345          Class Name: Recordset
 346 --------------------------------------------------------------------------------------*/
 347
 348 class ADORecordSet_ado extends ADORecordSet {
 349
 350         var $bind = false;
 351         var $databaseType = "ado";
 352         var $dataProvider = "ado";
 353         var $_tarr = false; // caches the types
 354         var $_flds; // and field objects
 355         var $canSeek = true;
 356         var $hideErrors = true;
 357
 358         function ADORecordSet_ado($id,$mode=false)
 359         {
 360                 if ($mode === false) {
 361                         global $ADODB_FETCH_MODE;
 362                         $mode = $ADODB_FETCH_MODE;
 363                 }
 364                 $this->fetchMode = $mode;
 365                 return $this->ADORecordSet($id,$mode);
 366         }
 367
 368
 369         // returns the field object
 370         function &FetchField($fieldOffset = -1) {
 371                 $off=$fieldOffset+1; // offsets begin at 1
 372
 373                 $o= new ADOFieldObject();
 374                 $rs = $this->_queryID;
 375                 $f = $rs->Fields($fieldOffset);
 376                 $o->name = $f->Name;
 377                 $t = $f->Type;
 378                 $o->type = $this->MetaType($t);
 379                 $o->max_length = $f->DefinedSize;
 380                 $o->ado_type = $t;
 381
 382
 383                 //print "off=$off name=$o->name type=$o->type len=$o->max_length<br>";
 384                 return $o;
 385         }
 386
 387         /* Use associative array to get fields array */
 388         function Fields($colname)
 389         {
 390                 if ($this->fetchMode & ADODB_FETCH_ASSOC) return $this->fields[$colname];
 391                 if (!$this->bind) {
 392                         $this->bind = array();
 393                         for ($i=0; $i < $this->_numOfFields; $i++) {
 394                                 $o = $this->FetchField($i);
 395                                 $this->bind[strtoupper($o->name)] = $i;
 396                         }
 397                 }
 398
 399                  return $this->fields[$this->bind[strtoupper($colname)]];
 400         }
 401
 402
 403         function _initrs()
 404         {
 405                 $rs = $this->_queryID;
 406                 $this->_numOfRows = $rs->RecordCount;
 407
 408                 $f = $rs->Fields;
 409                 $this->_numOfFields = $f->Count;
 410         }
 411
 412
 413          // should only be used to move forward as we normally use forward-only cursors
 414         function _seek($row)
 415         {
 416            $rs = $this->_queryID;
 417                 // absoluteposition doesn't work -- my maths is wrong ?
 418                 //      $rs->AbsolutePosition->$row-2;
 419                 //      return true;
 420                 if ($this->_currentRow > $row) return false;
 421                 @$rs->Move((integer)$row - $this->_currentRow-1); //adBookmarkFirst
 422                 return true;
 423         }
 424
 425 /*
 426         OLEDB types
 427
 428          enum DBTYPEENUM
 429         {       DBTYPE_EMPTY    = 0,
 430         DBTYPE_NULL     = 1,
 431         DBTYPE_I2       = 2,
 432         DBTYPE_I4       = 3,
 433         DBTYPE_R4       = 4,
 434         DBTYPE_R8       = 5,
 435         DBTYPE_CY       = 6,
 436         DBTYPE_DATE     = 7,
 437         DBTYPE_BSTR     = 8,
 438         DBTYPE_IDISPATCH        = 9,
 439         DBTYPE_ERROR    = 10,
 440         DBTYPE_BOOL     = 11,
 441         DBTYPE_VARIANT  = 12,
 442         DBTYPE_IUNKNOWN = 13,
 443         DBTYPE_DECIMAL  = 14,
 444         DBTYPE_UI1      = 17,
 445         DBTYPE_ARRAY    = 0x2000,
 446         DBTYPE_BYREF    = 0x4000,
 447         DBTYPE_I1       = 16,
 448         DBTYPE_UI2      = 18,
 449         DBTYPE_UI4      = 19,
 450         DBTYPE_I8       = 20,
 451         DBTYPE_UI8      = 21,
 452         DBTYPE_GUID     = 72,
 453         DBTYPE_VECTOR   = 0x1000,
 454         DBTYPE_RESERVED = 0x8000,
 455         DBTYPE_BYTES    = 128,
 456         DBTYPE_STR      = 129,
 457         DBTYPE_WSTR     = 130,
 458         DBTYPE_NUMERIC  = 131,
 459         DBTYPE_UDT      = 132,
 460         DBTYPE_DBDATE   = 133,
 461         DBTYPE_DBTIME   = 134,
 462         DBTYPE_DBTIMESTAMP      = 135
 463
 464         ADO Types
 465
 466         adEmpty = 0,
 467         adTinyInt       = 16,
 468         adSmallInt      = 2,
 469         adInteger       = 3,
 470         adBigInt        = 20,
 471         adUnsignedTinyInt       = 17,
 472         adUnsignedSmallInt      = 18,
 473         adUnsignedInt   = 19,
 474         adUnsignedBigInt        = 21,
 475         adSingle        = 4,
 476         adDouble        = 5,
 477         adCurrency      = 6,
 478         adDecimal       = 14,
 479         adNumeric       = 131,
 480         adBoolean       = 11,
 481         adError = 10,
 482         adUserDefined   = 132,
 483         adVariant       = 12,
 484         adIDispatch     = 9,
 485         adIUnknown      = 13,
 486         adGUID  = 72,
 487         adDate  = 7,
 488         adDBDate        = 133,
 489         adDBTime        = 134,
 490         adDBTimeStamp   = 135,
 491         adBSTR  = 8,
 492         adChar  = 129,
 493         adVarChar       = 200,
 494         adLongVarChar   = 201,
 495         adWChar = 130,
 496         adVarWChar      = 202,
 497         adLongVarWChar  = 203,
 498         adBinary        = 128,
 499         adVarBinary     = 204,
 500         adLongVarBinary = 205,
 501         adChapter       = 136,
 502         adFileTime      = 64,
 503         adDBFileTime    = 137,
 504         adPropVariant   = 138,
 505         adVarNumeric    = 139
 506 */
 507         function MetaType($t,$len=-1,$fieldobj=false)
 508         {
 509                 if (is_object($t)) {
 510                         $fieldobj = $t;
 511                         $t = $fieldobj->type;
 512                         $len = $fieldobj->max_length;
 513                 }
 514
 515                 if (!is_numeric($t)) return $t;
 516
 517                 switch ($t) {
 518                 case 0:
 519                 case 12: // variant
 520                 case 8: // bstr
 521                 case 129: //char
 522                 case 130: //wc
 523                 case 200: // varc
 524                 case 202:// varWC
 525                 case 128: // bin
 526                 case 204: // varBin
 527                 case 72: // guid
 528                         if ($len <= $this->blobSize) return 'C';
 529
 530                 case 201:
 531                 case 203:
 532                         return 'X';
 533                 case 128:
 534                 case 204:
 535                 case 205:
 536                          return 'B';
 537                 case 7:
 538                 case 133: return 'D';
 539
 540                 case 134:
 541                 case 135: return 'T';
 542
 543                 case 11: return 'L';
 544
 545                 case 16://      adTinyInt       = 16,
 546                 case 2://adSmallInt     = 2,
 547                 case 3://adInteger      = 3,
 548                 case 4://adBigInt       = 20,
 549                 case 17://adUnsignedTinyInt     = 17,
 550                 case 18://adUnsignedSmallInt    = 18,
 551                 case 19://adUnsignedInt = 19,
 552                 case 20://adUnsignedBigInt      = 21,
 553                         return 'I';
 554                 default: return 'N';
 555                 }
 556         }
 557
 558         // time stamp not supported yet
 559         function _fetch()
 560         {
 561                 $rs = $this->_queryID;
 562                 if (!$rs or $rs->EOF) {
 563                         $this->fields = false;
 564                         return false;
 565                 }
 566                 $this->fields = array();
 567
 568                 if (!$this->_tarr) {
 569                         $tarr = array();
 570                         $flds = array();
 571                         for ($i=0,$max = $this->_numOfFields; $i < $max; $i++) {
 572                                 $f = $rs->Fields($i);
 573                                 $flds[] = $f;
 574                                 $tarr[] = $f->Type;
 575                         }
 576                         // bind types and flds only once
 577                         $this->_tarr = $tarr;
 578                         $this->_flds = $flds;
 579                 }
 580                 $t = reset($this->_tarr);
 581                 $f = reset($this->_flds);
 582
 583                 if ($this->hideErrors)  $olde = error_reporting(E_ERROR|E_CORE_ERROR);// sometimes $f->value be null
 584                 for ($i=0,$max = $this->_numOfFields; $i < $max; $i++) {
 585                         //echo "<p>",$t,' ';var_dump($f->value); echo '</p>';
 586                         switch($t) {
 587                         case 135: // timestamp
 588                                 if (!strlen((string)$f->value)) $this->fields[] = false;
 589                                 else {
 590                                         if (!is_numeric($f->value)) # $val = variant_date_to_timestamp($f->value);
 591                                                 // VT_DATE stores dates as (float) fractional days since 1899/12/30 00:00:00
 592                                                 $val= (float) variant_cast($f->value,VT_R8)*3600*24-2209161600;
 593                                         else
 594                                                 $val = $f->value;
 595                                         $this->fields[] = adodb_date('Y-m-d H:i:s',$val);
 596                                 }
 597                                 break;
 598                         case 133:// A date value (yyyymmdd)
 599                                 if ($val = $f->value) {
 600                                         $this->fields[] = substr($val,0,4).'-'.substr($val,4,2).'-'.substr($val,6,2);
 601                                 } else
 602                                         $this->fields[] = false;
 603                                 break;
 604                         case 7: // adDate
 605                                 if (!strlen((string)$f->value)) $this->fields[] = false;
 606                                 else {
 607                                         if (!is_numeric($f->value)) $val = variant_date_to_timestamp($f->value);
 608                                         else $val = $f->value;
 609
 610                                         if (($val % 86400) == 0) $this->fields[] = adodb_date('Y-m-d',$val);
 611                                         else $this->fields[] = adodb_date('Y-m-d H:i:s',$val);
 612                                 }
 613                                 break;
 614                         case 1: // null
 615                                 $this->fields[] = false;
 616                                 break;
 617                         case 6: // currency is not supported properly;
 618                                 ADOConnection::outp( '<b>'.$f->Name.': currency type not supported by PHP</b>');
 619                                 $this->fields[] = (float) $f->value;
 620                                 break;
 621                         default:
 622                                 $this->fields[] = $f->value;
 623                                 break;
 624                         }
 625                         //print " $f->value $t, ";
 626                         $f = next($this->_flds);
 627                         $t = next($this->_tarr);
 628                 } // for
 629                 if ($this->hideErrors) error_reporting($olde);
 630                 @$rs->MoveNext(); // @ needed for some versions of PHP!
 631
 632                 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
 633                         $this->fields = &$this->GetRowAssoc(ADODB_ASSOC_CASE);
 634                 }
 635                 return true;
 636         }
 637
 638                 function NextRecordSet()
 639                 {
 640                         $rs = $this->_queryID;
 641                         $this->_queryID = $rs->NextRecordSet();
 642                         //$this->_queryID = $this->_QueryId->NextRecordSet();
 643                         if ($this->_queryID == null) return false;
 644
 645                         $this->_currentRow = -1;
 646                         $this->_currentPage = -1;
 647                         $this->bind = false;
 648                         $this->fields = false;
 649                         $this->_flds = false;
 650                         $this->_tarr = false;
 651
 652                         $this->_inited = false;
 653                         $this->Init();
 654                         return true;
 655                 }
 656
 657         function _close() {
 658                 $this->_flds = false;
 659                 @$this->_queryID->Close();// by Pete Dishman (peterd@telephonetics.co.uk)
 660                 $this->_queryID = false;
 661         }
 662
 663 }
 664
 665 ?>