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.
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,...)");
27 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']) {
78 case '1': $s = 'Interbase 5.5 or earlier'; break;
79 case '2': $s = 'Interbase 5.6'; break;
81 case '3': $s = 'Interbase 6.0'; break;
83 $arr['version'] = ADOConnection
::_findvers($s);
84 $arr['description'] = $s;
90 if ($this->transOff
) return true;
92 $this->autoCommit
= false;
93 $this->_transactionID
= $this->_connectionID
;//ibase_trans($this->ibasetrans, $this->_connectionID);
94 return $this->_transactionID
;
97 function CommitTrans($ok=true)
99 if (!$ok) return $this->RollbackTrans();
100 if ($this->transOff
) return true;
101 if ($this->transCnt
) $this->transCnt
-= 1;
103 $this->autoCommit
= true;
104 if ($this->_transactionID
) {
106 $ret = ibase_commit($this->_transactionID
);
108 $this->_transactionID
= false;
112 function RollbackTrans()
114 if ($this->transOff
) return true;
115 if ($this->transCnt
) $this->transCnt
-= 1;
117 $this->autoCommit
= true;
118 if ($this->_transactionID
)
119 $ret = ibase_rollback($this->_transactionID
);
120 $this->_transactionID
= false;
125 function &MetaIndexes ($table, $primary = FALSE, $owner=false)
127 // save old fetch mode
128 global $ADODB_FETCH_MODE;
130 $save = $ADODB_FETCH_MODE;
131 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
132 if ($this->fetchMode
!== FALSE) {
133 $savem = $this->SetFetchMode(FALSE);
135 $table = strtoupper($table);
136 $sql = "SELECT * FROM RDB\$INDICES WHERE RDB\$RELATION_NAME = '".$table."'";
138 $sql .= " AND RDB\$INDEX_NAME NOT LIKE 'RDB\$%'";
140 $sql .= " AND RDB\$INDEX_NAME NOT LIKE 'RDB\$FOREIGN%'";
143 $rs = $this->Execute($sql);
144 if (!is_object($rs)) {
147 $this->SetFetchMode($savem);
149 $ADODB_FETCH_MODE = $save;
154 while ($row = $rs->FetchRow()) {
156 if (!isset($indexes[$index])) {
157 if (is_null($row[3])) {$row[3] = 0;}
158 $indexes[$index] = array(
159 'unique' => ($row[3] == 1),
163 $sql = "SELECT * FROM RDB\$INDEX_SEGMENTS WHERE RDB\$INDEX_NAME = '".$name."' ORDER BY RDB\$FIELD_POSITION ASC";
164 $rs1 = $this->Execute($sql);
165 while ($row1 = $rs1->FetchRow()) {
166 $indexes[$index]['columns'][$row1[2]] = $row1[1];
171 $this->SetFetchMode($savem);
173 $ADODB_FETCH_MODE = $save;
179 // See http://community.borland.com/article/0,1410,25844,00.html
180 function RowLock($tables,$where,$col)
182 if ($this->autoCommit
) $this->BeginTrans();
183 $this->Execute("UPDATE $table SET $col=$col WHERE $where "); // is this correct - jlim?
188 function CreateSequence($seqname,$startID=1)
190 $ok = $this->Execute(("INSERT INTO RDB\$GENERATORS (RDB\$GENERATOR_NAME) VALUES (UPPER('$seqname'))" ));
191 if (!$ok) return false;
192 return $this->Execute("SET GENERATOR $seqname TO ".($startID-1).';');
195 function DropSequence($seqname)
197 $seqname = strtoupper($seqname);
198 $this->Execute("delete from RDB\$GENERATORS where RDB\$GENERATOR_NAME='$seqname'");
201 function GenID($seqname='adodbseq',$startID=1)
203 $getnext = ("SELECT Gen_ID($seqname,1) FROM RDB\$DATABASE");
204 $rs = @$this->Execute($getnext);
206 $this->Execute(("INSERT INTO RDB\$GENERATORS (RDB\$GENERATOR_NAME) VALUES (UPPER('$seqname'))" ));
207 $this->Execute("SET GENERATOR $seqname TO ".($startID-1).';');
208 $rs = $this->Execute($getnext);
210 if ($rs && !$rs->EOF
) $this->genID
= (integer) reset($rs->fields
);
211 else $this->genID
= 0; // false
213 if ($rs) $rs->Close();
218 function SelectDB($dbName)
223 function _handleerror()
225 $this->_errorMsg
= ibase_errmsg();
230 if (preg_match('/error code = ([\-0-9]*)/i', $this->_errorMsg
,$arr)) return (integer) $arr[1];
236 return $this->_errorMsg
;
239 // returns true or false
240 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
242 if (!function_exists('ibase_pconnect')) return false;
243 if ($argDatabasename) $argHostname .= ':'.$argDatabasename;
244 $this->_connectionID
= ibase_connect($argHostname,$argUsername,$argPassword,$this->charSet
,$this->buffers
,$this->dialect
);
245 if ($this->dialect
!= 1) { // http://www.ibphoenix.com/ibp_60_del_id_ds.html
246 $this->replaceQuote
= "''";
248 if ($this->_connectionID
=== false) {
249 $this->_handleerror();
253 ibase_timefmt($this->ibase_timefmt
);
256 // returns true or false
257 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
259 if (!function_exists('ibase_pconnect')) return false;
260 if ($argDatabasename) $argHostname .= ':'.$argDatabasename;
261 $this->_connectionID
= ibase_pconnect($argHostname,$argUsername,$argPassword,$this->charSet
,$this->buffers
,$this->dialect
);
262 if ($this->dialect
!= 1) { // http://www.ibphoenix.com/ibp_60_del_id_ds.html
263 $this->replaceQuote
= "''";
265 if ($this->_connectionID
=== false) {
266 $this->_handleerror();
270 ibase_timefmt($this->ibase_timefmt
);
274 function Prepare($sql)
276 $stmt = ibase_prepare($this->_connectionID
,$sql);
277 if (!$stmt) return false;
278 return array($sql,$stmt);
281 // returns query ID if successful, otherwise false
282 // there have been reports of problems with nested queries - the code is probably not re-entrant?
283 function _query($sql,$iarr=false)
286 if (!$this->autoCommit
&& $this->_transactionID
) {
287 $conn = $this->_transactionID
;
290 $conn = $this->_connectionID
;
293 if (is_array($sql)) {
294 $fn = 'ibase_execute';
297 if (is_array($iarr)) {
298 if (ADODB_PHPVER
>= 0x4050) { // actually 4.0.4
299 $fnarr =& array_merge( array($sql) , $iarr);
300 $ret = call_user_func_array($fn,$fnarr);
302 switch(sizeof($iarr)) {
303 case 1: $ret = $fn($sql,$iarr[0]); break;
304 case 2: $ret = $fn($sql,$iarr[0],$iarr[1]); break;
305 case 3: $ret = $fn($sql,$iarr[0],$iarr[1],$iarr[2]); break;
306 case 4: $ret = $fn($sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3]); break;
307 case 5: $ret = $fn($sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3],$iarr[4]); break;
308 case 6: $ret = $fn($sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3],$iarr[4],$iarr[5]); break;
309 case 7: $ret = $fn($sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3],$iarr[4],$iarr[5],$iarr[6]); break;
310 default: ADOConnection
::outp( "Too many parameters to ibase query $sql");
311 case 8: $ret = $fn($sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3],$iarr[4],$iarr[5],$iarr[6],$iarr[7]); break;
314 } else $ret = $fn($sql);
318 if (is_array($iarr)) {
319 if (ADODB_PHPVER
>= 0x4050) { // actually 4.0.4
320 $fnarr =& array_merge( array($conn,$sql) , $iarr);
321 $ret = call_user_func_array($fn,$fnarr);
323 switch(sizeof($iarr)) {
324 case 1: $ret = $fn($conn,$sql,$iarr[0]); break;
325 case 2: $ret = $fn($conn,$sql,$iarr[0],$iarr[1]); break;
326 case 3: $ret = $fn($conn,$sql,$iarr[0],$iarr[1],$iarr[2]); break;
327 case 4: $ret = $fn($conn,$sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3]); break;
328 case 5: $ret = $fn($conn,$sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3],$iarr[4]); break;
329 case 6: $ret = $fn($conn,$sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3],$iarr[4],$iarr[5]); break;
330 case 7: $ret = $fn($conn,$sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3],$iarr[4],$iarr[5],$iarr[6]); break;
331 default: ADOConnection
::outp( "Too many parameters to ibase query $sql");
332 case 8: $ret = $fn($conn,$sql,$iarr[0],$iarr[1],$iarr[2],$iarr[3],$iarr[4],$iarr[5],$iarr[6],$iarr[7]); break;
335 } else $ret = $fn($conn,$sql);
337 if ($docommit && $ret === true) ibase_commit($this->_connectionID
);
339 $this->_handleerror();
343 // returns true or false
346 if (!$this->autoCommit
) @ibase_rollback
($this->_connectionID
);
347 return @ibase_close
($this->_connectionID
);
351 function _ConvertFieldType(&$fld, $ftype, $flen, $fscale, $fsubtype, $fprecision, $isInterbase6)
353 $fscale = abs($fscale);
354 $fld->max_length
= $flen;
362 $fld->type
= ($ftype == 7 ?
'smallint' : 'integer');
365 $fld->type
= 'numeric';
366 $fld->max_length
= $fprecision;
367 $fld->scale
= $fscale;
370 $fld->type
= 'decimal';
371 $fld->max_length
= $fprecision;
372 $fld->scale
= $fscale;
377 $fld->type
= 'decimal';
378 $fld->scale
= $fscale;
379 $fld->max_length
= ($ftype == 7 ?
4 : 9);
381 $fld->type
= ($ftype == 7 ?
'smallint' : 'integer');
389 $fld->type
= 'decimal';
390 $fld->max_length
= 18;
394 $fld->type
= 'numeric';
395 $fld->max_length
= $fprecision;
396 $fld->scale
= $fscale;
399 $fld->type
= 'decimal';
400 $fld->max_length
= $fprecision;
401 $fld->scale
= $fscale;
407 $fld->type
= 'float';
414 $fld->type
= 'decimal';
415 $fld->max_length
= 15;
418 $fld->type
= 'double';
423 $fld->type
= 'timestamp';
433 $fld->type
= 'varchar';
436 $fld->type
= 'cstring';
440 $fld->max_length
= -1;
445 // returns array of ADOFieldObjects for current table
446 function &MetaColumns($table)
448 global $ADODB_FETCH_MODE;
450 if ($this->metaColumnsSQL
) {
452 $save = $ADODB_FETCH_MODE;
453 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
455 $rs = $this->Execute(sprintf($this->metaColumnsSQL
,strtoupper($table)));
457 $ADODB_FETCH_MODE = $save;
458 if ($rs === false) return false;
462 $isInterbase6 = ($this->dialect
==3 ?
true : false);
464 while (!$rs->EOF
) { //print_r($rs->fields);
465 $fld = new ADOFieldObject();
466 $fld->name
= trim($rs->fields
[0]);
468 $this->_ConvertFieldType($fld, $rs->fields
[7], $rs->fields
[3], $rs->fields
[4], $rs->fields
[5], $rs->fields
[6], $isInterbase6);
469 if (isset($rs->fields
[1]) && $rs->fields
[1]) {
470 $fld->not_null
= true;
472 if (isset($rs->fields
[2])) {
474 $fld->has_default
= true;
475 $d = substr($rs->fields
[2],strlen('default '));
479 case 'integer': $fld->default_value
= (int) $d; break;
483 case 'varchar': $fld->default_value
= (string) substr($d,1,strlen($d)-2); break;
485 case 'float': $fld->default_value
= (float) $d; break;
486 default: $fld->default_value
= $d; break;
488 // case 35:$tt = 'TIMESTAMP'; break;
490 if ((isset($rs->fields
[5])) && ($fld->type
== 'blob')) {
491 $fld->sub_type
= $rs->fields
[5];
493 $fld->sub_type
= null;
496 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM
) $retarr[] = $fld;
497 else $retarr[strtoupper($fld->name
)] = $fld;
507 function BlobEncode( $blob )
509 $blobid = ibase_blob_create( $this->_connectionID
);
510 ibase_blob_add( $blobid, $blob );
511 return ibase_blob_close( $blobid );
514 // since we auto-decode all blob's since 2.42,
515 // BlobDecode should not do any transforms
516 function BlobDecode($blob)
524 // old blobdecode function
525 // still used to auto-decode all blob's
526 function _BlobDecode( $blob )
528 $blobid = ibase_blob_open( $blob );
529 $realblob = ibase_blob_get( $blobid,$this->maxblobsize
); // 2nd param is max size of blob -- Kevin Boillet <kevinboillet@yahoo.fr>
530 while($string = ibase_blob_get($blobid, 8192)){
531 $realblob .= $string;
533 ibase_blob_close( $blobid );
538 function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB')
540 $fd = fopen($path,'rb');
541 if ($fd === false) return false;
542 $blob_id = ibase_blob_create($this->_connectionID
);
546 while ($val = fread($fd,32768)){
547 ibase_blob_add($blob_id, $val);
550 /* close and get $blob_id_str for inserting into table */
551 $blob_id_str = ibase_blob_close($blob_id);
554 return $this->Execute("UPDATE $table SET $column=(?) WHERE $where",array($blob_id_str)) != false;
558 Insert a null into the blob field of the table first.
559 Then use UpdateBlob to store the blob.
563 $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
564 $conn->UpdateBlob('blobtable','blobcol',$blob,'id=1');
566 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
568 $blob_id = ibase_blob_create($this->_connectionID
);
570 // ibase_blob_add($blob_id, $val);
572 // replacement that solves the problem by which only the first modulus 64K /
573 // of $val are stored at the blob field ////////////////////////////////////
574 // Thx Abel Berenstein aberenstein#afip.gov.ar
577 $tail_size = $len %
$chunk_size;
578 $n_chunks = ($len - $tail_size) / $chunk_size;
580 for ($n = 0; $n < $n_chunks; $n++
) {
581 $start = $n * $chunk_size;
582 $data = substr($val, $start, $chunk_size);
583 ibase_blob_add($blob_id, $data);
587 $start = $n_chunks * $chunk_size;
588 $data = substr($val, $start, $tail_size);
589 ibase_blob_add($blob_id, $data);
591 // end replacement /////////////////////////////////////////////////////////
593 $blob_id_str = ibase_blob_close($blob_id);
595 return $this->Execute("UPDATE $table SET $column=(?) WHERE $where",array($blob_id_str)) != false;
600 function OldUpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
602 $blob_id = ibase_blob_create($this->_connectionID
);
603 ibase_blob_add($blob_id, $val);
604 $blob_id_str = ibase_blob_close($blob_id);
605 return $this->Execute("UPDATE $table SET $column=(?) WHERE $where",array($blob_id_str)) != false;
608 // Format date column in sql string given an input format that understands Y M D
609 // Only since Interbase 6.0 - uses EXTRACT
610 // problem - does not zero-fill the day and month yet
611 function SQLDate($fmt, $col=false)
613 if (!$col) $col = $this->sysDate
;
617 for ($i=0; $i < $len; $i++
) {
623 $s .= "extract(year from $col)";
627 $s .= "extract(month from $col)";
631 $s .= "cast(((extract(month from $col)+2) / 3) as integer)";
635 $s .= "(extract(day from $col))";
639 $s .= "(extract(hour from $col))";
643 $s .= "(extract(minute from $col))";
647 $s .= "CAST((extract(second from $col)) AS INTEGER)";
653 $ch = substr($fmt,$i,1);
655 $s .= $this->qstr($ch);
663 /*--------------------------------------------------------------------------------------
664 Class Name: Recordset
665 --------------------------------------------------------------------------------------*/
667 class ADORecordset_ibase
extends ADORecordSet
670 var $databaseType = "ibase";
674 function ADORecordset_ibase($id,$mode=false)
676 global $ADODB_FETCH_MODE;
678 $this->fetchMode
= ($mode === false) ?
$ADODB_FETCH_MODE : $mode;
679 return $this->ADORecordSet($id);
682 /* Returns: an object containing field information.
683 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
684 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
685 fetchField() is retrieved. */
687 function &FetchField($fieldOffset = -1)
689 $fld = new ADOFieldObject
;
690 $ibf = ibase_field_info($this->_queryID
,$fieldOffset);
691 $fld->name
= strtolower($ibf['alias']);
692 if (empty($fld->name
)) $fld->name
= strtolower($ibf['name']);
693 $fld->type
= $ibf['type'];
694 $fld->max_length
= $ibf['length'];
700 $this->_numOfRows
= -1;
701 $this->_numOfFields
= @ibase_num_fields
($this->_queryID
);
703 // cache types for blob decode check
704 for ($i=0, $max = $this->_numOfFields
; $i < $max; $i++
) {
705 $f1 = $this->FetchField($i);
706 $this->_cacheType
[] = $f1->type
;
719 $f = @ibase_fetch_row
($this->_queryID
);
721 $this->fields
= false;
724 // OPN stuff start - optimized
725 // fix missing nulls and decode blobs automatically
727 global $ADODB_ANSI_PADDING_OFF;
728 //$ADODB_ANSI_PADDING_OFF=1;
729 $rtrim = !empty($ADODB_ANSI_PADDING_OFF);
731 for ($i=0, $max = $this->_numOfFields
; $i < $max; $i++
) {
732 if ($this->_cacheType
[$i]=="BLOB") {
734 $f[$i] = $this->connection
->_BlobDecode($f[$i]);
739 if (!isset($f[$i])) {
741 } else if ($rtrim && is_string($f[$i])) {
742 $f[$i] = rtrim($f[$i]);
749 if ($this->fetchMode
== ADODB_FETCH_ASSOC
) {
750 $this->fields
= &$this->GetRowAssoc(ADODB_ASSOC_CASE
);
751 } else if ($this->fetchMode
== ADODB_FETCH_BOTH
) {
752 $this->fields
=& array_merge($this->fields
,$this->GetRowAssoc(ADODB_ASSOC_CASE
));
757 /* Use associative array to get fields array */
758 function Fields($colname)
760 if ($this->fetchMode
& ADODB_FETCH_ASSOC
) return $this->fields
[$colname];
762 $this->bind
= array();
763 for ($i=0; $i < $this->_numOfFields
; $i++
) {
764 $o = $this->FetchField($i);
765 $this->bind
[strtoupper($o->name
)] = $i;
769 return $this->fields
[$this->bind
[strtoupper($colname)]];
776 return @ibase_free_result
($this->_queryID
);
779 function MetaType($t,$len=-1,$fieldobj=false)
783 $t = $fieldobj->type
;
784 $len = $fieldobj->max_length
;
786 switch (strtoupper($t)) {
793 if ($len <= $this->blobSize
) return 'C';
799 case 'DATE': return 'D';
801 //case 'T': return 'T';
803 //case 'L': return 'L';
806 case 'INTEGER': return 'I';