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
19 var $databaseType = "ado";
20 var $_bindInputArray = false;
21 var $fmtDate = "'Y-m-d'";
22 var $fmtTimeStamp = "'Y-m-d, h:i:sA'";
23 var $replaceQuote = "''"; // string to use to replace quotes
24 var $dataProvider = "ado";
25 var $hasAffectedRows = true;
26 var $adoParameterType = 201; // 201 = long varchar, 203=long wide varchar, 205 = long varbinary
27 var $_affectedRows = false;
28 var $_thisTransactions;
29 var $_cursor_type = 3; // 3=adOpenStatic,0=adOpenForwardOnly,1=adOpenKeyset,2=adOpenDynamic
30 var $_cursor_location = 3; // 2=adUseServer, 3 = adUseClient;
32 var $_execute_option = -1;
33 var $poorAffectedRows = true;
38 $this->_affectedRows = new VARIANT;
43 if (!empty($this->_connectionID)) $desc = $this->_connectionID->provider;
44 return array('description' => $desc, 'version' => '');
47 function _affectedrows()
49 if (PHP_VERSION >= 5) return $this->_affectedRows;
51 return $this->_affectedRows->value;
54 // you can also pass a connection string like this:
56 // $DB->Connect('USER ID=sa;PASSWORD=pwd;SERVER=mangrove;DATABASE=ai',false,false,'SQLOLEDB');
57 function _connect($argHostname, $argUsername, $argPassword, $argProvider = 'MSDASQL')
62 if (!empty($this->charPage))
63 $dbc = new COM('ADODB.Connection', null, $this->charPage);
65 $dbc = new COM('ADODB.Connection');
67 if (!$dbc) return false;
69 /* special support if provider is mssql or access */
70 if ($argProvider == 'mssql') {
71 $u = 'User Id'; //User parameter name for OLEDB
73 $argProvider = "SQLOLEDB"; // SQL Server Provider
76 //if ($argDatabasename) $argHostname .= ";Initial Catalog=$argDatabasename";
78 //use trusted conection for SQL if username not specified
79 if (!$argUsername) $argHostname .= ";Trusted_Connection=Yes";
80 } elseif ($argProvider == 'access')
81 $argProvider = "Microsoft.Jet.OLEDB.4.0"; // Microsoft Jet Provider
83 if ($argProvider) $dbc->Provider = $argProvider;
85 if ($argUsername) $argHostname .= ";$u=$argUsername";
86 if ($argPassword) $argHostname .= ";$p=$argPassword";
88 if ($this->debug) ADOConnection::outp("Host=" . $argHostname . "<BR>\n version=$dbc->version");
89 // @ added below for php 4.0.1 and earlier
90 @$dbc->Open((string)$argHostname);
92 $this->_connectionID = $dbc;
94 $dbc->CursorLocation = $this->_cursor_location;
95 return $dbc->State > 0;
98 // returns true or false
99 function _pconnect($argHostname, $argUsername, $argPassword, $argProvider = 'MSDASQL')
101 return $this->_connect($argHostname, $argUsername, $argPassword, $argProvider);
105 adSchemaCatalogs = 1,
106 adSchemaCharacterSets = 2,
107 adSchemaCollations = 3,
109 adSchemaCheckConstraints = 5,
110 adSchemaConstraintColumnUsage = 6,
111 adSchemaConstraintTableUsage = 7,
112 adSchemaKeyColumnUsage = 8,
113 adSchemaReferentialContraints = 9,
114 adSchemaTableConstraints = 10,
115 adSchemaColumnsDomainUsage = 11,
116 adSchemaIndexes = 12,
117 adSchemaColumnPrivileges = 13,
118 adSchemaTablePrivileges = 14,
119 adSchemaUsagePrivileges = 15,
120 adSchemaProcedures = 16,
121 adSchemaSchemata = 17,
122 adSchemaSQLLanguages = 18,
123 adSchemaStatistics = 19,
125 adSchemaTranslations = 21,
126 adSchemaProviderTypes = 22,
128 adSchemaViewColumnUsage = 24,
129 adSchemaViewTableUsage = 25,
130 adSchemaProcedureParameters = 26,
131 adSchemaForeignKeys = 27,
132 adSchemaPrimaryKeys = 28,
133 adSchemaProcedureColumns = 29,
134 adSchemaDBInfoKeywords = 30,
135 adSchemaDBInfoLiterals = 31,
137 adSchemaDimensions = 33,
138 adSchemaHierarchies = 34,
140 adSchemaMeasures = 36,
141 adSchemaProperties = 37,
146 function &MetaTables()
149 $dbc = $this->_connectionID;
151 $adors = @$dbc->OpenSchema(20); //tables
153 $f = $adors->Fields(2); //table/view name
154 $t = $adors->Fields(3); //table type
155 while (!$adors->EOF) {
156 $tt = substr($t->value, 0, 6);
157 if ($tt != 'SYSTEM' && $tt != 'ACCESS')
159 //print $f->value . ' ' . $t->value.'<br>';
168 function &MetaColumns($table)
170 $table = strtoupper($table);
172 $dbc = $this->_connectionID;
174 $adors = @$dbc->OpenSchema(4); //tables
177 $t = $adors->Fields(2); //table/view name
178 while (!$adors->EOF) {
180 if (strtoupper($t->Value) == $table) {
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;
198 /* returns queryID or false */
199 function &_query($sql, $inputarr = false)
202 $dbc = $this->_connectionID;
207 if (!empty($this->charPage))
208 $oCmd = new COM('ADODB.Command', null, $this->charPage);
210 $oCmd = new COM('ADODB.Command');
211 $oCmd->ActiveConnection = $dbc;
212 $oCmd->CommandText = $sql;
213 $oCmd->CommandType = 1;
215 foreach ($inputarr as $val) {
216 // name, type, direction 1 = input, len,
217 $this->adoParameterType = 130;
218 $p = $oCmd->CreateParameter('name', $this->adoParameterType, 1, strlen($val), $val);
219 //print $p->Type.' '.$p->value;
220 $oCmd->Parameters->Append($p);
223 $rs = $oCmd->Execute();
225 if ($dbc->Errors->Count > 0) return false;
229 $rs = @$dbc->Execute($sql, $this->_affectedRows, $this->_execute_option);
231 $rs = new COM('ADODB.Recordset');
233 $rs->Open ($sql, $dbc, $this->_cursor_type,$this->_lock_type, $this->_execute_option);
236 if ($dbc->Errors->Count > 0) return false;
237 if (!$rs) return false;
239 if ($rs->State == 0) return true; // 0 = adStateClosed means no records returned
243 function BeginTrans()
245 if ($this->transOff) return true;
247 if (isset($this->_thisTransactions))
248 if (!$this->_thisTransactions) return false;
250 $o = $this->_connectionID->Properties("Transaction DDL");
251 $this->_thisTransactions = $o ? true : false;
252 if (!$o) return false;
254 @$this->_connectionID->BeginTrans();
255 $this->transCnt += 1;
259 function CommitTrans($ok = true)
261 if (!$ok) return $this->RollbackTrans();
262 if ($this->transOff) return true;
264 @$this->_connectionID->CommitTrans();
265 if ($this->transCnt) @$this->transCnt -= 1;
269 function RollbackTrans()
271 if ($this->transOff) return true;
272 @$this->_connectionID->RollbackTrans();
273 if ($this->transCnt) @$this->transCnt -= 1;
277 /* Returns: the last error message from previous database operation */
281 $errc = $this->_connectionID->Errors;
282 if ($errc->Count == 0) return '';
283 $err = $errc->Item($errc->Count - 1);
284 return $err->Description;
289 $errc = $this->_connectionID->Errors;
290 if ($errc->Count == 0) return 0;
291 $err = $errc->Item($errc->Count - 1);
292 return $err->NativeError;
295 // returns true or false
298 if ($this->_connectionID) $this->_connectionID->Close();
299 $this->_connectionID = false;
305 /*--------------------------------------------------------------------------------------
306 Class Name: Recordset
307 --------------------------------------------------------------------------------------*/
309 class ADORecordSet_ado extends ADORecordSet
313 var $databaseType = "ado";
314 var $dataProvider = "ado";
315 var $_tarr = false; // caches the types
316 var $_flds; // and field objects
318 var $hideErrors = true;
320 function ADORecordSet_ado($id, $mode = false)
322 if ($mode === false) {
323 global $ADODB_FETCH_MODE;
324 $mode = $ADODB_FETCH_MODE;
326 $this->fetchMode = $mode;
327 return $this->ADORecordSet($id, $mode);
330 // returns the field object
331 function FetchField($fieldOffset = -1)
333 $off = $fieldOffset + 1; // offsets begin at 1
335 $o = new ADOFieldObject();
336 $rs = $this->_queryID;
337 $f = $rs->Fields($fieldOffset);
340 $o->type = $this->MetaType($t);
341 $o->max_length = $f->DefinedSize;
344 //print "off=$off name=$o->name type=$o->type len=$o->max_length<br>";
348 /* Use associative array to get fields array */
349 function Fields($colname)
351 if ($this->fetchMode & ADODB_FETCH_ASSOC) return $this->fields[$colname];
353 $this->bind = array();
354 for ($i = 0; $i < $this->_numOfFields; $i++) {
355 $o = $this->FetchField($i);
356 $this->bind[strtoupper($o->name)] = $i;
360 return $this->fields[$this->bind[strtoupper($colname)]];
365 $rs = $this->_queryID;
366 $this->_numOfRows = $rs->RecordCount;
369 $this->_numOfFields = $f->Count;
372 // should only be used to move forward as we normally use forward-only cursors
375 $rs = $this->_queryID;
376 // absoluteposition doesn't work -- my maths is wrong ?
377 // $rs->AbsolutePosition->$row-2;
379 if ($this->_currentRow > $row) return false;
380 @$rs->Move((integer)$row - $this->_currentRow - 1); //adBookmarkFirst
397 DBTYPE_IDISPATCH = 9,
401 DBTYPE_IUNKNOWN = 13,
404 DBTYPE_ARRAY = 0x2000,
405 DBTYPE_BYREF = 0x4000,
412 DBTYPE_VECTOR = 0x1000,
413 DBTYPE_RESERVED = 0x8000,
417 DBTYPE_NUMERIC = 131,
421 DBTYPE_DBTIMESTAMP = 135
430 adUnsignedTinyInt = 17,
431 adUnsignedSmallInt = 18,
433 adUnsignedBigInt = 21,
456 adLongVarWChar = 203,
459 adLongVarBinary = 205,
466 function MetaType($t, $len = -1, $fieldobj = false)
470 $t = $fieldobj->type;
471 $len = $fieldobj->max_length;
474 if (!is_numeric($t)) return $t;
487 if ($len <= $this->blobSize) return 'C';
507 case 16: // adTinyInt = 16,
508 case 2: //adSmallInt = 2,
509 case 3: //adInteger = 3,
510 case 4: //adBigInt = 20,
511 case 17: //adUnsignedTinyInt = 17,
512 case 18: //adUnsignedSmallInt = 18,
513 case 19: //adUnsignedInt = 19,
514 case 20: //adUnsignedBigInt = 21,
521 // time stamp not supported yet
524 $rs = $this->_queryID;
525 if (!$rs or $rs->EOF) {
526 $this->fields = false;
529 $this->fields = array();
534 for ($i = 0, $max = $this->_numOfFields; $i < $max; $i++) {
535 $f = $rs->Fields($i);
539 // bind types and flds only once
540 $this->_tarr = $tarr;
541 $this->_flds = $flds;
543 $t = reset($this->_tarr);
544 $f = reset($this->_flds);
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++) {
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);
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);
558 $this->fields[] = false;
561 if (!strlen((string)$f->value)) $this->fields[] = false;
562 else $this->fields[] = adodb_date('Y-m-d', (float)$f->value);
565 $this->fields[] = false;
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;
572 $this->fields[] = $f->value;
575 //print " $f->value $t, ";
576 $f = next($this->_flds);
577 $t = next($this->_tarr);
579 if ($this->hideErrors) error_reporting($olde);
580 @$rs->MoveNext(); // @ needed for some versions of PHP!
582 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
583 $this->fields = &$this->GetRowAssoc(ADODB_ASSOC_CASE);
588 function NextRecordSet()
590 $rs = $this->_queryID;
591 $this->_queryID = $rs->NextRecordSet();
592 //$this->_queryID = $this->_QueryId->NextRecordSet();
593 if ($this->_queryID == null) return false;
595 $this->_currentRow = -1;
596 $this->_currentPage = -1;
598 $this->fields = false;
599 $this->_flds = false;
600 $this->_tarr = false;
602 $this->_inited = false;
609 $this->_flds = false;
610 @$this->_queryID->Close(); // by Pete Dishman (peterd@telephonetics.co.uk)
611 $this->_queryID = false;