/**
* {@inheritdoc}
*/
public function bindParam($column, &$variable, $type = null)
{
$this->_bindParam[$column] =& $variable;
if ($type && isset(self::$_typeMap[$type])) {
$type = self::$_typeMap[$type];
} else {
$type = DB2_CHAR;
}
if (!db2_bind_param($this->_stmt, $column, "variable", DB2_PARAM_IN, $type)) {
throw new DB2Exception(db2_stmt_errormsg());
}
return true;
}
/**
* Binds a parameter to the specified variable name.
*
* @param mixed $parameter Name the parameter, either integer or string.
* @param mixed $variable Reference to PHP variable containing the value.
* @param mixed $type OPTIONAL Datatype of SQL parameter.
* @param mixed $length OPTIONAL Length of SQL parameter.
* @param mixed $options OPTIONAL Other options.
* @return bool
* @throws \Zend\Db\Statement\Db2Exception
*/
public function _bindParam($parameter, &$variable, $type = null, $length = null, $options = null)
{
if ($type === null) {
$type = Db2_PARAM_IN;
}
if (isset($options['data-type'])) {
$datatype = $options['data-type'];
} else {
$datatype = Db2_CHAR;
}
if (!db2_bind_param($this->_stmt, $position, "variable", $type, $datatype)) {
throw new Db2Exception(db2_stmt_errormsg(), db2_stmt_error());
}
return true;
}
/**
* (non-PHPdoc)
* @see PreparedStatement::executePreparedStatement()
*/
public function executePreparedStatement(array $data, $msg = '')
{
if (!$this->stmt) {
$this->DBM->registerError($msg, "No prepared statement to execute");
return false;
}
$this->DBM->countQuery($this->parsedSQL);
$GLOBALS['log']->info("Executing Query: {$this->parsedSQL} with " . var_export($data, true));
$i = 0;
foreach ($data as $v) {
$i++;
$k = 'bind_' . $i;
${$k} = $v;
db2_bind_param($this->stmt, $i, $k, DB2_PARAM_IN);
}
$this->query_time = microtime(true);
$res = db2_execute($this->stmt);
return $this->finishStatement($res, $msg);
}
/**
* Binds a parameter to the specified variable name.
*
* @param mixed $parameter Name the parameter, either integer or string.
* @param mixed $variable Reference to PHP variable containing the value.
* @param mixed $type OPTIONAL Datatype of SQL parameter.
* @param mixed $length OPTIONAL Length of SQL parameter.
* @param mixed $options OPTIONAL Other options.
* @return bool
* @throws Zend_Db_Statement_Db2_Exception
*/
public function _bindParam($parameter, &$variable, $type = null, $length = null, $options = null)
{
if ($type === null) {
$type = DB2_PARAM_IN;
}
if (isset($options['data-type'])) {
$datatype = $options['data-type'];
} else {
$datatype = DB2_CHAR;
}
if (!db2_bind_param($this->_stmt, $position, "variable", $type, $datatype)) {
/**
* @see Zend_Db_Statement_Db2_Exception
*/
require_once PHP_LIBRARY_PATH . 'Zend/Db/Statement/Db2/Exception.php';
throw new Zend_Db_Statement_Db2_Exception(db2_stmt_errormsg(), db2_stmt_error());
}
return true;
}
/**
* For faking prepared SQL statements on DBs that don't support
* it directly.
* @param $preparedQuery String: a 'preparable' SQL statement
* @param $args Array of arguments to fill it with
* @return String: executable statement
*/
public function fillPrepared($preparedQuery, $args)
{
reset($args);
$this->preparedArgs =& $args;
foreach ($args as $i => $arg) {
db2_bind_param($preparedQuery, $i + 1, $args[$i]);
}
return $preparedQuery;
}
/**
* Binds a PHP variable to a parameter in the prepared statement.
*
* @param mixed $parameter
* @param string $variable
* @param string $type OPTIONAL
* @param integer $length OPTIONAL
* @param array $options OPTIONAL
* @return void
* @throws Zend_Db_Statement_Db2_Exception
*/
public function bindParam($parameter, &$variable, $type = null, $length = null, $options = null)
{
Zend_Db_Statement::bindParam($parameter, $variable, $length, $options);
if (!is_int($parameter)) {
require_once 'Zend/Db/Statement/Db2/Exception.php';
throw new Zend_Db_Statement_Db2_Exception('Binding parameters by name is not supported in the DB2 Adapter');
}
if ($type === null) {
$type = DB2_PARAM_IN;
}
if (isset($options['data-type'])) {
$datatype = $options['data-type'];
} else {
$datatype = DB2_CHAR;
}
if ($parameter > 0 && $parameter <= count($this->_sqlParam)) {
if (!db2_bind_param($this->_stmt, $parameter, "variable", $type, $datatype)) {
require_once 'Zend/Db/Statement/Db2/Exception.php';
throw new Zend_Db_Statement_Db2_Exception(db2_stmt_errormsg($this->_stmt), db2_stmt_error($this->_stmt));
}
} else {
require_once 'Zend/Db/Statement/Db2/Exception.php';
throw new Zend_Db_Statement_Db2_Exception("Position '{$parameter}' not valid");
}
}
</tr>
</thead>
<tbody>
<?php
$conn = dbConnect();
$sql = "SELECT GUIDEID, CARTYPE, FIRSTNAME, LASTNAME, MAXAMOUNT FROM EMM_ZOO.TOUR_VEHICLE V, EMM_ZOO.CARS C, EMM_ZOO.TOUR_DRIVER D, EMM_ZOO.EMPLOYEE E WHERE V.CARID = C.CARID AND V.DRIVERID = D.DRIVERID AND D.EMPID = E.EMPID";
$stmt = db2_prepare($conn, $sql);
db2_execute($stmt);
while ($row = db2_fetch_assoc($stmt)) {
$type = $row['CARTYPE'];
$dName = $row['FIRSTNAME'] . " " . $row['LASTNAME'];
$gID = $row['GUIDEID'];
$amount = $row['MAXAMOUNT'];
$sqlGuide = "SELECT FIRSTNAME, LASTNAME FROM EMM_ZOO.TOUR_GUIDE WHERE GUIDEID = ?";
$stmtGuide = db2_prepare($conn, $sqlGuide);
db2_bind_param($stmtGuide, 1, $gID);
db2_execute($stmtGuide);
?>
<tr>
<td class=""><?php
echo $id;
?>
</td>
<td class=""><?php
echo $name;
?>
</td>
<?php
while ($row = db2_fetch_assoc($stmtGuide)) {
$gName = $row['FIRSTNAME'] . " " . $row['LASTNAME'];
?>
/**
* Inspects the SQL statement to deduce if binding parameters is necessary and if so
* also binds the parameters. Currently only a stored procedure message is supported.
* @param $sql
* @param $suppress
* @param $stmt
* @param $sp_msg
* @return bool false if binding failed, true if binding succeeded or wasn't necessary
*/
protected function bindPreparedSqlParams($sql, $suppress, $stmt, &$sp_msg)
{
if (preg_match('/^CALL.+,\\s*\\?/i', $sql)) {
// 20110519 Frank Steegmans: Note at the time of this implementation we are not using stored procedures
// anywhere except for creating full text indexes in add_drop_contraint. Furthermore
// we are also not using parameterized prepared queries. If either one of these assumptions
// changes this code needs to be revisited.
try {
$sp_msg = null;
$this->commit();
// XXX TODO: DIRTY HACK to work around auto-commit off problem. I.e. TS index creation will hang if tables hasn't been committed yet.
// HENCE THIS COMMIT IS ONLY INTENDED FOR THE CREATION OF TS INDEXES. This should be moved into its execution objects in phase 3
$proceed = $suppress ? @db2_bind_param($stmt, 1, "sp_msg", DB2_PARAM_OUT) : db2_bind_param($stmt, 1, "sp_msg", DB2_PARAM_OUT);
return $proceed;
} catch (Exception $e) {
$this->log->error("IBMDB2Manager.query caught exception when running db2_bind_param for: {$sql} -> " . $e->getMessage());
throw $e;
}
}
return true;
}
/**
* Update images.
*
* @param array $images
*
* @return array (updated images)
*/
public function update($images)
{
global $DB;
$images = zbx_toArray($images);
$this->validateUpdate($images);
foreach ($images as $image) {
$values = array();
if (isset($image['name'])) {
$values['name'] = zbx_dbstr($image['name']);
}
if (isset($image['image'])) {
// decode BASE64
$image['image'] = base64_decode($image['image']);
// validate image
$this->checkImage($image['image']);
switch ($DB['TYPE']) {
case ZBX_DB_POSTGRESQL:
$values['image'] = "'" . pg_escape_bytea($image['image']) . "'";
break;
case ZBX_DB_SQLITE3:
$values['image'] = zbx_dbstr(bin2hex($image['image']));
break;
case ZBX_DB_MYSQL:
$values['image'] = zbx_dbstr($image['image']);
break;
case ZBX_DB_ORACLE:
$sql = 'SELECT i.image FROM images i WHERE i.imageid=' . zbx_dbstr($image['imageid']) . ' FOR UPDATE';
if (!($stmt = oci_parse($DB['DB'], $sql))) {
$e = oci_error($DB['DB']);
self::exception(ZBX_API_ERROR_PARAMETERS, 'SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
if (!oci_execute($stmt, OCI_DEFAULT)) {
$e = oci_error($stmt);
self::exception(ZBX_API_ERROR_PARAMETERS, 'SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
if (false === ($row = oci_fetch_assoc($stmt))) {
self::exception(ZBX_API_ERROR_PARAMETERS, 'DBerror');
}
$row['IMAGE']->truncate();
$row['IMAGE']->save($image['image']);
$row['IMAGE']->free();
break;
case ZBX_DB_DB2:
$stmt = db2_prepare($DB['DB'], 'UPDATE images SET image=? WHERE imageid=' . zbx_dbstr($image['imageid']));
if (!$stmt) {
self::exception(ZBX_API_ERROR_PARAMETERS, db2_conn_errormsg($DB['DB']));
}
// not unused, db2_bind_param requires variable name as string
$variable = $image['image'];
if (!db2_bind_param($stmt, 1, 'variable', DB2_PARAM_IN, DB2_BINARY)) {
self::exception(ZBX_API_ERROR_PARAMETERS, db2_conn_errormsg($DB['DB']));
}
if (!db2_execute($stmt)) {
self::exception(ZBX_API_ERROR_PARAMETERS, db2_conn_errormsg($DB['DB']));
}
break;
}
}
if ($values) {
$sqlUpd = array();
foreach ($values as $field => $value) {
$sqlUpd[] = $field . '=' . $value;
}
$sql = 'UPDATE images SET ' . implode(', ', $sqlUpd) . ' WHERE imageid=' . zbx_dbstr($image['imageid']);
$result = DBexecute($sql);
if (!$result) {
self::exception(ZBX_API_ERROR_PARAMETERS, _('Could not save image!'));
}
}
}
return array('imageids' => zbx_objectValues($images, 'imageid'));
}
/**
* Update images.
*
* @param array $images
*
* @return array (updated images)
*/
public function update($images)
{
global $DB;
$images = zbx_toArray($images);
if (self::$userData['type'] < USER_TYPE_ZABBIX_ADMIN) {
self::exception(ZBX_API_ERROR_PERMISSIONS, _('No permissions to referred object or it does not exist!'));
}
foreach ($images as $image) {
if (!isset($image['imageid'])) {
self::exception(ZBX_API_ERROR_PARAMETERS, _('Wrong fields for image.'));
}
$imageExists = $this->get(array('filter' => array('name' => $image['name']), 'output' => array('imageid'), 'nopermissions' => true));
$imageExists = reset($imageExists);
if ($imageExists && bccomp($imageExists['imageid'], $image['imageid']) != 0) {
self::exception(ZBX_API_ERROR_PARAMETERS, _s('Image "%1$s" already exists.', $image['name']));
}
$values = array();
if (isset($image['name'])) {
$values['name'] = zbx_dbstr($image['name']);
}
if (isset($image['imagetype'])) {
$values['imagetype'] = $image['imagetype'];
}
if (isset($image['image'])) {
// decode BASE64
$image['image'] = base64_decode($image['image']);
// validate image
$this->checkImage($image['image']);
switch ($DB['TYPE']) {
case ZBX_DB_POSTGRESQL:
$values['image'] = "'" . pg_escape_bytea($image['image']) . "'";
break;
case ZBX_DB_SQLITE3:
$values['image'] = zbx_dbstr(bin2hex($image['image']));
break;
case ZBX_DB_MYSQL:
$values['image'] = zbx_dbstr($image['image']);
break;
case ZBX_DB_ORACLE:
$sql = 'SELECT i.image FROM images i WHERE i.imageid=' . zbx_dbstr($image['imageid']) . ' FOR UPDATE';
if (!($stmt = oci_parse($DB['DB'], $sql))) {
$e = oci_error($DB['DB']);
self::exception(ZBX_API_ERROR_PARAMETERS, 'SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
if (!oci_execute($stmt, OCI_DEFAULT)) {
$e = oci_error($stmt);
self::exception(ZBX_API_ERROR_PARAMETERS, 'SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
if (false === ($row = oci_fetch_assoc($stmt))) {
self::exception(ZBX_API_ERROR_PARAMETERS, 'DBerror');
}
$row['IMAGE']->truncate();
$row['IMAGE']->save($image['image']);
$row['IMAGE']->free();
break;
case ZBX_DB_DB2:
$stmt = db2_prepare($DB['DB'], 'UPDATE images SET image=? WHERE imageid=' . zbx_dbstr($image['imageid']));
if (!$stmt) {
self::exception(ZBX_API_ERROR_PARAMETERS, db2_conn_errormsg($DB['DB']));
}
// not unused, db2_bind_param requires variable name as string
$variable = $image['image'];
if (!db2_bind_param($stmt, 1, 'variable', DB2_PARAM_IN, DB2_BINARY)) {
self::exception(ZBX_API_ERROR_PARAMETERS, db2_conn_errormsg($DB['DB']));
}
if (!db2_execute($stmt)) {
self::exception(ZBX_API_ERROR_PARAMETERS, db2_conn_errormsg($DB['DB']));
}
break;
}
}
$sqlUpd = array();
foreach ($values as $field => $value) {
$sqlUpd[] = $field . '=' . $value;
}
$sql = 'UPDATE images SET ' . implode(', ', $sqlUpd) . ' WHERE imageid=' . zbx_dbstr($image['imageid']);
$result = DBexecute($sql);
if (!$result) {
self::exception(ZBX_API_ERROR_PARAMETERS, _('Could not save image!'));
}
}
return array('imageids' => zbx_objectValues($images, 'imageid'));
}
/**
* Binds a parameter to the specified variable name.
*
* @param mixed $parameter Name the parameter, either integer or string.
* @param mixed $variable Reference to PHP variable containing the value.
* @param mixed $type OPTIONAL Datatype of SQL parameter.
* @param mixed $length OPTIONAL Length of SQL parameter.
* @param mixed $options OPTIONAL Other options.
* @return bool
* @throws Zend_Db_Statement_Db2_Exception
*/
public function bindParam($parameter, &$variable, $type = null, $length = null, $options = null)
{
$position = $this->_normalizeBindParam($parameter, $variable, true, true);
// the value is returned 0-indexed, but db2_bind_param() wants it to be 1-indexed
$position++;
if ($type === null) {
$type = DB2_PARAM_IN;
}
if (isset($options['data-type'])) {
$datatype = $options['data-type'];
} else {
$datatype = DB2_CHAR;
}
if (!db2_bind_param($this->_stmt, $position, "variable", $type, $datatype)) {
require_once 'Zend/Db/Statement/Db2/Exception.php';
throw new Zend_Db_Statement_Db2_Exception(db2_stmt_errormsg($this->_stmt), db2_stmt_error($this->_stmt));
}
return true;
}
/**
* this function used for special stored procedure call only
*
* @param $conn
* @param $sql
* @return bool
*/
public function execXMLStoredProcedure($conn, $sql, $bindArray)
{
$internalKey = $bindArray['internalKey'];
$controlKey = $bindArray['controlKey'];
$inputXml = $bindArray['inputXml'];
$outputXml = $bindArray['outputXml'];
// @todo see why error doesn't properly bubble up to top level.
$crsr = @db2_prepare($conn, $sql);
if (!$crsr) {
$this->setStmtError();
return false;
}
// stored procedure takes four parameters. Each 'name' will be bound to a real PHP variable
$params = array(array('position' => 1, 'name' => "internalKey", 'inout' => DB2_PARAM_IN), array('position' => 2, 'name' => "controlKey", 'inout' => DB2_PARAM_IN), array('position' => 3, 'name' => "inputXml", 'inout' => DB2_PARAM_IN), array('position' => 4, 'name' => "outputXml", 'inout' => DB2_PARAM_OUT));
// bind the four parameters
foreach ($params as $param) {
$ret = db2_bind_param($crsr, $param['position'], $param['name'], $param['inout']);
if (!$ret) {
// unable to bind a param. Set error and exit
$this->setStmtError($crsr);
return false;
}
}
$ret = @db2_execute($crsr);
if (!$ret) {
// execution of XMLSERVICE stored procedure failed.
$this->setStmtError($crsr);
return false;
}
return $outputXml;
}
/**
* Update images
*
* @param array $images
* @return array (updated images)
*/
public static function update($images)
{
global $DB, $USER_DETAILS;
try {
$images = zbx_toArray($images);
self::BeginTransaction(__METHOD__);
if ($USER_DETAILS['type'] < USER_TYPE_ZABBIX_ADMIN) {
self::exception(ZBX_API_ERROR_PERMISSIONS, S_NO_PERMISSIONS);
}
foreach ($images as $num => $image) {
if (!isset($image['imageid'])) {
self::exception(ZBX_API_ERROR_PARAMETERS, 'Wrong fields for image.');
}
$options = array('filter' => array('name' => $image['name']), 'output' => API_OUTPUT_SHORTEN, 'nopermissions' => 1);
$image_exists = self::get($options);
$image_exists = reset($image_exists);
if (!empty($image_exists) && $image_exists['imageid'] != $image['imageid']) {
self::exception(ZBX_API_ERROR_PARAMETERS, S_IMAGE . ' [ ' . $image['name'] . ' ] ' . S_ALREADY_EXISTS_SMALL);
}
$values = array();
if (isset($image['name'])) {
$values['name'] = zbx_dbstr($image['name']);
}
if (isset($image['imagetype'])) {
$values['imagetype'] = $image['imagetype'];
}
if (isset($image['image'])) {
// Decode BASE64
$image['image'] = base64_decode($image['image']);
if ($DB['TYPE'] == 'POSTGRESQL') {
$values['image'] = "'" . pg_escape_bytea($image['image']) . "'";
} else {
if ($DB['TYPE'] == 'SQLITE3') {
$values['image'] = zbx_dbstr(bin2hex($image['image']));
} else {
if ($DB['TYPE'] == 'MYSQL') {
$values['image'] = zbx_dbstr($image['image']);
} else {
if ($DB['TYPE'] == 'ORACLE') {
$sql = 'SELECT image FROM images WHERE imageid = ' . $image['imageid'] . ' FOR UPDATE';
if (!($stmt = oci_parse($DB['DB'], $sql))) {
$e = oci_error();
self::exception(ZBX_API_ERROR_PARAMETERS, 'SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
if (!oci_execute($stmt, OCI_DEFAULT)) {
$e = oci_error();
self::exception(ZBX_API_ERROR_PARAMETERS, 'SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
if (FALSE === ($row = oci_fetch_assoc($stmt))) {
self::exception(ZBX_API_ERROR_PARAMETERS, 'DBerror');
}
$row['IMAGE']->truncate();
$row['IMAGE']->save($image['image']);
$row['IMAGE']->free();
} else {
if ($DB['TYPE'] == 'IBM_DB2') {
$stmt = db2_prepare($DB['DB'], 'UPDATE images SET image=? WHERE imageid=' . $image['imageid']);
if (!$stmt) {
self::exception(ZBX_API_ERROR_PARAMETERS, db2_conn_errormsg($DB['DB']));
}
$variable = $image['image'];
if (!db2_bind_param($stmt, 1, "variable", DB2_PARAM_IN, DB2_BINARY)) {
self::exception(ZBX_API_ERROR_PARAMETERS, db2_conn_errormsg($DB['DB']));
}
if (!db2_execute($stmt)) {
self::exception(ZBX_API_ERROR_PARAMETERS, db2_conn_errormsg($DB['DB']));
}
}
}
}
}
}
}
$sql_upd = array();
foreach ($values as $field => $value) {
$sql_upd[] = $field . '=' . $value;
}
$sql = 'UPDATE images SET ' . implode(', ', $sql_upd) . ' WHERE imageid=' . $image['imageid'];
$result = DBexecute($sql);
if (!$result) {
self::exception(ZBX_API_ERROR_PARAMETERS, S_COULD_NOT_SAVE_IMAGE);
}
}
self::EndTransaction(true, __METHOD__);
return array('imageids' => zbx_objectValues($images, 'imageid'));
} catch (APIException $e) {
self::EndTransaction(false, __METHOD__);
$error = $e->getErrors();
$error = reset($error);
self::setError(__METHOD__, $e->getCode(), $error);
return false;
}
}
/**
* Execute an SQL query with blob fields processing
* @param String sql
* @param Array blobs
* @param Array blobTypes
* @return Boolean
*/
public function execWithBlobProcessing($sql, $blobs, $blobTypes = array())
{
$stml = db2_prepare($this->conn, $sql);
$numblobs = 1;
foreach ($blobs as $bfield) {
db2_bind_param($stml, $numblobs, "bfield", DB2_BINARY, DB2_PARAM_IN);
$numblobs += 1;
}
$ret = @db2_execute($stml);
db2_commit($this->conn);
return $ret;
}