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 Requires ODBC. Works on Windows and Unix.
13 define("_ADODB_ODBC_LAYER", 2 );
15 /*--------------------------------------------------------------------------------------
16 --------------------------------------------------------------------------------------*/
18 class ADODB_odbc extends ADOConnection {
19 var $databaseType = "odbc";
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 = "odbc";
24 var $hasAffectedRows = true;
25 var $binmode = ODBC_BINMODE_RETURN;
26 var $useFetchArray = false; // setting this to true will make array elements in FETCH_ASSOC mode case-sensitive
27 // breaking backward-compat
28 //var $longreadlen = 8000; // default number of chars to return for a Blob/Long field
29 var $_bindInputArray = false;
30 var $curmode = SQL_CUR_USE_DRIVER; // See sqlext.h, SQL_CUR_DEFAULT == SQL_CUR_USE_DRIVER == 2L
31 var $_genSeqSQL = "create table %s (id integer)";
32 var $_autocommit = true;
33 var $_haserrorfunctions = true;
34 var $_has_stupid_odbc_fetch_api_change = true;
35 var $_lastAffectedRows = 0;
39 $this->_haserrorfunctions = ADODB_PHPVER >= 0x4050;
40 $this->_has_stupid_odbc_fetch_api_change = ADODB_PHPVER >= 0x4200;
46 if (!empty($this->host) && ADODB_PHPVER >= 0x4300) {
47 $dsn = strtoupper($this->host);
51 if (!function_exists('odbc_data_source')) return false;
55 $rez = odbc_data_source($this->_connectionID,
56 $first ? SQL_FETCH_FIRST : SQL_FETCH_NEXT);
58 if (!is_array($rez)) break;
59 if (strtoupper($rez['server']) == $dsn) {
64 if (!$found) return ADOConnection::ServerInfo();
65 if (!isset($rez['version'])) $rez['version'] = '';
68 return ADOConnection::ServerInfo();
72 function CreateSequence($seqname='adodbseq',$start=1)
74 if (empty($this->_genSeqSQL)) return false;
75 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
76 if (!$ok) return false;
78 return $this->Execute("insert into $seqname values($start)");
81 var $_dropSeqSQL = 'drop table %s';
82 function DropSequence($seqname)
84 if (empty($this->_dropSeqSQL)) return false;
85 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
89 This algorithm is not very efficient, but works even if table locking
92 Will return false if unable to generate an ID after $MAXLOOPS attempts.
94 function GenID($seq='adodbseq',$start=1)
96 // if you have to modify the parameter below, your database is overloaded,
97 // or you need to implement generation of id's yourself!
100 while (--$MAXLOOPS>=0) {
101 $num = $this->GetOne("select id from $seq");
102 if ($num === false) {
103 $this->Execute(sprintf($this->_genSeqSQL ,$seq));
106 $ok = $this->Execute("insert into $seq values($start)");
107 if (!$ok) return false;
109 $this->Execute("update $seq set id=id+1 where id=$num");
111 if ($this->affected_rows() > 0) {
117 if ($fn = $this->raiseErrorFn) {
118 $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
126 if ($this->_haserrorfunctions) {
127 if ($this->_errorMsg !== false) return $this->_errorMsg;
128 if (empty($this->_connectionID)) return @odbc_errormsg();
129 return @odbc_errormsg($this->_connectionID);
130 } else return ADOConnection::ErrorMsg();
136 if ($this->_haserrorfunctions) {
137 if ($this->_errorCode !== false) {
138 // bug in 4.0.6, error number can be corrupted string (should be 6 digits)
139 return (strlen($this->_errorCode)<=2) ? 0 : $this->_errorCode;
142 if (empty($this->_connectionID)) $e = @odbc_error();
143 else $e = @odbc_error($this->_connectionID);
145 // bug in 4.0.6, error number can be corrupted string (should be 6 digits)
146 // so we check and patch
147 if (strlen($e)<=2) return 0;
149 } else return ADOConnection::ErrorNo();
153 // returns true or false
154 function _connect($argDSN, $argUsername, $argPassword, $argDatabasename)
156 global $php_errormsg;
158 if (!function_exists('odbc_connect')) return false;
160 if ($this->debug && $argDatabasename && $this->databaseType != 'vfp') {
161 ADOConnection::outp("For odbc Connect(), $argDatabasename is not used. Place dsn in 1st parameter.");
164 if ($this->curmode === false) $this->_connectionID = odbc_connect($argDSN,$argUsername,$argPassword);
165 else $this->_connectionID = odbc_connect($argDSN,$argUsername,$argPassword,$this->curmode);
166 $this->_errorMsg = $php_errormsg;
167 if (isset($this->connectStmt)) $this->Execute($this->connectStmt);
169 //if ($this->_connectionID) odbc_autocommit($this->_connectionID,true);
170 return $this->_connectionID != false;
173 // returns true or false
174 function _pconnect($argDSN, $argUsername, $argPassword, $argDatabasename)
176 global $php_errormsg;
178 if (!function_exists('odbc_connect')) return false;
181 if ($this->debug && $argDatabasename) {
182 ADOConnection::outp("For odbc PConnect(), $argDatabasename is not used. Place dsn in 1st parameter.");
184 // print "dsn=$argDSN u=$argUsername p=$argPassword<br>"; flush();
185 if ($this->curmode === false) $this->_connectionID = odbc_connect($argDSN,$argUsername,$argPassword);
186 else $this->_connectionID = odbc_pconnect($argDSN,$argUsername,$argPassword,$this->curmode);
188 $this->_errorMsg = $php_errormsg;
189 if ($this->_connectionID && $this->autoRollback) @odbc_rollback($this->_connectionID);
190 if (isset($this->connectStmt)) $this->Execute($this->connectStmt);
192 return $this->_connectionID != false;
195 function BeginTrans()
197 if (!$this->hasTransactions) return false;
198 if ($this->transOff) return true;
199 $this->transCnt += 1;
200 $this->_autocommit = false;
201 return odbc_autocommit($this->_connectionID,false);
204 function CommitTrans($ok=true)
206 if ($this->transOff) return true;
207 if (!$ok) return $this->RollbackTrans();
208 if ($this->transCnt) $this->transCnt -= 1;
209 $this->_autocommit = true;
210 $ret = odbc_commit($this->_connectionID);
211 odbc_autocommit($this->_connectionID,true);
215 function RollbackTrans()
217 if ($this->transOff) return true;
218 if ($this->transCnt) $this->transCnt -= 1;
219 $this->_autocommit = true;
220 $ret = odbc_rollback($this->_connectionID);
221 odbc_autocommit($this->_connectionID,true);
225 function MetaPrimaryKeys($table)
227 global $ADODB_FETCH_MODE;
229 $savem = $ADODB_FETCH_MODE;
230 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
231 $qid = @odbc_primarykeys($this->_connectionID,'','',$table);
234 $ADODB_FETCH_MODE = $savem;
237 $rs = new ADORecordSet_odbc($qid);
238 $ADODB_FETCH_MODE = $savem;
240 if (!$rs) return false;
241 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
243 $arr =& $rs->GetArray();
247 for ($i=0; $i < sizeof($arr); $i++) {
248 if ($arr[$i][3]) $arr2[] = $arr[$i][3];
255 function &MetaTables($ttype=false)
257 global $ADODB_FETCH_MODE;
259 $savem = $ADODB_FETCH_MODE;
260 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
261 $qid = odbc_tables($this->_connectionID);
263 $rs = new ADORecordSet_odbc($qid);
265 $ADODB_FETCH_MODE = $savem;
266 if (!$rs) return false;
268 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
270 $arr =& $rs->GetArray();
277 $isview = strncmp($ttype,'V',1) === 0;
279 for ($i=0; $i < sizeof($arr); $i++) {
280 if (!$arr[$i][2]) continue;
284 if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2];
285 } elseif (strncmp($type,'SYS',3) !== 0) $arr2[] = $arr[$i][2];
286 } elseif (strncmp($type,'SYS',3) !== 0) $arr2[] = $arr[$i][2];
292 / SQL data type codes /
293 #define SQL_UNKNOWN_TYPE 0
295 #define SQL_NUMERIC 2
296 #define SQL_DECIMAL 3
297 #define SQL_INTEGER 4
298 #define SQL_SMALLINT 5
302 #if (ODBCVER >= 0x0300)
303 #define SQL_DATETIME 9
305 #define SQL_VARCHAR 12
307 / One-parameter shortcuts for date/time data types /
308 #if (ODBCVER >= 0x0300)
309 #define SQL_TYPE_DATE 91
310 #define SQL_TYPE_TIME 92
311 #define SQL_TYPE_TIMESTAMP 93
313 #define SQL_UNICODE (-95)
314 #define SQL_UNICODE_VARCHAR (-96)
315 #define SQL_UNICODE_LONGVARCHAR (-97)
317 function ODBCTypes($t)
319 switch ((integer)$t) {
344 case -11: // uniqidentifier
354 function &MetaColumns($table)
356 global $ADODB_FETCH_MODE;
358 $table = strtoupper($table);
360 $this->_findschema($table,$schema);
362 $savem = $ADODB_FETCH_MODE;
363 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
365 if (false) { // after testing, confirmed that the following does not work becoz of a bug
366 $qid2 = odbc_tables($this->_connectionID);
367 $rs = new ADORecordSet_odbc($qid2);
368 $ADODB_FETCH_MODE = $savem;
369 if (!$rs) return false;
370 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
374 if ($table == strtoupper($rs->fields[2])) {
383 $qid = odbc_columns($this->_connectionID,$q,$o,strtoupper($table),'%');
384 } else switch ($this->databaseType) {
388 $qid = odbc_columns($this->_connectionID);
392 $qid = @odbc_columns($this->_connectionID,'%','%',strtoupper($table),'%');
393 if (empty($qid)) $qid = odbc_columns($this->_connectionID);
396 if (empty($qid)) return false;
398 $rs = new ADORecordSet_odbc($qid);
399 $ADODB_FETCH_MODE = $savem;
401 if (!$rs) return false;
403 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
423 //adodb_pr($rs->fields);
424 if (strtoupper($rs->fields[2]) == $table && (!$schema || strtoupper($rs->fields[1]) == $schema)) {
425 $fld = new ADOFieldObject();
426 $fld->name = $rs->fields[3];
427 $fld->type = $this->ODBCTypes($rs->fields[4]);
429 // ref: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_odk.asp
430 // access uses precision to store length for char/varchar
431 if ($fld->type == 'C' or $fld->type == 'X') {
432 if ($this->databaseType == 'access')
433 $fld->max_length = $rs->fields[6];
434 else if ($rs->fields[4] <= -95) // UNICODE
435 $fld->max_length = $rs->fields[7]/2;
437 $fld->max_length = $rs->fields[7];
439 $fld->max_length = $rs->fields[7];
440 $fld->not_null = !empty($rs->fields[10]);
441 $fld->scale = $rs->fields[8];
442 $retarr[strtoupper($fld->name)] = $fld;
443 } elseif (sizeof($retarr)>0)
447 $rs->Close(); //-- crashes 4.03pl1 -- why?
452 function Prepare($sql)
454 if (! $this->_bindInputArray) return $sql; // no binding
455 $stmt = odbc_prepare($this->_connectionID,$sql);
457 // we don't know whether odbc driver is parsing prepared stmts, so just return sql
460 return array($sql,$stmt,false);
463 /* returns queryID or false */
464 function _query($sql,$inputarr=false)
466 GLOBAL $php_errormsg;
471 if (is_array($sql)) {
474 $stmtid = odbc_prepare($this->_connectionID,$sql);
476 if ($stmtid == false) {
477 $this->_errorMsg = $php_errormsg;
482 if (! odbc_execute($stmtid,$inputarr)) {
483 //@odbc_free_result($stmtid);
484 if ($this->_haserrorfunctions) {
485 $this->_errorMsg = odbc_errormsg();
486 $this->_errorCode = odbc_error();
491 } elseif (is_array($sql)) {
493 if (!odbc_execute($stmtid)) {
494 //@odbc_free_result($stmtid);
495 if ($this->_haserrorfunctions) {
496 $this->_errorMsg = odbc_errormsg();
497 $this->_errorCode = odbc_error();
502 $stmtid = odbc_exec($this->_connectionID,$sql);
504 $this->_lastAffectedRows = 0;
506 if (@odbc_num_fields($stmtid) == 0) {
507 $this->_lastAffectedRows = odbc_num_rows($stmtid);
510 $this->_lastAffectedRows = 0;
511 odbc_binmode($stmtid,$this->binmode);
512 odbc_longreadlen($stmtid,$this->maxblobsize);
515 if ($this->_haserrorfunctions) {
516 $this->_errorMsg = '';
517 $this->_errorCode = 0;
519 $this->_errorMsg = $php_errormsg;
521 if ($this->_haserrorfunctions) {
522 $this->_errorMsg = odbc_errormsg();
523 $this->_errorCode = odbc_error();
525 $this->_errorMsg = $php_errormsg;
531 Insert a null into the blob field of the table first.
532 Then use UpdateBlob to store the blob.
536 $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
537 $conn->UpdateBlob('blobtable','blobcol',$blob,'id=1');
539 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
541 return $this->Execute("UPDATE $table SET $column=? WHERE $where",array($val)) != false;
544 // returns true or false
547 $ret = @odbc_close($this->_connectionID);
548 $this->_connectionID = false;
552 function _affectedrows()
554 return $this->_lastAffectedRows;
559 /*--------------------------------------------------------------------------------------
560 Class Name: Recordset
561 --------------------------------------------------------------------------------------*/
563 class ADORecordSet_odbc extends ADORecordSet {
566 var $databaseType = "odbc";
567 var $dataProvider = "odbc";
569 var $_has_stupid_odbc_fetch_api_change;
571 function ADORecordSet_odbc($id,$mode=false)
573 if ($mode === false) {
574 global $ADODB_FETCH_MODE;
575 $mode = $ADODB_FETCH_MODE;
577 $this->fetchMode = $mode;
579 $this->_queryID = $id;
581 // the following is required for mysql odbc driver in 4.3.1 -- why?
583 $this->_currentRow = -1;
584 //$this->ADORecordSet($id);
587 // returns the field object
588 function &FetchField($fieldOffset = -1)
591 $off=$fieldOffset+1; // offsets begin at 1
593 $o= new ADOFieldObject();
594 $o->name = @odbc_field_name($this->_queryID,$off);
595 $o->type = @odbc_field_type($this->_queryID,$off);
596 $o->max_length = @odbc_field_len($this->_queryID,$off);
597 if (ADODB_ASSOC_CASE == 0) $o->name = strtolower($o->name);
598 else if (ADODB_ASSOC_CASE == 1) $o->name = strtoupper($o->name);
602 /* Use associative array to get fields array */
603 function Fields($colname)
605 if ($this->fetchMode & ADODB_FETCH_ASSOC) return $this->fields[$colname];
607 $this->bind = array();
608 for ($i=0; $i < $this->_numOfFields; $i++) {
609 $o = $this->FetchField($i);
610 $this->bind[strtoupper($o->name)] = $i;
614 return $this->fields[$this->bind[strtoupper($colname)]];
619 global $ADODB_COUNTRECS;
620 $this->_numOfRows = ($ADODB_COUNTRECS) ? @odbc_num_rows($this->_queryID) : -1;
621 $this->_numOfFields = @odbc_num_fields($this->_queryID);
622 // some silly drivers such as db2 as/400 and intersystems cache return _numOfRows = 0
623 if ($this->_numOfRows == 0) $this->_numOfRows = -1;
624 //$this->useFetchArray = $this->connection->useFetchArray;
625 $this->_has_stupid_odbc_fetch_api_change = ADODB_PHPVER >= 0x4200;
633 // speed up SelectLimit() by switching to ADODB_FETCH_NUM as ADODB_FETCH_ASSOC is emulated
634 function &GetArrayLimit($nrows,$offset=-1)
637 $rs =& $this->GetArray($nrows);
640 $savem = $this->fetchMode;
641 $this->fetchMode = ADODB_FETCH_NUM;
642 $this->Move($offset);
643 $this->fetchMode = $savem;
645 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
646 $this->fields =& $this->GetRowAssoc(ADODB_ASSOC_CASE);
651 while (!$this->EOF && $nrows != $cnt) {
652 $results[$cnt++] = $this->fields;
661 if ($this->_numOfRows != 0 && !$this->EOF) {
662 $this->_currentRow++;
664 if ($this->_has_stupid_odbc_fetch_api_change)
665 $rez = @odbc_fetch_into($this->_queryID,$this->fields);
667 $rez = @odbc_fetch_into($this->_queryID,$row,$this->fields);
669 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
670 $this->fields =& $this->GetRowAssoc(ADODB_ASSOC_CASE);
675 $this->fields = false;
683 if ($this->_has_stupid_odbc_fetch_api_change)
684 $rez = @odbc_fetch_into($this->_queryID,$this->fields,$row);
686 $rez = @odbc_fetch_into($this->_queryID,$row,$this->fields);
689 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
690 $this->fields =& $this->GetRowAssoc(ADODB_ASSOC_CASE);
694 $this->fields = false;
700 return @odbc_free_result($this->_queryID);