lib/WikiDB/adodb/drivers/adodb-ado.inc.php

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