3 V4.20 22 Feb 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 return $this->_affectedRows
->value
;
51 // you can also pass a connection string like this:
53 // $DB->Connect('USER ID=sa;PASSWORD=pwd;SERVER=mangrove;DATABASE=ai',false,false,'SQLOLEDB');
54 function _connect($argHostname, $argUsername, $argPassword, $argProvider= 'MSDASQL')
59 if (!empty($this->charPage
))
60 $dbc = new COM('ADODB.Connection',null,$this->charPage
);
62 $dbc = new COM('ADODB.Connection');
64 if (! $dbc) return false;
66 /* special support if provider is mssql or access */
67 if ($argProvider=='mssql') {
68 $u = 'User Id'; //User parameter name for OLEDB
70 $argProvider = "SQLOLEDB"; // SQL Server Provider
73 //if ($argDatabasename) $argHostname .= ";Initial Catalog=$argDatabasename";
75 //use trusted conection for SQL if username not specified
76 if (!$argUsername) $argHostname .= ";Trusted_Connection=Yes";
77 } else if ($argProvider=='access')
78 $argProvider = "Microsoft.Jet.OLEDB.4.0"; // Microsoft Jet Provider
80 if ($argProvider) $dbc->Provider
= $argProvider;
82 if ($argUsername) $argHostname .= ";$u=$argUsername";
83 if ($argPassword)$argHostname .= ";$p=$argPassword";
85 if ($this->debug
) ADOConnection
::outp( "Host=".$argHostname."<BR>\n version=$dbc->version");
86 // @ added below for php 4.0.1 and earlier
87 @$dbc->Open((string) $argHostname);
89 $this->_connectionID
= $dbc;
91 $dbc->CursorLocation
= $this->_cursor_location
;
92 return $dbc->State
> 0;
95 // returns true or false
96 function _pconnect($argHostname, $argUsername, $argPassword, $argProvider='MSDASQL')
98 return $this->_connect($argHostname,$argUsername,$argPassword,$argProvider);
102 adSchemaCatalogs = 1,
103 adSchemaCharacterSets = 2,
104 adSchemaCollations = 3,
106 adSchemaCheckConstraints = 5,
107 adSchemaConstraintColumnUsage = 6,
108 adSchemaConstraintTableUsage = 7,
109 adSchemaKeyColumnUsage = 8,
110 adSchemaReferentialContraints = 9,
111 adSchemaTableConstraints = 10,
112 adSchemaColumnsDomainUsage = 11,
113 adSchemaIndexes = 12,
114 adSchemaColumnPrivileges = 13,
115 adSchemaTablePrivileges = 14,
116 adSchemaUsagePrivileges = 15,
117 adSchemaProcedures = 16,
118 adSchemaSchemata = 17,
119 adSchemaSQLLanguages = 18,
120 adSchemaStatistics = 19,
122 adSchemaTranslations = 21,
123 adSchemaProviderTypes = 22,
125 adSchemaViewColumnUsage = 24,
126 adSchemaViewTableUsage = 25,
127 adSchemaProcedureParameters = 26,
128 adSchemaForeignKeys = 27,
129 adSchemaPrimaryKeys = 28,
130 adSchemaProcedureColumns = 29,
131 adSchemaDBInfoKeywords = 30,
132 adSchemaDBInfoLiterals = 31,
134 adSchemaDimensions = 33,
135 adSchemaHierarchies = 34,
137 adSchemaMeasures = 36,
138 adSchemaProperties = 37,
143 function &MetaTables()
146 $dbc = $this->_connectionID
;
148 $adors=@$dbc->OpenSchema(20);//tables
150 $f = $adors->Fields(2);//table/view name
151 $t = $adors->Fields(3);//table type
152 while (!$adors->EOF
){
153 $tt=substr($t->value
,0,6);
154 if ($tt!='SYSTEM' && $tt !='ACCESS')
156 //print $f->value . ' ' . $t->value.'<br>';
165 function &MetaColumns($table)
167 $table = strtoupper($table);
169 $dbc = $this->_connectionID
;
171 $adors=@$dbc->OpenSchema(4);//tables
174 $t = $adors->Fields(2);//table/view name
175 while (!$adors->EOF
){
178 if (strtoupper($t->Value
) == $table) {
180 $fld = new ADOFieldObject();
181 $c = $adors->Fields(3);
182 $fld->name
= $c->Value
;
183 $fld->type
= 'CHAR'; // cannot discover type in ADO!
184 $fld->max_length
= -1;
185 $arr[strtoupper($fld->name
)]=$fld;
199 /* returns queryID or false */
200 function &_query($sql,$inputarr=false)
203 $dbc = $this->_connectionID
;
208 if (!empty($this->charPage
))
209 $oCmd = new COM('ADODB.Command',null,$this->charPage
);
211 $oCmd = new COM('ADODB.Command');
212 $oCmd->ActiveConnection
= $dbc;
213 $oCmd->CommandText
= $sql;
214 $oCmd->CommandType
= 1;
216 foreach($inputarr as $val) {
217 // name, type, direction 1 = input, len,
218 $this->adoParameterType
= 130;
219 $p = $oCmd->CreateParameter('name',$this->adoParameterType
,1,strlen($val),$val);
220 //print $p->Type.' '.$p->value;
221 $oCmd->Parameters
->Append($p);
224 $rs = $oCmd->Execute();
226 if ($dbc->Errors
->Count
> 0) return false;
230 $rs = @$dbc->Execute($sql,$this->_affectedRows
, $this->_execute_option
);
232 $rs = new COM('ADODB.Recordset');
234 $rs->Open ($sql, $dbc, $this->_cursor_type,$this->_lock_type, $this->_execute_option);
237 if ($dbc->Errors
->Count
> 0) return false;
238 if (! $rs) return false;
240 if ($rs->State
== 0) return true; // 0 = adStateClosed means no records returned
245 function BeginTrans()
247 if ($this->transOff
) return true;
249 if (isset($this->_thisTransactions
))
250 if (!$this->_thisTransactions
) return false;
252 $o = $this->_connectionID
->Properties("Transaction DDL");
253 $this->_thisTransactions
= $o ?
true : false;
254 if (!$o) return false;
256 @$this->_connectionID
->BeginTrans();
257 $this->transCnt +
= 1;
260 function CommitTrans($ok=true)
262 if (!$ok) return $this->RollbackTrans();
263 if ($this->transOff
) return true;
265 @$this->_connectionID
->CommitTrans();
266 if ($this->transCnt
) @$this->transCnt
-= 1;
269 function RollbackTrans() {
270 if ($this->transOff
) return true;
271 @$this->_connectionID
->RollbackTrans();
272 if ($this->transCnt
) @$this->transCnt
-= 1;
276 /* Returns: the last error message from previous database operation */
280 $errc = $this->_connectionID
->Errors
;
281 if ($errc->Count
== 0) return '';
282 $err = $errc->Item($errc->Count
-1);
283 return $err->Description
;
288 $errc = $this->_connectionID
->Errors
;
289 if ($errc->Count
== 0) return 0;
290 $err = $errc->Item($errc->Count
-1);
291 return $err->NativeError
;
294 // returns true or false
297 if ($this->_connectionID
) $this->_connectionID
->Close();
298 $this->_connectionID
= false;
305 /*--------------------------------------------------------------------------------------
306 Class Name: Recordset
307 --------------------------------------------------------------------------------------*/
309 class ADORecordSet_ado
extends ADORecordSet
{
312 var $databaseType = "ado";
313 var $dataProvider = "ado";
314 var $_tarr = false; // caches the types
315 var $_flds; // and field objects
317 var $hideErrors = true;
319 function ADORecordSet_ado($id,$mode=false)
321 if ($mode === false) {
322 global $ADODB_FETCH_MODE;
323 $mode = $ADODB_FETCH_MODE;
325 $this->fetchMode
= $mode;
326 return $this->ADORecordSet($id,$mode);
330 // returns the field object
331 function FetchField($fieldOffset = -1) {
332 $off=$fieldOffset+
1; // offsets begin at 1
334 $o= new ADOFieldObject();
335 $rs = $this->_queryID
;
336 $f = $rs->Fields($fieldOffset);
339 $o->type
= $this->MetaType($t);
340 $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)]];
366 $rs = $this->_queryID
;
367 $this->_numOfRows
= $rs->RecordCount
;
370 $this->_numOfFields
= $f->Count
;
374 // should only be used to move forward as we normally use forward-only cursors
377 $rs = $this->_queryID
;
378 // absoluteposition doesn't work -- my maths is wrong ?
379 // $rs->AbsolutePosition->$row-2;
381 if ($this->_currentRow
> $row) return false;
382 @$rs->Move((integer)$row - $this->_currentRow
-1); //adBookmarkFirst
399 DBTYPE_IDISPATCH = 9,
403 DBTYPE_IUNKNOWN = 13,
406 DBTYPE_ARRAY = 0x2000,
407 DBTYPE_BYREF = 0x4000,
414 DBTYPE_VECTOR = 0x1000,
415 DBTYPE_RESERVED = 0x8000,
419 DBTYPE_NUMERIC = 131,
423 DBTYPE_DBTIMESTAMP = 135
432 adUnsignedTinyInt = 17,
433 adUnsignedSmallInt = 18,
435 adUnsignedBigInt = 21,
458 adLongVarWChar = 203,
461 adLongVarBinary = 205,
468 function MetaType($t,$len=-1,$fieldobj=false)
472 $t = $fieldobj->type
;
473 $len = $fieldobj->max_length
;
476 if (!is_numeric($t)) return $t;
489 if ($len <= $this->blobSize
) return 'C';
499 case 133: return 'D';
502 case 135: return 'T';
506 case 16:// adTinyInt = 16,
507 case 2://adSmallInt = 2,
508 case 3://adInteger = 3,
509 case 4://adBigInt = 20,
510 case 17://adUnsignedTinyInt = 17,
511 case 18://adUnsignedSmallInt = 18,
512 case 19://adUnsignedInt = 19,
513 case 20://adUnsignedBigInt = 21,
519 // time stamp not supported yet
522 $rs = $this->_queryID
;
523 if (!$rs or $rs->EOF
) {
524 $this->fields
= false;
527 $this->fields
= array();
532 for ($i=0,$max = $this->_numOfFields
; $i < $max; $i++
) {
533 $f = $rs->Fields($i);
537 // bind types and flds only once
538 $this->_tarr
= $tarr;
539 $this->_flds
= $flds;
541 $t = reset($this->_tarr
);
542 $f = reset($this->_flds
);
544 if ($this->hideErrors
) $olde = error_reporting(E_ERROR|E_CORE_ERROR
);// sometimes $f->value be null
545 for ($i=0,$max = $this->_numOfFields
; $i < $max; $i++
) {
548 case 135: // timestamp
549 if (!strlen((string)$f->value
)) $this->fields
[] = false;
550 else $this->fields
[] = adodb_date('Y-m-d H:i:s',(float)$f->value
);
552 case 133:// A date value (yyyymmdd)
553 if ($val = $f->value
) {
554 $this->fields
[] = substr($val,0,4).'-'.substr($val,4,2).'-'.substr($val,6,2);
556 $this->fields
[] = false;
559 if (!strlen((string)$f->value
)) $this->fields
[] = false;
560 else $this->fields
[] = adodb_date('Y-m-d',(float)$f->value
);
563 $this->fields
[] = false;
565 case 6: // currency is not supported properly;
566 ADOConnection
::outp( '<b>'.$f->Name
.': currency type not supported by PHP</b>');
567 $this->fields
[] = (float) $f->value
;
570 $this->fields
[] = $f->value
;
573 //print " $f->value $t, ";
574 $f = next($this->_flds
);
575 $t = next($this->_tarr
);
577 if ($this->hideErrors
) error_reporting($olde);
578 @$rs->MoveNext(); // @ needed for some versions of PHP!
580 if ($this->fetchMode
& ADODB_FETCH_ASSOC
) {
581 $this->fields
= &$this->GetRowAssoc(ADODB_ASSOC_CASE
);
586 function NextRecordSet()
588 $rs = $this->_queryID
;
589 $this->_queryID
= $rs->NextRecordSet();
590 //$this->_queryID = $this->_QueryId->NextRecordSet();
591 if ($this->_queryID
== null) return false;
593 $this->_currentRow
= -1;
594 $this->_currentPage
= -1;
596 $this->fields
= false;
597 $this->_flds
= false;
598 $this->_tarr
= false;
600 $this->_inited
= false;
606 $this->_flds
= false;
607 @$this->_queryID
->Close();// by Pete Dishman (peterd@telephonetics.co.uk)
608 $this->_queryID
= false;