3 V5.14 8 Sept 2011 (c) 2000-2011 (jlim#natsoft.com). All rights reserved.
5 This is a version of the ADODB driver for DB2. It uses the 'ibm_db2' PECL extension
6 for PHP (http://pecl.php.net/package/ibm_db2), which in turn requires DB2 V8.2.2 or
9 Originally tested with PHP 5.1.1 and Apache 2.0.55 on Windows XP SP2.
10 More recently tested with PHP 5.1.2 and Apache 2.0.55 on Windows XP SP2.
12 This file was ported from "adodb-odbc.inc.php" by Larry Menard, "larry.menard#rogers.com".
13 I ripped out what I believed to be a lot of redundant or obsolete code, but there are
14 probably still some remnants of the ODBC support in this file; I'm relying on reviewers
15 of this code to point out any other things that can be removed.
18 // security - hide paths
19 if (!defined('ADODB_DIR')) die();
21 define("_ADODB_DB2_LAYER", 2 );
23 /*--------------------------------------------------------------------------------------
24 --------------------------------------------------------------------------------------*/
30 class ADODB_db2
extends ADOConnection
{
31 var $databaseType = "db2";
32 var $fmtDate = "'Y-m-d'";
33 var $concat_operator = '||';
35 var $sysTime = 'CURRENT TIME';
36 var $sysDate = 'CURRENT DATE';
37 var $sysTimeStamp = 'CURRENT TIMESTAMP';
39 var $fmtTimeStamp = "'Y-m-d H:i:s'";
40 var $replaceQuote = "''"; // string to use to replace quotes
41 var $dataProvider = "db2";
42 var $hasAffectedRows = true;
44 var $binmode = DB2_BINARY
;
46 var $useFetchArray = false; // setting this to true will make array elements in FETCH_ASSOC mode case-sensitive
47 // breaking backward-compat
48 var $_bindInputArray = false;
49 var $_genIDSQL = "VALUES NEXTVAL FOR %s";
50 var $_genSeqSQL = "CREATE SEQUENCE %s START WITH %s NO MAXVALUE NO CYCLE";
51 var $_dropSeqSQL = "DROP SEQUENCE %s";
52 var $_autocommit = true;
53 var $_haserrorfunctions = true;
54 var $_lastAffectedRows = 0;
55 var $uCaseTables = true; // for meta* functions, uppercase table names
56 var $hasInsertID = true;
61 return ADOConnection
::GetOne('VALUES IDENTITY_VAL_LOCAL()');
66 $this->_haserrorfunctions
= ADODB_PHPVER
>= 0x4050;
69 // returns true or false
70 function _connect($argDSN, $argUsername, $argPassword, $argDatabasename)
74 if (!function_exists('db2_connect')) {
75 ADOConnection
::outp("Warning: The old ODBC based DB2 driver has been renamed 'odbc_db2'. This ADOdb driver calls PHP's native db2 extension which is not installed.");
78 // This needs to be set before the connect().
79 // Replaces the odbc_binmode() call that was in Execute()
80 ini_set('ibm_db2.binmode', $this->binmode
);
82 if ($argDatabasename && empty($argDSN)) {
84 if (stripos($argDatabasename,'UID=') && stripos($argDatabasename,'PWD=')) $this->_connectionID
= db2_connect($argDatabasename,null,null);
85 else $this->_connectionID
= db2_connect($argDatabasename,$argUsername,$argPassword);
87 if ($argDatabasename) $schema = $argDatabasename;
88 if (stripos($argDSN,'UID=') && stripos($argDSN,'PWD=')) $this->_connectionID
= db2_connect($argDSN,null,null);
89 else $this->_connectionID
= db2_connect($argDSN,$argUsername,$argPassword);
91 if (isset($php_errormsg)) $php_errormsg = '';
93 // For db2_connect(), there is an optional 4th arg. If present, it must be
94 // an array of valid options. So far, we don't use them.
96 $this->_errorMsg
= @db2_conn_errormsg
();
97 if (isset($this->connectStmt
)) $this->Execute($this->connectStmt
);
99 if ($this->_connectionID
&& isset($schema)) $this->Execute("SET SCHEMA=$schema");
100 return $this->_connectionID
!= false;
103 // returns true or false
104 function _pconnect($argDSN, $argUsername, $argPassword, $argDatabasename)
106 global $php_errormsg;
108 if (!function_exists('db2_connect')) return null;
110 // This needs to be set before the connect().
111 // Replaces the odbc_binmode() call that was in Execute()
112 ini_set('ibm_db2.binmode', $this->binmode
);
114 if (isset($php_errormsg)) $php_errormsg = '';
115 $this->_errorMsg
= isset($php_errormsg) ?
$php_errormsg : '';
117 if ($argDatabasename && empty($argDSN)) {
119 if (stripos($argDatabasename,'UID=') && stripos($argDatabasename,'PWD=')) $this->_connectionID
= db2_pconnect($argDatabasename,null,null);
120 else $this->_connectionID
= db2_pconnect($argDatabasename,$argUsername,$argPassword);
122 if ($argDatabasename) $schema = $argDatabasename;
123 if (stripos($argDSN,'UID=') && stripos($argDSN,'PWD=')) $this->_connectionID
= db2_pconnect($argDSN,null,null);
124 else $this->_connectionID
= db2_pconnect($argDSN,$argUsername,$argPassword);
126 if (isset($php_errormsg)) $php_errormsg = '';
128 $this->_errorMsg
= @db2_conn_errormsg
();
129 if ($this->_connectionID
&& $this->autoRollback
) @db2_rollback
($this->_connectionID
);
130 if (isset($this->connectStmt
)) $this->Execute($this->connectStmt
);
132 if ($this->_connectionID
&& isset($schema)) $this->Execute("SET SCHEMA=$schema");
133 return $this->_connectionID
!= false;
136 // format and return date string in database timestamp format
137 function DBTimeStamp($ts)
139 if (empty($ts) && $ts !== 0) return 'null';
140 if (is_string($ts)) $ts = ADORecordSet
::UnixTimeStamp($ts);
141 return 'TO_DATE('.adodb_date($this->fmtTimeStamp
,$ts).",'YYYY-MM-DD HH24:MI:SS')";
144 // Format date column in sql string given an input format that understands Y M D
145 function SQLDate($fmt, $col=false)
147 // use right() and replace() ?
148 if (!$col) $col = $this->sysDate
;
150 /* use TO_CHAR() if $fmt is TO_CHAR() allowed fmt */
151 if ($fmt== 'Y-m-d H:i:s')
152 return 'TO_CHAR('.$col.", 'YYYY-MM-DD HH24:MI:SS')";
157 for ($i=0; $i < $len; $i++
) {
158 if ($s) $s .= $this->concat_operator
;
163 if ($len==1) return "year($col)";
164 $s .= "char(year($col))";
167 if ($len==1) return "monthname($col)";
168 $s .= "substr(monthname($col),1,3)";
171 if ($len==1) return "month($col)";
172 $s .= "right(digits(month($col)),2)";
176 if ($len==1) return "day($col)";
177 $s .= "right(digits(day($col)),2)";
181 if ($len==1) return "hour($col)";
182 if ($col != $this->sysDate
) $s .= "right(digits(hour($col)),2)";
187 if ($len==1) return "minute($col)";
188 if ($col != $this->sysDate
)
189 $s .= "right(digits(minute($col)),2)";
194 if ($len==1) return "second($col)";
195 if ($col != $this->sysDate
)
196 $s .= "right(digits(second($col)),2)";
202 $ch = substr($fmt,$i,1);
204 $s .= $this->qstr($ch);
211 function ServerInfo()
213 $row = $this->GetRow("SELECT service_level, fixpack_num FROM TABLE(sysproc.env_get_inst_info())
218 $info['version'] = $row[0].':'.$row[1];
219 $info['fixpack'] = $row[1];
220 $info['description'] = '';
222 return ADOConnection
::ServerInfo();
228 function CreateSequence($seqname='adodbseq',$start=1)
230 if (empty($this->_genSeqSQL
)) return false;
231 $ok = $this->Execute(sprintf($this->_genSeqSQL
,$seqname,$start));
232 if (!$ok) return false;
236 function DropSequence($seqname)
238 if (empty($this->_dropSeqSQL
)) return false;
239 return $this->Execute(sprintf($this->_dropSeqSQL
,$seqname));
242 function SelectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false)
244 $nrows = (integer) $nrows;
246 // could also use " OPTIMIZE FOR $nrows ROWS "
247 if ($nrows >= 0) $sql .= " FETCH FIRST $nrows ROWS ONLY ";
248 $rs = $this->Execute($sql,$inputArr);
250 if ($offset > 0 && $nrows < 0);
253 $sql .= " FETCH FIRST $nrows ROWS ONLY ";
255 $rs = ADOConnection
::SelectLimit($sql,-1,$offset,$inputArr);
262 This algorithm is not very efficient, but works even if table locking
265 Will return false if unable to generate an ID after $MAXLOOPS attempts.
267 function GenID($seq='adodbseq',$start=1)
269 // if you have to modify the parameter below, your database is overloaded,
270 // or you need to implement generation of id's yourself!
271 $num = $this->GetOne("VALUES NEXTVAL FOR $seq");
278 if ($this->_haserrorfunctions
) {
279 if ($this->_errorMsg
!== false) return $this->_errorMsg
;
280 if (empty($this->_connectionID
)) return @db2_conn_errormsg
();
281 return @db2_conn_errormsg
($this->_connectionID
);
282 } else return ADOConnection
::ErrorMsg();
288 if ($this->_haserrorfunctions
) {
289 if ($this->_errorCode
!== false) {
290 // bug in 4.0.6, error number can be corrupted string (should be 6 digits)
291 return (strlen($this->_errorCode
)<=2) ?
0 : $this->_errorCode
;
294 if (empty($this->_connectionID
)) $e = @db2_conn_error
();
295 else $e = @db2_conn_error
($this->_connectionID
);
297 // bug in 4.0.6, error number can be corrupted string (should be 6 digits)
298 // so we check and patch
299 if (strlen($e)<=2) return 0;
301 } else return ADOConnection
::ErrorNo();
306 function BeginTrans()
308 if (!$this->hasTransactions
) return false;
309 if ($this->transOff
) return true;
310 $this->transCnt +
= 1;
311 $this->_autocommit
= false;
312 return db2_autocommit($this->_connectionID
,false);
315 function CommitTrans($ok=true)
317 if ($this->transOff
) return true;
318 if (!$ok) return $this->RollbackTrans();
319 if ($this->transCnt
) $this->transCnt
-= 1;
320 $this->_autocommit
= true;
321 $ret = db2_commit($this->_connectionID
);
322 db2_autocommit($this->_connectionID
,true);
326 function RollbackTrans()
328 if ($this->transOff
) return true;
329 if ($this->transCnt
) $this->transCnt
-= 1;
330 $this->_autocommit
= true;
331 $ret = db2_rollback($this->_connectionID
);
332 db2_autocommit($this->_connectionID
,true);
336 function MetaPrimaryKeys($table)
338 global $ADODB_FETCH_MODE;
340 if ($this->uCaseTables
) $table = strtoupper($table);
342 $this->_findschema($table,$schema);
344 $savem = $ADODB_FETCH_MODE;
345 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
346 $qid = @db2_primarykeys
($this->_connectionID
,'',$schema,$table);
349 $ADODB_FETCH_MODE = $savem;
352 $rs = new ADORecordSet_db2($qid);
353 $ADODB_FETCH_MODE = $savem;
355 if (!$rs) return false;
357 $arr = $rs->GetArray();
360 for ($i=0; $i < sizeof($arr); $i++
) {
361 if ($arr[$i][3]) $arr2[] = $arr[$i][3];
366 function MetaForeignKeys($table, $owner = FALSE, $upper = FALSE, $asociative = FALSE )
368 global $ADODB_FETCH_MODE;
370 if ($this->uCaseTables
) $table = strtoupper($table);
372 $this->_findschema($table,$schema);
374 $savem = $ADODB_FETCH_MODE;
375 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
376 $qid = @db2_foreign_keys
($this->_connectionID
,'',$schema,$table);
378 $ADODB_FETCH_MODE = $savem;
381 $rs = new ADORecordSet_db2($qid);
383 $ADODB_FETCH_MODE = $savem;
395 if (!$rs) return false;
397 $foreign_keys = array();
399 if (strtoupper(trim($rs->fields
[2])) == $table && (!$schema ||
strtoupper($rs->fields
[1]) == $schema)) {
400 if (!is_array($foreign_keys[$rs->fields
[5].'.'.$rs->fields
[6]]))
401 $foreign_keys[$rs->fields
[5].'.'.$rs->fields
[6]] = array();
402 $foreign_keys[$rs->fields
[5].'.'.$rs->fields
[6]][$rs->fields
[7]] = $rs->fields
[3];
412 function MetaTables($ttype=false,$schema=false)
414 global $ADODB_FETCH_MODE;
416 $savem = $ADODB_FETCH_MODE;
417 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
418 $qid = db2_tables($this->_connectionID
);
420 $rs = new ADORecordSet_db2($qid);
422 $ADODB_FETCH_MODE = $savem;
428 $arr = $rs->GetArray();
433 $isview = strncmp($ttype,'V',1) === 0;
435 for ($i=0; $i < sizeof($arr); $i++
) {
436 if (!$arr[$i][2]) continue;
438 $owner = $arr[$i][1];
439 $schemaval = ($schema) ?
$arr[$i][1].'.' : '';
442 if (strncmp($type,'V',1) === 0) $arr2[] = $schemaval.$arr[$i][2];
443 } else if (strncmp($owner,'SYS',3) !== 0) $arr2[] = $schemaval.$arr[$i][2];
444 } else if (strncmp($owner,'SYS',3) !== 0) $arr2[] = $schemaval.$arr[$i][2];
450 See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2datetime_data_type_changes.asp
451 / SQL data type codes /
452 #define SQL_UNKNOWN_TYPE 0
454 #define SQL_NUMERIC 2
455 #define SQL_DECIMAL 3
456 #define SQL_INTEGER 4
457 #define SQL_SMALLINT 5
461 #if (DB2VER >= 0x0300)
462 #define SQL_DATETIME 9
464 #define SQL_VARCHAR 12
467 / One-parameter shortcuts for date/time data types /
468 #if (DB2VER >= 0x0300)
469 #define SQL_TYPE_DATE 91
470 #define SQL_TYPE_TIME 92
471 #define SQL_TYPE_TIMESTAMP 93
473 #define SQL_UNICODE (-95)
474 #define SQL_UNICODE_VARCHAR (-96)
475 #define SQL_UNICODE_LONGVARCHAR (-97)
477 function DB2Types($t)
479 switch ((integer)$t) {
507 case -11: // uniqidentifier
517 function MetaColumns($table, $normalize=true)
519 global $ADODB_FETCH_MODE;
522 if ($this->uCaseTables
) $table = strtoupper($table);
524 $this->_findschema($table,$schema);
526 $savem = $ADODB_FETCH_MODE;
527 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
530 $qid = db2_columns($this->_connectionID
, "", $schema, $table, $colname);
531 if (empty($qid)) return $false;
533 $rs = new ADORecordSet_db2($qid);
534 $ADODB_FETCH_MODE = $savem;
536 if (!$rs) return $false;
557 if (strtoupper(trim($rs->fields
[2])) == $table && (!$schema ||
strtoupper($rs->fields
[1]) == $schema)) {
558 $fld = new ADOFieldObject();
559 $fld->name
= $rs->fields
[3];
560 $fld->type
= $this->DB2Types($rs->fields
[4]);
562 // ref: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_odk.asp
563 // access uses precision to store length for char/varchar
564 if ($fld->type
== 'C' or $fld->type
== 'X') {
565 if ($rs->fields
[4] <= -95) // UNICODE
566 $fld->max_length
= $rs->fields
[7]/2;
568 $fld->max_length
= $rs->fields
[7];
570 $fld->max_length
= $rs->fields
[7];
571 $fld->not_null
= !empty($rs->fields
[10]);
572 $fld->scale
= $rs->fields
[8];
573 $fld->primary_key
= false;
574 $retarr[strtoupper($fld->name
)] = $fld;
575 } else if (sizeof($retarr)>0)
580 if (empty($retarr)) $retarr = false;
582 $qid = db2_primary_keys($this->_connectionID
, "", $schema, $table);
583 if (empty($qid)) return $false;
585 $rs = new ADORecordSet_db2($qid);
586 $ADODB_FETCH_MODE = $savem;
588 if (!$rs) return $retarr;
601 if (strtoupper(trim($rs->fields
[2])) == $table && (!$schema ||
strtoupper($rs->fields
[1]) == $schema)) {
602 $retarr[strtoupper($rs->fields
[3])]->primary_key
= true;
603 } else if (sizeof($retarr)>0)
609 if (empty($retarr)) $retarr = false;
614 function Prepare($sql)
616 if (! $this->_bindInputArray
) return $sql; // no binding
617 $stmt = db2_prepare($this->_connectionID
,$sql);
619 // we don't know whether db2 driver is parsing prepared stmts, so just return sql
622 return array($sql,$stmt,false);
625 /* returns queryID or false */
626 function _query($sql,$inputarr=false)
628 GLOBAL $php_errormsg;
629 if (isset($php_errormsg)) $php_errormsg = '';
633 if (is_array($sql)) {
636 $stmtid = db2_prepare($this->_connectionID
,$sql);
638 if ($stmtid == false) {
639 $this->_errorMsg
= isset($php_errormsg) ?
$php_errormsg : '';
644 if (! db2_execute($stmtid,$inputarr)) {
645 if ($this->_haserrorfunctions
) {
646 $this->_errorMsg
= db2_stmt_errormsg();
647 $this->_errorCode
= db2_stmt_error();
652 } else if (is_array($sql)) {
654 if (!db2_execute($stmtid)) {
655 if ($this->_haserrorfunctions
) {
656 $this->_errorMsg
= db2_stmt_errormsg();
657 $this->_errorCode
= db2_stmt_error();
662 $stmtid = @db2_exec
($this->_connectionID
,$sql);
664 $this->_lastAffectedRows
= 0;
666 if (@db2_num_fields
($stmtid) == 0) {
667 $this->_lastAffectedRows
= db2_num_rows($stmtid);
670 $this->_lastAffectedRows
= 0;
673 if ($this->_haserrorfunctions
) {
674 $this->_errorMsg
= '';
675 $this->_errorCode
= 0;
677 $this->_errorMsg
= isset($php_errormsg) ?
$php_errormsg : '';
679 if ($this->_haserrorfunctions
) {
680 $this->_errorMsg
= db2_stmt_errormsg();
681 $this->_errorCode
= db2_stmt_error();
683 $this->_errorMsg
= isset($php_errormsg) ?
$php_errormsg : '';
690 Insert a null into the blob field of the table first.
691 Then use UpdateBlob to store the blob.
695 $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
696 $conn->UpdateBlob('blobtable','blobcol',$blob,'id=1');
698 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
700 return $this->Execute("UPDATE $table SET $column=? WHERE $where",array($val)) != false;
703 // returns true or false
706 $ret = @db2_close
($this->_connectionID
);
707 $this->_connectionID
= false;
711 function _affectedrows()
713 return $this->_lastAffectedRows
;
718 /*--------------------------------------------------------------------------------------
719 Class Name: Recordset
720 --------------------------------------------------------------------------------------*/
722 class ADORecordSet_db2
extends ADORecordSet
{
725 var $databaseType = "db2";
726 var $dataProvider = "db2";
729 function ADORecordSet_db2($id,$mode=false)
731 if ($mode === false) {
732 global $ADODB_FETCH_MODE;
733 $mode = $ADODB_FETCH_MODE;
735 $this->fetchMode
= $mode;
737 $this->_queryID
= $id;
741 // returns the field object
742 function FetchField($offset = -1)
744 $o= new ADOFieldObject();
745 $o->name
= @db2_field_name
($this->_queryID
,$offset);
746 $o->type
= @db2_field_type
($this->_queryID
,$offset);
747 $o->max_length
= db2_field_width($this->_queryID
,$offset);
748 if (ADODB_ASSOC_CASE
== 0) $o->name
= strtolower($o->name
);
749 else if (ADODB_ASSOC_CASE
== 1) $o->name
= strtoupper($o->name
);
753 /* Use associative array to get fields array */
754 function Fields($colname)
756 if ($this->fetchMode
& ADODB_FETCH_ASSOC
) return $this->fields
[$colname];
758 $this->bind
= array();
759 for ($i=0; $i < $this->_numOfFields
; $i++
) {
760 $o = $this->FetchField($i);
761 $this->bind
[strtoupper($o->name
)] = $i;
765 return $this->fields
[$this->bind
[strtoupper($colname)]];
771 global $ADODB_COUNTRECS;
772 $this->_numOfRows
= ($ADODB_COUNTRECS) ? @db2_num_rows
($this->_queryID
) : -1;
773 $this->_numOfFields
= @db2_num_fields
($this->_queryID
);
774 // some silly drivers such as db2 as/400 and intersystems cache return _numOfRows = 0
775 if ($this->_numOfRows
== 0) $this->_numOfRows
= -1;
783 // speed up SelectLimit() by switching to ADODB_FETCH_NUM as ADODB_FETCH_ASSOC is emulated
784 function GetArrayLimit($nrows,$offset=-1)
787 $rs = $this->GetArray($nrows);
790 $savem = $this->fetchMode
;
791 $this->fetchMode
= ADODB_FETCH_NUM
;
792 $this->Move($offset);
793 $this->fetchMode
= $savem;
795 if ($this->fetchMode
& ADODB_FETCH_ASSOC
) {
796 $this->fields
= $this->GetRowAssoc(ADODB_ASSOC_CASE
);
801 while (!$this->EOF
&& $nrows != $cnt) {
802 $results[$cnt++
] = $this->fields
;
812 if ($this->_numOfRows
!= 0 && !$this->EOF
) {
813 $this->_currentRow++
;
815 $this->fields
= @db2_fetch_array
($this->_queryID
);
817 if ($this->fetchMode
& ADODB_FETCH_ASSOC
) {
818 $this->fields
= $this->GetRowAssoc(ADODB_ASSOC_CASE
);
823 $this->fields
= false;
831 $this->fields
= db2_fetch_array($this->_queryID
);
833 if ($this->fetchMode
& ADODB_FETCH_ASSOC
) {
834 $this->fields
= $this->GetRowAssoc(ADODB_ASSOC_CASE
);
838 $this->fields
= false;
844 return @db2_free_result
($this->_queryID
);