library/adodb/drivers/adodb-ado.inc.php

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