summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--datadict/datadict-db2.inc.php76
-rw-r--r--drivers/adodb-db2.inc.php1829
2 files changed, 1562 insertions, 343 deletions
diff --git a/datadict/datadict-db2.inc.php b/datadict/datadict-db2.inc.php
index 69c40986..93bf79b7 100644
--- a/datadict/datadict-db2.inc.php
+++ b/datadict/datadict-db2.inc.php
@@ -1,5 +1,4 @@
<?php
-
/**
@version v5.21.0-dev ??-???-2016
@copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
@@ -18,6 +17,7 @@ class ADODB2_db2 extends ADODB_DataDict {
var $databaseType = 'db2';
var $seqField = false;
+ var $dropCol = 'ALTER TABLE %s DROP COLUMN %s';
function ActualType($meta)
{
@@ -60,20 +60,66 @@ class ADODB2_db2 extends ADODB_DataDict {
return $suffix;
}
- function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
+ function alterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
{
- if ($this->debug) ADOConnection::outp("AlterColumnSQL not supported");
- return array();
+ $tabname = $this->TableName ($tabname);
+ $sql = array();
+ list($lines,$pkey,$idxs) = $this->_GenFields($flds);
+ // genfields can return FALSE at times
+ if ($lines == null) $lines = array();
+ $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
+
+ $dataTypeWords = array('SET','DATA','TYPE');
+
+ foreach($lines as $v)
+ {
+ /*
+ * We must now post-process the line to insert the 'SET DATA TYPE'
+ * text into the alter statement
+ */
+ $e = explode(' ',$v);
+
+ array_splice($e,1,0,$dataTypeWords);
+
+ $v = implode(' ',$e);
+
+ $sql[] = $alter . $v;
+ }
+ if (is_array($idxs))
+ {
+ foreach($idxs as $idx => $idxdef) {
+ $sql_idxs = $this->CreateIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
+ $sql = array_merge($sql, $sql_idxs);
+ }
+
+ }
+ return $sql;
}
- function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
+
+ function dropColumnSql($tabname, $flds, $tableflds='',$tableoptions='')
{
- if ($this->debug) ADOConnection::outp("DropColumnSQL not supported");
- return array();
+
+
+ $tabname = $this->connection->getMetaCasedValue($tabname);
+ $flds = $this->connection->getMetaCasedValue($flds);
+
+ if (ADODB_ASSOC_CASE == ADODB_ASSOC_CASE_NATIVE )
+ {
+ /*
+ * METACASE_NATIVE
+ */
+ $tabname = $this->connection->nameQuote . $tabname . $this->connection->nameQuote;
+ $flds = $this->connection->nameQuote . $flds . $this->connection->nameQuote;
+ }
+ $sql = sprintf($this->dropCol,$tabname,$flds);
+ return (array)$sql;
+
}
+
- function ChangeTableSQL($tablename, $flds, $tableoptions = false, $dropOldFlds=false)
+ function changeTableSQL($tablename, $flds, $tableoptions = false, $dropOldFields=false)
{
/**
@@ -85,17 +131,25 @@ class ADODB2_db2 extends ADODB_DataDict {
$validTypes = array("CHAR","VARC");
$invalidTypes = array("BIGI","BLOB","CLOB","DATE", "DECI","DOUB", "INTE", "REAL","SMAL", "TIME");
// check table exists
- $cols = $this->MetaColumns($tablename);
+
+
+ $cols = $this->metaColumns($tablename);
if ( empty($cols)) {
- return $this->CreateTableSQL($tablename, $flds, $tableoptions);
+ return $this->createTableSQL($tablename, $flds, $tableoptions);
}
// already exists, alter table instead
list($lines,$pkey) = $this->_GenFields($flds);
- $alter = 'ALTER TABLE ' . $this->TableName($tablename);
+ $alter = 'ALTER TABLE ' . $this->tableName($tablename);
$sql = array();
foreach ( $lines as $id => $v ) {
+ /*
+ * If the metaCasing was NATIVE the col returned with nameQuotes
+ * around the field. We need to remove this for the metaColumn
+ * match
+ */
+ $id = str_replace($this->connection->nameQuote,'',$id);
if ( isset($cols[$id]) && is_object($cols[$id]) ) {
/**
If the first field of $v is the fieldname, and
diff --git a/drivers/adodb-db2.inc.php b/drivers/adodb-db2.inc.php
index a56385d2..4b1bbb54 100644
--- a/drivers/adodb-db2.inc.php
+++ b/drivers/adodb-db2.inc.php
@@ -3,18 +3,25 @@
@version v5.21.0-dev ??-???-2016
@copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
@copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community
+ Released under both BSD license and Lesser GPL library license.
+ Whenever there is any discrepancy between the two licenses,
+ the BSD license will take precedence.
- This is a version of the ADODB driver for DB2. It uses the 'ibm_db2' PECL extension
- for PHP (http://pecl.php.net/package/ibm_db2), which in turn requires DB2 V8.2.2 or
- higher.
+ Set tabs to 4 for best viewing.
- Originally tested with PHP 5.1.1 and Apache 2.0.55 on Windows XP SP2.
- More recently tested with PHP 5.1.2 and Apache 2.0.55 on Windows XP SP2.
-
- This file was ported from "adodb-odbc.inc.php" by Larry Menard, "larry.menard#rogers.com".
- I ripped out what I believed to be a lot of redundant or obsolete code, but there are
- probably still some remnants of the ODBC support in this file; I'm relying on reviewers
- of this code to point out any other things that can be removed.
+* Driver for use with IBM DB2 Native Client
+*
+* Originally DB2 drivers were dependent on an ODBC driver, and some installations
+* may still use that. To use an ODBC driver connection, use the odbc_db2
+* ADOdb driver. For Linux, you need the 'ibm_db2' PECL extension for PHP,
+* For Windows, you need to locate an appropriate version of the php_ibm_db2.dll,
+* as well as the IBM data server client software.
+* This is basically a full rewrite of the original driver, for information
+* about all the changes, see the update information on the ADOdb website
+* for version 5.21.0
+*
+* @link http://pecl.php.net/package/ibm_db2 Pecl Extension For DB2
+* @author Mark Newnham
*/
// security - hide paths
@@ -22,11 +29,8 @@ if (!defined('ADODB_DIR')) die();
define("_ADODB_DB2_LAYER", 2 );
-/*--------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------*/
-
-
-
+/*--------------------------------------------------------------------
+----------------------------------------------------------------------*/
class ADODB_db2 extends ADOConnection {
@@ -45,101 +49,375 @@ class ADODB_db2 extends ADOConnection {
var $binmode = DB2_BINARY;
- var $useFetchArray = false; // setting this to true will make array elements in FETCH_ASSOC mode case-sensitive
- // breaking backward-compat
- var $_bindInputArray = false;
+ /*
+ * 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";
- var $_genSeqSQL = "CREATE SEQUENCE %s START WITH %s NO MAXVALUE NO CYCLE";
+ var $_genSeqSQL = "
+ CREATE SEQUENCE %s START WITH %s
+ NO MAXVALUE NO CYCLE INCREMENT BY 1 NO CACHE
+ ";
var $_dropSeqSQL = "DROP SEQUENCE %s";
var $_autocommit = true;
- var $_haserrorfunctions = true;
var $_lastAffectedRows = 0;
- var $uCaseTables = true; // for meta* functions, uppercase table names
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 = '"';
+
+ /*
+ * Executed after successful connection
+ */
+ public $connectStmt = '';
+
+ /*
+ * Holds the current database name
+ */
+ private $databaseName = '';
+ /*
+ * Holds information about the stored procedure request
+ * currently being built
+ */
+ private $storedProcedureParameters = false;
+
+
+ function __construct() {}
function _insertid()
{
return ADOConnection::GetOne('VALUES IDENTITY_VAL_LOCAL()');
}
- function __construct()
+ public function _connect($argDSN, $argUsername, $argPassword, $argDatabasename)
{
- $this->_haserrorfunctions = ADODB_PHPVER >= 0x4050;
+ return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename);
}
-
- // returns true or false
- function _connect($argDSN, $argUsername, $argPassword, $argDatabasename)
+
+ public function _pconnect($argDSN, $argUsername, $argPassword, $argDatabasename)
+ {
+ return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename,true);
+ }
+
+ private function doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename, $persistent=false)
{
+ global $php_errormsg;
+
if (!function_exists('db2_connect')) {
- ADOConnection::outp("Warning: The old ODBC based DB2 driver has been renamed 'odbc_db2'. This ADOdb driver calls PHP's native db2 extension which is not installed.");
+ ADOConnection::outp("DB2 extension not installed.");
+ return null;
+ }
+
+ $connectionParameters = $this->unpackParameters($argDSN,
+ $argUsername,
+ $argPassword,
+ $argDatabasename);
+
+ if ($connectionParameters == null)
+ {
+ /*
+ * Error thrown
+ */
return null;
}
- // This needs to be set before the connect().
- // Replaces the odbc_binmode() call that was in Execute()
+
+ $argDSN = $connectionParameters['dsn'];
+ $argUsername = $connectionParameters['uid'];
+ $argPassword = $connectionParameters['pwd'];
+ $argDatabasename = $connectionParameters['database'];
+ $useCataloguedConnection = $connectionParameters['catalogue'];
+
+ if ($this->debug){
+ 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);
+ }
+ /*
+ * This needs to be set before the connect().
+ */
ini_set('ibm_db2.binmode', $this->binmode);
- if ($argDatabasename && empty($argDSN)) {
+ if ($persistent)
+ $db2Function = 'db2_pconnect';
+ else
+ $db2Function = 'db2_connect';
+
+ /*
+ * We need to flatten out the connectionParameters
+ */
- if (stripos($argDatabasename,'UID=') && stripos($argDatabasename,'PWD=')) $this->_connectionID = db2_connect($argDatabasename,null,null);
- else $this->_connectionID = db2_connect($argDatabasename,$argUsername,$argPassword);
- } else {
- if ($argDatabasename) $schema = $argDatabasename;
- if (stripos($argDSN,'UID=') && stripos($argDSN,'PWD=')) $this->_connectionID = db2_connect($argDSN,null,null);
- else $this->_connectionID = db2_connect($argDSN,$argUsername,$argPassword);
+ $db2Options = array();
+ if ($this->connectionParameters)
+ {
+ foreach($this->connectionParameters as $p)
+ foreach($p as $k=>$v)
+ $db2Options[$k] = $v;
}
-
- // For db2_connect(), there is an optional 4th arg. If present, it must be
- // an array of valid options. So far, we don't use them.
+
+ if ($useCataloguedConnection)
+ $this->_connectionID = $db2Function($argDatabasename,
+ $argUsername,
+ $argPassword,
+ $db2Options);
+ else
+ $this->_connectionID = $db2Function($argDSN,
+ null,
+ null,
+ $db2Options);
+
+ $php_errormsg = '';
$this->_errorMsg = @db2_conn_errormsg();
- if (isset($this->connectStmt)) $this->Execute($this->connectStmt);
+
+ if ($this->_connectionID && $this->connectStmt)
+ $this->execute($this->connectStmt);
- if ($this->_connectionID && isset($schema)) $this->Execute("SET SCHEMA=$schema");
return $this->_connectionID != false;
- }
-
- // returns true or false
- function _pconnect($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)
{
- if (!function_exists('db2_connect')) return null;
-
- // This needs to be set before the connect().
- // Replaces the odbc_binmode() call that was in Execute()
- ini_set('ibm_db2.binmode', $this->binmode);
-
- $this->_errorMsg = '';
-
- if ($argDatabasename && empty($argDSN)) {
-
- if (stripos($argDatabasename,'UID=') && stripos($argDatabasename,'PWD=')) $this->_connectionID = db2_pconnect($argDatabasename,null,null);
- else $this->_connectionID = db2_pconnect($argDatabasename,$argUsername,$argPassword);
- } else {
- if ($argDatabasename) $schema = $argDatabasename;
- if (stripos($argDSN,'UID=') && stripos($argDSN,'PWD=')) $this->_connectionID = db2_pconnect($argDSN,null,null);
- else $this->_connectionID = db2_pconnect($argDSN,$argUsername,$argPassword);
+
+ global $php_errormsg;
+
+ $connectionParameters = array('dsn'=>'',
+ 'uid'=>'',
+ 'pwd'=>'',
+ 'database'=>'',
+ 'catalogue'=>true
+ );
+
+ /*
+ * 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;
+ */
+
+ if (!$argDSN && !$argDatabasename)
+ {
+ $errorMessage = 'Supply either catalogued or uncatalogued connection parameters';
+ $this->_errorMsg = $errorMessage;
+ if ($this->debug)
+ ADOConnection::outp($errorMessage);
+ return null;
+ }
+
+ $useCataloguedConnection = true;
+ $schemaName = '';
+
+ 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);
+
+ }
+ $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';
+ $php_errormsg = $errorMessage;
+ if ($this->debug)
+ ADOConnection::outp($errorMessage);
+ return null;
+ }
+ }
+
+ if (!$this->isDsn($argDSN) && $this->isDsn($argDatabasename))
+ {
+ /*
+ * Switch them around for next test
+ */
+ $temp = $argDSN;
+ $argDsn = $argDatabasename;
+ $argDatabasenME = $temp;
+ }
+
+ if ($this->isDsn($argDSN))
+ {
+
+ 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';
+ $php_errormsg = $errorMessage;
+ if ($this->debug)
+ ADOConnection::outp($errorMessage);
+ return null;
+ }
+
+ 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;
+
+ }
+ elseif ($argDatabasename)
+ {
+ $this->databaseName = $argDatabasename;
+ $argDSN .= ';database=' . $argDatabasename;
+ $argDatabasename = '';
+ $useCataloguedConnection = false;
+
+ }
+ else
+ {
+ $errorMessage = 'Uncatalogued connection parameters ';
+ $errorMessage.= 'must contain a database= argument';
+ $php_errormsg = $errorMessage;
+ if ($this->debug)
+ ADOConnection::outp($errorMessage);
+ return null;
+ }
}
+
+ if ($argDSN && !$argDatabasename && $useCataloguedConnection)
+ {
+ $argDatabasename = $argDSN;
+ $argDSN = '';
+ }
+
- $this->_errorMsg = @db2_conn_errormsg();
- if ($this->_connectionID && $this->autoRollback) @db2_rollback($this->_connectionID);
- if (isset($this->connectStmt)) $this->Execute($this->connectStmt);
+ 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->databaseName = $argDatabasename;
+ elseif (!$this->databaseName)
+ $this->databaseName = $this->getDatabasenameFromDsn($argDSN);
+
+
+ $connectionParameters = array('dsn'=>$argDSN,
+ 'uid'=>$argUsername,
+ 'pwd'=>$argPassword,
+ 'database'=>$argDatabasename,
+ 'catalogue'=>$useCataloguedConnection
+ );
+
+ return $connectionParameters;
+
+ }
- if ($this->_connectionID && isset($schema)) $this->Execute("SET SCHEMA=$schema");
- return $this->_connectionID != 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;
}
+
- // format and return date string in database timestamp format
- function DBTimeStamp($ts, $isfld = 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);
+
+ 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);
+ if (is_string($ts)) $ts = ADORecordSet::unixTimeStamp($ts);
return 'TO_DATE('.adodb_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
- function SQLDate($fmt, $col=false)
+ /**
+ * Format date column in sql string given an input format that understands Y M D
+ *
+ * @param string $fmt
+ * @param bool $col
+ *
+ * @return string
+ */
+ function sqlDate($fmt, $col=false)
{
- // use right() and replace() ?
if (!$col) $col = $this->sysDate;
/* use TO_CHAR() if $fmt is TO_CHAR() allowed fmt */
@@ -203,10 +481,12 @@ class ADODB_db2 extends ADOConnection {
}
- function ServerInfo()
+ function serverInfo()
{
- $row = $this->GetRow("SELECT service_level, fixpack_num FROM TABLE(sysproc.env_get_inst_info())
- as INSTANCEINFO");
+ $sql = "SELECT service_level, fixpack_num
+ FROM TABLE(sysproc.env_get_inst_info())
+ AS INSTANCEINFO";
+ $row = $this->GetRow($sql);
if ($row) {
@@ -214,108 +494,119 @@ class ADODB_db2 extends ADOConnection {
$info['fixpack'] = $row[1];
$info['description'] = '';
} else {
- return ADOConnection::ServerInfo();
+ return ADOConnection::serverInfo();
}
return $info;
}
- function CreateSequence($seqname='adodbseq',$start=1)
+ function createSequence($seqname='adodbseq',$start=1)
{
- if (empty($this->_genSeqSQL)) return false;
- $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname,$start));
- if (!$ok) return false;
+ if (empty($this->_genSeqSQL))
+ return false;
+
+ $ok = $this->execute(sprintf($this->_genSeqSQL,$seqname,$start));
+ if (!$ok)
+ return false;
return true;
}
- function DropSequence($seqname = 'adodbseq')
+ function dropSequence($seqname='adodbseq')
{
if (empty($this->_dropSeqSQL)) return false;
- return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
+ return $this->execute(sprintf($this->_dropSeqSQL,$seqname));
}
- function SelectLimit($sql, $nrows = -1, $offset = -1, $inputArr = false, $secs2cache = 0)
+ function selectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false,$secs2cache=0)
{
- $nrows = (int) $nrows;
- $offset = (int) $offset;
- if ($offset <= 0) {
- // could also use " OPTIMIZE FOR $nrows ROWS "
- if ($nrows >= 0) $sql .= " FETCH FIRST $nrows ROWS ONLY ";
- $rs = $this->Execute($sql,$inputArr);
- } else {
+ $nrows = (integer) $nrows;
+
+ if ($offset <= 0)
+ {
+ if ($nrows >= 0)
+ $sql .= " FETCH FIRST $nrows ROWS ONLY ";
+
+ $rs = $this->execute($sql,$inputArr);
+
+ }
+ else
+ {
if ($offset > 0 && $nrows < 0);
- else {
+
+ else
+ {
$nrows += $offset;
$sql .= " FETCH FIRST $nrows ROWS ONLY ";
}
- $rs = ADOConnection::SelectLimit($sql,-1,$offset,$inputArr);
+
+ /*
+ * DB2 has no native support for mid table offset
+ */
+ $rs = ADOConnection::selectLimit($sql,$nrows,$offset,$inputArr);
+
}
-
+
return $rs;
}
- /*
- This algorithm is not very efficient, but works even if table locking
- is not available.
-
- Will return false if unable to generate an ID after $MAXLOOPS attempts.
- */
- function GenID($seq='adodbseq',$start=1)
+
+ function errorMsg()
{
- // if you have to modify the parameter below, your database is overloaded,
- // or you need to implement generation of id's yourself!
- $num = $this->GetOne("VALUES NEXTVAL FOR $seq");
- return $num;
+ if ($this->_errorMsg !== false)
+ return $this->_errorMsg;
+
+ if (empty($this->_connectionID))
+ return @db2_conn_errormsg();
+
+ return @db2_conn_errormsg($this->_connectionID);
}
-
- function ErrorMsg()
+ function errorNo()
{
- if ($this->_haserrorfunctions) {
- if ($this->_errorMsg !== false) return $this->_errorMsg;
- if (empty($this->_connectionID)) return @db2_conn_errormsg();
- return @db2_conn_errormsg($this->_connectionID);
- } else return ADOConnection::ErrorMsg();
- }
- function ErrorNo()
- {
+ if ($this->_errorCode !== false)
+ return $this->_errorCode;
+
- if ($this->_haserrorfunctions) {
- if ($this->_errorCode !== false) {
- // bug in 4.0.6, error number can be corrupted string (should be 6 digits)
- return (strlen($this->_errorCode)<=2) ? 0 : $this->_errorCode;
- }
-
- if (empty($this->_connectionID)) $e = @db2_conn_error();
- else $e = @db2_conn_error($this->_connectionID);
-
- // bug in 4.0.6, error number can be corrupted string (should be 6 digits)
- // so we check and patch
- if (strlen($e)<=2) return 0;
- return $e;
- } else return ADOConnection::ErrorNo();
+ if (empty($this->_connectionID))
+ $e = @db2_conn_error();
+
+ else
+ $e = @db2_conn_error($this->_connectionID);
+
+ return $e;
}
- function BeginTrans()
+ function beginTrans()
{
- if (!$this->hasTransactions) return false;
- if ($this->transOff) return true;
+ if (!$this->hasTransactions)
+ return false;
+ if ($this->transOff)
+ return true;
+
$this->transCnt += 1;
+
$this->_autocommit = false;
+
return db2_autocommit($this->_connectionID,false);
}
function CommitTrans($ok=true)
{
- if ($this->transOff) return true;
- if (!$ok) return $this->RollbackTrans();
- if ($this->transCnt) $this->transCnt -= 1;
+ if ($this->transOff)
+ return true;
+
+ if (!$ok)
+ return $this->RollbackTrans();
+
+ if ($this->transCnt)
+ $this->transCnt -= 1;
+
$this->_autocommit = true;
- $ret = db2_commit($this->_connectionID);
- db2_autocommit($this->_connectionID,true);
+ $ret = @db2_commit($this->_connectionID);
+ @db2_autocommit($this->_connectionID,true);
return $ret;
}
@@ -324,123 +615,400 @@ class ADODB_db2 extends ADOConnection {
if ($this->transOff) return true;
if ($this->transCnt) $this->transCnt -= 1;
$this->_autocommit = true;
- $ret = db2_rollback($this->_connectionID);
- db2_autocommit($this->_connectionID,true);
+ $ret = @db2_rollback($this->_connectionID);
+ @db2_autocommit($this->_connectionID,true);
return $ret;
}
- function MetaPrimaryKeys($table, $owner = 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)
{
- global $ADODB_FETCH_MODE;
+
+ $primaryKeys = array();
+
+ global $ADODB_FETCH_MODE;
- if ($this->uCaseTables) $table = strtoupper($table);
$schema = '';
$this->_findschema($table,$schema);
+
+ $table = $this->getTableCasedValue($table);
- $savem = $ADODB_FETCH_MODE;
+ $savem = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
- $qid = @db2_primarykeys($this->_connectionID,'',$schema,$table);
+ $this->setFetchMode(ADODB_FETCH_NUM);
- if (!$qid) {
- $ADODB_FETCH_MODE = $savem;
- return false;
- }
- $rs = new ADORecordSet_db2($qid);
- $ADODB_FETCH_MODE = $savem;
- if (!$rs) return false;
+ $sql = "SELECT *
+ FROM syscat.indexes
+ WHERE tabname='$table'";
+
+ $rows = $this->getAll($sql);
+
+ $this->setFetchMode($savem);
+ $ADODB_FETCH_MODE = $savem;
- $arr = $rs->GetArray();
- $rs->Close();
- $arr2 = array();
- for ($i=0; $i < sizeof($arr); $i++) {
- if ($arr[$i][3]) $arr2[] = $arr[$i][3];
+ if (empty($rows))
+ return false;
+
+ 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 $arr2;
+ return $primaryKeys;
}
- function MetaForeignKeys($table, $owner = FALSE, $upper = FALSE, $asociative = FALSE )
+ /**
+ * returns assoc array where keys are tables, and values are foreign keys
+ *
+ * @param string $table
+ * @param string $owner [optional][discarded]
+ * @param bool $upper [optional][discarded]
+ * @param bool $associative[optional][discarded]
+ *
+ * @return mixed[] Array of foreign key information
+ */
+ public function metaForeignKeys($table, $owner = FALSE, $upper = FALSE, $asociative = FALSE )
{
- global $ADODB_FETCH_MODE;
+
+ global $ADODB_FETCH_MODE;
- if ($this->uCaseTables) $table = strtoupper($table);
$schema = '';
$this->_findschema($table,$schema);
$savem = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
- $qid = @db2_foreign_keys($this->_connectionID,'',$schema,$table);
- if (!$qid) {
- $ADODB_FETCH_MODE = $savem;
+
+ $this->setFetchMode(ADODB_FETCH_NUM);
+
+ $sql = "SELECT SUBSTR(tabname,1,20) table_name,
+ SUBSTR(constname,1,20) fk_name,
+ SUBSTR(REFTABNAME,1,12) parent_table,
+ SUBSTR(refkeyname,1,20) pk_orig_table,
+ fk_colnames
+ FROM syscat.references
+ WHERE tabname = '$table'";
+
+ $results = $this->getAll($sql);
+
+ $ADODB_FETCH_MODE = $savem;
+ $this->setFetchMode($savem);
+
+ if (empty($results))
return false;
+
+ $foreignKeys = array();
+
+ foreach ($results as $r)
+ {
+ $parentTable = trim($this->getMetaCasedValue($r[2]));
+ $keyName = trim($this->getMetaCasedValue($r[1]));
+ $foreignKeys[$parentTable] = $keyName;
}
- $rs = new ADORecordSet_db2($qid);
+
+ return $foreignKeys;
+ }
- $ADODB_FETCH_MODE = $savem;
+ /**
+ * 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;
+ $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
+
/*
- $rs->fields indices
- 0 PKTABLE_CAT
- 1 PKTABLE_SCHEM
- 2 PKTABLE_NAME
- 3 PKCOLUMN_NAME
- 4 FKTABLE_CAT
- 5 FKTABLE_SCHEM
- 6 FKTABLE_NAME
- 7 FKCOLUMN_NAME
+ * 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'
*/
- if (!$rs) return false;
-
- $foreign_keys = array();
- while (!$rs->EOF) {
- if (strtoupper(trim($rs->fields[2])) == $table && (!$schema || strtoupper($rs->fields[1]) == $schema)) {
- if (!is_array($foreign_keys[$rs->fields[5].'.'.$rs->fields[6]]))
- $foreign_keys[$rs->fields[5].'.'.$rs->fields[6]] = array();
- $foreign_keys[$rs->fields[5].'.'.$rs->fields[6]][$rs->fields[7]] = $rs->fields[3];
- }
- $rs->MoveNext();
+ $ttype = strtoupper($ttype);
+ if ($ttype) {
+ /*
+ * @todo We could do valid type checking or array type
+ */
+ if ($ttype == 'V')
+ $ttype = 'VIEW';
+ if ($ttype == 'T')
+ $ttype = 'TABLE';
}
+
+ if (!$schema)
+ $schema = '%';
+
+ if (!$mask)
+ $mask = '%';
+
+ $qid = @db2_tables($this->_connectionID,NULL,$schema,$mask,$ttype);
+ $rs = new ADORecordSet_db2($qid);
+
+ $ADODB_FETCH_MODE = $savem;
+
+ if (!$rs)
+ return false;
+
+ $arr = $rs->getArray();
+
$rs->Close();
- return $foreign_key;
- }
+
+ $tableList = array();
+ /*
+ * Array items
+ * ---------------------------------
+ * 0 TABLE_CAT The catalog that contains the table.
+ * The value is NULL if this table does not have catalogs.
+ * 1 TABLE_SCHEM Name of the schema that contains the table.
+ * 2 TABLE_NAME Name of the table.
+ * 3 TABLE_TYPE Table type identifier for the table.
+ * 4 REMARKS Description of the table.
+ */
+
+ for ($i=0; $i < sizeof($arr); $i++)
+ {
+
+ $tableRow = $arr[$i];
+ $tableName = $tableRow[2];
+ $tableType = $tableRow[3];
+
+ if (!$tableName)
+ continue;
+
+ if ($ttype == '' && (strcmp($tableType,'TABLE') <> 0 && strcmp($tableType,'VIEW') <> 0))
+ continue;
+
+ /*
+ * Set metacasing if required
+ */
+ $tableName = $this->getMetaCasedValue($tableName);
+
+ /*
+ * If we requested a schema, we prepend the schema
+ name to the table name
+ */
+ if (strcmp($schema,'%') <> 0)
+ $tableName = $schema . '.' . $tableName;
+
+ $tableList[] = $tableName;
+
+ }
+ return $tableList;
+ }
+
+ /**
+ * 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;
- function MetaTables($ttype = false, $schema = false, $mask = false)
- {
- global $ADODB_FETCH_MODE;
+ /* Array(
+ * [name_of_index] => Array(
+ * [unique] => true or false
+ * [columns] => Array(
+ * [0] => firstcol
+ * [1] => nextcol
+ * [2] => etc........
+ * )
+ * )
+ * )
+ */
+ $indices = array();
+ $primaryKeyName = '';
+
+ $table = $this->getTableCasedValue($table);
- $savem = $ADODB_FETCH_MODE;
+
+ $savem = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
- $qid = db2_tables($this->_connectionID);
-
- $rs = new ADORecordSet_db2($qid);
+ $this->setFetchMode(ADODB_FETCH_NUM);
+ $sql = "SELECT *
+ FROM syscat.indexes
+ WHERE tabname='$table'";
+
+ $rows = $this->getAll($sql);
+
+ $this->setFetchMode($savem);
$ADODB_FETCH_MODE = $savem;
- if (!$rs) {
- $false = false;
- return $false;
- }
-
- $arr = $rs->GetArray();
- $rs->Close();
- $arr2 = array();
+
+ if (empty($rows))
+ return false;
- if ($ttype) {
- $isview = strncmp($ttype,'V',1) === 0;
+ foreach ($rows as $r)
+ {
+
+ $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;
+ }
+
}
- for ($i=0; $i < sizeof($arr); $i++) {
- if (!$arr[$i][2]) continue;
- $type = $arr[$i][3];
- $owner = $arr[$i][1];
- $schemaval = ($schema) ? $arr[$i][1].'.' : '';
- if ($ttype) {
- if ($isview) {
- if (strncmp($type,'V',1) === 0) $arr2[] = $schemaval.$arr[$i][2];
- } else if (strncmp($owner,'SYS',3) !== 0) $arr2[] = $schemaval.$arr[$i][2];
- } else if (strncmp($owner,'SYS',3) !== 0) $arr2[] = $schemaval.$arr[$i][2];
+
+ 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)
+
+ * @return array of procedures on current database.
+ *
+ */
+ public function metaProcedures($procedureNamePattern = null, $catalog = null, $schemaPattern = null) {
+
+
+ global $ADODB_FETCH_MODE;
+
+ $metaProcedures = array();
+ $procedureSQL = '';
+ $catalogSQL = '';
+ $schemaSQL = '';
+
+ $savem = $ADODB_FETCH_MODE;
+ $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
+
+ if ($procedureNamePattern)
+ $procedureSQL = "AND ROUTINENAME LIKE " . strtoupper($this->qstr($procedureNamePattern));
+
+ if ($catalog)
+ $catalogSQL = "AND OWNER=" . strtoupper($this->qstr($catalog));
+
+ 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";
+
+ $SQL = "SELECT $fields
+ FROM syscat.routines
+ WHERE OWNER IS NOT NULL
+ $procedureSQL
+ $catalogSQL
+ $schemaSQL
+ ORDER BY ROUTINENAME
+ ";
+
+ $result = $this->execute($SQL);
+
+ $ADODB_FETCH_MODE = $savem;
+
+ 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]
+ );
}
- return $arr2;
+
+ return $metaProcedures;
+
+ }
+
+ /**
+ * Lists databases. Because instances are independant, we only know about
+ * the current database name
+ *
+ * @return string[]
+ */
+ public function metaDatabases(){
+
+ $dbName = $this->getMetaCasedValue($this->databaseName);
+
+ return (array)$dbName;
+
}
+
+
+
/*
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2datetime_data_type_changes.asp
@@ -510,26 +1078,32 @@ See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2
}
}
- function MetaColumns($table, $normalize=true)
+ public function metaColumns($table, $normalize=true)
{
- global $ADODB_FETCH_MODE;
-
- $false = false;
- if ($this->uCaseTables) $table = strtoupper($table);
- $schema = '';
- $this->_findschema($table,$schema);
-
+ global $ADODB_FETCH_MODE;
+
$savem = $ADODB_FETCH_MODE;
- $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
- $colname = "%";
- $qid = db2_columns($this->_connectionID, "", $schema, $table, $colname);
- if (empty($qid)) return $false;
+ $schema = '%';
+ $this->_findschema($table,$schema);
+ $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);
- $ADODB_FETCH_MODE = $savem;
- if (!$rs) return $false;
+ if (!$rs)
+ return false;
+
$rs->_fetch();
$retarr = array();
@@ -548,15 +1122,25 @@ See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2
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 (strtoupper(trim($rs->fields[2])) == $table && (!$schema || strtoupper($rs->fields[1]) == $schema)) {
- $fld = new ADOFieldObject();
+ while (!$rs->EOF)
+ {
+ if ($rs->fields[2] == $table)
+ {
+
+ $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
+
if ($fld->type == 'C' or $fld->type == 'X') {
if ($rs->fields[4] <= -95) // UNICODE
$fld->max_length = $rs->fields[7]/2;
@@ -564,24 +1148,42 @@ See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2
$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 = false;
- $retarr[strtoupper($fld->name)] = $fld;
- } else if (sizeof($retarr)>0)
+
+ $fld->not_null = !empty($rs->fields[10]);
+ $fld->scale = $rs->fields[8];
+ $fld->primary_key = false;
+
+ //$columnName = $this->getMetaCasedValue($fld->name);
+ $columnName = strtoupper($fld->name);
+ $retarr[$columnName] = $fld;
+
+ }
+ else if (sizeof($retarr)>0)
break;
+
$rs->MoveNext();
+
}
+
$rs->Close();
- if (empty($retarr)) $retarr = false;
+ if (empty($retarr))
+ $retarr = false;
- $qid = db2_primary_keys($this->_connectionID, "", $schema, $table);
- if (empty($qid)) return $false;
+ /*
+ * Now we find out if the column is part of a primary key
+ */
+
+ $qid = @db2_primary_keys($this->_connectionID, "", $schema, $table);
+ if (empty($qid))
+ return false;
$rs = new ADORecordSet_db2($qid);
- $ADODB_FETCH_MODE = $savem;
- if (!$rs) return $retarr;
+ if (!$rs)
+ {
+ $ADODB_FETCH_MODE = $savem;
+ return $retarr;
+ }
$rs->_fetch();
/*
@@ -594,23 +1196,371 @@ See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2
5 PK_NAME
*/
while (!$rs->EOF) {
- if (strtoupper(trim($rs->fields[2])) == $table && (!$schema || strtoupper($rs->fields[1]) == $schema)) {
+ 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)
+ }
+ else if (sizeof($retarr)>0)
break;
+
$rs->MoveNext();
}
$rs->Close();
- if (empty($retarr)) $retarr = false;
+ $ADODB_FETCH_MODE = $savem;
+
+ 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);
+
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) {
+
+ global $ADODB_FETCH_MODE;
+
+ $this->storedProcedureParameters = array('name'=>'',
+ 'resource'=>false,
+ 'in'=>array(),
+ 'out'=>array(),
+ 'index'=>array(),
+ 'parameters'=>array(),
+ 'keyvalue' => array());
+
+ //$procedureName = strtoupper($procedureName);
+ //$procedureName = $this->getTableCasedValue($procedureName);
+
+ $savem = $ADODB_FETCH_MODE;
+ $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
+
+ $qid = db2_procedures($this->_connectionID, NULL , '%' , $procedureName );
+
+ $ADODB_FETCH_MODE = $savem;
+
+ 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;
+
+ $qid = db2_procedure_columns($this->_connectionID, NULL , '%' , $procedureName , NULL );
+
+ $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;
+
+ $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,'?');
+
+ /*
+ * We now know how many parameters to bind to the stored procedure
+ */
+ $parameterList = implode(',',$parameterMarkers);
+
+ $sql = sprintf($preparedStatement,$procedureName,$parameterList);
+
+ $spResource = @db2_prepare($this->_connectionID,$sql);
+
+ if (!$spResource)
+ {
+ $errorMessage = @db2_conn_errormsg($this->_connectionID);
+ $this->_errorMsg = $errorMessage;
+
+ if ($this->debug)
+ ADOConnection::outp($errorMessage);
+
+ return false;
+ }
+
+ $this->storedProcedureParameters['resource'] = $spResource;
+
+ 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 compatibilty. Everything
+ * will be handled by the storedProcedureParameters
+ * array
+ */
+ return array($sql,$spResource);
+
+ }
+
+ private function storedProcedureParameter(&$stmt,
+ &$var,
+ $name,
+ $isOutput=false,
+ $maxLen=4000,
+ $type=false)
+ {
+
+
+ $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);
+
+ $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);
+
+ $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;
+
+ 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()
+ {
+
+ /*
+ * Get the previously built resource
+ */
+ $stmtid = $this->storedProcedureParameters['resource'];
+
+ /*
+ * 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 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
+ );
+
+ if (!$ok)
+ {
+ $this->_errorMsg = @db2_stmt_errormsg();
+ $this->_errorCode = @db2_stmt_error();
- function Prepare($sql)
+ if ($this->debug)
+ ADOConnection::outp($this->_errorMsg);
+ return false;
+ }
+
+ 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;
+
+ }
+
+ /*
+ * All bound, execute
+ */
+
+ if (!@db2_execute($stmtid))
+ {
+ $this->_errorMsg = @db2_stmt_errormsg();
+ $this->_errorCode = @db2_stmt_error();
+
+ 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;
+ }
+
+ 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)
{
+
+ /*
+ * 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");
+ }
+
+
+ /**
+ * 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
- $stmt = db2_prepare($this->_connectionID,$sql);
+
+ $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;
@@ -618,66 +1568,158 @@ See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2
return array($sql,$stmt,false);
}
- /* returns queryID or false */
- function _query($sql,$inputarr=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)
{
- $last_php_error = $this->resetLastError();
- $this->_errorMsg = '';
+
+ GLOBAL $php_errormsg;
+
+ if (isset($php_errormsg))
+ $php_errormsg = '';
+ $this->_error = '';
- if ($inputarr) {
- if (is_array($sql)) {
+ $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;
+
+ default:
+ $db2Options = array('db2_attr_case'=>DB2_CASE_NATURAL);
+ $setOption = @db2_set_option($this->_connectionID,$db2Options,1);
+ }
+
+ $db2Options = array('db2_attr_case'=>DB2_CASE_LOWER);
+ $setOption = @db2_set_option($this->_connectionID,$db2Options,1);
+
+ if ($inputarr)
+ {
+ if (is_array($sql))
+ {
$stmtid = $sql[1];
- } else {
- $stmtid = db2_prepare($this->_connectionID,$sql);
+ }
+ else
+ {
+ $stmtid = @db2_prepare($this->_connectionID,$sql);
- if ($stmtid == false) {
- $this->_errorMsg = $this->getChangedErrorMsg($last_php_error);
+ if ($stmtid == false)
+ {
+ $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : '';
return false;
}
}
- if (! db2_execute($stmtid,$inputarr)) {
- if ($this->_haserrorfunctions) {
- $this->_errorMsg = db2_stmt_errormsg();
- $this->_errorCode = db2_stmt_error();
- }
+ 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)) {
- $stmtid = $sql[1];
- if (!db2_execute($stmtid)) {
- if ($this->_haserrorfunctions) {
- $this->_errorMsg = db2_stmt_errormsg();
- $this->_errorCode = db2_stmt_error();
+ }
+ else if (is_array($sql))
+ {
+
+ /*
+ * 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();
+
+ /*
+ * 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;
}
+ }
+
+ if (!@db2_execute($stmtid))
+ {
+ $this->_errorMsg = @db2_stmt_errormsg();
+ $this->_errorCode = @db2_stmt_error();
+ if ($this->debug)
+ ADOConnection::outp($this->_errorMsg);
return false;
}
- } else
- $stmtid = @db2_exec($this->_connectionID,$sql);
+
+ return $stmtid;
+ }
+ else
+ {
+ $stmtid = @db2_exec($this->_connectionID,$sql);
+ }
$this->_lastAffectedRows = 0;
- if ($stmtid) {
- if (@db2_num_fields($stmtid) == 0) {
+ if ($stmtid)
+ {
+ if (@db2_num_fields($stmtid) == 0)
+ {
$this->_lastAffectedRows = db2_num_rows($stmtid);
$stmtid = true;
- } else {
+ }
+ else
+ {
$this->_lastAffectedRows = 0;
}
- if ($this->_haserrorfunctions) {
- $this->_errorMsg = '';
- $this->_errorCode = 0;
- } else {
- $this->_errorMsg = $this->getChangedErrorMsg($last_php_error);
- }
- } else {
- if ($this->_haserrorfunctions) {
- $this->_errorMsg = db2_stmt_errormsg();
- $this->_errorCode = db2_stmt_error();
- } else {
- $this->_errorMsg = $this->getChangedErrorMsg($last_php_error);
- }
+ $this->_errorMsg = '';
+ $this->_errorCode = 0;
+
+ }
+ else
+ {
+
+ $this->_errorMsg = @db2_stmt_errormsg();
+ $this->_errorCode = @db2_stmt_error();
+
}
return $stmtid;
}
@@ -688,12 +1730,12 @@ See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2
Usage:
- $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
+ $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')
+ function updateBlob($table,$column,$val,$where,$blobtype='BLOB')
{
- return $this->Execute("UPDATE $table SET $column=? WHERE $where",array($val)) != false;
+ return $this->execute("UPDATE $table SET $column=? WHERE $where",array($val)) != false;
}
// returns true or false
@@ -708,6 +1750,87 @@ See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2
{
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;
+
+ 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;
+
+ /*
+ * 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;
+ }
+
+ /**
+ * 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;
+ }
}
@@ -735,21 +1858,30 @@ class ADORecordSet_db2 extends ADORecordSet {
// returns the field object
- function FetchField($offset = -1)
+ 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);
+ $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;
}
/* Use associative array to get fields array */
- function Fields($colname)
+ 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++) {
@@ -764,11 +1896,15 @@ class ADORecordSet_db2 extends ADORecordSet {
function _initrs()
{
- global $ADODB_COUNTRECS;
+ global $ADODB_COUNTRECS;
$this->_numOfRows = ($ADODB_COUNTRECS) ? @db2_num_rows($this->_queryID) : -1;
+
$this->_numOfFields = @db2_num_fields($this->_queryID);
+
// some silly drivers such as db2 as/400 and intersystems cache return _numOfRows = 0
- if ($this->_numOfRows == 0) $this->_numOfRows = -1;
+
+ if ($this->_numOfRows == 0)
+ $this->_numOfRows = -1;
}
function _seek($row)
@@ -776,21 +1912,15 @@ class ADORecordSet_db2 extends ADORecordSet {
return false;
}
- // speed up SelectLimit() by switching to ADODB_FETCH_NUM as ADODB_FETCH_ASSOC is emulated
- function GetArrayLimit($nrows,$offset=-1)
+ function getArrayLimit($nrows,$offset=0)
{
if ($offset <= 0) {
$rs = $this->GetArray($nrows);
return $rs;
}
- $savem = $this->fetchMode;
- $this->fetchMode = ADODB_FETCH_NUM;
+
$this->Move($offset);
- $this->fetchMode = $savem;
- if ($this->fetchMode & ADODB_FETCH_ASSOC) {
- $this->fields = $this->GetRowAssoc();
- }
$results = array();
$cnt = 0;
@@ -798,46 +1928,81 @@ class ADORecordSet_db2 extends ADORecordSet {
$results[$cnt++] = $this->fields;
$this->MoveNext();
}
-
+
return $results;
}
-
- function MoveNext()
+ function moveNext()
{
- if ($this->_numOfRows != 0 && !$this->EOF) {
- $this->_currentRow++;
-
+ if ($this->EOF || $this->_numOfRows == 0)
+ return false;
+
+ $this->_currentRow++;
+
+ $this->processCoreFetch();
+ return $this->processMoveRecord();
+
+ }
+
+ final private function processCoreFetch()
+ {
+ switch ($this->fetchMode){
+ case ADODB_FETCH_ASSOC:
+
+ /*
+ * 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);
- if ($this->fields) {
- if ($this->fetchMode & ADODB_FETCH_ASSOC) {
- $this->fields = $this->GetRowAssoc();
- }
- return true;
- }
+ break;
}
- $this->fields = false;
- $this->EOF = true;
- return false;
- }
+ }
- function _fetch()
+ final private function processMoveRecord()
+ {
+ if (!$this->fields){
+ $this->EOF = true;
+ return false;
+ }
+
+ return true;
+ }
+
+ function _fetch()
{
-
- $this->fields = db2_fetch_array($this->_queryID);
- if ($this->fields) {
- if ($this->fetchMode & ADODB_FETCH_ASSOC) {
- $this->fields = $this->GetRowAssoc();
- }
+ $this->processCoreFetch();
+ if ($this->fields)
return true;
- }
+
$this->fields = false;
return false;
}
function _close()
{
- return @db2_free_result($this->_queryID);
+ $ok = @db2_free_result($this->_queryID);
+ if (!$ok)
+ {
+ $this->_errorMsg = @db2_stmt_errormsg($this->_queryId);
+ $this->_errorCode = @db2_stmt_error();
+
+ if ($this->debug)
+ ADOConnection::outp($this->_errorMsg);
+ return false;
+ }
+
}
-}
+} \ No newline at end of file
generated by cgit v1.3 (git 2.53.0) at 2026-07-06 19:57:25 +0000