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.
8 Latest version is available at http://php.weblogs.com/
10 Interbase data driver. Requires interbase client. Works on Windows and Unix.
12 3 Jan 2002 -- suggestions by Hans-Peter Oeri <kampfcaspar75@oeri.ch>
13 changed transaction handling and added experimental blob stuff
15 Docs to interbase at the website
16 http://www.synectics.co.za/php3/tutorial/IB_PHP3_API.html
19 http://www.volny.cz/iprenosil/interbase/ip_ib_code.htm#_code_creategen
21 $rs = $conn->Execute('select gen_id(adodb,1) from rdb$database');
23 $conn->Execute("insert into table (id, col1,...) values ($id, $val1,...)");
26 class ADODB_ibase extends ADOConnection
28 var $databaseType = "ibase";
29 var $dataProvider = "ibase";
30 var $replaceQuote = "''"; // string to use to replace quotes
31 var $ibase_timefmt = '%Y-%m-%d'; // For hours,mins,secs change to '%Y-%m-%d %H:%M:%S';
32 var $fmtDate = "'Y-m-d'";
33 var $fmtTimeStamp = "'Y-m-d, H:i:s'";
34 var $concat_operator = '||';
36 var $metaTablesSQL = "select rdb\$relation_name from rdb\$relations where rdb\$relation_name not like 'RDB\$%'";
38 var $metaColumnsSQL = "select a.rdb\$field_name, a.rdb\$null_flag, a.rdb\$default_source, b.rdb\$field_length, b.rdb\$field_scale, b.rdb\$field_sub_type, b.rdb\$field_precision, b.rdb\$field_type from rdb\$relation_fields a, rdb\$fields b where a.rdb\$field_source = b.rdb\$field_name and a.rdb\$relation_name = '%s' order by a.rdb\$field_position asc";
42 var $_bindInputArray = true;
45 var $sysDate = "cast('TODAY' as date)";
46 var $sysTimeStamp = "cast('NOW' as timestamp)";
47 var $ansiOuter = true;
48 var $hasAffectedRows = false;
49 var $poorAffectedRows = true;
50 var $blobEncodeType = 'C';
52 function ADODB_ibase()
54 if (defined('IBASE_DEFAULT')) $this->ibasetrans = IBASE_DEFAULT;
57 function MetaPrimaryKeys($table, $owner_notused = false, $internalKey = false)
59 if ($internalKey) return array('RDB$DB_KEY');
61 $table = strtoupper($table);
63 $sql = 'SELECT S.RDB$FIELD_NAME AFIELDNAME
64 FROM RDB$INDICES I JOIN RDB$INDEX_SEGMENTS S ON I.RDB$INDEX_NAME=S.RDB$INDEX_NAME
65 WHERE I.RDB$RELATION_NAME=\'' . $table . '\' and I.RDB$INDEX_NAME like \'RDB$PRIMARY%\'
66 ORDER BY I.RDB$INDEX_NAME,S.RDB$FIELD_POSITION';
68 $a = $this->GetCol($sql, false, true);
69 if ($a && sizeof($a) > 0) return $a;
75 $arr['dialect'] = $this->dialect;
76 switch ($arr['dialect']) {
79 $s = 'Interbase 5.5 or earlier';
89 $arr['version'] = ADOConnection::_findvers($s);
90 $arr['description'] = $s;
96 if ($this->transOff) return true;
98 $this->autoCommit = false;
99 $this->_transactionID = $this->_connectionID; //ibase_trans($this->ibasetrans, $this->_connectionID);
100 return $this->_transactionID;
103 function CommitTrans($ok = true)
105 if (!$ok) return $this->RollbackTrans();
106 if ($this->transOff) return true;
107 if ($this->transCnt) $this->transCnt -= 1;
109 $this->autoCommit = true;
110 if ($this->_transactionID) {
112 $ret = ibase_commit($this->_transactionID);
114 $this->_transactionID = false;
118 function RollbackTrans()
120 if ($this->transOff) return true;
121 if ($this->transCnt) $this->transCnt -= 1;
123 $this->autoCommit = true;
124 if ($this->_transactionID)
125 $ret = ibase_rollback($this->_transactionID);
126 $this->_transactionID = false;
131 function &MetaIndexes($table, $primary = FALSE, $owner = false)
133 // save old fetch mode
134 global $ADODB_FETCH_MODE;
136 $save = $ADODB_FETCH_MODE;
137 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
138 if ($this->fetchMode !== FALSE) {
139 $savem = $this->SetFetchMode(FALSE);
141 $table = strtoupper($table);
142 $sql = "SELECT * FROM RDB\$INDICES WHERE RDB\$RELATION_NAME = '" . $table . "'";
144 $sql .= " AND RDB\$INDEX_NAME NOT LIKE 'RDB\$%'";
146 $sql .= " AND RDB\$INDEX_NAME NOT LIKE 'RDB\$FOREIGN%'";
149 $rs = $this->Execute($sql);
150 if (!is_object($rs)) {
153 $this->SetFetchMode($savem);
155 $ADODB_FETCH_MODE = $save;
160 while ($row = $rs->FetchRow()) {
162 if (!isset($indexes[$index])) {
163 if (is_null($row[3])) {
166 $indexes[$index] = array(
167 'unique' => ($row[3] == 1),
171 $sql = "SELECT * FROM RDB\$INDEX_SEGMENTS WHERE RDB\$INDEX_NAME = '" . $name . "' ORDER BY RDB\$FIELD_POSITION ASC";
172 $rs1 = $this->Execute($sql);
173 while ($row1 = $rs1->FetchRow()) {
174 $indexes[$index]['columns'][$row1[2]] = $row1[1];
179 $this->SetFetchMode($savem);
181 $ADODB_FETCH_MODE = $save;
186 // See http://community.borland.com/article/0,1410,25844,00.html
187 function RowLock($tables, $where, $col)
189 if ($this->autoCommit) $this->BeginTrans();
190 $this->Execute("UPDATE $table SET $col=$col WHERE $where "); // is this correct - jlim?
194 function CreateSequence($seqname, $startID = 1)
196 $ok = $this->Execute(("INSERT INTO RDB\$GENERATORS (RDB\$GENERATOR_NAME) VALUES (UPPER('$seqname'))"));
197 if (!$ok) return false;
198 return $this->Execute("SET GENERATOR $seqname TO " . ($startID - 1) . ';');
201 function DropSequence($seqname)
203 $seqname = strtoupper($seqname);
204 $this->Execute("delete from RDB\$GENERATORS where RDB\$GENERATOR_NAME='$seqname'");
207 function GenID($seqname = 'adodbseq', $startID = 1)
209 $getnext = ("SELECT Gen_ID($seqname,1) FROM RDB\$DATABASE");
210 $rs = @$this->Execute($getnext);
212 $this->Execute(("INSERT INTO RDB\$GENERATORS (RDB\$GENERATOR_NAME) VALUES (UPPER('$seqname'))"));
213 $this->Execute("SET GENERATOR $seqname TO " . ($startID - 1) . ';');
214 $rs = $this->Execute($getnext);
216 if ($rs && !$rs->EOF) $this->genID = (integer)reset($rs->fields);
217 else $this->genID = 0; // false
219 if ($rs) $rs->Close();
224 function SelectDB($dbName)
229 function _handleerror()
231 $this->_errorMsg = ibase_errmsg();
236 if (preg_match('/error code = ([\-0-9]*)/i', $this->_errorMsg, $arr)) return (integer)$arr[1];
242 return $this->_errorMsg;
245 // returns true or false
246 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
248 if (!function_exists('ibase_pconnect')) return false;
249 if ($argDatabasename) $argHostname .= ':' . $argDatabasename;
250 $this->_connectionID = ibase_connect($argHostname, $argUsername, $argPassword, $this->charSet, $this->buffers, $this->dialect);
251 if ($this->dialect != 1) { // http://www.ibphoenix.com/ibp_60_del_id_ds.html
252 $this->replaceQuote = "''";
254 if ($this->_connectionID === false) {
255 $this->_handleerror();
259 ibase_timefmt($this->ibase_timefmt);
263 // returns true or false
264 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
266 if (!function_exists('ibase_pconnect')) return false;
267 if ($argDatabasename) $argHostname .= ':' . $argDatabasename;
268 $this->_connectionID = ibase_pconnect($argHostname, $argUsername, $argPassword, $this->charSet, $this->buffers, $this->dialect);
269 if ($this->dialect != 1) { // http://www.ibphoenix.com/ibp_60_del_id_ds.html
270 $this->replaceQuote = "''";
272 if ($this->_connectionID === false) {
273 $this->_handleerror();
277 ibase_timefmt($this->ibase_timefmt);
281 function Prepare($sql)
283 $stmt = ibase_prepare($this->_connectionID, $sql);
284 if (!$stmt) return false;
285 return array($sql, $stmt);
288 // returns query ID if successful, otherwise false
289 // there have been reports of problems with nested queries - the code is probably not re-entrant?
290 function _query($sql, $iarr = false)
293 if (!$this->autoCommit && $this->_transactionID) {
294 $conn = $this->_transactionID;
297 $conn = $this->_connectionID;
300 if (is_array($sql)) {
301 $fn = 'ibase_execute';
304 if (is_array($iarr)) {
305 if (ADODB_PHPVER >= 0x4050) { // actually 4.0.4
306 if (!isset($iarr[0])) $iarr[0] = ''; // PHP5 compat hack
307 $fnarr =& array_merge(array($sql), $iarr);
308 $ret = call_user_func_array($fn, $fnarr);
310 switch (sizeof($iarr)) {
312 $ret = $fn($sql, $iarr[0]);
315 $ret = $fn($sql, $iarr[0], $iarr[1]);
318 $ret = $fn($sql, $iarr[0], $iarr[1], $iarr[2]);
321 $ret = $fn($sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3]);
324 $ret = $fn($sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3], $iarr[4]);
327 $ret = $fn($sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3], $iarr[4], $iarr[5]);
330 $ret = $fn($sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3], $iarr[4], $iarr[5], $iarr[6]);
333 ADOConnection::outp("Too many parameters to ibase query $sql");
335 $ret = $fn($sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3], $iarr[4], $iarr[5], $iarr[6], $iarr[7]);
339 } else $ret = $fn($sql);
343 if (is_array($iarr)) {
344 if (ADODB_PHPVER >= 0x4050) { // actually 4.0.4
345 if (!isset($iarr[0])) $iarr[0] = ''; // PHP5 compat hack
346 $fnarr =& array_merge(array($conn, $sql), $iarr);
347 $ret = call_user_func_array($fn, $fnarr);
349 switch (sizeof($iarr)) {
351 $ret = $fn($conn, $sql, $iarr[0]);
354 $ret = $fn($conn, $sql, $iarr[0], $iarr[1]);
357 $ret = $fn($conn, $sql, $iarr[0], $iarr[1], $iarr[2]);
360 $ret = $fn($conn, $sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3]);
363 $ret = $fn($conn, $sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3], $iarr[4]);
366 $ret = $fn($conn, $sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3], $iarr[4], $iarr[5]);
369 $ret = $fn($conn, $sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3], $iarr[4], $iarr[5], $iarr[6]);
372 ADOConnection::outp("Too many parameters to ibase query $sql");
374 $ret = $fn($conn, $sql, $iarr[0], $iarr[1], $iarr[2], $iarr[3], $iarr[4], $iarr[5], $iarr[6], $iarr[7]);
378 } else $ret = $fn($conn, $sql);
380 if ($docommit && $ret === true) ibase_commit($this->_connectionID);
382 $this->_handleerror();
386 // returns true or false
389 if (!$this->autoCommit) @ibase_rollback($this->_connectionID);
390 return @ibase_close($this->_connectionID);
394 function _ConvertFieldType(&$fld, $ftype, $flen, $fscale, $fsubtype, $fprecision, $isInterbase6)
396 $fscale = abs($fscale);
397 $fld->max_length = $flen;
405 $fld->type = ($ftype == 7 ? 'smallint' : 'integer');
408 $fld->type = 'numeric';
409 $fld->max_length = $fprecision;
410 $fld->scale = $fscale;
413 $fld->type = 'decimal';
414 $fld->max_length = $fprecision;
415 $fld->scale = $fscale;
420 $fld->type = 'decimal';
421 $fld->scale = $fscale;
422 $fld->max_length = ($ftype == 7 ? 4 : 9);
424 $fld->type = ($ftype == 7 ? 'smallint' : 'integer');
432 $fld->type = 'decimal';
433 $fld->max_length = 18;
437 $fld->type = 'numeric';
438 $fld->max_length = $fprecision;
439 $fld->scale = $fscale;
442 $fld->type = 'decimal';
443 $fld->max_length = $fprecision;
444 $fld->scale = $fscale;
450 $fld->type = 'float';
457 $fld->type = 'decimal';
458 $fld->max_length = 15;
461 $fld->type = 'double';
466 $fld->type = 'timestamp';
476 $fld->type = 'varchar';
479 $fld->type = 'cstring';
483 $fld->max_length = -1;
489 // returns array of ADOFieldObjects for current table
490 function &MetaColumns($table)
492 global $ADODB_FETCH_MODE;
494 if ($this->metaColumnsSQL) {
496 $save = $ADODB_FETCH_MODE;
497 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
499 $rs = $this->Execute(sprintf($this->metaColumnsSQL, strtoupper($table)));
501 $ADODB_FETCH_MODE = $save;
502 if ($rs === false) return false;
506 $isInterbase6 = ($this->dialect == 3 ? true : false);
508 while (!$rs->EOF) { //print_r($rs->fields);
509 $fld = new ADOFieldObject();
510 $fld->name = trim($rs->fields[0]);
512 $this->_ConvertFieldType($fld, $rs->fields[7], $rs->fields[3], $rs->fields[4], $rs->fields[5], $rs->fields[6], $isInterbase6);
513 if (isset($rs->fields[1]) && $rs->fields[1]) {
514 $fld->not_null = true;
516 if (isset($rs->fields[2])) {
518 $fld->has_default = true;
519 $d = substr($rs->fields[2], strlen('default '));
520 switch ($fld->type) {
523 $fld->default_value = (int)$d;
529 $fld->default_value = (string)substr($d, 1, strlen($d) - 2);
533 $fld->default_value = (float)$d;
536 $fld->default_value = $d;
539 // case 35:$tt = 'TIMESTAMP'; break;
541 if ((isset($rs->fields[5])) && ($fld->type == 'blob')) {
542 $fld->sub_type = $rs->fields[5];
544 $fld->sub_type = null;
547 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
548 else $retarr[strtoupper($fld->name)] = $fld;
558 function BlobEncode($blob)
560 $blobid = ibase_blob_create($this->_connectionID);
561 ibase_blob_add($blobid, $blob);
562 return ibase_blob_close($blobid);
565 // since we auto-decode all blob's since 2.42,
566 // BlobDecode should not do any transforms
567 function BlobDecode($blob)
573 // old blobdecode function
574 // still used to auto-decode all blob's
575 function _BlobDecode($blob)
577 $blobid = ibase_blob_open($blob);
578 $realblob = ibase_blob_get($blobid, $this->maxblobsize); // 2nd param is max size of blob -- Kevin Boillet <kevinboillet@yahoo.fr>
579 while ($string = ibase_blob_get($blobid, 8192)) {
580 $realblob .= $string;
582 ibase_blob_close($blobid);
587 function UpdateBlobFile($table, $column, $path, $where, $blobtype = 'BLOB')
589 $fd = fopen($path, 'rb');
590 if ($fd === false) return false;
591 $blob_id = ibase_blob_create($this->_connectionID);
595 while ($val = fread($fd, 32768)) {
596 ibase_blob_add($blob_id, $val);
599 /* close and get $blob_id_str for inserting into table */
600 $blob_id_str = ibase_blob_close($blob_id);
603 return $this->Execute("UPDATE $table SET $column=(?) WHERE $where", array($blob_id_str)) != false;
607 Insert a null into the blob field of the table first.
608 Then use UpdateBlob to store the blob.
612 $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
613 $conn->UpdateBlob('blobtable','blobcol',$blob,'id=1');
615 function UpdateBlob($table, $column, $val, $where, $blobtype = 'BLOB')
617 $blob_id = ibase_blob_create($this->_connectionID);
619 // ibase_blob_add($blob_id, $val);
621 // replacement that solves the problem by which only the first modulus 64K /
622 // of $val are stored at the blob field ////////////////////////////////////
623 // Thx Abel Berenstein aberenstein#afip.gov.ar
626 $tail_size = $len % $chunk_size;
627 $n_chunks = ($len - $tail_size) / $chunk_size;
629 for ($n = 0; $n < $n_chunks; $n++) {
630 $start = $n * $chunk_size;
631 $data = substr($val, $start, $chunk_size);
632 ibase_blob_add($blob_id, $data);
636 $start = $n_chunks * $chunk_size;
637 $data = substr($val, $start, $tail_size);
638 ibase_blob_add($blob_id, $data);
640 // end replacement /////////////////////////////////////////////////////////
642 $blob_id_str = ibase_blob_close($blob_id);
644 return $this->Execute("UPDATE $table SET $column=(?) WHERE $where", array($blob_id_str)) != false;
648 function OldUpdateBlob($table, $column, $val, $where, $blobtype = 'BLOB')
650 $blob_id = ibase_blob_create($this->_connectionID);
651 ibase_blob_add($blob_id, $val);
652 $blob_id_str = ibase_blob_close($blob_id);
653 return $this->Execute("UPDATE $table SET $column=(?) WHERE $where", array($blob_id_str)) != false;
656 // Format date column in sql string given an input format that understands Y M D
657 // Only since Interbase 6.0 - uses EXTRACT
658 // problem - does not zero-fill the day and month yet
659 function SQLDate($fmt, $col = false)
661 if (!$col) $col = $this->sysDate;
665 for ($i = 0; $i < $len; $i++) {
671 $s .= "extract(year from $col)";
675 $s .= "extract(month from $col)";
679 $s .= "cast(((extract(month from $col)+2) / 3) as integer)";
683 $s .= "(extract(day from $col))";
687 $s .= "(extract(hour from $col))";
691 $s .= "(extract(minute from $col))";
695 $s .= "CAST((extract(second from $col)) AS INTEGER)";
701 $ch = substr($fmt, $i, 1);
703 $s .= $this->qstr($ch);
711 /*--------------------------------------------------------------------------------------
712 Class Name: Recordset
713 --------------------------------------------------------------------------------------*/
715 class ADORecordset_ibase extends ADORecordSet
718 var $databaseType = "ibase";
722 function ADORecordset_ibase($id, $mode = false)
724 global $ADODB_FETCH_MODE;
726 $this->fetchMode = ($mode === false) ? $ADODB_FETCH_MODE : $mode;
727 return $this->ADORecordSet($id);
730 /* Returns: an object containing field information.
731 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
732 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
733 fetchField() is retrieved. */
735 function &FetchField($fieldOffset = -1)
737 $fld = new ADOFieldObject;
738 $ibf = ibase_field_info($this->_queryID, $fieldOffset);
739 $fld->name = strtolower($ibf['alias']);
740 if (empty($fld->name)) $fld->name = strtolower($ibf['name']);
741 $fld->type = $ibf['type'];
742 $fld->max_length = $ibf['length'];
748 $this->_numOfRows = -1;
749 $this->_numOfFields = @ibase_num_fields($this->_queryID);
751 // cache types for blob decode check
752 for ($i = 0, $max = $this->_numOfFields; $i < $max; $i++) {
753 $f1 = $this->FetchField($i);
754 $this->_cacheType[] = $f1->type;
765 $f = @ibase_fetch_row($this->_queryID);
767 $this->fields = false;
770 // OPN stuff start - optimized
771 // fix missing nulls and decode blobs automatically
773 global $ADODB_ANSI_PADDING_OFF;
774 //$ADODB_ANSI_PADDING_OFF=1;
775 $rtrim = !empty($ADODB_ANSI_PADDING_OFF);
777 for ($i = 0, $max = $this->_numOfFields; $i < $max; $i++) {
778 if ($this->_cacheType[$i] == "BLOB") {
780 $f[$i] = $this->connection->_BlobDecode($f[$i]);
785 if (!isset($f[$i])) {
787 } elseif ($rtrim && is_string($f[$i])) {
788 $f[$i] = rtrim($f[$i]);
795 if ($this->fetchMode == ADODB_FETCH_ASSOC) {
796 $this->fields = &$this->GetRowAssoc(ADODB_ASSOC_CASE);
797 } elseif ($this->fetchMode == ADODB_FETCH_BOTH) {
798 $this->fields =& array_merge($this->fields, $this->GetRowAssoc(ADODB_ASSOC_CASE));
803 /* Use associative array to get fields array */
804 function Fields($colname)
806 if ($this->fetchMode & ADODB_FETCH_ASSOC) return $this->fields[$colname];
808 $this->bind = array();
809 for ($i = 0; $i < $this->_numOfFields; $i++) {
810 $o = $this->FetchField($i);
811 $this->bind[strtoupper($o->name)] = $i;
815 return $this->fields[$this->bind[strtoupper($colname)]];
821 return @ibase_free_result($this->_queryID);
824 function MetaType($t, $len = -1, $fieldobj = false)
828 $t = $fieldobj->type;
829 $len = $fieldobj->max_length;
831 switch (strtoupper($t)) {
838 if ($len <= $this->blobSize) return 'C';
847 //case 'T': return 'T';
849 //case 'L': return 'L';