diff options
| author | Mark Newnham <mark@newnhams.com> | 2025-10-11 15:30:21 -0600 |
|---|---|---|
| committer | Mark Newnham <mark@newnhams.com> | 2025-10-11 15:30:21 -0600 |
| commit | acb90e82c7f22e387f92842d7e898f68ca3fde9b (patch) | |
| tree | 117d2d1a089b9f8cf4aeb8699cba88af86e6b4d8 | |
| parent | 283453a98a62fe7562730c9dfa41922d4593e027 (diff) | |
| download | adodb-acb90e82c7f22e387f92842d7e898f68ca3fde9b.tar.gz adodb-acb90e82c7f22e387f92842d7e898f68ca3fde9b.tar.bz2 adodb-acb90e82c7f22e387f92842d7e898f68ca3fde9b.zip | |
Add updated rowcount and selectlimit offset
| -rw-r--r-- | drivers/adodb-db2.inc.php | 3483 |
1 files changed, 1766 insertions, 1717 deletions
diff --git a/drivers/adodb-db2.inc.php b/drivers/adodb-db2.inc.php index e463914b..be2c7b8a 100644 --- a/drivers/adodb-db2.inc.php +++ b/drivers/adodb-db2.inc.php @@ -38,807 +38,851 @@ define("_ADODB_DB2_LAYER", 2 ); class ADODB_db2 extends ADOConnection { - var $databaseType = "db2"; - var $fmtDate = "'Y-m-d'"; - var $concat_operator = '||'; + var $databaseType = "db2"; + var $fmtDate = "'Y-m-d'"; + var $concat_operator = '||'; - var $sysTime = 'CURRENT TIME'; - var $sysDate = 'CURRENT DATE'; - var $sysTimeStamp = 'CURRENT TIMESTAMP'; + var $sysTime = 'CURRENT TIME'; + var $sysDate = 'CURRENT DATE'; + var $sysTimeStamp = 'CURRENT TIMESTAMP'; - var $fmtTimeStamp = "'Y-m-d H:i:s'"; - var $replaceQuote = "''"; // string to use to replace quotes - var $dataProvider = "db2"; - var $hasAffectedRows = true; + var $fmtTimeStamp = "'Y-m-d H:i:s'"; + var $replaceQuote = "''"; // string to use to replace quotes + var $dataProvider = "db2"; + var $hasAffectedRows = true; - var $binmode = DB2_BINARY; + var $binmode = DB2_BINARY; - /* - * setting this to true will make array elements in FETCH_ASSOC - * mode case-sensitive breaking backward-compat - */ - var $useFetchArray = false; - var $_bindInputArray = true; - var $_genIDSQL = "VALUES NEXTVAL FOR %s"; - - /** - * SQL to create a new sequence - * - * @var string - */ - var $_genSeqSQL = " - CREATE SEQUENCE %s START WITH %s - NO MAXVALUE NO CYCLE INCREMENT BY 1 NO CACHE - "; + /* + * setting this to true will make array elements in FETCH_ASSOC + * mode case-sensitive breaking backward-compat + */ + var $useFetchArray = false; + var $_bindInputArray = true; + var $_genIDSQL = "VALUES NEXTVAL FOR %s"; + + /** + * SQL to create a new sequence + * + * @var string + */ + var $_genSeqSQL = " + CREATE SEQUENCE %s START WITH %s + NO MAXVALUE NO CYCLE INCREMENT BY 1 NO CACHE + "; - /** - * SQL to drop a sequence - * - * @var string - */ - var $_dropSeqSQL = "DROP SEQUENCE %s"; + /** + * SQL to drop a sequence + * + * @var string + */ + var $_dropSeqSQL = "DROP SEQUENCE %s"; - var $_autocommit = true; - var $_lastAffectedRows = 0; - var $hasInsertID = true; - var $hasGenID = true; + var $_autocommit = true; + var $_lastAffectedRows = 0; + var $hasInsertID = true; + var $hasGenID = true; + /* + * Character used to wrap column and table names for escaping special + * characters in column and table names as well as forcing upper and + * lower case + */ + public $nameQuote = '"'; - /* - * Character used to wrap column and table names for escaping special - * characters in column and table names as well as forcing upper and - * lower case - */ - public $nameQuote = '"'; + /* + * Holds information about the stored procedure request + * currently being built + */ + private $storedProcedureParameters = false; - /* - * Holds information about the stored procedure request - * currently being built - */ - private $storedProcedureParameters = false; + /** + * Supports older DB2 versions without record counts + * + * @var boolean + */ + public bool $useLegacyRecordCounting = false; - function __construct() {} + /** + * Supports older DB2 versions without 'OFFSET' support + * + * @var boolean + */ + public bool $useLegacyRowOffsets = false; - protected function _insertID($table = '', $column = '') - { - return ADOConnection::GetOne('VALUES IDENTITY_VAL_LOCAL()'); - } + function __construct() {} - public function _connect($argDSN, $argUsername, $argPassword, $argDatabasename) - { - return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename); - } + protected function _insertID($table = '', $column = '') + { + return ADOConnection::GetOne('VALUES IDENTITY_VAL_LOCAL()'); + } - public function _pconnect($argDSN, $argUsername, $argPassword, $argDatabasename) - { - return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename,true); - } + public function _connect($argDSN, $argUsername, $argPassword, $argDatabasename) + { + return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename); + } - private function doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename, $persistent=false) - { + public function _pconnect($argDSN, $argUsername, $argPassword, $argDatabasename) + { + return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename,true); + } - if (!function_exists('db2_connect')) { - ADOConnection::outp("DB2 extension not installed."); - return null; - } + private function doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename, $persistent=false) + { - $connectionParameters = $this->unpackParameters( - $argDSN, - $argUsername, - $argPassword, - $argDatabasename - ); + if (!function_exists('db2_connect')) { + ADOConnection::outp("DB2 extension not installed."); + return null; + } - if ($connectionParameters == null) - { - /* - * Error thrown - */ - return null; - } + $connectionParameters = $this->unpackParameters( + $argDSN, + $argUsername, + $argPassword, + $argDatabasename + ); - $argDSN = $connectionParameters['dsn']; - $argUsername = $connectionParameters['uid']; - $argPassword = $connectionParameters['pwd']; - $argDatabasename = $connectionParameters['database']; - $useCataloguedConnection = $connectionParameters['catalogue']; + if ($connectionParameters == null) + { + /* + * Error thrown + */ + return null; + } - if ($this->debug){ - if (strcmp($argDSN,'*LOCAL') == 0) - { - $connectMessage = '*LOCAL connection'; - } - else if ($useCataloguedConnection) - { - $connectMessage = "Catalogued connection using parameters: "; - $connectMessage .= "DB=$argDatabasename / "; - $connectMessage .= "UID=$argUsername / "; - $connectMessage .= "PWD=$argPassword"; - } - else - { - $connectMessage = "Uncatalogued connection using DSN: $argDSN"; - } - ADOConnection::outp($connectMessage); - } + $argDSN = $connectionParameters['dsn']; + $argUsername = $connectionParameters['uid']; + $argPassword = $connectionParameters['pwd']; + $argDatabasename = $connectionParameters['database']; + $useCataloguedConnection = $connectionParameters['catalogue']; - /* - * This needs to be set before the connect(). - */ - ini_set('ibm_db2.binmode', $this->binmode); + if ($this->debug){ + if (strcmp($argDSN,'*LOCAL') == 0) + { + $connectMessage = '*LOCAL connection'; + } + else if ($useCataloguedConnection) + { + $connectMessage = "Catalogued connection using parameters: "; + $connectMessage .= "DB=$argDatabasename / "; + $connectMessage .= "UID=$argUsername / "; + $connectMessage .= "PWD=$argPassword"; + } + else + { + $connectMessage = "Uncatalogued connection using DSN: $argDSN"; + } + ADOConnection::outp($connectMessage); + } - if ($persistent) - $db2Function = 'db2_pconnect'; - else - $db2Function = 'db2_connect'; + /* + * This needs to be set before the connect(). + */ + ini_set('ibm_db2.binmode', $this->binmode); - /* - * We need to flatten out the connectionParameters - */ + if ($persistent) + $db2Function = 'db2_pconnect'; + else + $db2Function = 'db2_connect'; - $db2Options = array(); - if ($this->connectionParameters) - { - foreach($this->connectionParameters as $p) - foreach($p as $k=>$v) - $db2Options[$k] = $v; - } + /* + * We need to flatten out the connectionParameters + */ - if ($useCataloguedConnection) - { - $this->_connectionID = $db2Function($argDatabasename, - $argUsername, - $argPassword, - $db2Options); - } - else + $db2Options = array(); + if ($this->connectionParameters) + { + foreach($this->connectionParameters as $p) + foreach($p as $k=>$v) + $db2Options[$k] = $v; + } - $this->_connectionID = $db2Function($argDSN, - '', - '', - $db2Options); + if ($useCataloguedConnection) + { + $this->_connectionID = $db2Function($argDatabasename, + $argUsername, + $argPassword, + $db2Options); + } + else + $this->_connectionID = $db2Function($argDSN, + '', + '', + $db2Options); - $this->_errorMsg = @db2_conn_errormsg(); - if ($this->_connectionID && $this->connectStmt) - $this->execute($this->connectStmt); + $this->_errorMsg = @db2_conn_errormsg(); - if ($this->_connectionID && $argDatabasename) - $this->execute("SET SCHEMA=$argDatabasename"); + if ($this->_connectionID && $this->connectStmt) + $this->execute($this->connectStmt); - return $this->_connectionID != false; + if ($this->_connectionID && $argDatabasename) + $this->execute("SET SCHEMA=$argDatabasename"); - } + return $this->_connectionID != false; - /** - * Validates and preprocesses the passed parameters for consistency - * - * @param string $argDSN Either DSN or database - * @param string $argUsername User name or null - * @param string $argPassword Password or null - * @param string $argDatabasename Either DSN or database - * - * @return mixed array if correct, null if not - */ - private function unpackParameters($argDSN, $argUsername, $argPassword, $argDatabasename) - { + } + /** + * Validates and preprocesses the passed parameters for consistency + * + * @param string $argDSN Either DSN or database + * @param string $argUsername User name or null + * @param string $argPassword Password or null + * @param string $argDatabasename Either DSN or database + * + * @return mixed array if correct, null if not + */ + private function unpackParameters($argDSN, $argUsername, $argPassword, $argDatabasename) + { - $connectionParameters = array( - 'dsn'=>'', - 'uid'=>'', - 'pwd'=>'', - 'database'=>'', - 'catalogue'=>true - ); - /* - * Shortcut for *LOCAL - */ - if (strcmp($argDSN,'*LOCAL') == 0) - { - $connectionParameters['dsn'] = $argDSN; - $connectionParameters['database'] = $argDatabasename; - $connectionParameters['catalogue'] = false; + $connectionParameters = array( + 'dsn'=>'', + 'uid'=>'', + 'pwd'=>'', + 'database'=>'', + 'catalogue'=>true + ); - return $connectionParameters; - } + /* + * Shortcut for *LOCAL + */ + if (strcmp($argDSN,'*LOCAL') == 0) + { + $connectionParameters['dsn'] = $argDSN; + $connectionParameters['database'] = $argDatabasename; + $connectionParameters['catalogue'] = false; - /* - * Uou can either connect to a catalogued connection - * with a database name e.g. 'SAMPLE' - * or an uncatalogued connection with a DSN like connection - * DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password; - */ + return $connectionParameters; + } - if (!$argDSN && !$argDatabasename) - { - $errorMessage = 'Supply either catalogued or uncatalogued connection parameters'; - $this->_errorMsg = $errorMessage; - if ($this->debug) - ADOConnection::outp($errorMessage); - return null; - } + /* + * Uou can either connect to a catalogued connection + * with a database name e.g. 'SAMPLE' + * or an uncatalogued connection with a DSN like connection + * DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password; + */ - $useCataloguedConnection = true; - $schemaName = ''; + if (!$argDSN && !$argDatabasename) + { + $errorMessage = 'Supply either catalogued or uncatalogued connection parameters'; + $this->_errorMsg = $errorMessage; + if ($this->debug) + ADOConnection::outp($errorMessage); + return null; + } - if ($argDSN && $argDatabasename) - { - /* - * If a catalogued connection if provided, - * as well as user and password - * that will take priority - */ - if ($argUsername && $argPassword && !$this->isDsn($argDatabasename)) - { - if ($this->debug){ - $errorMessage = 'Warning: Because you provided user,'; - $errorMessage.= 'password and database, DSN connection '; - $errorMessage.= 'parameters were discarded'; - ADOConnection::outp($errorMessage); + $useCataloguedConnection = true; + $schemaName = ''; - } - $argDSN = ''; - } - else if ($this->isDsn($argDSN) && $this->isDsn($argDatabasename)) - { - $errorMessage = 'Supply uncatalogued connection parameters '; - $errorMessage.= 'in either the database or DSN arguments, '; - $errorMessage.= 'but not both'; + if ($argDSN && $argDatabasename) + { + /* + * If a catalogued connection if provided, + * as well as user and password + * that will take priority + */ + if ($argUsername && $argPassword && !$this->isDsn($argDatabasename)) + { + if ($this->debug){ + $errorMessage = 'Warning: Because you provided user,'; + $errorMessage.= 'password and database, DSN connection '; + $errorMessage.= 'parameters were discarded'; + ADOConnection::outp($errorMessage); - if ($this->debug) - ADOConnection::outp($errorMessage); - return null; - } - } + } + $argDSN = ''; + } + else if ($this->isDsn($argDSN) && $this->isDsn($argDatabasename)) + { + $errorMessage = 'Supply uncatalogued connection parameters '; + $errorMessage.= 'in either the database or DSN arguments, '; + $errorMessage.= 'but not both'; - if (!$this->isDsn($argDSN) && $this->isDsn($argDatabasename)) - { - /* - * Switch them around for next test - */ - $temp = $argDSN; - $argDsn = $argDatabasename; - $argDatabasenME = $temp; - } + if ($this->debug) + ADOConnection::outp($errorMessage); + return null; + } + } - if ($this->isDsn($argDSN)) - { + if (!$this->isDsn($argDSN) && $this->isDsn($argDatabasename)) + { + /* + * Switch them around for next test + */ + $temp = $argDSN; + $argDsn = $argDatabasename; + $argDatabasenME = $temp; + } - if (!preg_match('/uid=/i',$argDSN) - || !preg_match('/pwd=/i',$argDSN)) - { - $errorMessage = 'For uncatalogued connections, provide '; - $errorMessage.= 'both UID and PWD in the connection string'; + if ($this->isDsn($argDSN)) + { - if ($this->debug) - ADOConnection::outp($errorMessage); - return null; - } + if (!preg_match('/uid=/i',$argDSN) + || !preg_match('/pwd=/i',$argDSN)) + { + $errorMessage = 'For uncatalogued connections, provide '; + $errorMessage.= 'both UID and PWD in the connection string'; - if (preg_match('/database=/i',$argDSN)) - { - if ($argDatabasename) - { - $argDatabasename = ''; - if ($this->debug) - { - $errorMessage = 'Warning: Because you provided '; - $errorMessage.= 'database information in the DSN '; - $errorMessage.= 'parameters, the supplied database '; - $errorMessage.= 'name was discarded'; - ADOConnection::outp($errorMessage); - } - } - $useCataloguedConnection = false; + if ($this->debug) + ADOConnection::outp($errorMessage); + return null; + } - } - elseif ($argDatabasename) - { - $this->database = $argDatabasename; - $argDSN .= ';database=' . $argDatabasename; - $argDatabasename = ''; - $useCataloguedConnection = false; + if (preg_match('/database=/i',$argDSN)) + { + if ($argDatabasename) + { + $argDatabasename = ''; + if ($this->debug) + { + $errorMessage = 'Warning: Because you provided '; + $errorMessage.= 'database information in the DSN '; + $errorMessage.= 'parameters, the supplied database '; + $errorMessage.= 'name was discarded'; + ADOConnection::outp($errorMessage); + } + } + $useCataloguedConnection = false; - } - else - { - $errorMessage = 'Uncatalogued connection parameters '; - $errorMessage.= 'must contain a database= argument'; + } + elseif ($argDatabasename) + { + $this->database = $argDatabasename; + $argDSN .= ';database=' . $argDatabasename; + $argDatabasename = ''; + $useCataloguedConnection = false; - if ($this->debug) - ADOConnection::outp($errorMessage); - return null; - } - } + } + else + { + $errorMessage = 'Uncatalogued connection parameters '; + $errorMessage.= 'must contain a database= argument'; - if ($argDSN && !$argDatabasename && $useCataloguedConnection) - { - $argDatabasename = $argDSN; - $argDSN = ''; - } + if ($this->debug) + ADOConnection::outp($errorMessage); + return null; + } + } + if ($argDSN && !$argDatabasename && $useCataloguedConnection) + { + $argDatabasename = $argDSN; + $argDSN = ''; + } - if ($useCataloguedConnection - && (!$argDatabasename - || !$argUsername - || !$argPassword)) - { - $errorMessage = 'For catalogued connections, provide '; - $errorMessage.= 'database, username and password'; - $this->_errorMsg = $errorMessage; - if ($this->debug) - ADOConnection::outp($errorMessage); - return null; + if ($useCataloguedConnection + && (!$argDatabasename + || !$argUsername + || !$argPassword)) + { - } + $errorMessage = 'For catalogued connections, provide '; + $errorMessage.= 'database, username and password'; + $this->_errorMsg = $errorMessage; + if ($this->debug) + ADOConnection::outp($errorMessage); + return null; - if ($argDatabasename) - $this->database = $argDatabasename; - elseif (!$this->database) - $this->database = $this->getDatabasenameFromDsn($argDSN); + } + if ($argDatabasename) + $this->database = $argDatabasename; + elseif (!$this->database) + $this->database = $this->getDatabasenameFromDsn($argDSN); - $connectionParameters = array('dsn'=>$argDSN, - 'uid'=>$argUsername, - 'pwd'=>$argPassword, - 'database'=>$argDatabasename, - 'catalogue'=>$useCataloguedConnection - ); - return $connectionParameters; + $connectionParameters = array('dsn'=>$argDSN, + 'uid'=>$argUsername, + 'pwd'=>$argPassword, + 'database'=>$argDatabasename, + 'catalogue'=>$useCataloguedConnection + ); - } + return $connectionParameters; - /** - * Does the provided string look like a DSN - * - * @param string $dsnString - * - * @return bool - */ - private function isDsn($dsnString){ - $dsnArray = preg_split('/[;=]+/',$dsnString); - if (count($dsnArray) > 2) - return true; - return false; - } + } + /** + * Does the provided string look like a DSN + * + * @param string $dsnString + * + * @return bool + */ + private function isDsn($dsnString){ + $dsnArray = preg_split('/[;=]+/',$dsnString); + if (count($dsnArray) > 2) + return true; + return false; + } - /** - * Gets the database name from the DSN - * - * @param string $dsnString - * - * @return string - */ - private function getDatabasenameFromDsn($dsnString){ - $dsnArray = preg_split('/[;=]+/',$dsnString); - $dbIndex = array_search('database',$dsnArray); + /** + * Gets the database name from the DSN + * + * @param string $dsnString + * + * @return string + */ + private function getDatabasenameFromDsn($dsnString){ - return $dsnArray[$dbIndex + 1]; - } + $dsnArray = preg_split('/[;=]+/',$dsnString); + $dbIndex = array_search('database',$dsnArray); + return $dsnArray[$dbIndex + 1]; + } - /** - * format and return date string in database timestamp format - * - * @param mixed $ts either a string or a unixtime - * @param bool $isField discarded - * - * @return string - */ - function dbTimeStamp($ts,$isField=false) - { - if (empty($ts) && $ts !== 0) return 'null'; - if (is_string($ts)) $ts = ADORecordSet::unixTimeStamp($ts); - return 'TO_DATE('.date($this->fmtTimeStamp,$ts).",'YYYY-MM-DD HH24:MI:SS')"; - } - /** - * Format date column in sql string given an input format that understands Y M D - * - * @param string $fmt The format string e.g. 'm/d/Y' - * @param string $col optionally either a column name,an ISO date string or a - * ate geerating DB2 function like CURRENT DATE - * - * @return string - */ - public function sqlDate($fmt, $col=false) - { - $usesSysDate = false; - $trailer = ''; - $dCol = ''; - $tCol = ''; - if (!$col) { - /* - * If no column is provided, we use the system date or time - */ - - $dCol = $this->sysDate; - $tCol = $this->sysTime; - - /* - * Anything not from a column needs this - */ - $trailer = 'FROM SYSIBM.SYSDUMMY1'; - - } else if (preg_match('/(\d{4})-(\d{2})-(\d{2})/',$col)) { - /* - * If the column is an ISO date string, we use it as is - */ - $trailer = 'FROM SYSIBM.SYSDUMMY1'; - if (substr($col,0,1) != "'") { - $col = $this->qstr($col); - } - } + /** + * format and return date string in database timestamp format + * + * @param mixed $ts either a string or a unixtime + * @param bool $isField discarded + * + * @return string + */ + function dbTimeStamp($ts,$isField=false) + { + if (empty($ts) && $ts !== 0) return 'null'; + if (is_string($ts)) $ts = ADORecordSet::unixTimeStamp($ts); + return 'TO_DATE('.date($this->fmtTimeStamp,$ts).",'YYYY-MM-DD HH24:MI:SS')"; + } - /* use TO_CHAR() if $fmt is TO_CHAR() allowed fmt */ - if ($fmt== 'Y-m-d H:i:s') - return 'TO_CHAR('.$col.", 'YYYY-MM-DD HH24:MI:SS')"; + /** + * Format date column in sql string given an input format that understands Y M D + * + * @param string $fmt The format string e.g. 'm/d/Y' + * @param string $col optionally either a column name,an ISO date string or a + * ate geerating DB2 function like CURRENT DATE + * + * @return string + */ + public function sqlDate($fmt, $col=false) + { + $usesSysDate = false; + $trailer = ''; + $dCol = ''; + $tCol = ''; + if (!$col) { + /* + * If no column is provided, we use the system date or time + */ + + $dCol = $this->sysDate; + $tCol = $this->sysTime; + + /* + * Anything not from a column needs this + */ + $trailer = 'FROM SYSIBM.SYSDUMMY1'; + + } else if (preg_match('/(\d{4})-(\d{2})-(\d{2})/',$col)) { + /* + * If the column is an ISO date string, we use it as is + */ + $trailer = 'FROM SYSIBM.SYSDUMMY1'; + if (substr($col,0,1) != "'") { + $col = $this->qstr($col); + } + } - $s = ''; + /* use TO_CHAR() if $fmt is TO_CHAR() allowed fmt */ + if ($fmt== 'Y-m-d H:i:s') + return 'TO_CHAR('.$col.", 'YYYY-MM-DD HH24:MI:SS')"; - $len = strlen($fmt); - for ($i=0; $i < $len; $i++) { - if ($s) { - $s .= $this->concat_operator; - } - - $ch = $fmt[$i]; - - switch($ch) { - case 'Y': - case 'y': - - if ($len==1) { - return "YEAR($col$dCol) $trailer"; - } - - /* - * the CHAR creates a CHAR(20) so trim it - */ - $s .= "RTRIM(CHAR(YEAR($col$dCol)))"; - - break; + $s = ''; - case 'M': - - /* - * Returns "January" we want "Jan" - */ - $cmd = "SUBSTR(MONTHNAME($col$dCol),1,3)"; - - if ($len==1) { - return "$cmd $trailer"; - } - $s .= $cmd; - - break; + $len = strlen($fmt); + for ($i=0; $i < $len; $i++) { + if ($s) { + $s .= $this->concat_operator; + } + + $ch = $fmt[$i]; + + switch($ch) { + case 'Y': + case 'y': + + if ($len==1) { + return "YEAR($col$dCol) $trailer"; + } + + /* + * the CHAR creates a CHAR(20) so trim it + */ + $s .= "RTRIM(CHAR(YEAR($col$dCol)))"; + + break; - case 'm': - - $cmd = "RIGHT(DIGITS(MONTH($col$dCol)),2)"; - if ($len==1) { - return "$cmd $trailer"; - } + case 'M': + + /* + * Returns "January" we want "Jan" + */ + $cmd = "SUBSTR(MONTHNAME($col$dCol),1,3)"; + + if ($len==1) { + return "$cmd $trailer"; + } + $s .= $cmd; + + break; - $s .= $cmd; - break; + case 'm': + + $cmd = "RIGHT(DIGITS(MONTH($col$dCol)),2)"; + if ($len==1) { + return "$cmd $trailer"; + } - case 'D': - case 'd': + $s .= $cmd; + break; - $cmd = "RIGHT(DIGITS(DAY($col$dCol)),2)"; + case 'D': + case 'd': - if ($len==1) { - return "$cmd $trailer"; - } + $cmd = "RIGHT(DIGITS(DAY($col$dCol)),2)"; - $s .= $cmd; - break; + if ($len==1) { + return "$cmd $trailer"; + } - case 'H': - case 'h': - - $cmd = "RIGHT(DIGITS(HOUR($col$tCol)),2)"; - if ($len==1) { - return "$cmd $trailer"; - } - - $s .= $cmd; - - break; + $s .= $cmd; + break; - case 'i': - case 'I': + case 'H': + case 'h': + + $cmd = "RIGHT(DIGITS(HOUR($col$tCol)),2)"; + if ($len==1) { + return "$cmd $trailer"; + } + + $s .= $cmd; + + break; - $cmd = "RIGHT(DIGITS(MINUTE($col$tCol)),2)"; - if ($len==1) { - return "$cmd $trailer"; - } - - $s .= $cmd; - - break; - - case 'S': - case 's': - $cmd = "RIGHT(DIGITS(SECOND($col$tCol)),2)"; - if ($len==1) { - return "$cmd $trailer"; - } - - $s .= $cmd; - break; + case 'i': + case 'I': - default: - - if ($ch == '\\') { - $i++; - $ch = substr($fmt,$i,1); - } - - $s .= $this->qstr($ch); - } - } + $cmd = "RIGHT(DIGITS(MINUTE($col$tCol)),2)"; + if ($len==1) { + return "$cmd $trailer"; + } + + $s .= $cmd; + + break; + + case 'S': + case 's': + $cmd = "RIGHT(DIGITS(SECOND($col$tCol)),2)"; + if ($len==1) { + return "$cmd $trailer"; + } + + $s .= $cmd; + break; - $s .= " $trailer"; + default: + + if ($ch == '\\') { + $i++; + $ch = substr($fmt, $i, 1); + } + + $s .= $this->qstr($ch); + } + } - return $s; - } + $s .= " $trailer"; - /** - * Return information about the database server - * - * @return array - */ - public function serverInfo() - { - global $ADODB_FETCH_MODE; - $savem = $ADODB_FETCH_MODE; - - $this->setFetchMode(ADODB_FETCH_NUM); + return $s; + } - $sql = "SELECT service_level, fixpack_num - FROM TABLE(sysproc.env_get_inst_info()) - AS INSTANCEINFO"; - $row = $this->GetRow($sql); + /** + * Return information about the database server + * + * @return array + */ + public function serverInfo() + { + global $ADODB_FETCH_MODE; + $savem = $ADODB_FETCH_MODE; + + $this->setFetchMode(ADODB_FETCH_NUM); - $this->setFetchMode($savem); + $sql = "SELECT service_level, fixpack_num + FROM TABLE(sysproc.env_get_inst_info()) + AS INSTANCEINFO"; + $row = $this->GetRow($sql); - if ($row) { - $info['version'] = $row[0].':'.$row[1]; - $info['fixpack'] = $row[1]; - $info['description'] = ''; - } else { - return ADOConnection::serverInfo(); - } + $this->setFetchMode($savem); - return $info; - } + if ($row) { + $info['version'] = $row[0].':'.$row[1]; + $info['fixpack'] = $row[1]; + $info['description'] = ''; + } else { + return ADOConnection::serverInfo(); + } - - function selectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false,$secs2cache=0) - { - $nrows = (integer) $nrows; + return $info; + } - if ($offset <= 0) - { - if ($nrows >= 0) - $sql .= " FETCH FIRST $nrows ROWS ONLY "; + /** + * Select a limited number of rows. + * + * @param string $sql The SQL to execute + * @param int $nrows Number of rows to get + * @param int $offset Row to start calculations from (1-based) + * @param array|bool $inputArr Array of bind variables + * @param int $secs2cache Private parameter only used by jlim + * + * @return ADORecordSet The recordset ($rs->databaseType == 'array') + */ + function selectLimit($sql, $nrows=-1, $offset=-1, $inputArr=false, $secs2cache=0) + { + $nrows = (integer) $nrows; - $rs = $this->execute($sql,$inputArr); + if ($this->useLegacyRowOffsets) { + /* + * Disables support for OFFSET + */ + if ($offset <= 0) { + if ($nrows >= 0) + $sql .= " FETCH FIRST $nrows ROWS ONLY "; - } - else - { - if ($offset > 0 && $nrows < 0); + $rs = $this->execute($sql, $inputArr); - else - { - $nrows += $offset; - $sql .= " FETCH FIRST $nrows ROWS ONLY "; - } + } else { + + if ($offset > 0 && $nrows < 0) { - /* - * DB2 has no native support for mid table offset - */ - $rs = ADOConnection::selectLimit($sql,$nrows,$offset,$inputArr); + } else { + $nrows += $offset; + $sql .= " FETCH FIRST $nrows ROWS ONLY "; + } - } + /* + * Disabled DB2 support for mid table offset + */ + $rs = ADOConnection::selectLimit($sql, $nrows, $offset, $inputArr); - return $rs; - } + } + } else { + $rowString = ''; + $offsetString = ''; - function errorMsg() - { - if ($this->_errorMsg !== false) - return $this->_errorMsg; + if ($nrows > 0) { + $rowString = sprintf('FETCH FIRST %d ROWS ONLY', $nrows); + } - if (empty($this->_connectionID)) - return @db2_conn_errormsg(); + if ($offset > 0) { + $offsetString = sprintf('OFFSET %d ROWS', $offset); + } - return @db2_conn_errormsg($this->_connectionID); - } + $sql = sprintf('%s %s %s', $sql, $offsetString, $rowString); + if ($secs2cache) { + $rs = $this->cacheExecute($secs2cache, $sql, $inputArr); + } else { + $rs = $this->execute($sql, $inputArr); + } + } - function errorNo() - { + return $rs; + } - if ($this->_errorCode !== false) - return $this->_errorCode; + function errorMsg() + { + if ($this->_errorMsg !== false) + return $this->_errorMsg; - if (empty($this->_connectionID)) - $e = @db2_conn_error(); + if (empty($this->_connectionID)) + return @db2_conn_errormsg(); - else - $e = @db2_conn_error($this->_connectionID); + return @db2_conn_errormsg($this->_connectionID); + } - return $e; - } + function errorNo() + { + if ($this->_errorCode !== false) + return $this->_errorCode; - function beginTrans() - { - if (!$this->hasTransactions) - return false; - if ($this->transOff) - return true; + if (empty($this->_connectionID)) + $e = @db2_conn_error(); - $this->transCnt += 1; + else + $e = @db2_conn_error($this->_connectionID); - $this->_autocommit = false; + return $e; + } - return db2_autocommit($this->_connectionID,false); - } - function CommitTrans($ok=true) - { - if ($this->transOff) - return true; - if (!$ok) - return $this->RollbackTrans(); + function beginTrans() + { + if (!$this->hasTransactions) + return false; + if ($this->transOff) + return true; - if ($this->transCnt) - $this->transCnt -= 1; + $this->transCnt += 1; - $this->_autocommit = true; - $ret = @db2_commit($this->_connectionID); - @db2_autocommit($this->_connectionID,true); - return $ret; - } + $this->_autocommit = false; - function RollbackTrans() - { - if ($this->transOff) return true; - if ($this->transCnt) $this->transCnt -= 1; - $this->_autocommit = true; - $ret = @db2_rollback($this->_connectionID); - @db2_autocommit($this->_connectionID,true); - return $ret; - } + return db2_autocommit($this->_connectionID,false); + } - /** - * Return a list of Primary Keys for a specified table - * - * We don't use db2_statistics as the function does not seem to play - * well with mixed case table names - * - * @param string $table - * @param bool $primary (optional) only return primary keys - * @param bool $owner (optional) not used in this driver - * - * @return string[] Array of indexes - */ - public function metaPrimaryKeys($table,$owner=false) - { + function CommitTrans($ok=true) + { + if ($this->transOff) + return true; - $primaryKeys = array(); + if (!$ok) + return $this->RollbackTrans(); - global $ADODB_FETCH_MODE; + if ($this->transCnt) + $this->transCnt -= 1; - $schema = ''; - $this->_findschema($table,$schema); + $this->_autocommit = true; + $ret = @db2_commit($this->_connectionID); + @db2_autocommit($this->_connectionID,true); + return $ret; + } - $table = $this->metaTables('T','',$table); - if ($table == false) - return false; + function RollbackTrans() + { + if ($this->transOff) return true; + if ($this->transCnt) $this->transCnt -= 1; + $this->_autocommit = true; + $ret = @db2_rollback($this->_connectionID); + @db2_autocommit($this->_connectionID,true); + return $ret; + } - $table = $table[0]; + /** + * Return a list of Primary Keys for a specified table + * + * We don't use db2_statistics as the function does not seem to play + * well with mixed case table names + * + * @param string $table + * @param bool $primary (optional) only return primary keys + * @param bool $owner (optional) not used in this driver + * + * @return string[] Array of indexes + */ + public function metaPrimaryKeys($table,$owner=false) + { - $savem = $ADODB_FETCH_MODE; - $ADODB_FETCH_MODE = ADODB_FETCH_NUM; - $this->setFetchMode(ADODB_FETCH_NUM); + $primaryKeys = array(); + global $ADODB_FETCH_MODE; - $sql = "SELECT * - FROM SYSCAT.INDEXES - WHERE TABNAME='$table'"; + $schema = ''; + $this->_findschema($table,$schema); - $rows = $this->getAll($sql); + $table = $this->metaTables('T','',$table); + if ($table == false) + return false; - $this->setFetchMode($savem); - $ADODB_FETCH_MODE = $savem; + $table = $table[0]; - if (empty($rows)) - return false; + $savem = $ADODB_FETCH_MODE; + $ADODB_FETCH_MODE = ADODB_FETCH_NUM; + $this->setFetchMode(ADODB_FETCH_NUM); - foreach ($rows as $r) - { - if ($r[7] != 'P') - continue; - $cols = explode('+',$r[6]); - foreach ($cols as $colIndex=>$col) - { - if ($colIndex == 0) - continue; - $columnName = $this->getMetaCasedValue($col); - $primaryKeys[] = $columnName; - } - break; - } - return $primaryKeys; - } + $sql = "SELECT * + FROM SYSCAT.INDEXES + WHERE TABNAME='$table'"; - /** - * Returns a list of Foreign Keys associated with a specific table. - * - * @param string $table - * @param string $owner discarded - * @param bool $upper discarded - * @param bool $associative discarded - * - * @return string[]|false An array where keys are tables, and values are foreign keys; - * false if no foreign keys could be found. - */ - public function metaForeignKeys($table, $owner = '', $upper = false, $associative = false) - { + $rows = $this->getAll($sql); - global $ADODB_FETCH_MODE; + $this->setFetchMode($savem); + $ADODB_FETCH_MODE = $savem; - $schema = ''; - $this->_findschema($table,$schema); + if (empty($rows)) + return false; - $metaTables = $this->metaTables('T','',$table); + foreach ($rows as $r) + { + if ($r[7] != 'P') + continue; - if ($metaTables == false) { - return false; - } + $cols = explode('+',$r[6]); + foreach ($cols as $colIndex=>$col) + { + if ($colIndex == 0) + continue; + $columnName = $this->getMetaCasedValue($col); + $primaryKeys[] = $columnName; + } + break; + } + return $primaryKeys; + } - $table = $metaTables[0]; + /** + * Returns a list of Foreign Keys associated with a specific table. + * + * @param string $table + * @param string $owner discarded + * @param bool $upper discarded + * @param bool $associative discarded + * + * @return string[]|false An array where keys are tables, and values are foreign keys; + * false if no foreign keys could be found. + */ + public function metaForeignKeys($table, $owner = '', $upper = false, $associative = false) + { - $baseFetchMode = $ADODB_FETCH_MODE; - - $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; - - $this->setFetchMode(ADODB_FETCH_ASSOC); - $sql = "SELECT * - FROM syscat.references - WHERE tabname = '$table'"; + global $ADODB_FETCH_MODE; - $results = $this->getAll($sql); + $schema = ''; + $this->_findschema($table,$schema); - $this->setFetchMode($baseFetchMode); - - if (empty($results)) - return false; + $metaTables = $this->metaTables('T','',$table); - $foreignKeys = array(); + if ($metaTables == false) { + return false; + } - foreach ($results as $r) - { + $table = $metaTables[0]; - /* - + $baseFetchMode = $ADODB_FETCH_MODE; + + $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; + + $this->setFetchMode(ADODB_FETCH_ASSOC); + $sql = "SELECT * + FROM syscat.references + WHERE tabname = '$table'"; + + $results = $this->getAll($sql); + + $this->setFetchMode($baseFetchMode); + + if (empty($results)) + return false; + + $foreignKeys = array(); + + foreach ($results as $r) + { + + /* + [CONSTNAME] => SQL250829011849680 [TABSCHEMA] => DB2INST1 [TABNAME] => TESTTABLE_2 @@ -855,307 +899,307 @@ class ADODB_db2 extends ADOConnection { [PK_COLNAMES] => ID [DEFINER] => DB2INST1 ) - */ - $referenceTable = trim($r['REFTABNAME']); + */ + $referenceTable = trim($r['REFTABNAME']); - if (!array_key_exists($referenceTable,$foreignKeys)) { - $foreignKeys[$referenceTable] = array(); - } - $pkColnames = array_filter(preg_split('/ +/', $r['PK_COLNAMES'])); - $fkColnames = array_filter(preg_split('/ +/', $r['FK_COLNAMES'])); + if (!array_key_exists($referenceTable,$foreignKeys)) { + $foreignKeys[$referenceTable] = array(); + } + $pkColnames = array_filter(preg_split('/ +/', $r['PK_COLNAMES'])); + $fkColnames = array_filter(preg_split('/ +/', $r['FK_COLNAMES'])); - foreach ($pkColnames as $i=>$pkColname) { + foreach ($pkColnames as $i=>$pkColname) { - $pkColname = trim($pkColname); - $fkColname = trim($fkColnames[$i]); - if ($baseFetchMode == ADODB_FETCH_ASSOC) { - $foreignKeys[$referenceTable][$pkColname] = $fkColname; - } else { - $foreignKeys[$referenceTable][] = sprintf('%s=%s',$pkColname,$fkColname); - } - - } + $pkColname = trim($pkColname); + $fkColname = trim($fkColnames[$i]); + if ($baseFetchMode == ADODB_FETCH_ASSOC) { + $foreignKeys[$referenceTable][$pkColname] = $fkColname; + } else { + $foreignKeys[$referenceTable][] = sprintf('%s=%s',$pkColname,$fkColname); + } + + } - } - return $foreignKeys; - } + } + return $foreignKeys; + } - /** - * Returns a list of tables - * - * @param string $ttype (optional) - * @param string $schema (optional) - * @param string $mask (optional) - * - * @return array - */ - public function metaTables($ttype=false,$schema=false,$mask=false) - { - - global $ADODB_FETCH_MODE; + /** + * Returns a list of tables + * + * @param string $ttype (optional) + * @param string $schema (optional) + * @param string $mask (optional) + * + * @return array + */ + public function metaTables($ttype=false,$schema=false,$mask=false) + { + + global $ADODB_FETCH_MODE; - $savem = $ADODB_FETCH_MODE; - $this->SetFetchMode(ADODB_FETCH_ASSOC); - - /* - * Values for TABLE_TYPE - * --------------------------- - * ALIAS, HIERARCHY TABLE, INOPERATIVE VIEW, NICKNAME, - * MATERIALIZED QUERY TABLE, SYSTEM TABLE, TABLE, - * TYPED TABLE, TYPED VIEW, and VIEW - * - * If $ttype passed as '', match 'TABLE' and 'VIEW' - * If $ttype passed as 'T' it is assumed to be 'TABLE' - * if $ttype passed as 'V' it is assumed to be 'VIEW' - */ + $savem = $ADODB_FETCH_MODE; + $this->SetFetchMode(ADODB_FETCH_ASSOC); + + /* + * Values for TABLE_TYPE + * --------------------------- + * ALIAS, HIERARCHY TABLE, INOPERATIVE VIEW, NICKNAME, + * MATERIALIZED QUERY TABLE, SYSTEM TABLE, TABLE, + * TYPED TABLE, TYPED VIEW, and VIEW + * + * If $ttype passed as '', match 'TABLE' and 'VIEW' + * If $ttype passed as 'T' it is assumed to be 'TABLE' + * if $ttype passed as 'V' it is assumed to be 'VIEW' + */ - $sqlFilters = array(); - $sqlFilter = ''; - $returnedColumn = 'TRIM(TABNAME)'; + $sqlFilters = array(); + $sqlFilter = ''; + $returnedColumn = 'TRIM(TABNAME)'; - if ($ttype) { + if ($ttype) { - /* - * We only support 'T' for TABLE and 'V' for VIEW - * All types are returned if $ttype is passed as '' - */ - $ttype = strtoupper(substr($ttype,0,1)); - - if ($ttype == 'V') { - $sqlFilters[] = "TYPE='V'"; - } else if ($ttype == 'T') { - $sqlFilters[] = "TYPE='T'"; - } - } + /* + * We only support 'T' for TABLE and 'V' for VIEW + * All types are returned if $ttype is passed as '' + */ + $ttype = strtoupper(substr($ttype,0,1)); + + if ($ttype == 'V') { + $sqlFilters[] = "TYPE='V'"; + } else if ($ttype == 'T') { + $sqlFilters[] = "TYPE='T'"; + } + } - if ($schema && $schema != '%') { - - $schema = strtoupper($schema); - $returnedColumn = $this->concat('TRIM(TABSCHEMA)',"'.'",'TRIM(TABNAME)'); + if ($schema && $schema != '%') { + + $schema = strtoupper($schema); + $returnedColumn = $this->concat('TRIM(TABSCHEMA)',"'.'",'TRIM(TABNAME)'); - if (strpos($schema,'%') !== false) { - $sqlFilters[] = "UPPER(TABSCHEMA) LIKE '$schema'"; - } else { - $sqlFilters[] = "UPPER(TABSCHEMA)='" . strtoupper($schema) . "'"; - } - } + if (strpos($schema,'%') !== false) { + $sqlFilters[] = "UPPER(TABSCHEMA) LIKE '$schema'"; + } else { + $sqlFilters[] = "UPPER(TABSCHEMA)='" . strtoupper($schema) . "'"; + } + } - if ($mask) { - /* - * Mask is SQL LIKE format. We match on upper case - * unless the match is quoted in which case we match - * exactly - */ - - if (in_array(substr($mask,0,1),array('"','['))) - { - if (strpos($mask,'%') !== false) - { - $sqlFilters[] = "TABNAME LIKE '$mask'"; - } else { - $sqlFilters[] = "TABNAME='$mask'"; - } - } else { - $mask = strtoupper($mask); - if (strpos($mask,'%') !== false) { - $sqlFilters[] = "UPPER(TABNAME) LIKE '$mask'"; - } else { - $sqlFilters[] = "UPPER(TABNAME)='$mask'"; - } - } - } + if ($mask) { + /* + * Mask is SQL LIKE format. We match on upper case + * unless the match is quoted in which case we match + * exactly + */ + + if (in_array(substr($mask,0,1),array('"','['))) + { + if (strpos($mask,'%') !== false) + { + $sqlFilters[] = "TABNAME LIKE '$mask'"; + } else { + $sqlFilters[] = "TABNAME='$mask'"; + } + } else { + $mask = strtoupper($mask); + if (strpos($mask,'%') !== false) { + $sqlFilters[] = "UPPER(TABNAME) LIKE '$mask'"; + } else { + $sqlFilters[] = "UPPER(TABNAME)='$mask'"; + } + } + } - if (count($sqlFilters) > 0) { - $sqlFilter = 'WHERE ' . implode(' AND ',$sqlFilters); - } + if (count($sqlFilters) > 0) { + $sqlFilter = 'WHERE ' . implode(' AND ',$sqlFilters); + } - $sql = "SELECT $returnedColumn FROM SYSCAT.TABLES $sqlFilter ORDER BY TABSCHEMA, TABNAME"; + $sql = "SELECT $returnedColumn FROM SYSCAT.TABLES $sqlFilter ORDER BY TABSCHEMA, TABNAME"; - $metaTables = $this->getCol($sql); - - $this->SetFetchMode($savem); - - if (count($metaTables) == 0) - return false; + $metaTables = $this->getCol($sql); + + $this->SetFetchMode($savem); + + if (count($metaTables) == 0) + return false; - return $metaTables; - } + return $metaTables; + } - /** - * Return a list of indexes for a specified table - * - * We don't use db2_statistics as the function does not seem to play - * well with mixed case table names - * - * @param string $table - * @param bool $primary (optional) only return primary keys - * @param bool $owner (optional) not used in this driver - * - * @return string[] Array of indexes - */ - public function metaIndexes($table, $primary = false, $owner = false) { + /** + * Return a list of indexes for a specified table + * + * We don't use db2_statistics as the function does not seem to play + * well with mixed case table names + * + * @param string $table + * @param bool $primary (optional) only return primary keys + * @param bool $owner (optional) not used in this driver + * + * @return string[] Array of indexes + */ + public function metaIndexes($table, $primary = false, $owner = false) { - global $ADODB_FETCH_MODE; + global $ADODB_FETCH_MODE; - /* Array( - * [name_of_index] => Array( - * [unique] => true or false - * [columns] => Array( - * [0] => firstcol - * [1] => nextcol - * [2] => etc........ - * ) - * ) - * ) - */ - $indices = array(); - $primaryKeyName = ''; + /* Array( + * [name_of_index] => Array( + * [unique] => true or false + * [columns] => Array( + * [0] => firstcol + * [1] => nextcol + * [2] => etc........ + * ) + * ) + * ) + */ + $indices = array(); + $primaryKeyName = ''; - $table = $this->getTableCasedValue($table); + $table = $this->getTableCasedValue($table); - $savem = $ADODB_FETCH_MODE; - $ADODB_FETCH_MODE = ADODB_FETCH_NUM; - $this->setFetchMode(ADODB_FETCH_NUM); + $savem = $ADODB_FETCH_MODE; + $ADODB_FETCH_MODE = ADODB_FETCH_NUM; + $this->setFetchMode(ADODB_FETCH_NUM); - $sql = "SELECT * - FROM syscat.indexes - WHERE tabname='$table'"; + $sql = "SELECT * + FROM syscat.indexes + WHERE tabname='$table'"; - $rows = $this->getAll($sql); + $rows = $this->getAll($sql); - $this->setFetchMode($savem); - $ADODB_FETCH_MODE = $savem; + $this->setFetchMode($savem); + $ADODB_FETCH_MODE = $savem; - if (empty($rows)) - return false; + if (empty($rows)) + return false; - foreach ($rows as $r) - { + foreach ($rows as $r) + { - $primaryIndex = $r[7] == 'P'?1:0; - if (!$primary) - /* - * Primary key not requested, ignore that one - */ - if ($r[7] == 'P') - continue; + $primaryIndex = $r[7] == 'P'?1:0; + if (!$primary) + /* + * Primary key not requested, ignore that one + */ + if ($r[7] == 'P') + continue; - $indexName = $this->getMetaCasedValue($r[1]); - if (!isset($indices[$indexName])) - { - $unique = ($r[7] == 'U')?1:0; - $indices[$indexName] = array('unique'=>$unique, - 'primary'=>$primaryIndex, - 'columns'=>array() - ); - } - $cols = explode('+',$r[6]); - foreach ($cols as $colIndex=>$col) - { - if ($colIndex == 0) - continue; - $columnName = $this->getMetaCasedValue($col); - $indices[$indexName]['columns'][] = $columnName; - } + $indexName = $this->getMetaCasedValue($r[1]); + if (!isset($indices[$indexName])) + { + $unique = ($r[7] == 'U')?1:0; + $indices[$indexName] = array('unique'=>$unique, + 'primary'=>$primaryIndex, + 'columns'=>array() + ); + } + $cols = explode('+',$r[6]); + foreach ($cols as $colIndex=>$col) + { + if ($colIndex == 0) + continue; + $columnName = $this->getMetaCasedValue($col); + $indices[$indexName]['columns'][] = $columnName; + } - } + } - return $indices; + return $indices; - } + } - /** - * List procedures or functions in an array. - * - * We interrogate syscat.routines instead of calling the PHP - * function procedures because ADOdb requires the type of procedure - * this is not available in the php function - * - * @param string $procedureNamePattern (optional) - * @param string $catalog (optional) - * @param string $schemaPattern (optional) + /** + * List procedures or functions in an array. + * + * We interrogate syscat.routines instead of calling the PHP + * function procedures because ADOdb requires the type of procedure + * this is not available in the php function + * + * @param string $procedureNamePattern (optional) + * @param string $catalog (optional) + * @param string $schemaPattern (optional) - * @return array of procedures on current database. - * - */ - public function metaProcedures($procedureNamePattern = null, $catalog = null, $schemaPattern = null) { + * @return array of procedures on current database. + * + */ + public function metaProcedures($procedureNamePattern = null, $catalog = null, $schemaPattern = null) { - global $ADODB_FETCH_MODE; + global $ADODB_FETCH_MODE; - $metaProcedures = array(); - $procedureSQL = ''; - $catalogSQL = ''; - $schemaSQL = ''; + $metaProcedures = array(); + $procedureSQL = ''; + $catalogSQL = ''; + $schemaSQL = ''; - $savem = $ADODB_FETCH_MODE; - $ADODB_FETCH_MODE = ADODB_FETCH_NUM; + $savem = $ADODB_FETCH_MODE; + $ADODB_FETCH_MODE = ADODB_FETCH_NUM; - if ($procedureNamePattern) - $procedureSQL = "AND ROUTINENAME LIKE " . strtoupper($this->qstr($procedureNamePattern)); + if ($procedureNamePattern) + $procedureSQL = "AND ROUTINENAME LIKE " . strtoupper($this->qstr($procedureNamePattern)); - if ($catalog) - $catalogSQL = "AND OWNER=" . strtoupper($this->qstr($catalog)); + if ($catalog) + $catalogSQL = "AND OWNER=" . strtoupper($this->qstr($catalog)); - if ($schemaPattern) - $schemaSQL = "AND ROUTINESCHEMA LIKE {$this->qstr($schemaPattern)}"; + if ($schemaPattern) + $schemaSQL = "AND ROUTINESCHEMA LIKE {$this->qstr($schemaPattern)}"; - $fields = " - ROUTINENAME, - CASE ROUTINETYPE - WHEN 'P' THEN 'PROCEDURE' - WHEN 'F' THEN 'FUNCTION' - ELSE 'METHOD' - END AS ROUTINETYPE_NAME, - ROUTINESCHEMA, - REMARKS"; + $fields = " + ROUTINENAME, + CASE ROUTINETYPE + WHEN 'P' THEN 'PROCEDURE' + WHEN 'F' THEN 'FUNCTION' + ELSE 'METHOD' + END AS ROUTINETYPE_NAME, + ROUTINESCHEMA, + REMARKS"; - $SQL = "SELECT $fields - FROM syscat.routines - WHERE OWNER IS NOT NULL - $procedureSQL - $catalogSQL - $schemaSQL - ORDER BY ROUTINENAME - "; + $SQL = "SELECT $fields + FROM syscat.routines + WHERE OWNER IS NOT NULL + $procedureSQL + $catalogSQL + $schemaSQL + ORDER BY ROUTINENAME + "; - $result = $this->execute($SQL); + $result = $this->execute($SQL); - $ADODB_FETCH_MODE = $savem; + $ADODB_FETCH_MODE = $savem; - if (!$result) - return false; + if (!$result) + return false; - while ($r = $result->fetchRow()){ - $procedureName = $this->getMetaCasedValue($r[0]); - $schemaName = $this->getMetaCasedValue($r[2]); - $metaProcedures[$procedureName] = array('type'=> $r[1], - 'catalog' => '', - 'schema' => $schemaName, - 'remarks' => $r[3] - ); - } + while ($r = $result->fetchRow()){ + $procedureName = $this->getMetaCasedValue($r[0]); + $schemaName = $this->getMetaCasedValue($r[2]); + $metaProcedures[$procedureName] = array('type'=> $r[1], + 'catalog' => '', + 'schema' => $schemaName, + 'remarks' => $r[3] + ); + } - return $metaProcedures; + return $metaProcedures; - } + } - /** - * Lists databases. Because instances are independent, we only know about - * the current database name - * - * @return string[] - */ - public function metaDatabases(){ + /** + * Lists databases. Because instances are independent, we only know about + * the current database name + * + * @return string[] + */ + public function metaDatabases(){ - $dbName = $this->getMetaCasedValue($this->database); + $dbName = $this->getMetaCasedValue($this->database); - return (array)$dbName; + return (array)$dbName; - } + } @@ -1188,993 +1232,998 @@ See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2 #define SQL_UNICODE_VARCHAR (-96) #define SQL_UNICODE_LONGVARCHAR (-97) */ - function DB2Types($t) - { - switch ((integer)$t) { - case 1: - case 12: - case 0: - case -95: - case -96: - return 'C'; - case -97: - case -1: //text - return 'X'; - case -4: //image - return 'B'; + function DB2Types($t) + { + switch ((integer)$t) { + case 1: + case 12: + case 0: + case -95: + case -96: + return 'C'; + case -97: + case -1: //text + return 'X'; + case -4: //image + return 'B'; + + case 9: + case 91: + return 'D'; - case 9: - case 91: - return 'D'; + case 10: + case 11: + case 92: + case 93: + return 'T'; - case 10: - case 11: - case 92: - case 93: - return 'T'; + case 4: + case 5: + case -6: + return 'I'; - case 4: - case 5: - case -6: - return 'I'; + case -11: // uniqidentifier + return 'R'; + case -7: //bit + return 'L'; - case -11: // uniqidentifier - return 'R'; - case -7: //bit - return 'L'; + default: + return 'N'; + } + } - default: - return 'N'; - } - } + public function metaColumns($table, $ignored=true) + { + global $ADODB_FETCH_MODE; - public function metaColumns($table, $ignored=true) - { - global $ADODB_FETCH_MODE; + $schema = '%'; + $this->_findschema($table,$schema); - $schema = '%'; - $this->_findschema($table,$schema); + $findMatchingTable = $this->metaTables('T','',$table); + + if ($findMatchingTable === false) + return false; - $findMatchingTable = $this->metaTables('T','',$table); - - if ($findMatchingTable === false) - return false; + $table = $findMatchingTable[0]; - $table = $findMatchingTable[0]; + $savem = $ADODB_FETCH_MODE; + $this->setFetchMode(ADODB_FETCH_NUM); + $ADODB_FETCH_MODE = ADODB_FETCH_NUM; - $savem = $ADODB_FETCH_MODE; - $this->setFetchMode(ADODB_FETCH_NUM); - $ADODB_FETCH_MODE = ADODB_FETCH_NUM; + /* + * The returned table name has the correct casing + */ - /* - * The returned table name has the correct casing - */ + //$table = $this->getTableCasedValue($table); + $colname = "%"; + $qid = db2_columns($this->_connectionID, null, $schema, $table, $colname); + if (empty($qid)) + { + if ($this->debug) + { + $errorMessage = @db2_conn_errormsg($this->_connectionID); + ADOConnection::outp($errorMessage); + } + return false; + } - //$table = $this->getTableCasedValue($table); - $colname = "%"; - $qid = db2_columns($this->_connectionID, null, $schema, $table, $colname); - if (empty($qid)) - { - if ($this->debug) - { - $errorMessage = @db2_conn_errormsg($this->_connectionID); - ADOConnection::outp($errorMessage); - } - return false; - } + $rs = new ADORecordSet_db2($qid); - $rs = new ADORecordSet_db2($qid); + if (!$rs) + return false; - if (!$rs) - return false; + $rs->_fetch(); - $rs->_fetch(); + $retarr = array(); - $retarr = array(); + /* + $rs->fields indices + 0 TABLE_QUALIFIER + 1 TABLE_SCHEM + 2 TABLE_NAME + 3 COLUMN_NAME + 4 DATA_TYPE + 5 TYPE_NAME + 6 PRECISION + 7 LENGTH + 8 SCALE + 9 RADIX + 10 NULLABLE + 11 REMARKS + 12 Column Default + 13 SQL Data Type + 14 SQL DateTime SubType + 15 Max length in Octets + 16 Ordinal Position + 17 Is NULLABLE + */ + while (!$rs->EOF) + { - /* - $rs->fields indices - 0 TABLE_QUALIFIER - 1 TABLE_SCHEM - 2 TABLE_NAME - 3 COLUMN_NAME - 4 DATA_TYPE - 5 TYPE_NAME - 6 PRECISION - 7 LENGTH - 8 SCALE - 9 RADIX - 10 NULLABLE - 11 REMARKS - 12 Column Default - 13 SQL Data Type - 14 SQL DateTime SubType - 15 Max length in Octets - 16 Ordinal Position - 17 Is NULLABLE - */ - while (!$rs->EOF) - { + if ($rs->fields[2] == $table) + { - if ($rs->fields[2] == $table) - { + $fld = new ADOFieldObject(); + $fld->name = $rs->fields[3]; + $fld->type = $this->DB2Types($rs->fields[4]); - $fld = new ADOFieldObject(); - $fld->name = $rs->fields[3]; - $fld->type = $this->DB2Types($rs->fields[4]); + // ref: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_odk.asp + // access uses precision to store length for char/varchar - // ref: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_odk.asp - // access uses precision to store length for char/varchar + if ($fld->type == 'C' or $fld->type == 'X') { + if ($rs->fields[4] <= -95) // UNICODE + $fld->max_length = $rs->fields[7]/2; + else + $fld->max_length = $rs->fields[7]; + } else + $fld->max_length = $rs->fields[7]; - if ($fld->type == 'C' or $fld->type == 'X') { - if ($rs->fields[4] <= -95) // UNICODE - $fld->max_length = $rs->fields[7]/2; - else - $fld->max_length = $rs->fields[7]; - } else - $fld->max_length = $rs->fields[7]; + $fld->not_null = !empty($rs->fields[10]); + $fld->scale = $rs->fields[8]; + $fld->primary_key = 0; - $fld->not_null = !empty($rs->fields[10]); - $fld->scale = $rs->fields[8]; - $fld->primary_key = 0; + //$columnName = $this->getMetaCasedValue($fld->name); + $columnName = strtoupper($fld->name); + $retarr[$columnName] = $fld; - //$columnName = $this->getMetaCasedValue($fld->name); - $columnName = strtoupper($fld->name); - $retarr[$columnName] = $fld; + } + else if (sizeof($retarr)>0) + break; - } - else if (sizeof($retarr)>0) - break; + $rs->MoveNext(); - $rs->MoveNext(); + } - } + $this->setFetchMode($savem); - $this->setFetchMode($savem); + $rs->Close(); + if (empty($retarr)) + $retarr = false; - $rs->Close(); - if (empty($retarr)) - $retarr = false; + + $this->setFetchMode(ADODB_FETCH_NUM ); + + /* + * Now we find out if the column is part of a primary key + */ + $primaryKeys = $this->metaPrimaryKeys($table); + if (!$primaryKeys) { + return $retarr; + } - - $this->setFetchMode(ADODB_FETCH_NUM ); - - /* - * Now we find out if the column is part of a primary key - */ - $primaryKeys = $this->metaPrimaryKeys($table); - if (!$primaryKeys) { - return $retarr; - } + foreach ($primaryKeys as $pk) + { + $pk = strtoupper($pk); + if (isset($retarr[$pk])) { + $retarr[$pk]->primary_key = true; + } + } - foreach ($primaryKeys as $pk) - { - $pk = strtoupper($pk); - if (isset($retarr[$pk])) { - $retarr[$pk]->primary_key = true; - } - } + return $retarr; + - return $retarr; - + $qid = @db2_primary_keys($this->_connectionID, "", $schema, $table); + if (empty($qid)) + return false; - $qid = @db2_primary_keys($this->_connectionID, "", $schema, $table); - if (empty($qid)) - return false; + $rs = new ADORecordSet_db2($qid); - $rs = new ADORecordSet_db2($qid); + if (!$rs) + { + $ADODB_FETCH_MODE = $savem; + return $retarr; + } + $rs->_fetch(); - if (!$rs) - { - $ADODB_FETCH_MODE = $savem; - return $retarr; - } - $rs->_fetch(); + /* + $rs->fields indices + 0 TABLE_CAT + 1 TABLE_SCHEM + 2 TABLE_NAME + 3 COLUMN_NAME + 4 KEY_SEQ + 5 PK_NAME + */ + while (!$rs->EOF) { - /* - $rs->fields indices - 0 TABLE_CAT - 1 TABLE_SCHEM - 2 TABLE_NAME - 3 COLUMN_NAME - 4 KEY_SEQ - 5 PK_NAME - */ - while (!$rs->EOF) { + if (strtoupper(trim($rs->fields[2])) == $table + && (!$schema || strtoupper($rs->fields[1]) == $schema)) + { + $retarr[strtoupper($rs->fields[3])]->primary_key = true; + } + else if (sizeof($retarr)>0) + break; - if (strtoupper(trim($rs->fields[2])) == $table - && (!$schema || strtoupper($rs->fields[1]) == $schema)) - { - $retarr[strtoupper($rs->fields[3])]->primary_key = true; - } - else if (sizeof($retarr)>0) - break; + $rs->MoveNext(); + } + $rs->Close(); - $rs->MoveNext(); - } - $rs->Close(); + $ADODB_FETCH_MODE = $savem; - $ADODB_FETCH_MODE = $savem; + if (empty($retarr)) + return false; - if (empty($retarr)) - return false; + /* + * If the fetch mode is numeric, return as numeric array + */ + if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) + $retarr = array_values($retarr); - /* - * If the fetch mode is numeric, return as numeric array - */ - if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) - $retarr = array_values($retarr); + return $retarr; + } - return $retarr; - } + /** + * In this version if prepareSp, we just check to make sure + * that the name of the stored procedure is correct + * If true, we returns an array + * else false + * + * @param string $procedureName + * @param mixed $parameters (not used in db2 connections) + * @return mixed[] + */ + function prepareSp($procedureName,$parameters=false) { - /** - * In this version if prepareSp, we just check to make sure - * that the name of the stored procedure is correct - * If true, we returns an array - * else false - * - * @param string $procedureName - * @param mixed $parameters (not used in db2 connections) - * @return mixed[] - */ - function prepareSp($procedureName,$parameters=false) { + global $ADODB_FETCH_MODE; - global $ADODB_FETCH_MODE; + $this->storedProcedureParameters = array('name'=>'', + 'resource'=>false, + 'in'=>array(), + 'out'=>array(), + 'index'=>array(), + 'parameters'=>array(), + 'keyvalue' => array()); - $this->storedProcedureParameters = array('name'=>'', - 'resource'=>false, - 'in'=>array(), - 'out'=>array(), - 'index'=>array(), - 'parameters'=>array(), - 'keyvalue' => array()); + //$procedureName = strtoupper($procedureName); + //$procedureName = $this->getTableCasedValue($procedureName); - //$procedureName = strtoupper($procedureName); - //$procedureName = $this->getTableCasedValue($procedureName); + $savem = $ADODB_FETCH_MODE; + $ADODB_FETCH_MODE = ADODB_FETCH_NUM; - $savem = $ADODB_FETCH_MODE; - $ADODB_FETCH_MODE = ADODB_FETCH_NUM; + $qid = db2_procedures($this->_connectionID, NULL , '%' , $procedureName ); - $qid = db2_procedures($this->_connectionID, NULL , '%' , $procedureName ); + $ADODB_FETCH_MODE = $savem; - $ADODB_FETCH_MODE = $savem; + if (!$qid) + { + if ($this->debug) + ADOConnection::outp(sprintf('No Procedure of name %s available',$procedureName)); + return false; + } - if (!$qid) - { - if ($this->debug) - ADOConnection::outp(sprintf('No Procedure of name %s available',$procedureName)); - return false; - } + $this->storedProcedureParameters['name'] = $procedureName; + /* + * Now we know we have a valid procedure name, lets see if it requires + * parameters + */ + $savem = $ADODB_FETCH_MODE; + $ADODB_FETCH_MODE = ADODB_FETCH_NUM; - $this->storedProcedureParameters['name'] = $procedureName; - /* - * Now we know we have a valid procedure name, lets see if it requires - * parameters - */ - $savem = $ADODB_FETCH_MODE; - $ADODB_FETCH_MODE = ADODB_FETCH_NUM; + $qid = db2_procedure_columns($this->_connectionID, NULL , '%' , $procedureName , NULL ); - $qid = db2_procedure_columns($this->_connectionID, NULL , '%' , $procedureName , NULL ); + $ADODB_FETCH_MODE = $savem; - $ADODB_FETCH_MODE = $savem; + if (!$qid) + { + if ($this->debug) + ADOConnection::outp(sprintf('No columns of name %s available',$procedureName)); + return false; + } + $rs = new ADORecordSet_db2($qid); + if (!$rs) + return false; - if (!$qid) - { - if ($this->debug) - ADOConnection::outp(sprintf('No columns of name %s available',$procedureName)); - return false; - } - $rs = new ADORecordSet_db2($qid); - if (!$rs) - return false; + $preparedStatement = 'CALL %s(%s)'; + $parameterMarkers = array(); + while (!$rs->EOF) + { + $parameterName = $rs->fields[3]; + if ($parameterName == '') + { + $rs->moveNext(); + continue; + } + $parameterType = $rs->fields[4]; + $ordinalPosition = $rs->fields[17]; + switch($parameterType) + { + case DB2_PARAM_IN: + case DB2_PARAM_INOUT: + $this->storedProcedureParameters['in'][$parameterName] = ''; + break; + case DB2_PARAM_INOUT: + case DB2_PARAM_OUT: + $this->storedProcedureParameters['out'][$parameterName] = ''; + break; + } + $this->storedProcedureParameters['index'][$parameterName] = $ordinalPosition; + $this->storedProcedureParameters['parameters'][$ordinalPosition] = $rs->fields; + $rs->moveNext(); - $preparedStatement = 'CALL %s(%s)'; - $parameterMarkers = array(); - while (!$rs->EOF) - { - $parameterName = $rs->fields[3]; - if ($parameterName == '') - { - $rs->moveNext(); - continue; - } - $parameterType = $rs->fields[4]; - $ordinalPosition = $rs->fields[17]; - switch($parameterType) - { - case DB2_PARAM_IN: - case DB2_PARAM_INOUT: - $this->storedProcedureParameters['in'][$parameterName] = ''; - break; - case DB2_PARAM_INOUT: - case DB2_PARAM_OUT: - $this->storedProcedureParameters['out'][$parameterName] = ''; - break; - } - $this->storedProcedureParameters['index'][$parameterName] = $ordinalPosition; - $this->storedProcedureParameters['parameters'][$ordinalPosition] = $rs->fields; - $rs->moveNext(); + } + $parameterCount = count($this->storedProcedureParameters['index']); + $parameterMarkers = array_fill(0,$parameterCount,'?'); - } - $parameterCount = count($this->storedProcedureParameters['index']); - $parameterMarkers = array_fill(0,$parameterCount,'?'); + /* + * We now know how many parameters to bind to the stored procedure + */ + $parameterList = implode(',',$parameterMarkers); - /* - * We now know how many parameters to bind to the stored procedure - */ - $parameterList = implode(',',$parameterMarkers); + $sql = sprintf($preparedStatement,$procedureName,$parameterList); - $sql = sprintf($preparedStatement,$procedureName,$parameterList); + $spResource = @db2_prepare($this->_connectionID,$sql); - $spResource = @db2_prepare($this->_connectionID,$sql); + if (!$spResource) + { + $errorMessage = @db2_conn_errormsg($this->_connectionID); + $this->_errorMsg = $errorMessage; - if (!$spResource) - { - $errorMessage = @db2_conn_errormsg($this->_connectionID); - $this->_errorMsg = $errorMessage; + if ($this->debug) + ADOConnection::outp($errorMessage); - if ($this->debug) - ADOConnection::outp($errorMessage); + return false; + } - return false; - } + $this->storedProcedureParameters['resource'] = $spResource; - $this->storedProcedureParameters['resource'] = $spResource; + if ($this->debug) + { - if ($this->debug) - { + ADOConnection::outp('The following parameters will be used in the SP call'); + ADOConnection::outp(print_r($this->storedProcedureParameters)); + } + /* + * We now have a stored parameter resource + * to bind to. The spResource and sql that is returned are + * not usable, its for dummy compatibility. Everything + * will be handled by the storedProcedureParameters + * array + */ + return array($sql,$spResource); - ADOConnection::outp('The following parameters will be used in the SP call'); - ADOConnection::outp(print_r($this->storedProcedureParameters)); - } - /* - * We now have a stored parameter resource - * to bind to. The spResource and sql that is returned are - * not usable, its for dummy compatibility. Everything - * will be handled by the storedProcedureParameters - * array - */ - return array($sql,$spResource); + } - } + private function storedProcedureParameter(&$stmt, + &$var, + $name, + $isOutput=false, + $maxLen=4000, + $type=false) + { - private function storedProcedureParameter(&$stmt, - &$var, - $name, - $isOutput=false, - $maxLen=4000, - $type=false) - { + $name = strtoupper($name); - $name = strtoupper($name); + /* + * Must exist in the list of parameter names for the type + */ + if ($isOutput + && !isset( $this->storedProcedureParameters['out'][$name])) + { + $errorMessage = sprintf('%s is not a valid OUT parameter name',$name); - /* - * Must exist in the list of parameter names for the type - */ - if ($isOutput - && !isset( $this->storedProcedureParameters['out'][$name])) - { - $errorMessage = sprintf('%s is not a valid OUT parameter name',$name); + $this->_errorMsg = $errorMessage; + if ($this->debug) + ADOConnection::outp($errorMessage); + return false; + } - $this->_errorMsg = $errorMessage; - if ($this->debug) - ADOConnection::outp($errorMessage); - return false; - } + if (!$isOutput + && !isset( $this->storedProcedureParameters['in'][$name])) + { + $errorMessage = sprintf('%s is not a valid IN parameter name',$name); - if (!$isOutput - && !isset( $this->storedProcedureParameters['in'][$name])) - { - $errorMessage = sprintf('%s is not a valid IN parameter name',$name); + $this->_errorMsg = $errorMessage; + if ($this->debug) + ADOConnection::outp($errorMessage); + return false; + } - $this->_errorMsg = $errorMessage; - if ($this->debug) - ADOConnection::outp($errorMessage); - return false; - } + /* + * We will use these values to bind to when we execute + * the query + */ + $this->storedProcedureParameters['keyvalue'][$name] = &$var; - /* - * We will use these values to bind to when we execute - * the query - */ - $this->storedProcedureParameters['keyvalue'][$name] = &$var; + return true; - return true; + } - } + /** + * Executes a prepared stored procedure. + * + * The function uses the previously accumulated information and + * resources in the $storedProcedureParameters array + * + * @return mixed The statement id if successful, or false + */ + private function executeStoredProcedure() + { - /** - * Executes a prepared stored procedure. - * - * The function uses the previously accumulated information and - * resources in the $storedProcedureParameters array - * - * @return mixed The statement id if successful, or false - */ - private function executeStoredProcedure() - { + /* + * Get the previously built resource + */ + $stmtid = $this->storedProcedureParameters['resource']; - /* - * Get the previously built resource - */ - $stmtid = $this->storedProcedureParameters['resource']; + /* + * Bind our variables to the DB2 procedure + */ + foreach ($this->storedProcedureParameters['keyvalue'] as $spName=>$spValue){ - /* - * Bind our variables to the DB2 procedure - */ - foreach ($this->storedProcedureParameters['keyvalue'] as $spName=>$spValue){ + /* + * Get the ordinal position, required for binding + */ + $ordinalPosition = $this->storedProcedureParameters['index'][$spName]; - /* - * Get the ordinal position, required for binding - */ - $ordinalPosition = $this->storedProcedureParameters['index'][$spName]; + /* + * Get the db2 column dictionary for the parameter + */ + $columnDictionary = $this->storedProcedureParameters['parameters'][$ordinalPosition]; + $parameterType = $columnDictionary[4]; + $dataType = $columnDictionary[5]; + $precision = $columnDictionary[10]; + $scale = $columnDictionary[9]; - /* - * Get the db2 column dictionary for the parameter - */ - $columnDictionary = $this->storedProcedureParameters['parameters'][$ordinalPosition]; - $parameterType = $columnDictionary[4]; - $dataType = $columnDictionary[5]; - $precision = $columnDictionary[10]; - $scale = $columnDictionary[9]; + $ok = @db2_bind_param ($this->storedProcedureParameters['resource'], + $ordinalPosition , + $spName, + $parameterType, + $dataType, + $precision, + $scale + ); - $ok = @db2_bind_param ($this->storedProcedureParameters['resource'], - $ordinalPosition , - $spName, - $parameterType, - $dataType, - $precision, - $scale - ); + if (!$ok) + { + $this->_errorMsg = @db2_stmt_errormsg(); + $this->_errorCode = @db2_stmt_error(); - if (!$ok) - { - $this->_errorMsg = @db2_stmt_errormsg(); - $this->_errorCode = @db2_stmt_error(); + if ($this->debug) + ADOConnection::outp($this->_errorMsg); + return false; + } - if ($this->debug) - ADOConnection::outp($this->_errorMsg); - return false; - } + if ($this->debug) + ADOConnection::outp("Correctly Bound parameter $spName to procedure"); - if ($this->debug) - ADOConnection::outp("Correctly Bound parameter $spName to procedure"); + /* + * Build a variable in the current environment that matches + * the parameter name + */ + ${$spName} = $spValue; - /* - * Build a variable in the current environment that matches - * the parameter name - */ - ${$spName} = $spValue; + } - } + /* + * All bound, execute + */ - /* - * All bound, execute - */ + if (!@db2_execute($stmtid)) + { + $this->_errorMsg = @db2_stmt_errormsg(); + $this->_errorCode = @db2_stmt_error(); - if (!@db2_execute($stmtid)) - { - $this->_errorMsg = @db2_stmt_errormsg(); - $this->_errorCode = @db2_stmt_error(); + if ($this->debug) + ADOConnection::outp($this->_errorMsg); + return false; + } - if ($this->debug) - ADOConnection::outp($this->_errorMsg); - return false; - } + /* + * We now take the changed parameters back into the + * stored procedures array where we can query them later + * Remember that $spValue was passed in by reference, so we + * can access the value in the variable that was originally + * passed to inParameter or outParameter + */ + foreach ($this->storedProcedureParameters['keyvalue'] as $spName=>$spValue) + { + /* + * We make it available to the environment + */ + $spValue = ${$spName}; + $this->storedProcedureParameters['keyvalue'][$spName] = $spValue; + } - /* - * We now take the changed parameters back into the - * stored procedures array where we can query them later - * Remember that $spValue was passed in by reference, so we - * can access the value in the variable that was originally - * passed to inParameter or outParameter - */ - foreach ($this->storedProcedureParameters['keyvalue'] as $spName=>$spValue) - { - /* - * We make it available to the environment - */ - $spValue = ${$spName}; - $this->storedProcedureParameters['keyvalue'][$spName] = $spValue; - } + return $stmtid; + } - return $stmtid; - } + /** + * + * Accepts an input or output parameter to bind to either a stored + * or prepared statements. For DB2, this should not be called as an + * API. always wrap with inParameter and outParameter + * + * @param mixed[] $stmt Statement returned by Prepare() or PrepareSP(). + * @param mixed $var PHP variable to bind to. Can set to null (for isNull support). + * @param string $name Name of stored procedure variable name to bind to. + * @param int $isOutput optional) Indicates direction of parameter + * 0/false=IN 1=OUT 2= IN/OUT + * This is ignored for Stored Procedures + * @param int $maxLen (optional)Holds an maximum length of the variable. + * This is ignored for Stored Procedures + * @param int $type (optional) The data type of $var. + * This is ignored for Stored Procedures + * + * @return bool Success of the operation + */ + public function parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false) + { - /** - * - * Accepts an input or output parameter to bind to either a stored - * or prepared statements. For DB2, this should not be called as an - * API. always wrap with inParameter and outParameter - * - * @param mixed[] $stmt Statement returned by Prepare() or PrepareSP(). - * @param mixed $var PHP variable to bind to. Can set to null (for isNull support). - * @param string $name Name of stored procedure variable name to bind to. - * @param int $isOutput optional) Indicates direction of parameter - * 0/false=IN 1=OUT 2= IN/OUT - * This is ignored for Stored Procedures - * @param int $maxLen (optional)Holds an maximum length of the variable. - * This is ignored for Stored Procedures - * @param int $type (optional) The data type of $var. - * This is ignored for Stored Procedures - * - * @return bool Success of the operation - */ - public function parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false) - { + /* + * If the $stmt is the name of a stored procedure we are + * setting up, we will process it one way, otherwise + * we assume we are setting up a prepared statement + */ + if (is_array($stmt)) + { + if ($this->debug) + ADOConnection::outp("Adding parameter to stored procedure"); + if ($stmt[1] == $this->storedProcedureParameters['resource']) + return $this->storedProcedureParameter($stmt[1], + $var, + $name, + $isOutput, + $maxLen, + $type); - /* - * If the $stmt is the name of a stored procedure we are - * setting up, we will process it one way, otherwise - * we assume we are setting up a prepared statement - */ - if (is_array($stmt)) - { - if ($this->debug) - ADOConnection::outp("Adding parameter to stored procedure"); - if ($stmt[1] == $this->storedProcedureParameters['resource']) - return $this->storedProcedureParameter($stmt[1], - $var, - $name, - $isOutput, - $maxLen, - $type); + } - } + /* + * We are going to add a parameter to a prepared statement + */ + if ($this->debug) + ADOConnection::outp("Adding parameter to prepared statement"); + } - /* - * We are going to add a parameter to a prepared statement - */ - if ($this->debug) - ADOConnection::outp("Adding parameter to prepared statement"); - } + /** + * Prepares a prepared SQL statement, not used for stored procedures + * + * @param string $sql + * + * @return mixed + */ + function prepare($sql) + { - /** - * Prepares a prepared SQL statement, not used for stored procedures - * - * @param string $sql - * - * @return mixed - */ - function prepare($sql) - { + if (! $this->_bindInputArray) return $sql; // no binding - if (! $this->_bindInputArray) return $sql; // no binding + $stmt = @db2_prepare($this->_connectionID,$sql); + if (!$stmt) { + // we don't know whether db2 driver is parsing prepared stmts, so just return sql + return $sql; + } + return array($sql,$stmt,false); + } - $stmt = @db2_prepare($this->_connectionID,$sql); - if (!$stmt) { - // we don't know whether db2 driver is parsing prepared stmts, so just return sql - return $sql; - } - return array($sql,$stmt,false); - } + /** + * Executes a query + * + * @param mixed $sql + * @param mixed $inputarr An optional array of parameters + * + * @return mixed either the queryID or false + */ + function _query($sql, $inputarr = false) + { + $db2Options = array(); + /* + * Use DB2 Internal case handling for best speed + */ + switch(ADODB_ASSOC_CASE) + { + case ADODB_ASSOC_CASE_UPPER: + $db2Options = array('db2_attr_case'=>DB2_CASE_UPPER); + $setOption = @db2_set_option($this->_connectionID,$db2Options,1); + break; - /** - * Executes a query - * - * @param mixed $sql - * @param mixed $inputarr An optional array of parameters - * - * @return mixed either the queryID or false - */ - function _query($sql, $inputarr = false) - { - $db2Options = array(); - /* - * Use DB2 Internal case handling for best speed - */ - switch(ADODB_ASSOC_CASE) - { - case ADODB_ASSOC_CASE_UPPER: - $db2Options = array('db2_attr_case'=>DB2_CASE_UPPER); - $setOption = @db2_set_option($this->_connectionID,$db2Options,1); - break; + case ADODB_ASSOC_CASE_LOWER: + $db2Options = array('db2_attr_case'=>DB2_CASE_LOWER); + $setOption = @db2_set_option($this->_connectionID,$db2Options,1); + break; - case ADODB_ASSOC_CASE_LOWER: - $db2Options = array('db2_attr_case'=>DB2_CASE_LOWER); - $setOption = @db2_set_option($this->_connectionID,$db2Options,1); - break; + default: + $db2Options = array('db2_attr_case'=>DB2_CASE_NATURAL); + $setOption = @db2_set_option($this->_connectionID,$db2Options,1); + } - default: - $db2Options = array('db2_attr_case'=>DB2_CASE_NATURAL); - $setOption = @db2_set_option($this->_connectionID,$db2Options,1); - } + if (!$this->useLegacyRecordCounting) { + $db2Options = array('rowcount'=>DB2_ROWCOUNT_PREFETCH_ON); + $setOption = @db2_set_option($this->_connectionID,$db2Options,1); + } - if ($inputarr) - { - if (is_array($sql)) - { - $stmtid = $sql[1]; - } - else - { - $stmtid = @db2_prepare($this->_connectionID,$sql); + if ($inputarr) + { + if (is_array($sql)) + { + $stmtid = $sql[1]; + } + else + { + $stmtid = @db2_prepare($this->_connectionID,$sql); - if ($stmtid == false) - { - $this->_errorMsg = @db2_stmt_errormsg(); - $this->_errorCode = @db2_stmt_error(); + if ($stmtid == false) + { + $this->_errorMsg = @db2_stmt_errormsg(); + $this->_errorCode = @db2_stmt_error(); - if ($this->debug) - ADOConnection::outp($this->_errorMsg); + if ($this->debug) + ADOConnection::outp($this->_errorMsg); - return false; - } - } + return false; + } + } - if (! @db2_execute($stmtid,$inputarr)) - { - $this->_errorMsg = @db2_stmt_errormsg(); - $this->_errorCode = @db2_stmt_error(); - if ($this->debug) - ADOConnection::outp($this->_errorMsg); - return false; - } + if (! @db2_execute($stmtid,$inputarr)) + { + $this->_errorMsg = @db2_stmt_errormsg(); + $this->_errorCode = @db2_stmt_error(); + if ($this->debug) + ADOConnection::outp($this->_errorMsg); + return false; + } - } - else if (is_array($sql)) - { + } + else if (is_array($sql)) + { - /* - * Either a prepared statement or a stored procedure - */ + /* + * Either a prepared statement or a stored procedure + */ - if (is_array($this->storedProcedureParameters) - && is_resource($this->storedProcedureParameters['resource'] - )) - /* - * This is all handled in the separate method for - * readability - */ - return $this->executeStoredProcedure(); + if (is_array($this->storedProcedureParameters) + && is_resource($this->storedProcedureParameters['resource'] + )) + /* + * This is all handled in the separate method for + * readability + */ + return $this->executeStoredProcedure(); - /* - * First, we prepare the statement - */ - $stmtid = @db2_prepare($this->_connectionID,$sql[0]); - if (!$stmtid){ - $this->_errorMsg = @db2_stmt_errormsg(); - $this->_errorCode = @db2_stmt_error(); - if ($this->debug) - ADOConnection::outp("Prepare failed: " . $this->_errorMsg); + /* + * First, we prepare the statement + */ + $stmtid = @db2_prepare($this->_connectionID,$sql[0]); + if (!$stmtid){ + $this->_errorMsg = @db2_stmt_errormsg(); + $this->_errorCode = @db2_stmt_error(); + if ($this->debug) + ADOConnection::outp("Prepare failed: " . $this->_errorMsg); - return false; - } - /* - * We next bind some input parameters - */ - $ordinal = 1; - foreach ($sql[1] as $psVar=>$psVal){ - ${$psVar} = $psVal; - $ok = @db2_bind_param($stmtid, $ordinal, $psVar, DB2_PARAM_IN); - if (!$ok) - { - $this->_errorMsg = @db2_stmt_errormsg(); - $this->_errorCode = @db2_stmt_error(); - if ($this->debug) - ADOConnection::outp("Bind failed: " . $this->_errorMsg); - return false; - } - } + return false; + } + /* + * We next bind some input parameters + */ + $ordinal = 1; + foreach ($sql[1] as $psVar=>$psVal){ + ${$psVar} = $psVal; + $ok = @db2_bind_param($stmtid, $ordinal, $psVar, DB2_PARAM_IN); + if (!$ok) + { + $this->_errorMsg = @db2_stmt_errormsg(); + $this->_errorCode = @db2_stmt_error(); + if ($this->debug) + ADOConnection::outp("Bind failed: " . $this->_errorMsg); + return false; + } + } - if (!@db2_execute($stmtid)) - { - $this->_errorMsg = @db2_stmt_errormsg(); - $this->_errorCode = @db2_stmt_error(); - if ($this->debug) - ADOConnection::outp($this->_errorMsg); - return false; - } + if (!@db2_execute($stmtid)) + { + $this->_errorMsg = @db2_stmt_errormsg(); + $this->_errorCode = @db2_stmt_error(); + if ($this->debug) + ADOConnection::outp($this->_errorMsg); + return false; + } - return $stmtid; - } - else - { + return $stmtid; + } + else + { - $stmtid = @db2_exec($this->_connectionID,$sql); - } - $this->_lastAffectedRows = 0; - if ($stmtid) - { - if (@db2_num_fields($stmtid) == 0) - { - $this->_lastAffectedRows = db2_num_rows($stmtid); - $stmtid = true; - } - else - { - $this->_lastAffectedRows = 0; - } + $stmtid = @db2_exec($this->_connectionID,$sql); + } + $this->_lastAffectedRows = 0; + if ($stmtid) + { + if (@db2_num_fields($stmtid) == 0) + { + $this->_lastAffectedRows = db2_num_rows($stmtid); + $stmtid = true; + } + else + { + $this->_lastAffectedRows = 0; + } - $this->_errorMsg = ''; - $this->_errorCode = 0; + $this->_errorMsg = ''; + $this->_errorCode = 0; - } - else - { + } + else + { - $this->_errorMsg = @db2_stmt_errormsg(); - $this->_errorCode = @db2_stmt_error(); + $this->_errorMsg = @db2_stmt_errormsg(); + $this->_errorCode = @db2_stmt_error(); - } - return $stmtid; - } + } + return $stmtid; + } - /* - Insert a null into the blob field of the table first. - Then use UpdateBlob to store the blob. + /* + Insert a null into the blob field of the table first. + Then use UpdateBlob to store the blob. - Usage: + Usage: - $conn->execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); - $conn->UpdateBlob('blobtable','blobcol',$blob,'id=1'); - */ - function updateBlob($table,$column,$val,$where,$blobtype='BLOB') - { - return $this->execute("UPDATE $table SET $column=? WHERE $where",$val) != false; - } + $conn->execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); + $conn->UpdateBlob('blobtable','blobcol',$blob,'id=1'); + */ + function updateBlob($table,$column,$val,$where,$blobtype='BLOB') + { + return $this->execute("UPDATE $table SET $column=? WHERE $where",$val) != false; + } - // returns true or false - function _close() - { - $ret = @db2_close($this->_connectionID); - $this->_connectionID = false; - return $ret; - } + // returns true or false + function _close() + { + $ret = @db2_close($this->_connectionID); + $this->_connectionID = false; + return $ret; + } - function _affectedrows() - { - return $this->_lastAffectedRows; - } + function _affectedrows() + { + return $this->_lastAffectedRows; + } - /** - * Gets a meta cased parameter - * - * Receives an input variable to be processed per the metaCasing - * rule, and returns the same value, processed - * - * @param string $value - * - * @return string - */ - final public function getMetaCasedValue($value) - { - global $ADODB_ASSOC_CASE; + /** + * Gets a meta cased parameter + * + * Receives an input variable to be processed per the metaCasing + * rule, and returns the same value, processed + * + * @param string $value + * + * @return string + */ + final public function getMetaCasedValue($value) + { + global $ADODB_ASSOC_CASE; - switch($ADODB_ASSOC_CASE) - { - case ADODB_ASSOC_CASE_LOWER: - $value = strtolower($value); - break; - case ADODB_ASSOC_CASE_UPPER: - $value = strtoupper($value); - break; - } - return $value; - } + switch($ADODB_ASSOC_CASE) + { + case ADODB_ASSOC_CASE_LOWER: + $value = strtolower($value); + break; + case ADODB_ASSOC_CASE_UPPER: + $value = strtoupper($value); + break; + } + return $value; + } - const TABLECASE_LOWER = 0; - const TABLECASE_UPPER = 1; - const TABLECASE_DEFAULT = 2; + const TABLECASE_LOWER = 0; + const TABLECASE_UPPER = 1; + const TABLECASE_DEFAULT = 2; - /** - * Controls the casing of the table provided to the meta functions - */ - private $tableCase = 2; + /** + * Controls the casing of the table provided to the meta functions + */ + private $tableCase = 2; - /** - * Sets the table case parameter - * - * @param int $caseOption - * @return null - */ - final public function setTableCasing($caseOption) - { - $this->tableCase = $caseOption; - } + /** + * Sets the table case parameter + * + * @param int $caseOption + * @return null + */ + final public function setTableCasing($caseOption) + { + $this->tableCase = $caseOption; + } - /** - * Gets the table casing parameter - * - * @return int $caseOption - */ - final public function getTableCasing() - { - return $this->tableCase; - } + /** + * Gets the table casing parameter + * + * @return int $caseOption + */ + final public function getTableCasing() + { + return $this->tableCase; + } - /** - * Gets a table cased parameter - * - * Receives an input variable to be processed per the tableCasing - * rule, and returns the same value, processed - * - * @param string $value - * - * @return string - */ - final public function getTableCasedValue($value) - { - switch($this->tableCase) - { - case self::TABLECASE_LOWER: - $value = strtolower($value); - break; - case self::TABLECASE_UPPER: - $value = strtoupper($value); - break; - } - return $value; - } + /** + * Gets a table cased parameter + * + * Receives an input variable to be processed per the tableCasing + * rule, and returns the same value, processed + * + * @param string $value + * + * @return string + */ + final public function getTableCasedValue($value) + { + switch($this->tableCase) + { + case self::TABLECASE_LOWER: + $value = strtolower($value); + break; + case self::TABLECASE_UPPER: + $value = strtoupper($value); + break; + } + return $value; + } } /*-------------------------------------------------------------------------------------- - Class Name: Recordset + Class Name: Recordset --------------------------------------------------------------------------------------*/ class ADORecordSet_db2 extends ADORecordSet { - var $bind = false; - var $databaseType = "db2"; - var $dataProvider = "db2"; - var $useFetchArray; + var $bind = false; + var $databaseType = "db2"; + var $dataProvider = "db2"; + var $useFetchArray; - // returns the field object - function fetchField($offset = 0) - { - $o = new ADOFieldObject(); - $o->name = @db2_field_name($this->_queryID,$offset); - $o->type = @db2_field_type($this->_queryID,$offset); - $o->max_length = @db2_field_width($this->_queryID,$offset); + // returns the field object + function fetchField($offset = 0) + { + $o = new ADOFieldObject(); + $o->name = @db2_field_name($this->_queryID,$offset); + $o->type = @db2_field_type($this->_queryID,$offset); + $o->max_length = @db2_field_width($this->_queryID,$offset); - /* - if (ADODB_ASSOC_CASE == 0) - $o->name = strtolower($o->name); - else if (ADODB_ASSOC_CASE == 1) - $o->name = strtoupper($o->name); - */ - return $o; - } + /* + if (ADODB_ASSOC_CASE == 0) + $o->name = strtolower($o->name); + else if (ADODB_ASSOC_CASE == 1) + $o->name = strtoupper($o->name); + */ + return $o; + } - /* Use associative array to get fields array */ - function fields($colname) - { + /* Use associative array to get fields array */ + function fields($colname) + { - if ($this->fetchMode & ADODB_FETCH_ASSOC) { - return $this->fields[$colname]; - } + if ($this->fetchMode & ADODB_FETCH_ASSOC) { + return $this->fields[$colname]; + } - if (!$this->bind) { - $this->bind = array(); - for ($i=0; $i < $this->_numOfFields; $i++) { - $o = $this->FetchField($i); - $this->bind[strtoupper($o->name)] = $i; - } - } + if (!$this->bind) { + $this->bind = array(); + for ($i=0; $i < $this->_numOfFields; $i++) { + $o = $this->FetchField($i); + $this->bind[strtoupper($o->name)] = $i; + } + } - return $this->fields[$this->bind[strtoupper($colname)]]; - } + return $this->fields[$this->bind[strtoupper($colname)]]; + } - function _initrs() - { - global $ADODB_COUNTRECS; - /* - * db2_num_rows only works with a scrollable cursor - */ - $this->_numOfRows = ($ADODB_COUNTRECS) ? @db2_num_rows($this->_queryID) : -1; + function _initrs() + { + global $ADODB_COUNTRECS; + /* + * If legacy rowcount is activated, this will return -1; + */ + $this->_numOfRows = ($ADODB_COUNTRECS) ? @db2_num_rows($this->_queryID) : -1; - $this->_numOfFields = @db2_num_fields($this->_queryID); + $this->_numOfFields = @db2_num_fields($this->_queryID); - if ($this->_numOfRows == 0) - $this->_numOfRows = -1; - } + if ($this->_numOfRows == 0) + $this->_numOfRows = -1; + } - function _seek($row) - { - return false; - } + function _seek($row) + { + return false; + } - function getArrayLimit($nrows,$offset=0) - { - if ($offset <= 0) { - $rs = $this->GetArray($nrows); - return $rs; - } + function getArrayLimit($nrows,$offset=0) + { + if ($offset <= 0) { + $rs = $this->GetArray($nrows); + return $rs; + } - $this->Move($offset); + $this->Move($offset); - $results = array(); - $cnt = 0; - while (!$this->EOF && $nrows != $cnt) { - $results[$cnt++] = $this->fields; - $this->MoveNext(); - } + $results = array(); + $cnt = 0; + while (!$this->EOF && $nrows != $cnt) { + $results[$cnt++] = $this->fields; + $this->MoveNext(); + } - return $results; - } + return $results; + } - function moveNext() - { - if ($this->EOF || $this->_numOfRows == 0) - return false; + function moveNext() + { + if ($this->EOF || $this->_numOfRows == 0) + return false; - $this->_currentRow++; + $this->_currentRow++; - $this->processCoreFetch(); - return $this->processMoveRecord(); + $this->processCoreFetch(); + return $this->processMoveRecord(); - } + } - private function processCoreFetch() - { - switch ($this->fetchMode){ - case ADODB_FETCH_ASSOC: + private function processCoreFetch() + { + switch ($this->fetchMode){ + case ADODB_FETCH_ASSOC: - /* - * Associative array - */ - $this->fields = @db2_fetch_assoc($this->_queryID); - break; + /* + * Associative array + */ + $this->fields = @db2_fetch_assoc($this->_queryID); + break; - case ADODB_FETCH_BOTH: - /* - * Fetch both numeric and Associative array - */ - $this->fields = @db2_fetch_both($this->_queryID); - break; - default: - /* - * Numeric array - */ - $this->fields = @db2_fetch_array($this->_queryID); - break; - } - } + case ADODB_FETCH_BOTH: + /* + * Fetch both numeric and Associative array + */ + $this->fields = @db2_fetch_both($this->_queryID); + break; + default: + /* + * Numeric array + */ + $this->fields = @db2_fetch_array($this->_queryID); + break; + } + } - private function processMoveRecord() - { - if (!$this->fields){ - $this->EOF = true; - return false; - } + private function processMoveRecord() + { + if (!$this->fields){ + $this->EOF = true; + return false; + } - return true; - } + return true; + } - function _fetch() - { - $this->processCoreFetch(); - if ($this->fields) - return true; + function _fetch() + { + $this->processCoreFetch(); + if ($this->fields) + return true; - $this->fields = false; - return false; - } + $this->fields = false; + return false; + } - function _close() - { - $ok = @db2_free_result($this->_queryID); - if (!$ok) - { - $this->connection->_errorMsg = @db2_stmt_errormsg($this->_queryID); - $this->connection->_errorCode = @db2_stmt_error(); + function _close() + { + $ok = @db2_free_result($this->_queryID); + if (!$ok) + { + $this->connection->_errorMsg = @db2_stmt_errormsg($this->_queryID); + $this->connection->_errorCode = @db2_stmt_error(); - if ($this->debug) - ADOConnection::outp($this->connection->_errorMsg); - return false; - } - } + if ($this->debug) + ADOConnection::outp($this->connection->_errorMsg); + return false; + } + } } |