doDB2Connect($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("DB2 extension not installed."); return null; } $connectionParameters = $this->unpackParameters($argDSN, $argUsername, $argPassword, $argDatabasename); if ($connectionParameters == null) { /* * Error thrown */ return null; } $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 ($persistent) $db2Function = 'db2_pconnect'; else $db2Function = 'db2_connect'; /* * We need to flatten out the connectionParameters */ $db2Options = array(); if ($this->connectionParameters) { foreach($this->connectionParameters as $p) foreach($p as $k=>$v) $db2Options[$k] = $v; } 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 ($this->_connectionID && $this->connectStmt) $this->execute($this->connectStmt); 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) { 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 = ''; } 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; } /** * 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); 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('.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 * * @param string $fmt * @param bool $col * * @return string */ function sqlDate($fmt, $col=false) { if (!$col) $col = $this->sysDate; /* 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')"; $s = ''; $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)"; $s .= "char(year($col))"; break; case 'M': if ($len==1) return "monthname($col)"; $s .= "substr(monthname($col),1,3)"; break; case 'm': if ($len==1) return "month($col)"; $s .= "right(digits(month($col)),2)"; break; case 'D': case 'd': if ($len==1) return "day($col)"; $s .= "right(digits(day($col)),2)"; break; case 'H': case 'h': if ($len==1) return "hour($col)"; if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)"; else $s .= "''"; break; case 'i': case 'I': if ($len==1) return "minute($col)"; if ($col != $this->sysDate) $s .= "right(digits(minute($col)),2)"; else $s .= "''"; break; case 'S': case 's': if ($len==1) return "second($col)"; if ($col != $this->sysDate) $s .= "right(digits(second($col)),2)"; else $s .= "''"; break; default: if ($ch == '\\') { $i++; $ch = substr($fmt,$i,1); } $s .= $this->qstr($ch); } } return $s; } function serverInfo() { $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(); } return $info; } function createSequence($seqname='adodbseq',$start=1) { if (empty($this->_genSeqSQL)) return false; $ok = $this->execute(sprintf($this->_genSeqSQL,$seqname,$start)); if (!$ok) return false; return true; } function dropSequence($seqname='adodbseq') { if (empty($this->_dropSeqSQL)) return false; return $this->execute(sprintf($this->_dropSeqSQL,$seqname)); } function selectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false,$secs2cache=0) { $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 { $nrows += $offset; $sql .= " FETCH FIRST $nrows ROWS ONLY "; } /* * DB2 has no native support for mid table offset */ $rs = ADOConnection::selectLimit($sql,$nrows,$offset,$inputArr); } return $rs; } function errorMsg() { if ($this->_errorMsg !== false) return $this->_errorMsg; if (empty($this->_connectionID)) return @db2_conn_errormsg(); return @db2_conn_errormsg($this->_connectionID); } function errorNo() { if ($this->_errorCode !== false) return $this->_errorCode; if (empty($this->_connectionID)) $e = @db2_conn_error(); else $e = @db2_conn_error($this->_connectionID); return $e; } function beginTrans() { 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; $this->_autocommit = true; $ret = @db2_commit($this->_connectionID); @db2_autocommit($this->_connectionID,true); return $ret; } 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 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) { $primaryKeys = array(); global $ADODB_FETCH_MODE; $schema = ''; $this->_findschema($table,$schema); $table = $this->getTableCasedValue($table); $savem = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $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 (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 $primaryKeys; } /** * 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; $schema = ''; $this->_findschema($table,$schema); $savem = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $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; } 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; $savem = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; /* * 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' */ $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(); $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; /* 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; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $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 (empty($rows)) return false; 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; } } 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 $metaProcedures; } /** * Lists databases. Because instances are independent, 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 / SQL data type codes / #define SQL_UNKNOWN_TYPE 0 #define SQL_CHAR 1 #define SQL_NUMERIC 2 #define SQL_DECIMAL 3 #define SQL_INTEGER 4 #define SQL_SMALLINT 5 #define SQL_FLOAT 6 #define SQL_REAL 7 #define SQL_DOUBLE 8 #if (DB2VER >= 0x0300) #define SQL_DATETIME 9 #endif #define SQL_VARCHAR 12 / One-parameter shortcuts for date/time data types / #if (DB2VER >= 0x0300) #define SQL_TYPE_DATE 91 #define SQL_TYPE_TIME 92 #define SQL_TYPE_TIMESTAMP 93 #define SQL_UNICODE (-95) #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'; case 9: case 91: return 'D'; case 10: case 11: case 92: case 93: return 'T'; case 4: case 5: case -6: return 'I'; case -11: // uniqidentifier return 'R'; case -7: //bit return 'L'; default: return 'N'; } } public function metaColumns($table, $normalize=true) { global $ADODB_FETCH_MODE; $savem = $ADODB_FETCH_MODE; $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); if (!$rs) return false; $rs->_fetch(); $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) { 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; 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 = 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; /* * 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); 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) { 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(); $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 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) { $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(); 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); 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) { GLOBAL $php_errormsg; if (isset($php_errormsg)) $php_errormsg = ''; $this->_error = ''; $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); if ($stmtid == false) { $this->_errorMsg = isset($php_errormsg) ? $php_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)) { /* * 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; } 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; } $this->_errorMsg = ''; $this->_errorCode = 0; } else { $this->_errorMsg = @db2_stmt_errormsg(); $this->_errorCode = @db2_stmt_error(); } return $stmtid; } /* Insert a null into the blob field of the table first. Then use UpdateBlob to store the blob. 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",array($val)) != false; } // returns true or false function _close() { $ret = @db2_close($this->_connectionID); $this->_connectionID = false; return $ret; } 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; 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; } } /*-------------------------------------------------------------------------------------- Class Name: Recordset --------------------------------------------------------------------------------------*/ class ADORecordSet_db2 extends ADORecordSet { var $bind = false; var $databaseType = "db2"; var $dataProvider = "db2"; var $useFetchArray; function __construct($id,$mode=false) { if ($mode === false) { global $ADODB_FETCH_MODE; $mode = $ADODB_FETCH_MODE; } $this->fetchMode = $mode; $this->_queryID = $id; } // 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; } /* Use associative array to get fields array */ function 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; } } return $this->fields[$this->bind[strtoupper($colname)]]; } function _initrs() { 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; } function _seek($row) { return false; } function getArrayLimit($nrows,$offset=0) { if ($offset <= 0) { $rs = $this->GetArray($nrows); return $rs; } $this->Move($offset); $results = array(); $cnt = 0; while (!$this->EOF && $nrows != $cnt) { $results[$cnt++] = $this->fields; $this->MoveNext(); } return $results; } function moveNext() { 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); break; } } final private function processMoveRecord() { if (!$this->fields){ $this->EOF = true; return false; } return true; } function _fetch() { $this->processCoreFetch(); if ($this->fields) return true; $this->fields = false; return false; } function _close() { $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; } } }