1<?php
2/**
3 * IBM DB2 Native Client driver.
4 *
5 * Originally DB2 drivers were dependent on an ODBC driver, and some installations
6 * may still use that. To use an ODBC driver connection, use the odbc_db2
7 * ADOdb driver. For Linux, you need the 'ibm_db2' PECL extension for PHP,
8 * For Windows, you need to locate an appropriate version of the php_ibm_db2.dll,
9 * as well as the IBM data server client software.
10 * This is basically a full rewrite of the original driver, for information
11 * about all the changes, see the update information on the ADOdb website
12 * for version 5.21.0.
13 *
14 * @link http://pecl.php.net/package/ibm_db2 PECL Extension For DB2
15 *
16 * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
17 *
18 * @package ADOdb
19 * @link https://adodb.org Project's web site and documentation
20 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
21 *
22 * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
23 * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
24 * any later version. This means you can use it in proprietary products.
25 * See the LICENSE.md file distributed with this source code for details.
26 * @license BSD-3-Clause
27 * @license LGPL-2.1-or-later
28 *
29 * @copyright 2000-2013 John Lim
30 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
31 * @author Mark Newnham
32 */
33
34// security - hide paths
35if (!defined('ADODB_DIR')) die();
36
37define("_ADODB_DB2_LAYER", 2 );
38
39
40class ADODB_db2 extends ADOConnection {
41	var $databaseType = "db2";
42	var $fmtDate = "'Y-m-d'";
43	var $concat_operator = '||';
44
45	var $sysTime = 'CURRENT TIME';
46	var $sysDate = 'CURRENT DATE';
47	var $sysTimeStamp = 'CURRENT TIMESTAMP';
48
49	var $fmtTimeStamp = "'Y-m-d H:i:s'";
50	var $replaceQuote = "''"; // string to use to replace quotes
51	var $dataProvider = "db2";
52	var $hasAffectedRows = true;
53
54	var $binmode = DB2_BINARY;
55
56	/*
57	* setting this to true will make array elements in FETCH_ASSOC
58	* mode case-sensitive breaking backward-compat
59	*/
60	var $useFetchArray = false;
61	var $_bindInputArray = true;
62	var $_genIDSQL = "VALUES NEXTVAL FOR %s";
63	var $_genSeqSQL = "
64	CREATE SEQUENCE %s START WITH %s
65	NO MAXVALUE NO CYCLE INCREMENT BY 1 NO CACHE
66	";
67	var $_dropSeqSQL = "DROP SEQUENCE %s";
68	var $_autocommit = true;
69	var $_lastAffectedRows = 0;
70	var $hasInsertID = true;
71	var $hasGenID    = true;
72
73	/*
74	 * Character used to wrap column and table names for escaping special
75	 * characters in column and table names as well as forcing upper and
76	 * lower case
77	 */
78	public $nameQuote = '"';
79
80	/*
81	 * Executed after successful connection
82	 */
83	public $connectStmt = '';
84
85	/*
86	 * Holds the current database name
87	 */
88	private $databaseName = '';
89
90	/*
91	 * Holds information about the stored procedure request
92	 * currently being built
93	 */
94	private $storedProcedureParameters = false;
95
96
97	function __construct() {}
98
99	protected function _insertID($table = '', $column = '')
100	{
101		return ADOConnection::GetOne('VALUES IDENTITY_VAL_LOCAL()');
102	}
103
104	public function _connect($argDSN, $argUsername, $argPassword, $argDatabasename)
105	{
106		return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename);
107	}
108
109	public function _pconnect($argDSN, $argUsername, $argPassword, $argDatabasename)
110	{
111		return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename,true);
112	}
113
114	private function doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename, $persistent=false)
115	{
116		global $php_errormsg;
117
118		if (!function_exists('db2_connect')) {
119			ADOConnection::outp("DB2 extension not installed.");
120			return null;
121		}
122
123		$connectionParameters = $this->unpackParameters($argDSN,
124														$argUsername,
125														$argPassword,
126														$argDatabasename);
127
128		if ($connectionParameters == null)
129		{
130			/*
131			 * Error thrown
132			 */
133			return null;
134		}
135
136		$argDSN 		         = $connectionParameters['dsn'];
137		$argUsername 	         = $connectionParameters['uid'];
138		$argPassword 	         = $connectionParameters['pwd'];
139		$argDatabasename         = $connectionParameters['database'];
140		$useCataloguedConnection = $connectionParameters['catalogue'];
141
142		if ($this->debug){
143			if ($useCataloguedConnection){
144				$connectMessage = "Catalogued connection using parameters: ";
145				$connectMessage .= "DB=$argDatabasename / ";
146				$connectMessage .= "UID=$argUsername / ";
147				$connectMessage .= "PWD=$argPassword";
148			}
149			else
150			{
151				$connectMessage = "Uncatalogued connection using DSN: $argDSN";
152			}
153			ADOConnection::outp($connectMessage);
154		}
155		/*
156		 * This needs to be set before the connect().
157		 */
158		ini_set('ibm_db2.binmode', $this->binmode);
159
160		if ($persistent)
161			$db2Function = 'db2_pconnect';
162		else
163			$db2Function = 'db2_connect';
164
165		/*
166		* We need to flatten out the connectionParameters
167		*/
168
169		$db2Options = array();
170		if ($this->connectionParameters)
171		{
172			foreach($this->connectionParameters as $p)
173				foreach($p as $k=>$v)
174					$db2Options[$k] = $v;
175		}
176
177		if ($useCataloguedConnection)
178			$this->_connectionID = $db2Function($argDatabasename,
179												$argUsername,
180												$argPassword,
181												$db2Options);
182		else
183			$this->_connectionID = $db2Function($argDSN,
184												null,
185												null,
186												$db2Options);
187
188		$php_errormsg = '';
189
190		$this->_errorMsg = @db2_conn_errormsg();
191
192		if ($this->_connectionID && $this->connectStmt)
193			$this->execute($this->connectStmt);
194
195		return $this->_connectionID != false;
196
197	}
198
199	/**
200	 * Validates and preprocesses the passed parameters for consistency
201	 *
202	 * @param	string	$argDSN				Either DSN or database
203	 * @param	string	$argUsername		User name or null
204	 * @param	string	$argPassword		Password or null
205	 * @param	string	$argDatabasename	Either DSN or database
206	 *
207	 * @return mixed  array if correct, null if not
208	 */
209	private function unpackParameters($argDSN, $argUsername, $argPassword, $argDatabasename)
210	{
211
212		global $php_errormsg;
213
214		$connectionParameters = array('dsn'=>'',
215									  'uid'=>'',
216									  'pwd'=>'',
217									  'database'=>'',
218									  'catalogue'=>true
219									  );
220
221		/*
222		 * Uou can either connect to a catalogued connection
223		 * with a database name e.g. 'SAMPLE'
224		 * or an uncatalogued connection with a DSN like connection
225		 * DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;
226		 */
227
228		if (!$argDSN && !$argDatabasename)
229		{
230			$errorMessage = 'Supply either catalogued or uncatalogued connection parameters';
231			$this->_errorMsg = $errorMessage;
232			if ($this->debug)
233				ADOConnection::outp($errorMessage);
234			return null;
235		}
236
237		$useCataloguedConnection = true;
238		$schemaName 			 = '';
239
240		if ($argDSN && $argDatabasename)
241		{
242			/*
243			 * If a catalogued connection if provided,
244			 * as well as user and password
245			 * that will take priority
246			 */
247			if ($argUsername && $argPassword && !$this->isDsn($argDatabasename))
248			{
249				if ($this->debug){
250					$errorMessage = 'Warning: Because you provided user,';
251					$errorMessage.= 'password and database, DSN connection ';
252					$errorMessage.= 'parameters were discarded';
253					ADOConnection::outp($errorMessage);
254
255				}
256				$argDSN = '';
257			}
258			else if ($this->isDsn($argDSN) && $this->isDsn($argDatabasename))
259			{
260				$errorMessage = 'Supply uncatalogued connection parameters ';
261				$errorMessage.= 'in either the database or DSN arguments, ';
262				$errorMessage.= 'but not both';
263				$php_errormsg = $errorMessage;
264				if ($this->debug)
265					ADOConnection::outp($errorMessage);
266				return null;
267			}
268		}
269
270		if (!$this->isDsn($argDSN) && $this->isDsn($argDatabasename))
271		{
272			/*
273			 * Switch them around for next test
274			 */
275			$temp           = $argDSN;
276			$argDsn         = $argDatabasename;
277			$argDatabasenME = $temp;
278		}
279
280		if ($this->isDsn($argDSN))
281		{
282
283			if (!preg_match('/uid=/i',$argDSN)
284			||  !preg_match('/pwd=/i',$argDSN))
285			{
286				$errorMessage = 'For uncatalogued connections, provide ';
287				$errorMessage.= 'both UID and PWD in the connection string';
288				$php_errormsg = $errorMessage;
289				if ($this->debug)
290					ADOConnection::outp($errorMessage);
291				return null;
292			}
293
294			if (preg_match('/database=/i',$argDSN))
295			{
296				if ($argDatabasename)
297				{
298					$argDatabasename = '';
299					if ($this->debug)
300					{
301						$errorMessage = 'Warning: Because you provided ';
302						$errorMessage.= 'database information in the DSN ';
303						$errorMessage.= 'parameters, the supplied database ';
304						$errorMessage.= 'name was discarded';
305						ADOConnection::outp($errorMessage);
306					}
307				}
308				$useCataloguedConnection = false;
309
310			}
311			elseif ($argDatabasename)
312			{
313				$this->databaseName = $argDatabasename;
314				$argDSN .= ';database=' . $argDatabasename;
315				$argDatabasename = '';
316				$useCataloguedConnection = false;
317
318			}
319			else
320			{
321				$errorMessage = 'Uncatalogued connection parameters ';
322				$errorMessage.= 'must contain a database= argument';
323				$php_errormsg = $errorMessage;
324				if ($this->debug)
325					ADOConnection::outp($errorMessage);
326				return null;
327			}
328		}
329
330		if ($argDSN && !$argDatabasename && $useCataloguedConnection)
331		{
332			$argDatabasename = $argDSN;
333			$argDSN          = '';
334		}
335
336
337		if ($useCataloguedConnection
338		&& (!$argDatabasename
339		|| !$argUsername
340		|| !$argPassword))
341		{
342
343			$errorMessage = 'For catalogued connections, provide ';
344			$errorMessage.= 'database, username and password';
345			$this->_errorMsg = $errorMessage;
346			if ($this->debug)
347				ADOConnection::outp($errorMessage);
348			return null;
349
350		}
351
352		if ($argDatabasename)
353			$this->databaseName = $argDatabasename;
354		elseif (!$this->databaseName)
355			$this->databaseName = $this->getDatabasenameFromDsn($argDSN);
356
357
358		$connectionParameters = array('dsn'=>$argDSN,
359									  'uid'=>$argUsername,
360									  'pwd'=>$argPassword,
361									  'database'=>$argDatabasename,
362									  'catalogue'=>$useCataloguedConnection
363									  );
364
365		return $connectionParameters;
366
367	}
368
369	/**
370	  * Does the provided string look like a DSN
371	  *
372	  * @param	string	$dsnString
373	  *
374	  * @return bool
375	  */
376	private function isDsn($dsnString){
377		$dsnArray = preg_split('/[;=]+/',$dsnString);
378		if (count($dsnArray) > 2)
379			return true;
380		return false;
381	}
382
383
384	/**
385	  * Gets the database name from the DSN
386	  *
387	  * @param	string	$dsnString
388	  *
389	  * @return string
390	  */
391	private function getDatabasenameFromDsn($dsnString){
392
393		$dsnArray = preg_split('/[;=]+/',$dsnString);
394		$dbIndex  = array_search('database',$dsnArray);
395
396		return $dsnArray[$dbIndex + 1];
397	}
398
399
400	/**
401	* format and return date string in database timestamp format
402	*
403	* @param	mixed	$ts		either a string or a unixtime
404	* @param	bool	$isField	discarded
405	*
406	* @return string
407	*/
408	function dbTimeStamp($ts,$isField=false)
409	{
410		if (empty($ts) && $ts !== 0) return 'null';
411		if (is_string($ts)) $ts = ADORecordSet::unixTimeStamp($ts);
412		return 'TO_DATE('.adodb_date($this->fmtTimeStamp,$ts).",'YYYY-MM-DD HH24:MI:SS')";
413	}
414
415	/**
416	* Format date column in sql string given an input format that understands Y M D
417	*
418	* @param	string	$fmt
419	* @param	bool	$col
420	*
421	* @return string
422	*/
423	function sqlDate($fmt, $col=false)
424	{
425		if (!$col) $col = $this->sysDate;
426
427		/* use TO_CHAR() if $fmt is TO_CHAR() allowed fmt */
428		if ($fmt== 'Y-m-d H:i:s')
429			return 'TO_CHAR('.$col.", 'YYYY-MM-DD HH24:MI:SS')";
430
431		$s = '';
432
433		$len = strlen($fmt);
434		for ($i=0; $i < $len; $i++) {
435			if ($s) $s .= $this->concat_operator;
436			$ch = $fmt[$i];
437			switch($ch) {
438			case 'Y':
439			case 'y':
440				if ($len==1) return "year($col)";
441				$s .= "char(year($col))";
442				break;
443			case 'M':
444				if ($len==1) return "monthname($col)";
445				$s .= "substr(monthname($col),1,3)";
446				break;
447			case 'm':
448				if ($len==1) return "month($col)";
449				$s .= "right(digits(month($col)),2)";
450				break;
451			case 'D':
452			case 'd':
453				if ($len==1) return "day($col)";
454				$s .= "right(digits(day($col)),2)";
455				break;
456			case 'H':
457			case 'h':
458				if ($len==1) return "hour($col)";
459				if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)";
460				else $s .= "''";
461				break;
462			case 'i':
463			case 'I':
464				if ($len==1) return "minute($col)";
465				if ($col != $this->sysDate)
466					$s .= "right(digits(minute($col)),2)";
467					else $s .= "''";
468				break;
469			case 'S':
470			case 's':
471				if ($len==1) return "second($col)";
472				if ($col != $this->sysDate)
473					$s .= "right(digits(second($col)),2)";
474				else $s .= "''";
475				break;
476			default:
477				if ($ch == '\\') {
478					$i++;
479					$ch = substr($fmt,$i,1);
480				}
481				$s .= $this->qstr($ch);
482			}
483		}
484		return $s;
485	}
486
487
488	function serverInfo()
489	{
490		$sql = "SELECT service_level, fixpack_num
491				  FROM TABLE(sysproc.env_get_inst_info())
492					AS INSTANCEINFO";
493		$row = $this->GetRow($sql);
494
495
496		if ($row) {
497			$info['version'] = $row[0].':'.$row[1];
498			$info['fixpack'] = $row[1];
499			$info['description'] = '';
500		} else {
501			return ADOConnection::serverInfo();
502		}
503
504		return $info;
505	}
506
507	function createSequence($seqname='adodbseq',$start=1)
508	{
509		if (empty($this->_genSeqSQL))
510			return false;
511
512		$ok = $this->execute(sprintf($this->_genSeqSQL,$seqname,$start));
513		if (!$ok)
514			return false;
515		return true;
516	}
517
518	function dropSequence($seqname='adodbseq')
519	{
520		if (empty($this->_dropSeqSQL)) return false;
521		return $this->execute(sprintf($this->_dropSeqSQL,$seqname));
522	}
523
524	function selectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false,$secs2cache=0)
525	{
526		$nrows = (integer) $nrows;
527
528		if ($offset <= 0)
529		{
530			if ($nrows >= 0)
531				$sql .=  " FETCH FIRST $nrows ROWS ONLY ";
532
533			$rs = $this->execute($sql,$inputArr);
534
535		}
536		else
537		{
538			if ($offset > 0 && $nrows < 0);
539
540			else
541			{
542				$nrows += $offset;
543				$sql .=  " FETCH FIRST $nrows ROWS ONLY ";
544			}
545
546			/*
547			 * DB2 has no native support for mid table offset
548			 */
549			$rs = ADOConnection::selectLimit($sql,$nrows,$offset,$inputArr);
550
551		}
552
553		return $rs;
554	}
555
556
557	function errorMsg()
558	{
559		if ($this->_errorMsg !== false)
560			return $this->_errorMsg;
561
562		if (empty($this->_connectionID))
563			return @db2_conn_errormsg();
564
565		return @db2_conn_errormsg($this->_connectionID);
566	}
567
568	function errorNo()
569	{
570
571		if ($this->_errorCode !== false)
572			return $this->_errorCode;
573
574
575		if (empty($this->_connectionID))
576			$e = @db2_conn_error();
577
578		else
579			$e = @db2_conn_error($this->_connectionID);
580
581		return $e;
582	}
583
584
585
586	function beginTrans()
587	{
588		if (!$this->hasTransactions)
589			return false;
590		if ($this->transOff)
591			return true;
592
593		$this->transCnt += 1;
594
595		$this->_autocommit = false;
596
597		return db2_autocommit($this->_connectionID,false);
598	}
599
600	function CommitTrans($ok=true)
601	{
602		if ($this->transOff)
603			return true;
604
605		if (!$ok)
606			return $this->RollbackTrans();
607
608		if ($this->transCnt)
609			$this->transCnt -= 1;
610
611		$this->_autocommit = true;
612		$ret = @db2_commit($this->_connectionID);
613		@db2_autocommit($this->_connectionID,true);
614		return $ret;
615	}
616
617	function RollbackTrans()
618	{
619		if ($this->transOff) return true;
620		if ($this->transCnt) $this->transCnt -= 1;
621		$this->_autocommit = true;
622		$ret = @db2_rollback($this->_connectionID);
623		@db2_autocommit($this->_connectionID,true);
624		return $ret;
625	}
626
627	/**
628	 * Return a list of Primary Keys for a specified table
629	 *
630	 * We don't use db2_statistics as the function does not seem to play
631	 * well with mixed case table names
632	 *
633	 * @param string   $table
634	 * @param bool     $primary    (optional) only return primary keys
635	 * @param bool     $owner      (optional) not used in this driver
636	 *
637	 * @return string[]    Array of indexes
638	 */
639	public function metaPrimaryKeys($table,$owner=false)
640	{
641
642		$primaryKeys = array();
643
644		global $ADODB_FETCH_MODE;
645
646		$schema = '';
647		$this->_findschema($table,$schema);
648
649		$table = $this->getTableCasedValue($table);
650
651		$savem 			  = $ADODB_FETCH_MODE;
652		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
653		$this->setFetchMode(ADODB_FETCH_NUM);
654
655
656		$sql = "SELECT *
657				  FROM syscat.indexes
658				 WHERE tabname='$table'";
659
660		$rows = $this->getAll($sql);
661
662		$this->setFetchMode($savem);
663		$ADODB_FETCH_MODE = $savem;
664
665		if (empty($rows))
666			return false;
667
668		foreach ($rows as $r)
669		{
670			if ($r[7] != 'P')
671				continue;
672
673			$cols = explode('+',$r[6]);
674			foreach ($cols as $colIndex=>$col)
675			{
676				if ($colIndex == 0)
677					continue;
678				$columnName = $this->getMetaCasedValue($col);
679				$primaryKeys[] = $columnName;
680			}
681			break;
682		}
683		return $primaryKeys;
684	}
685
686	/**
687	 * returns assoc array where keys are tables, and values are foreign keys
688	 *
689	 * @param	string	$table
690	 * @param	string	$owner		[optional][discarded]
691	 * @param	bool	$upper		[optional][discarded]
692	 * @param	bool	$associative[optional][discarded]
693	 *
694	 * @return	mixed[]			Array of foreign key information
695	 */
696	public function metaForeignKeys($table, $owner = FALSE, $upper = FALSE, $asociative = FALSE )
697	{
698
699		global $ADODB_FETCH_MODE;
700
701		$schema = '';
702		$this->_findschema($table,$schema);
703
704		$savem = $ADODB_FETCH_MODE;
705		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
706
707		$this->setFetchMode(ADODB_FETCH_NUM);
708
709		$sql = "SELECT SUBSTR(tabname,1,20) table_name,
710					   SUBSTR(constname,1,20) fk_name,
711					   SUBSTR(REFTABNAME,1,12) parent_table,
712					   SUBSTR(refkeyname,1,20) pk_orig_table,
713					   fk_colnames
714				 FROM syscat.references
715				WHERE tabname = '$table'";
716
717		$results = $this->getAll($sql);
718
719		$ADODB_FETCH_MODE = $savem;
720		$this->setFetchMode($savem);
721
722		if (empty($results))
723			return false;
724
725		$foreignKeys = array();
726
727		foreach ($results as $r)
728		{
729			$parentTable = trim($this->getMetaCasedValue($r[2]));
730			$keyName     = trim($this->getMetaCasedValue($r[1]));
731			$foreignKeys[$parentTable] = $keyName;
732		}
733
734		return $foreignKeys;
735	}
736
737	/**
738	 * Returns a list of tables
739	 *
740	 * @param string	$ttype (optional)
741	 * @param	string	$schema	(optional)
742	 * @param	string	$mask	(optional)
743	 *
744	 * @return array
745	 */
746	public function metaTables($ttype=false,$schema=false,$mask=false)
747	{
748
749		global $ADODB_FETCH_MODE;
750
751		$savem 			  = $ADODB_FETCH_MODE;
752		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
753
754		/*
755		* Values for TABLE_TYPE
756		* ---------------------------
757		* ALIAS, HIERARCHY TABLE, INOPERATIVE VIEW, NICKNAME,
758		* MATERIALIZED QUERY TABLE, SYSTEM TABLE, TABLE,
759		* TYPED TABLE, TYPED VIEW, and VIEW
760		*
761		* If $ttype passed as '', match 'TABLE' and 'VIEW'
762		* If $ttype passed as 'T' it is assumed to be 'TABLE'
763		* if $ttype passed as 'V' it is assumed to be 'VIEW'
764		*/
765		$ttype = strtoupper($ttype);
766		if ($ttype) {
767			/*
768			 * @todo We could do valid type checking or array type
769			 */
770			 if ($ttype == 'V')
771				$ttype = 'VIEW';
772			if ($ttype == 'T')
773				$ttype = 'TABLE';
774		}
775
776		if (!$schema)
777			$schema = '%';
778
779		if (!$mask)
780			$mask = '%';
781
782		$qid = @db2_tables($this->_connectionID,NULL,$schema,$mask,$ttype);
783
784		$rs = new ADORecordSet_db2($qid);
785
786		$ADODB_FETCH_MODE = $savem;
787
788		if (!$rs)
789			return false;
790
791		$arr = $rs->getArray();
792
793		$rs->Close();
794
795		$tableList = array();
796
797		/*
798		* Array items
799		* ---------------------------------
800		* 0 TABLE_CAT	The catalog that contains the table.
801		*				The value is NULL if this table does not have catalogs.
802		* 1 TABLE_SCHEM	Name of the schema that contains the table.
803		* 2 TABLE_NAME	Name of the table.
804		* 3 TABLE_TYPE	Table type identifier for the table.
805		* 4 REMARKS		Description of the table.
806		*/
807
808		for ($i=0; $i < sizeof($arr); $i++)
809		{
810
811			$tableRow = $arr[$i];
812			$tableName = $tableRow[2];
813			$tableType = $tableRow[3];
814
815			if (!$tableName)
816				continue;
817
818			if ($ttype == '' && (strcmp($tableType,'TABLE') <> 0 && strcmp($tableType,'VIEW') <> 0))
819				continue;
820
821			/*
822			 * Set metacasing if required
823			 */
824			$tableName = $this->getMetaCasedValue($tableName);
825
826			/*
827			 * If we requested a schema, we prepend the schema
828			   name to the table name
829			 */
830			if (strcmp($schema,'%') <> 0)
831				$tableName = $schema . '.' . $tableName;
832
833			$tableList[] = $tableName;
834
835		}
836		return $tableList;
837	}
838
839	/**
840	  * Return a list of indexes for a specified table
841	  *
842	  * We don't use db2_statistics as the function does not seem to play
843	  * well with mixed case table names
844	  *
845	  * @param string   $table
846	  * @param bool     $primary    (optional) only return primary keys
847	  * @param bool     $owner      (optional) not used in this driver
848	  *
849	  * @return string[]    Array of indexes
850	  */
851	public function metaIndexes($table, $primary = false, $owner = false) {
852
853		global $ADODB_FETCH_MODE;
854
855		 /* Array(
856		 *   [name_of_index] => Array(
857		 *     [unique] => true or false
858		 *     [columns] => Array(
859		 *       [0] => firstcol
860		 *       [1] => nextcol
861		 *       [2] => etc........
862		 *     )
863		 *   )
864		 * )
865		 */
866		$indices 		= array();
867		$primaryKeyName = '';
868
869		$table = $this->getTableCasedValue($table);
870
871
872		$savem 			  = $ADODB_FETCH_MODE;
873		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
874		$this->setFetchMode(ADODB_FETCH_NUM);
875
876		$sql = "SELECT *
877				  FROM syscat.indexes
878				 WHERE tabname='$table'";
879
880		$rows = $this->getAll($sql);
881
882		$this->setFetchMode($savem);
883		$ADODB_FETCH_MODE = $savem;
884
885		if (empty($rows))
886			return false;
887
888		foreach ($rows as $r)
889		{
890
891			$primaryIndex = $r[7] == 'P'?1:0;
892			if (!$primary)
893				/*
894				 * Primary key not requested, ignore that one
895				 */
896				if ($r[7] == 'P')
897					continue;
898
899			$indexName = $this->getMetaCasedValue($r[1]);
900			if (!isset($indices[$indexName]))
901			{
902				$unique = ($r[7] == 'U')?1:0;
903				$indices[$indexName] = array('unique'=>$unique,
904											 'primary'=>$primaryIndex,
905											 'columns'=>array()
906										);
907			}
908			$cols = explode('+',$r[6]);
909			foreach ($cols as $colIndex=>$col)
910			{
911				if ($colIndex == 0)
912					continue;
913				$columnName = $this->getMetaCasedValue($col);
914				$indices[$indexName]['columns'][] = $columnName;
915			}
916
917		}
918
919		return $indices;
920
921	}
922
923	/**
924	 * List procedures or functions in an array.
925	 *
926	 * We interrogate syscat.routines instead of calling the PHP
927	 * function procedures because ADOdb requires the type of procedure
928	 * this is not available in the php function
929	 *
930	 * @param	string $procedureNamePattern (optional)
931	 * @param	string $catalog				 (optional)
932	 * @param	string $schemaPattern		 (optional)
933
934	 * @return array of procedures on current database.
935	 *
936	 */
937	public function metaProcedures($procedureNamePattern = null, $catalog  = null, $schemaPattern  = null) {
938
939
940		global $ADODB_FETCH_MODE;
941
942		$metaProcedures = array();
943		$procedureSQL   = '';
944		$catalogSQL     = '';
945		$schemaSQL      = '';
946
947		$savem 			  = $ADODB_FETCH_MODE;
948		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
949
950		if ($procedureNamePattern)
951			$procedureSQL = "AND ROUTINENAME LIKE " . strtoupper($this->qstr($procedureNamePattern));
952
953		if ($catalog)
954			$catalogSQL = "AND OWNER=" . strtoupper($this->qstr($catalog));
955
956		if ($schemaPattern)
957			$schemaSQL = "AND ROUTINESCHEMA LIKE {$this->qstr($schemaPattern)}";
958
959
960		$fields = "
961		ROUTINENAME,
962		CASE ROUTINETYPE
963			 WHEN 'P' THEN 'PROCEDURE'
964			 WHEN 'F' THEN 'FUNCTION'
965			 ELSE 'METHOD'
966			 END AS ROUTINETYPE_NAME,
967		ROUTINESCHEMA,
968		REMARKS";
969
970		$SQL = "SELECT $fields
971				  FROM syscat.routines
972				 WHERE OWNER IS NOT NULL
973				  $procedureSQL
974				  $catalogSQL
975				  $schemaSQL
976				ORDER BY ROUTINENAME
977				";
978
979		$result = $this->execute($SQL);
980
981		$ADODB_FETCH_MODE = $savem;
982
983		if (!$result)
984			return false;
985
986		while ($r = $result->fetchRow()){
987			$procedureName = $this->getMetaCasedValue($r[0]);
988			$schemaName    = $this->getMetaCasedValue($r[2]);
989			$metaProcedures[$procedureName] = array('type'=> $r[1],
990												   'catalog' => '',
991												   'schema'  => $schemaName,
992												   'remarks' => $r[3]
993													);
994		}
995
996		return $metaProcedures;
997
998	}
999
1000	/**
1001	  * Lists databases. Because instances are independent, we only know about
1002	  * the current database name
1003	  *
1004	  * @return string[]
1005	  */
1006	public function metaDatabases(){
1007
1008		$dbName = $this->getMetaCasedValue($this->databaseName);
1009
1010		return (array)$dbName;
1011
1012	}
1013
1014
1015
1016
1017/*
1018See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2datetime_data_type_changes.asp
1019/ SQL data type codes /
1020#define	SQL_UNKNOWN_TYPE	0
1021#define SQL_CHAR			1
1022#define SQL_NUMERIC		 2
1023#define SQL_DECIMAL		 3
1024#define SQL_INTEGER		 4
1025#define SQL_SMALLINT		5
1026#define SQL_FLOAT		   6
1027#define SQL_REAL			7
1028#define SQL_DOUBLE		  8
1029#if (DB2VER >= 0x0300)
1030#define SQL_DATETIME		9
1031#endif
1032#define SQL_VARCHAR		12
1033
1034
1035/ One-parameter shortcuts for date/time data types /
1036#if (DB2VER >= 0x0300)
1037#define SQL_TYPE_DATE	  91
1038#define SQL_TYPE_TIME	  92
1039#define SQL_TYPE_TIMESTAMP 93
1040
1041#define SQL_UNICODE                             (-95)
1042#define SQL_UNICODE_VARCHAR                     (-96)
1043#define SQL_UNICODE_LONGVARCHAR                 (-97)
1044*/
1045	function DB2Types($t)
1046	{
1047		switch ((integer)$t) {
1048		case 1:
1049		case 12:
1050		case 0:
1051		case -95:
1052		case -96:
1053			return 'C';
1054		case -97:
1055		case -1: //text
1056			return 'X';
1057		case -4: //image
1058			return 'B';
1059
1060		case 9:
1061		case 91:
1062			return 'D';
1063
1064		case 10:
1065		case 11:
1066		case 92:
1067		case 93:
1068			return 'T';
1069
1070		case 4:
1071		case 5:
1072		case -6:
1073			return 'I';
1074
1075		case -11: // uniqidentifier
1076			return 'R';
1077		case -7: //bit
1078			return 'L';
1079
1080		default:
1081			return 'N';
1082		}
1083	}
1084
1085	public function metaColumns($table, $normalize=true)
1086	{
1087		global $ADODB_FETCH_MODE;
1088
1089		$savem = $ADODB_FETCH_MODE;
1090
1091		$schema = '%';
1092		$this->_findschema($table,$schema);
1093		$table = $this->getTableCasedValue($table);
1094		$colname = "%";
1095		$qid = db2_columns($this->_connectionID, null, $schema, $table, $colname);
1096		if (empty($qid))
1097		{
1098			if ($this->debug)
1099			{
1100				$errorMessage = @db2_conn_errormsg($this->_connectionID);
1101				ADOConnection::outp($errorMessage);
1102			}
1103			return false;
1104		}
1105
1106		$rs = new ADORecordSet_db2($qid);
1107
1108		if (!$rs)
1109			return false;
1110
1111		$rs->_fetch();
1112
1113		$retarr = array();
1114
1115		/*
1116		$rs->fields indices
1117		0 TABLE_QUALIFIER
1118		1 TABLE_SCHEM
1119		2 TABLE_NAME
1120		3 COLUMN_NAME
1121		4 DATA_TYPE
1122		5 TYPE_NAME
1123		6 PRECISION
1124		7 LENGTH
1125		8 SCALE
1126		9 RADIX
1127		10 NULLABLE
1128		11 REMARKS
1129		12 Column Default
1130		13 SQL Data Type
1131		14 SQL DateTime SubType
1132		15 Max length in Octets
1133		16 Ordinal Position
1134		17 Is NULLABLE
1135		*/
1136		while (!$rs->EOF)
1137		{
1138			if ($rs->fields[2] == $table)
1139			{
1140
1141				$fld       = new ADOFieldObject();
1142				$fld->name = $rs->fields[3];
1143				$fld->type = $this->DB2Types($rs->fields[4]);
1144
1145				// ref: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_odk.asp
1146				// access uses precision to store length for char/varchar
1147
1148				if ($fld->type == 'C' or $fld->type == 'X') {
1149					if ($rs->fields[4] <= -95) // UNICODE
1150						$fld->max_length = $rs->fields[7]/2;
1151					else
1152						$fld->max_length = $rs->fields[7];
1153				} else
1154					$fld->max_length = $rs->fields[7];
1155
1156				$fld->not_null         = !empty($rs->fields[10]);
1157				$fld->scale            = $rs->fields[8];
1158				$fld->primary_key      = false;
1159
1160				//$columnName = $this->getMetaCasedValue($fld->name);
1161				$columnName = strtoupper($fld->name);
1162				$retarr[$columnName] = $fld;
1163
1164			}
1165			else if (sizeof($retarr)>0)
1166				break;
1167
1168			$rs->MoveNext();
1169
1170		}
1171
1172		$rs->Close();
1173		if (empty($retarr))
1174			$retarr = false;
1175
1176		/*
1177		 * Now we find out if the column is part of a primary key
1178		 */
1179
1180		$qid = @db2_primary_keys($this->_connectionID, "", $schema, $table);
1181		if (empty($qid))
1182			return false;
1183
1184		$rs = new ADORecordSet_db2($qid);
1185
1186		if (!$rs)
1187		{
1188			$ADODB_FETCH_MODE = $savem;
1189			return $retarr;
1190		}
1191		$rs->_fetch();
1192
1193		/*
1194		$rs->fields indices
1195		0 TABLE_CAT
1196		1 TABLE_SCHEM
1197		2 TABLE_NAME
1198		3 COLUMN_NAME
1199		4 KEY_SEQ
1200		5 PK_NAME
1201		*/
1202		while (!$rs->EOF) {
1203			if (strtoupper(trim($rs->fields[2])) == $table
1204			&& (!$schema || strtoupper($rs->fields[1]) == $schema))
1205			{
1206				$retarr[strtoupper($rs->fields[3])]->primary_key = true;
1207			}
1208			else if (sizeof($retarr)>0)
1209				break;
1210
1211			$rs->MoveNext();
1212		}
1213		$rs->Close();
1214
1215		$ADODB_FETCH_MODE = $savem;
1216
1217		if (empty($retarr))
1218			return false;
1219
1220		/*
1221		* If the fetch mode is numeric, return as numeric array
1222		*/
1223		if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM)
1224			$retarr = array_values($retarr);
1225
1226		return $retarr;
1227	}
1228
1229	/**
1230	  * In this version if prepareSp, we just check to make sure
1231	  * that the name of the stored procedure is correct
1232	  * If true, we returns an array
1233	  * else false
1234	  *
1235	  * @param	string	$procedureName
1236	  * @param	mixed   $parameters (not used in db2 connections)
1237	  * @return mixed[]
1238	  */
1239	function prepareSp($procedureName,$parameters=false) {
1240
1241		global $ADODB_FETCH_MODE;
1242
1243		$this->storedProcedureParameters = array('name'=>'',
1244												 'resource'=>false,
1245												 'in'=>array(),
1246												 'out'=>array(),
1247												 'index'=>array(),
1248												 'parameters'=>array(),
1249												 'keyvalue' => array());
1250
1251		//$procedureName = strtoupper($procedureName);
1252		//$procedureName = $this->getTableCasedValue($procedureName);
1253
1254		$savem = $ADODB_FETCH_MODE;
1255		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
1256
1257		$qid = db2_procedures($this->_connectionID, NULL , '%' , $procedureName );
1258
1259		$ADODB_FETCH_MODE = $savem;
1260
1261		if (!$qid)
1262		{
1263			if ($this->debug)
1264				ADOConnection::outp(sprintf('No Procedure of name %s available',$procedureName));
1265			return false;
1266		}
1267
1268
1269
1270		$this->storedProcedureParameters['name'] = $procedureName;
1271		/*
1272		 * Now we know we have a valid procedure name, lets see if it requires
1273		 * parameters
1274		 */
1275		$savem = $ADODB_FETCH_MODE;
1276		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
1277
1278		$qid = db2_procedure_columns($this->_connectionID, NULL , '%' , $procedureName , NULL );
1279
1280		$ADODB_FETCH_MODE = $savem;
1281
1282		if (!$qid)
1283		{
1284			if ($this->debug)
1285				ADOConnection::outp(sprintf('No columns of name %s available',$procedureName));
1286			return false;
1287		}
1288		$rs = new ADORecordSet_db2($qid);
1289		if (!$rs)
1290			return false;
1291
1292		$preparedStatement = 'CALL %s(%s)';
1293		$parameterMarkers = array();
1294		while (!$rs->EOF)
1295		{
1296			$parameterName = $rs->fields[3];
1297			if ($parameterName == '')
1298			{
1299				$rs->moveNext();
1300				continue;
1301			}
1302			$parameterType = $rs->fields[4];
1303			$ordinalPosition = $rs->fields[17];
1304			switch($parameterType)
1305			{
1306			case DB2_PARAM_IN:
1307			case DB2_PARAM_INOUT:
1308				$this->storedProcedureParameters['in'][$parameterName] = '';
1309				break;
1310			case DB2_PARAM_INOUT:
1311			case DB2_PARAM_OUT:
1312				$this->storedProcedureParameters['out'][$parameterName] = '';
1313				break;
1314			}
1315			$this->storedProcedureParameters['index'][$parameterName] = $ordinalPosition;
1316			$this->storedProcedureParameters['parameters'][$ordinalPosition] = $rs->fields;
1317			$rs->moveNext();
1318
1319		}
1320		$parameterCount = count($this->storedProcedureParameters['index']);
1321		$parameterMarkers = array_fill(0,$parameterCount,'?');
1322
1323		/*
1324		 * We now know how many parameters to bind to the stored procedure
1325		 */
1326		$parameterList = implode(',',$parameterMarkers);
1327
1328		$sql = sprintf($preparedStatement,$procedureName,$parameterList);
1329
1330		$spResource = @db2_prepare($this->_connectionID,$sql);
1331
1332		if (!$spResource)
1333		{
1334			$errorMessage = @db2_conn_errormsg($this->_connectionID);
1335			$this->_errorMsg = $errorMessage;
1336
1337			if ($this->debug)
1338				ADOConnection::outp($errorMessage);
1339
1340			return false;
1341		}
1342
1343		$this->storedProcedureParameters['resource'] = $spResource;
1344
1345		if ($this->debug)
1346		{
1347
1348			ADOConnection::outp('The following parameters will be used in the SP call');
1349			ADOConnection::outp(print_r($this->storedProcedureParameters));
1350		}
1351		/*
1352		 * We now have a stored parameter resource
1353		 * to bind to. The spResource and sql that is returned are
1354		 * not usable, its for dummy compatibility. Everything
1355		 * will be handled by the storedProcedureParameters
1356		 * array
1357		 */
1358		return array($sql,$spResource);
1359
1360	}
1361
1362	private function storedProcedureParameter(&$stmt,
1363											  &$var,
1364											  $name,
1365											  $isOutput=false,
1366											  $maxLen=4000,
1367											  $type=false)
1368	{
1369
1370
1371		$name = strtoupper($name);
1372
1373		/*
1374		 * Must exist in the list of parameter names for the type
1375		 */
1376		if ($isOutput
1377		&& !isset( $this->storedProcedureParameters['out'][$name]))
1378		{
1379			$errorMessage = sprintf('%s is not a valid OUT parameter name',$name);
1380
1381			$this->_errorMsg = $errorMessage;
1382			if ($this->debug)
1383				ADOConnection::outp($errorMessage);
1384			return false;
1385		}
1386
1387		if (!$isOutput
1388		&& !isset( $this->storedProcedureParameters['in'][$name]))
1389		{
1390			$errorMessage = sprintf('%s is not a valid IN parameter name',$name);
1391
1392			$this->_errorMsg = $errorMessage;
1393			if ($this->debug)
1394				ADOConnection::outp($errorMessage);
1395			return false;
1396		}
1397
1398		/*
1399		 * We will use these values to bind to when we execute
1400		 * the query
1401		 */
1402		$this->storedProcedureParameters['keyvalue'][$name] = &$var;
1403
1404		return true;
1405
1406	}
1407
1408	/**
1409	* Executes a prepared stored procedure.
1410	*
1411	* The function uses the previously accumulated information and
1412	* resources in the $storedProcedureParameters array
1413	*
1414	* @return mixed	The statement id if successful, or false
1415	*/
1416	private function executeStoredProcedure()
1417	{
1418
1419		/*
1420		 * Get the previously built resource
1421		 */
1422		$stmtid = $this->storedProcedureParameters['resource'];
1423
1424		/*
1425		 * Bind our variables to the DB2 procedure
1426		 */
1427		foreach ($this->storedProcedureParameters['keyvalue'] as $spName=>$spValue){
1428
1429			/*
1430			 * Get the ordinal position, required for binding
1431			 */
1432			$ordinalPosition = $this->storedProcedureParameters['index'][$spName];
1433
1434			/*
1435			 * Get the db2 column dictionary for the parameter
1436			 */
1437			$columnDictionary = $this->storedProcedureParameters['parameters'][$ordinalPosition];
1438			$parameterType    = $columnDictionary[4];
1439			$dataType         = $columnDictionary[5];
1440			$precision        = $columnDictionary[10];
1441			$scale        	  = $columnDictionary[9];
1442
1443			$ok = @db2_bind_param ($this->storedProcedureParameters['resource'],
1444								  $ordinalPosition ,
1445								  $spName,
1446								  $parameterType,
1447								  $dataType,
1448								  $precision,
1449								  $scale
1450								  );
1451
1452			if (!$ok)
1453			{
1454				$this->_errorMsg  = @db2_stmt_errormsg();
1455				$this->_errorCode = @db2_stmt_error();
1456
1457				if ($this->debug)
1458					ADOConnection::outp($this->_errorMsg);
1459				return false;
1460			}
1461
1462			if ($this->debug)
1463				ADOConnection::outp("Correctly Bound parameter $spName to procedure");
1464
1465			/*
1466			 * Build a variable in the current environment that matches
1467			 * the parameter name
1468			 */
1469			${$spName} = $spValue;
1470
1471		}
1472
1473		/*
1474		 * All bound, execute
1475		 */
1476
1477		if (!@db2_execute($stmtid))
1478		{
1479			$this->_errorMsg = @db2_stmt_errormsg();
1480			$this->_errorCode = @db2_stmt_error();
1481
1482			if ($this->debug)
1483				ADOConnection::outp($this->_errorMsg);
1484			return false;
1485		}
1486
1487		/*
1488		 * We now take the changed parameters back into the
1489		 * stored procedures array where we can query them later
1490		 * Remember that $spValue was passed in by reference, so we
1491		 * can access the value in the variable that was originally
1492		 * passed to inParameter or outParameter
1493		 */
1494		foreach ($this->storedProcedureParameters['keyvalue'] as $spName=>$spValue)
1495		{
1496			/*
1497			 * We make it available to the environment
1498			 */
1499			$spValue = ${$spName};
1500			$this->storedProcedureParameters['keyvalue'][$spName] = $spValue;
1501		}
1502
1503		return $stmtid;
1504	}
1505
1506	/**
1507	 *
1508	 * Accepts an input or output parameter to bind to either a stored
1509	 * or prepared statements. For DB2, this should not be called as an
1510	 * API. always wrap with inParameter and outParameter
1511	 *
1512	 * @param mixed[] $stmt 		Statement returned by Prepare() or PrepareSP().
1513	 * @param mixed   $var 		PHP variable to bind to. Can set to null (for isNull support).
1514	 * @param string  $name 		Name of stored procedure variable name to bind to.
1515	 * @param int	 $isOutput 	optional) Indicates direction of parameter
1516	 * 							0/false=IN  1=OUT  2= IN/OUT
1517	 *							This is ignored for Stored Procedures
1518	 * @param int	$maxLen		(optional)Holds an maximum length of the variable.
1519	 *							This is ignored for Stored Procedures
1520	 * @param int	$type 		(optional) The data type of $var.
1521	 *							This is ignored for Stored Procedures
1522	 *
1523	 * @return bool				Success of the operation
1524	 */
1525	public function parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false)
1526	{
1527
1528		/*
1529		 * If the $stmt is the name of a stored procedure we are
1530		 * setting up, we will process it one way, otherwise
1531		 * we assume we are setting up a prepared statement
1532		*/
1533		if (is_array($stmt))
1534		{
1535			if ($this->debug)
1536				ADOConnection::outp("Adding parameter to stored procedure");
1537			if ($stmt[1] == $this->storedProcedureParameters['resource'])
1538				return $this->storedProcedureParameter($stmt[1],
1539														$var,
1540														$name,
1541														$isOutput,
1542														$maxLen,
1543														$type);
1544
1545		}
1546
1547		/*
1548		 * We are going to add a parameter to a prepared statement
1549		 */
1550		if ($this->debug)
1551			ADOConnection::outp("Adding parameter to prepared statement");
1552	}
1553
1554
1555	/**
1556	 * Prepares a prepared SQL statement, not used for stored procedures
1557	 *
1558	 * @param string	$sql
1559	 *
1560	 * @return mixed
1561	 */
1562	function prepare($sql)
1563	{
1564
1565		if (! $this->_bindInputArray) return $sql; // no binding
1566
1567		$stmt = @db2_prepare($this->_connectionID,$sql);
1568		if (!$stmt) {
1569			// we don't know whether db2 driver is parsing prepared stmts, so just return sql
1570			return $sql;
1571		}
1572		return array($sql,$stmt,false);
1573	}
1574
1575	/**
1576	 * Executes a query
1577	 *
1578	 * @param	mixed $sql
1579	 * @param	mixed $inputarr	An optional array of parameters
1580	 *
1581	 * @return mixed				either the queryID or false
1582	 */
1583	function _query(&$sql,$inputarr=false)
1584	{
1585
1586		GLOBAL $php_errormsg;
1587
1588		if (isset($php_errormsg))
1589			$php_errormsg = '';
1590		$this->_error = '';
1591
1592		$db2Options = array();
1593		/*
1594		 * Use DB2 Internal case handling for best speed
1595		 */
1596		switch(ADODB_ASSOC_CASE)
1597		{
1598		case ADODB_ASSOC_CASE_UPPER:
1599			$db2Options = array('db2_attr_case'=>DB2_CASE_UPPER);
1600			$setOption = @db2_set_option($this->_connectionID,$db2Options,1);
1601			break;
1602
1603		 case ADODB_ASSOC_CASE_LOWER:
1604			$db2Options = array('db2_attr_case'=>DB2_CASE_LOWER);
1605			$setOption = @db2_set_option($this->_connectionID,$db2Options,1);
1606			break;
1607
1608		default:
1609			$db2Options = array('db2_attr_case'=>DB2_CASE_NATURAL);
1610			$setOption = @db2_set_option($this->_connectionID,$db2Options,1);
1611		}
1612
1613		if ($inputarr)
1614		{
1615			if (is_array($sql))
1616			{
1617				$stmtid = $sql[1];
1618			}
1619			else
1620			{
1621				$stmtid = @db2_prepare($this->_connectionID,$sql);
1622
1623				if ($stmtid == false)
1624				{
1625					$this->_errorMsg = isset($php_errormsg) ? $php_errormsg : '';
1626					return false;
1627				}
1628			}
1629
1630			if (! @db2_execute($stmtid,$inputarr))
1631			{
1632				$this->_errorMsg = @db2_stmt_errormsg();
1633				$this->_errorCode = @db2_stmt_error();
1634				if ($this->debug)
1635					ADOConnection::outp($this->_errorMsg);
1636				return false;
1637			}
1638
1639		}
1640		else if (is_array($sql))
1641		{
1642
1643			/*
1644			 * Either a prepared statement or a stored procedure
1645			 */
1646
1647			if (is_array($this->storedProcedureParameters)
1648				&& is_resource($this->storedProcedureParameters['resource']
1649			))
1650				/*
1651				 * This is all handled in the separate method for
1652				 * readability
1653				 */
1654				return $this->executeStoredProcedure();
1655
1656			/*
1657			 * First, we prepare the statement
1658			 */
1659			$stmtid = @db2_prepare($this->_connectionID,$sql[0]);
1660			if (!$stmtid){
1661				$this->_errorMsg = @db2_stmt_errormsg();
1662				$this->_errorCode = @db2_stmt_error();
1663				if ($this->debug)
1664					ADOConnection::outp("Prepare failed: " . $this->_errorMsg);
1665
1666				return false;
1667			}
1668			/*
1669			 * We next bind some input parameters
1670			 */
1671			$ordinal = 1;
1672			foreach ($sql[1] as $psVar=>$psVal){
1673				${$psVar} = $psVal;
1674				$ok = @db2_bind_param($stmtid, $ordinal, $psVar, DB2_PARAM_IN);
1675				if (!$ok)
1676				{
1677					$this->_errorMsg = @db2_stmt_errormsg();
1678					$this->_errorCode = @db2_stmt_error();
1679					if ($this->debug)
1680						ADOConnection::outp("Bind failed: " . $this->_errorMsg);
1681					return false;
1682				}
1683			}
1684
1685			if (!@db2_execute($stmtid))
1686			{
1687				$this->_errorMsg = @db2_stmt_errormsg();
1688				$this->_errorCode = @db2_stmt_error();
1689				if ($this->debug)
1690					ADOConnection::outp($this->_errorMsg);
1691				return false;
1692			}
1693
1694			return $stmtid;
1695		}
1696		else
1697		{
1698
1699			$stmtid = @db2_exec($this->_connectionID,$sql);
1700		}
1701		$this->_lastAffectedRows = 0;
1702		if ($stmtid)
1703		{
1704			if (@db2_num_fields($stmtid) == 0)
1705			{
1706				$this->_lastAffectedRows = db2_num_rows($stmtid);
1707				$stmtid = true;
1708			}
1709			else
1710			{
1711				$this->_lastAffectedRows = 0;
1712			}
1713
1714			$this->_errorMsg = '';
1715			$this->_errorCode = 0;
1716
1717		}
1718		else
1719		{
1720
1721			$this->_errorMsg = @db2_stmt_errormsg();
1722			$this->_errorCode = @db2_stmt_error();
1723
1724		}
1725		return $stmtid;
1726	}
1727
1728	/*
1729		Insert a null into the blob field of the table first.
1730		Then use UpdateBlob to store the blob.
1731
1732		Usage:
1733
1734		$conn->execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
1735		$conn->UpdateBlob('blobtable','blobcol',$blob,'id=1');
1736	*/
1737	function updateBlob($table,$column,$val,$where,$blobtype='BLOB')
1738	{
1739		return $this->execute("UPDATE $table SET $column=? WHERE $where",array($val)) != false;
1740	}
1741
1742	// returns true or false
1743	function _close()
1744	{
1745		$ret = @db2_close($this->_connectionID);
1746		$this->_connectionID = false;
1747		return $ret;
1748	}
1749
1750	function _affectedrows()
1751	{
1752		return $this->_lastAffectedRows;
1753	}
1754
1755	/**
1756	 * Gets a meta cased parameter
1757	 *
1758	 * Receives an input variable to be processed per the metaCasing
1759	 * rule, and returns the same value, processed
1760	 *
1761	 * @param string $value
1762	 *
1763	 * @return string
1764	 */
1765	final public function getMetaCasedValue($value)
1766	{
1767		global $ADODB_ASSOC_CASE;
1768
1769		switch($ADODB_ASSOC_CASE)
1770		{
1771		case ADODB_ASSOC_CASE_LOWER:
1772			$value = strtolower($value);
1773			break;
1774		case ADODB_ASSOC_CASE_UPPER:
1775			$value = strtoupper($value);
1776			break;
1777		}
1778		return $value;
1779	}
1780
1781
1782	const TABLECASE_LOWER    =  0;
1783	const TABLECASE_UPPER    =  1;
1784	const TABLECASE_DEFAULT  =  2;
1785
1786	/**
1787	 * Controls the casing of the table provided to the meta functions
1788	 */
1789	private $tableCase = 2;
1790
1791	/**
1792	 * Sets the table case parameter
1793	 *
1794	 * @param int $caseOption
1795	 * @return null
1796	 */
1797	final public function setTableCasing($caseOption)
1798	{
1799		$this->tableCase = $caseOption;
1800	}
1801
1802	/**
1803	 * Gets the table casing parameter
1804	 *
1805	 * @return int $caseOption
1806	 */
1807	final public function getTableCasing()
1808	{
1809		return $this->tableCase;
1810	}
1811
1812	/**
1813	 * Gets a table cased parameter
1814	 *
1815	 * Receives an input variable to be processed per the tableCasing
1816	 * rule, and returns the same value, processed
1817	 *
1818	 * @param string $value
1819	 *
1820	 * @return string
1821	 */
1822	final public function getTableCasedValue($value)
1823	{
1824		switch($this->tableCase)
1825		{
1826		case self::TABLECASE_LOWER:
1827			$value = strtolower($value);
1828			break;
1829		case self::TABLECASE_UPPER:
1830			$value = strtoupper($value);
1831			break;
1832		}
1833		return $value;
1834	}
1835
1836}
1837
1838/*--------------------------------------------------------------------------------------
1839	 Class Name: Recordset
1840--------------------------------------------------------------------------------------*/
1841
1842class ADORecordSet_db2 extends ADORecordSet {
1843
1844	var $bind = false;
1845	var $databaseType = "db2";
1846	var $dataProvider = "db2";
1847	var $useFetchArray;
1848
1849	function __construct($id,$mode=false)
1850	{
1851		if ($mode === false) {
1852			global $ADODB_FETCH_MODE;
1853			$mode = $ADODB_FETCH_MODE;
1854		}
1855		$this->fetchMode = $mode;
1856
1857		$this->_queryID = $id;
1858	}
1859
1860
1861	// returns the field object
1862	function fetchField($offset = 0)
1863	{
1864		$o			   = new ADOFieldObject();
1865		$o->name 	   = @db2_field_name($this->_queryID,$offset);
1866		$o->type 	   = @db2_field_type($this->_queryID,$offset);
1867		$o->max_length = @db2_field_width($this->_queryID,$offset);
1868
1869		/*
1870		if (ADODB_ASSOC_CASE == 0)
1871			$o->name = strtolower($o->name);
1872		else if (ADODB_ASSOC_CASE == 1)
1873			$o->name = strtoupper($o->name);
1874		*/
1875		return $o;
1876	}
1877
1878	/* Use associative array to get fields array */
1879	function fields($colname)
1880	{
1881
1882		if ($this->fetchMode & ADODB_FETCH_ASSOC) {
1883			return $this->fields[$colname];
1884		}
1885
1886		if (!$this->bind) {
1887			$this->bind = array();
1888			for ($i=0; $i < $this->_numOfFields; $i++) {
1889				$o = $this->FetchField($i);
1890				$this->bind[strtoupper($o->name)] = $i;
1891			}
1892		}
1893
1894		 return $this->fields[$this->bind[strtoupper($colname)]];
1895	}
1896
1897
1898	function _initrs()
1899	{
1900		global $ADODB_COUNTRECS;
1901		$this->_numOfRows = ($ADODB_COUNTRECS) ? @db2_num_rows($this->_queryID) : -1;
1902
1903		$this->_numOfFields = @db2_num_fields($this->_queryID);
1904
1905		// some silly drivers such as db2 as/400 and intersystems cache return _numOfRows = 0
1906
1907		if ($this->_numOfRows == 0)
1908			$this->_numOfRows = -1;
1909	}
1910
1911	function _seek($row)
1912	{
1913		return false;
1914	}
1915
1916	function getArrayLimit($nrows,$offset=0)
1917	{
1918		if ($offset <= 0) {
1919			$rs = $this->GetArray($nrows);
1920			return $rs;
1921		}
1922
1923		$this->Move($offset);
1924
1925
1926		$results = array();
1927		$cnt = 0;
1928		while (!$this->EOF && $nrows != $cnt) {
1929			$results[$cnt++] = $this->fields;
1930			$this->MoveNext();
1931		}
1932
1933		return $results;
1934	}
1935
1936	function moveNext()
1937	{
1938		if ($this->EOF || $this->_numOfRows == 0)
1939			return false;
1940
1941		$this->_currentRow++;
1942
1943		$this->processCoreFetch();
1944		return $this->processMoveRecord();
1945
1946	}
1947
1948	private function processCoreFetch()
1949	{
1950		switch ($this->fetchMode){
1951		case ADODB_FETCH_ASSOC:
1952
1953			/*
1954			 * Associative array
1955			 */
1956			$this->fields = @db2_fetch_assoc($this->_queryID);
1957			break;
1958
1959		case ADODB_FETCH_BOTH:
1960			/*
1961			 * Fetch both numeric and Associative array
1962			 */
1963			$this->fields = @db2_fetch_both($this->_queryID);
1964			break;
1965		default:
1966			/*
1967			 * Numeric array
1968			 */
1969			$this->fields = @db2_fetch_array($this->_queryID);
1970			break;
1971		}
1972	}
1973
1974	private function processMoveRecord()
1975	{
1976		if (!$this->fields){
1977			$this->EOF = true;
1978			return false;
1979		}
1980
1981		return true;
1982	}
1983
1984	function _fetch()
1985	{
1986		$this->processCoreFetch();
1987		if ($this->fields)
1988			return true;
1989
1990		$this->fields = false;
1991		return false;
1992	}
1993
1994	function _close()
1995	{
1996		$ok = @db2_free_result($this->_queryID);
1997		if (!$ok)
1998		{
1999			$this->_errorMsg  = @db2_stmt_errormsg($this->_queryId);
2000			$this->_errorCode = @db2_stmt_error();
2001
2002			if ($this->debug)
2003				ADOConnection::outp($this->_errorMsg);
2004			return false;
2005		}
2006
2007	}
2008
2009}
2010