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.
9 Latest version is available at http://php.weblogs.com/
11 Microsoft ADO data driver. Requires ADO. Works only on MS Windows.
13 define("_ADODB_ADO_LAYER", 1 );
14 /*--------------------------------------------------------------------------------------
15 --------------------------------------------------------------------------------------*/
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;
31 var $_execute_option = -1;
32 var $poorAffectedRows = true;
37 $this->_affectedRows = new VARIANT;
42 if (!empty($this->_connectionID)) $desc = $this->_connectionID->provider;
43 return array('description' => $desc, 'version' => '');
46 function _affectedrows()
48 if (PHP_VERSION >= 5) return $this->_affectedRows;
50 return $this->_affectedRows->value;
53 // you can also pass a connection string like this:
55 // $DB->Connect('USER ID=sa;PASSWORD=pwd;SERVER=mangrove;DATABASE=ai',false,false,'SQLOLEDB');
56 function _connect($argHostname, $argUsername, $argPassword, $argProvider= 'MSDASQL')
61 if (!empty($this->charPage))
62 $dbc = new COM('ADODB.Connection',null,$this->charPage);
64 $dbc = new COM('ADODB.Connection');
66 if (! $dbc) return false;
68 /* special support if provider is mssql or access */
69 if ($argProvider=='mssql') {
70 $u = 'User Id'; //User parameter name for OLEDB
72 $argProvider = "SQLOLEDB"; // SQL Server Provider
75 //if ($argDatabasename) $argHostname .= ";Initial Catalog=$argDatabasename";
77 //use trusted conection for SQL if username not specified
78 if (!$argUsername) $argHostname .= ";Trusted_Connection=Yes";
79 } elseif ($argProvider=='access')
80 $argProvider = "Microsoft.Jet.OLEDB.4.0"; // Microsoft Jet Provider
82 if ($argProvider) $dbc->Provider = $argProvider;
84 if ($argUsername) $argHostname .= ";$u=$argUsername";
85 if ($argPassword)$argHostname .= ";$p=$argPassword";
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);
91 $this->_connectionID = $dbc;
93 $dbc->CursorLocation = $this->_cursor_location;
94 return $dbc->State > 0;
97 // returns true or false
98 function _pconnect($argHostname, $argUsername, $argPassword, $argProvider='MSDASQL')
100 return $this->_connect($argHostname,$argUsername,$argPassword,$argProvider);
104 adSchemaCatalogs = 1,
105 adSchemaCharacterSets = 2,
106 adSchemaCollations = 3,
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,
124 adSchemaTranslations = 21,
125 adSchemaProviderTypes = 22,
127 adSchemaViewColumnUsage = 24,
128 adSchemaViewTableUsage = 25,
129 adSchemaProcedureParameters = 26,
130 adSchemaForeignKeys = 27,
131 adSchemaPrimaryKeys = 28,
132 adSchemaProcedureColumns = 29,
133 adSchemaDBInfoKeywords = 30,
134 adSchemaDBInfoLiterals = 31,
136 adSchemaDimensions = 33,
137 adSchemaHierarchies = 34,
139 adSchemaMeasures = 36,
140 adSchemaProperties = 37,
145 function &MetaTables()
148 $dbc = $this->_connectionID;
150 $adors=@$dbc->OpenSchema(20);//tables
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')
158 //print $f->value . ' ' . $t->value.'<br>';
167 function &MetaColumns($table)
169 $table = strtoupper($table);
171 $dbc = $this->_connectionID;
173 $adors=@$dbc->OpenSchema(4);//tables
176 $t = $adors->Fields(2);//table/view name
177 while (!$adors->EOF){
179 if (strtoupper($t->Value) == $table) {
181 $fld = new ADOFieldObject();
182 $c = $adors->Fields(3);
183 $fld->name = $c->Value;
184 $fld->type = 'CHAR'; // cannot discover type in ADO!
185 $fld->max_length = -1;
186 $arr[strtoupper($fld->name)]=$fld;
197 /* returns queryID or false */
198 function &_query($sql,$inputarr=false)
201 $dbc = $this->_connectionID;
206 if (!empty($this->charPage))
207 $oCmd = new COM('ADODB.Command',null,$this->charPage);
209 $oCmd = new COM('ADODB.Command');
210 $oCmd->ActiveConnection = $dbc;
211 $oCmd->CommandText = $sql;
212 $oCmd->CommandType = 1;
214 foreach($inputarr as $val) {
215 // name, type, direction 1 = input, len,
216 $this->adoParameterType = 130;
217 $p = $oCmd->CreateParameter('name',$this->adoParameterType,1,strlen($val),$val);
218 //print $p->Type.' '.$p->value;
219 $oCmd->Parameters->Append($p);
222 $rs = $oCmd->Execute();
224 if ($dbc->Errors->Count > 0) return false;
228 $rs = @$dbc->Execute($sql,$this->_affectedRows, $this->_execute_option);
230 $rs = new COM('ADODB.Recordset');
232 $rs->Open ($sql, $dbc, $this->_cursor_type,$this->_lock_type, $this->_execute_option);
235 if ($dbc->Errors->Count > 0) return false;
236 if (! $rs) return false;
238 if ($rs->State == 0) return true; // 0 = adStateClosed means no records returned
242 function BeginTrans()
244 if ($this->transOff) return true;
246 if (isset($this->_thisTransactions))
247 if (!$this->_thisTransactions) return false;
249 $o = $this->_connectionID->Properties("Transaction DDL");
250 $this->_thisTransactions = $o ? true : false;
251 if (!$o) return false;
253 @$this->_connectionID->BeginTrans();
254 $this->transCnt += 1;
257 function CommitTrans($ok=true)
259 if (!$ok) return $this->RollbackTrans();
260 if ($this->transOff) return true;
262 @$this->_connectionID->CommitTrans();
263 if ($this->transCnt) @$this->transCnt -= 1;
266 function RollbackTrans() {
267 if ($this->transOff) return true;
268 @$this->_connectionID->RollbackTrans();
269 if ($this->transCnt) @$this->transCnt -= 1;
273 /* Returns: the last error message from previous database operation */
277 $errc = $this->_connectionID->Errors;
278 if ($errc->Count == 0) return '';
279 $err = $errc->Item($errc->Count-1);
280 return $err->Description;
285 $errc = $this->_connectionID->Errors;
286 if ($errc->Count == 0) return 0;
287 $err = $errc->Item($errc->Count-1);
288 return $err->NativeError;
291 // returns true or false
294 if ($this->_connectionID) $this->_connectionID->Close();
295 $this->_connectionID = false;
301 /*--------------------------------------------------------------------------------------
302 Class Name: Recordset
303 --------------------------------------------------------------------------------------*/
305 class ADORecordSet_ado extends ADORecordSet {
308 var $databaseType = "ado";
309 var $dataProvider = "ado";
310 var $_tarr = false; // caches the types
311 var $_flds; // and field objects
313 var $hideErrors = true;
315 function ADORecordSet_ado($id,$mode=false)
317 if ($mode === false) {
318 global $ADODB_FETCH_MODE;
319 $mode = $ADODB_FETCH_MODE;
321 $this->fetchMode = $mode;
322 return $this->ADORecordSet($id,$mode);
325 // returns the field object
326 function FetchField($fieldOffset = -1) {
327 $off=$fieldOffset+1; // offsets begin at 1
329 $o= new ADOFieldObject();
330 $rs = $this->_queryID;
331 $f = $rs->Fields($fieldOffset);
334 $o->type = $this->MetaType($t);
335 $o->max_length = $f->DefinedSize;
338 //print "off=$off name=$o->name type=$o->type len=$o->max_length<br>";
342 /* Use associative array to get fields array */
343 function Fields($colname)
345 if ($this->fetchMode & ADODB_FETCH_ASSOC) return $this->fields[$colname];
347 $this->bind = array();
348 for ($i=0; $i < $this->_numOfFields; $i++) {
349 $o = $this->FetchField($i);
350 $this->bind[strtoupper($o->name)] = $i;
354 return $this->fields[$this->bind[strtoupper($colname)]];
359 $rs = $this->_queryID;
360 $this->_numOfRows = $rs->RecordCount;
363 $this->_numOfFields = $f->Count;
366 // should only be used to move forward as we normally use forward-only cursors
369 $rs = $this->_queryID;
370 // absoluteposition doesn't work -- my maths is wrong ?
371 // $rs->AbsolutePosition->$row-2;
373 if ($this->_currentRow > $row) return false;
374 @$rs->Move((integer)$row - $this->_currentRow-1); //adBookmarkFirst
391 DBTYPE_IDISPATCH = 9,
395 DBTYPE_IUNKNOWN = 13,
398 DBTYPE_ARRAY = 0x2000,
399 DBTYPE_BYREF = 0x4000,
406 DBTYPE_VECTOR = 0x1000,
407 DBTYPE_RESERVED = 0x8000,
411 DBTYPE_NUMERIC = 131,
415 DBTYPE_DBTIMESTAMP = 135
424 adUnsignedTinyInt = 17,
425 adUnsignedSmallInt = 18,
427 adUnsignedBigInt = 21,
450 adLongVarWChar = 203,
453 adLongVarBinary = 205,
460 function MetaType($t,$len=-1,$fieldobj=false)
464 $t = $fieldobj->type;
465 $len = $fieldobj->max_length;
468 if (!is_numeric($t)) return $t;
481 if ($len <= $this->blobSize) return 'C';
491 case 133: return 'D';
494 case 135: return 'T';
498 case 16:// adTinyInt = 16,
499 case 2://adSmallInt = 2,
500 case 3://adInteger = 3,
501 case 4://adBigInt = 20,
502 case 17://adUnsignedTinyInt = 17,
503 case 18://adUnsignedSmallInt = 18,
504 case 19://adUnsignedInt = 19,
505 case 20://adUnsignedBigInt = 21,
511 // time stamp not supported yet
514 $rs = $this->_queryID;
515 if (!$rs or $rs->EOF) {
516 $this->fields = false;
519 $this->fields = array();
524 for ($i=0,$max = $this->_numOfFields; $i < $max; $i++) {
525 $f = $rs->Fields($i);
529 // bind types and flds only once
530 $this->_tarr = $tarr;
531 $this->_flds = $flds;
533 $t = reset($this->_tarr);
534 $f = reset($this->_flds);
536 if ($this->hideErrors) $olde = error_reporting(E_ERROR|E_CORE_ERROR);// sometimes $f->value be null
537 for ($i=0,$max = $this->_numOfFields; $i < $max; $i++) {
540 case 135: // timestamp
541 if (!strlen((string)$f->value)) $this->fields[] = false;
542 else $this->fields[] = adodb_date('Y-m-d H:i:s',(float)$f->value);
544 case 133:// A date value (yyyymmdd)
545 if ($val = $f->value) {
546 $this->fields[] = substr($val,0,4).'-'.substr($val,4,2).'-'.substr($val,6,2);
548 $this->fields[] = false;
551 if (!strlen((string)$f->value)) $this->fields[] = false;
552 else $this->fields[] = adodb_date('Y-m-d',(float)$f->value);
555 $this->fields[] = false;
557 case 6: // currency is not supported properly;
558 ADOConnection::outp( '<b>'.$f->Name.': currency type not supported by PHP</b>');
559 $this->fields[] = (float) $f->value;
562 $this->fields[] = $f->value;
565 //print " $f->value $t, ";
566 $f = next($this->_flds);
567 $t = next($this->_tarr);
569 if ($this->hideErrors) error_reporting($olde);
570 @$rs->MoveNext(); // @ needed for some versions of PHP!
572 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
573 $this->fields = &$this->GetRowAssoc(ADODB_ASSOC_CASE);
578 function NextRecordSet()
580 $rs = $this->_queryID;
581 $this->_queryID = $rs->NextRecordSet();
582 //$this->_queryID = $this->_QueryId->NextRecordSet();
583 if ($this->_queryID == null) return false;
585 $this->_currentRow = -1;
586 $this->_currentPage = -1;
588 $this->fields = false;
589 $this->_flds = false;
590 $this->_tarr = false;
592 $this->_inited = false;
598 $this->_flds = false;
599 @$this->_queryID->Close();// by Pete Dishman (peterd@telephonetics.co.uk)
600 $this->_queryID = false;