function uploadImageData($db, $file, $currentPictureId, $table, $id) { // insert the new record into the media's table and load the // corresponding blob with the media's data // (we use oracle's pseudo column rowid which identifies a row // within a table (but not within a database) to refer to the // right record later on) $sql = "DECLARE\n obj ORDSYS.ORDImage;\n iblob BLOB;\n BEGIN\n SELECT image INTO obj FROM {$table}\n WHERE {$id} = {$currentPictureId} FOR UPDATE;\n\n iblob := obj.source.localData;\n :extblob := iblob;\n\n UPDATE {$table} SET image = obj WHERE {$id} = {$currentPictureId};\n END;"; // the function OCINewDescriptor allocates storage to hold descriptors or // lob locators. // see http://www.php.net/manual/en/function.ocinewdescriptor.php $blob = OCINewDescriptor($db, OCI_D_LOB); $sql = strtr($sql, chr(13) . chr(10), " "); $stmt = OCIParse($db, $sql); // the function OCIBindByName binds a PHP variable to a oracle placeholder // (whether the variable will be used for input or output will be determined // run-time, and the necessary storage space will be allocated) // see http://www.php.net/manual/en/function.ocibindbyname.php OCIBindByName($stmt, ':extblob', $blob, -1, OCI_B_BLOB); echo "{$this->log} - {$sql} <br />"; OCIExecute($stmt, OCI_DEFAULT); // read the files data and load it into the blob $blob->savefile($file); OCIFreeStatement($stmt); $blob->free(); }
function add_image($name, $imagetype, $file) { if (!is_null($file)) { if ($file["error"] != 0 || $file["size"] == 0) { error("Incorrect Image"); } else { if ($file["size"] < 1024 * 1024) { global $DB; $imageid = get_dbid("images", "imageid"); $image = fread(fopen($file["tmp_name"], "r"), filesize($file["tmp_name"])); if ($DB['TYPE'] == "ORACLE") { DBstart(); $lobimage = OCINewDescriptor($DB['DB'], OCI_D_LOB); $stid = OCIParse($DB['DB'], "insert into images (imageid,name,imagetype,image)" . " values ({$imageid}," . zbx_dbstr($name) . "," . $imagetype . ",EMPTY_BLOB())" . " return image into :image"); if (!$stid) { $e = ocierror($stid); error("Parse SQL error [" . $e["message"] . "] in [" . $e["sqltext"] . "]"); return false; } OCIBindByName($stid, ':image', $lobimage, -1, OCI_B_BLOB); if (!OCIExecute($stid, OCI_DEFAULT)) { $e = ocierror($stid); error("Execute SQL error [" . $e["message"] . "] in [" . $e["sqltext"] . "]"); return false; } $result = DBend($lobimage->save($image)); if (!$result) { error("Couldn't save image!\n"); return false; } $lobimage->free(); OCIFreeStatement($stid); return $stid; } else { if ($DB['TYPE'] == "POSTGRESQL") { $image = pg_escape_bytea($image); } else { if ($DB['TYPE'] == "SQLITE3") { $image = bin2hex($image); } } } return DBexecute("insert into images (imageid,name,imagetype,image)" . " values ({$imageid}," . zbx_dbstr($name) . "," . $imagetype . "," . zbx_dbstr($image) . ")"); } else { error("Image size must be less than 1Mb"); } } } else { error("Select image to download"); } return false; }
function QueryB($sql) { global $conn; $stmt = OCIParse($conn, $sql); $DBody = OCINewDescriptor($conn, OCI_D_LOB); OCIBindByName($stmt, ":Body_Loc", $DBody, -1, OCI_B_BLOB); $err = OCIExecute($stmt, OCI_DEFAULT); if (!$err) { $error = OCIError($stmt); //echo '<strong>Произошла ошибка: <font color="#889999">'.$error["message"].'</font><br>Запрос: <font color="#889999">'.$error["sqltext"].'</font></strong>'; QError($error); die; } return $DBody; }
function retrieveImage($db, $id, $table, $column) { // the function OCINewDescriptor allocates storage to hold descriptors or // lob locators, // see http://www.php.net/manual/en/function.ocinewdescriptor.php $data; $blob = OCINewDescriptor($db, OCI_D_LOB); // construct the sql query with which we will get the media's data $sql = "DECLARE\n obj ORDSYS.ORDImage;\n BEGIN\n SELECT {$column} INTO obj FROM {$table} WHERE picture_id = :id;\n :extblob := obj.getContent;\n END;"; $sql = strtr($sql, chr(13) . chr(10), " "); $stmt = OCIParse($db, $sql); // the function OCIBindByName binds a PHP variable to a oracle placeholder // (wheter the variable will be used for input or output will be determined // run-time, and the necessary storage space will be allocated) // see http://www.php.net/manual/en/function.ocibindbyname.php OCIBindByName($stmt, ':extBlob', $blob, -1, OCI_B_BLOB); OCIBindByName($stmt, ':id', $id); OCIExecute($stmt, OCI_DEFAULT); // load the binary data $data = $blob->load(); return $data; }
function Bind(&$stmt, &$var, $size = 4000, $type = false, $name = false) { if (!is_array($stmt)) { return false; } if ($type == OCI_B_CURSOR && sizeof($stmt) >= 5) { return OCIBindByName($stmt[1], ":" . $name, $stmt[4], $size, $type); } if ($name == false) { if ($type !== false) { $rez = OCIBindByName($stmt[1], ":" . $name, $var, $size, $type); } else { $rez = OCIBindByName($stmt[1], ":" . $stmt[2], $var, $size); } // +1 byte for null terminator $stmt[2] += 1; } else { if ($type == OCI_B_BLOB) { //we have to create a new Descriptor here $_blob = OCINewDescriptor($this->_connectionID, OCI_D_LOB); $rez = OCIBindByName($stmt[1], ":" . $name, &$_blob, -1, OCI_B_BLOB); $rez = $_blob; } else { if ($type !== false) { $rez = OCIBindByName($stmt[1], ":" . $name, $var, $size, $type); } else { $rez = OCIBindByName($stmt[1], ":" . $name, $var, $size); } // +1 byte for null terminator } } return $rez; }
public function write($id, $data) { $query = "MERGE INTO " . self::$_table["saveHandler"]["options"]["name"] . " M "; $query .= "USING (SELECT '" . $id . "' AS ID, :TIME AS LIFETIME, :DADOS AS DATAVAL FROM DUAL) N "; $query .= "ON (M." . self::$_table["saveHandler"]["options"]["primary"][0] . " = N.ID ) "; $query .= "WHEN MATCHED THEN "; $query .= "UPDATE SET M." . self::$_table["saveHandler"]["options"]["lifetimeColumn"] . " = N.LIFETIME, "; $query .= "M." . self::$_table["saveHandler"]["options"]["dataColumn"] . " = N.DATAVAL "; $query .= "WHEN NOT MATCHED THEN INSERT( " . self::$_table["saveHandler"]["options"]["primary"][0] . ", "; $query .= self::$_table["saveHandler"]["options"]["lifetimeColumn"] . ", "; $query .= self::$_table["saveHandler"]["options"]["dataColumn"] . " ) "; $query .= "VALUES(N.ID, N.LIFETIME, N.DATAVAL) "; $stmt = OCIParse(self::$_db, $query); $clob = OCINewDescriptor(self::$_db, OCI_D_LOB); OCIBindByName($stmt, ':TIME', time()); OCIBindByName($stmt, ':DADOS', $clob, -1, OCI_B_CLOB); $clob->WriteTemporary($data, OCI_TEMP_CLOB); $exe = OCIExecute($stmt, OCI_DEFAULT); if ($exe === true) { $ret = true; OCICommit(self::$_db); } else { $ret = false; OCIRollback(self::$_db); } $clob->close(); $clob->free(); OCIFreeStatement($stmt); return $ret; }
function DBSaveLob($connection, $sql, $blobParamName, $data, $lobType) { // Guarda datos en un clob.. global $dbError; $lob = OCINewDescriptor($connection, OCI_D_LOB); $stmt = OCIParse($connection, $sql); OCIBindByName($stmt, ":" . $blobParamName, $lob, -1, $lobType); $error = !oci_execute($stmt, OCI_DEFAULT); $result = $lob->write($data); if ($result) { OCICommit($connection); } if ($error) { $dbError = oci_error($stmt); if (isset($dbError["offset"])) { throw new Exception($dbError["message"]); } } return $result; }
/** * Execute a prepared query statement helper method. * * @param mixed $result_class string which specifies which result class to use * @param mixed $result_wrap_class string which specifies which class to wrap results in * @return mixed a result handle or MDB2_OK on success, a MDB2 error on failure * @access private */ function &_execute($result_class = true, $result_wrap_class = false) { if (is_null($this->statement)) { $result =& parent::_execute($result_class, $result_wrap_class); return $result; } $this->db->last_query = $this->query; $this->db->debug($this->query, 'execute', array('is_manip' => $this->is_manip, 'when' => 'pre', 'parameters' => $this->values)); if ($this->db->getOption('disable_query')) { $result = $this->is_manip ? 0 : null; return $result; } $connection = $this->db->getConnection(); if (PEAR::isError($connection)) { return $connection; } $result = MDB2_OK; $lobs = $quoted_values = array(); $i = 0; foreach ($this->positions as $parameter) { if (!array_key_exists($parameter, $this->values)) { return $this->db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'Unable to bind to missing placeholder: ' . $parameter, __FUNCTION__); } $value = $this->values[$parameter]; $type = array_key_exists($parameter, $this->types) ? $this->types[$parameter] : null; if ($type == 'clob' || $type == 'blob') { $lobs[$i]['file'] = false; if (is_resource($value)) { $fp = $value; $value = ''; while (!feof($fp)) { $value .= fread($fp, 8192); } } elseif (preg_match('/^(\\w+:\\/\\/)(.*)$/', $value, $match)) { $lobs[$i]['file'] = true; if ($match[1] == 'file://') { $value = $match[2]; } } $lobs[$i]['value'] = $value; $lobs[$i]['descriptor'] = @OCINewDescriptor($connection, OCI_D_LOB); if (!is_object($lobs[$i]['descriptor'])) { $result = $this->db->raiseError(null, null, null, 'Unable to create descriptor for LOB in parameter: ' . $parameter, __FUNCTION__); break; } $lob_type = $type == 'blob' ? OCI_B_BLOB : OCI_B_CLOB; if (!@OCIBindByName($this->statement, ':' . $parameter, $lobs[$i]['descriptor'], -1, $lob_type)) { $result = $this->db->raiseError($this->statement, null, null, 'could not bind LOB parameter', __FUNCTION__); break; } } else { $quoted_values[$i] = $this->db->quote($value, $type, false); if (PEAR::isError($quoted_values[$i])) { return $quoted_values[$i]; } if (!@OCIBindByName($this->statement, ':' . $parameter, $quoted_values[$i])) { $result = $this->db->raiseError($this->statement, null, null, 'could not bind non LOB parameter', __FUNCTION__); break; } } ++$i; } $lob_keys = array_keys($lobs); if (!PEAR::isError($result)) { $mode = !empty($lobs) || $this->db->in_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS; if (!@OCIExecute($this->statement, $mode)) { $err =& $this->db->raiseError($this->statement, null, null, 'could not execute statement', __FUNCTION__); return $err; } if (!empty($lobs)) { foreach ($lob_keys as $i) { if (!is_null($lobs[$i]['value']) && $lobs[$i]['value'] !== '') { if ($lobs[$i]['file']) { $result = $lobs[$i]['descriptor']->savefile($lobs[$i]['value']); } else { $result = $lobs[$i]['descriptor']->save($lobs[$i]['value']); } if (!$result) { $result = $this->db->raiseError(null, null, null, 'Unable to save descriptor contents', __FUNCTION__); break; } } } if (!PEAR::isError($result)) { if (!$this->db->in_transaction) { if (!@OCICommit($connection)) { $result = $this->db->raiseError(null, null, null, 'Unable to commit transaction', __FUNCTION__); } } else { ++$this->db->uncommitedqueries; } } } } $lob_keys = array_keys($lobs); foreach ($lob_keys as $i) { $lobs[$i]['descriptor']->free(); } if (PEAR::isError($result)) { return $result; } if ($this->is_manip) { $affected_rows = $this->db->_affectedRows($connection, $this->statement); return $affected_rows; } $result =& $this->db->_wrapResult($this->statement, $this->result_types, $result_class, $result_wrap_class, $this->limit, $this->offset); $this->db->debug($this->query, 'execute', array('is_manip' => $this->is_manip, 'when' => 'post', 'result' => $result)); return $result; }
/** * Usage: store file pointed to by $var in a blob */ function UpdateBlobFile($table, $column, $val, $where, $blobtype = 'BLOB') { switch (strtoupper($blobtype)) { default: ADOConnection::outp("<b>UpdateBlob</b>: Unknown blobtype={$blobtype}"); return false; case 'BLOB': $type = OCI_B_BLOB; break; case 'CLOB': $type = OCI_B_CLOB; break; } if ($this->databaseType == 'oci8po') { $sql = "UPDATE {$table} set {$column}=EMPTY_{$blobtype}() WHERE {$where} RETURNING {$column} INTO ?"; } else { $sql = "UPDATE {$table} set {$column}=EMPTY_{$blobtype}() WHERE {$where} RETURNING {$column} INTO :blob"; } $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB); $arr['blob'] = array($desc, -1, $type); $this->BeginTrans(); $rs = ADODB_oci8::Execute($sql, $arr); if ($rez = !empty($rs)) { $desc->savefile($val); } $desc->free(); $this->CommitTrans(); if ($rez) { $rs->Close(); } return $rez; }
function QueryBind($strSql, $arBinds, $bIgnoreErrors = false, $error_position = "") { $this->DoConnect(); global $prev_Query; $prev_Query[] = $strSql; $this->db_Error = ""; if ($this->DebugToFile || $this->ShowSqlStat) { list($usec, $sec) = explode(" ", microtime()); $start_time = (double) $usec + (double) $sec; } $strBinds1 = ""; $strBinds2 = ""; $keys = array_keys($arBinds); $good_keys = array(); for ($i = 0; $i < count($keys); $i++) { if (strlen($arBinds[$keys[$i]]) > 0) { if ($strBinds1 == "") { $strBinds1 = " RETURNING "; $strBinds2 = " INTO "; } else { $strBinds1 .= ","; $strBinds2 .= ","; } $good_keys[] = $keys[$i]; $strBinds1 .= $keys[$i]; $strBinds2 .= ":" . $keys[$i]; } } $strSql .= $strBinds1 . $strBinds2; $result = @OCIParse($this->db_Conn, $strSql); if (!$result) { $error = OCIError($this->db_Conn); $this->db_Error = $error["message"]; if (!$bIgnoreErrors) { if ($this->debug || @session_start() && $_SESSION["SESS_AUTH"]["ADMIN"]) { echo "<br><font color=#ff0000>Parse Error: " . htmlspecialchars($strSql) . "</font>[" . $error["message"] . "]<br>"; } else { SendError("Parse Error:\n" . $strSql . " \n [" . $error["message"] . "]\n---------------\n\n"); } AddMessage2Log("Parse Error: " . $strSql . " [" . $error["message"] . "]", "main"); die("Query Error!"); } return false; } for ($i = 0; $i < count($good_keys); $i++) { $CLOB[$i] = OCINewDescriptor($this->db_Conn, OCI_D_LOB); OCIBindByName($result, ":" . $good_keys[$i], $CLOB[$i], -1, OCI_B_CLOB); } $this->cntQuery++; if (!@OCIExecute($result, OCI_DEFAULT)) { $error = OCIError($result); $this->db_Error = $error["message"]; if (!$bIgnoreErrors) { if ($this->debug || @session_start() && $_SESSION["SESS_AUTH"]["ADMIN"]) { echo "<br><font color=#ff0000>Query Error: " . htmlspecialchars($strSql) . "</font>[" . $error["message"] . "]<br>"; } else { SendError("Query Error:\n" . $strSql . " \n [" . $error["message"] . "]\n---------------\n\n"); } AddMessage2Log("Query Error: " . $strSql . " [" . $error["message"] . "]", "main"); die("Query Error!"); } return false; } for ($i = 0; $i < count($good_keys); $i++) { $CLOB[$i]->save($arBinds[$good_keys[$i]]); } if ($this->transaction == OCI_COMMIT_ON_SUCCESS) { OCICommit($this->db_Conn); } if ($this->DebugToFile || $this->ShowSqlStat) { list($usec, $sec) = explode(" ", microtime()); $end_time = (double) $usec + (double) $sec; $exec_time = round($end_time - $start_time, 10); if ($this->ShowSqlStat) { $this->cntQuery++; $this->timeQuery += $exec_time; $this->arQueryDebug[] = array("QUERY" => $strSql, "TIME" => $exec_time, "TRACE" => function_exists("debug_backtrace") ? debug_backtrace() : false); } if ($this->DebugToFile) { $fp = fopen($_SERVER["DOCUMENT_ROOT"] . "/oracle_debug.sql", "ab+"); fputs($fp, "TIME: " . $exec_time . " SESSION: " . session_id() . " \n" . $strSql . "\n\n----------------------------------------------------\n\n"); @fclose($fp); } } return new CDBResult($result); }
/** * all the RDBMS specific things needed close a DB connection * * @access private */ function _doQuery($query, $first = 0, $limit = 0, $prepared_query = 0) { $lobs = 0; $success = MDB_OK; $result = 0; $descriptors = array(); if ($prepared_query) { $columns = ''; $variables = ''; for (reset($this->clobs[$prepared_query]), $clob = 0; $clob < count($this->clobs[$prepared_query]); $clob++, next($this->clobs[$prepared_query])) { $position = key($this->clobs[$prepared_query]); if (gettype($descriptors[$position] = @OCINewDescriptor($this->connection, OCI_D_LOB)) != 'object') { $success = $this->raiseError(MDB_ERROR, NULL, NULL, 'Do query: Could not create descriptor for clob parameter'); break; } $columns .= ($lobs == 0 ? ' RETURNING ' : ',') . $this->prepared_queries[$prepared_query - 1]['Fields'][$position - 1]; $variables .= ($lobs == 0 ? ' INTO ' : ',') . ':clob' . $position; $lobs++; } if (!MDB::isError($success)) { for (reset($this->blobs[$prepared_query]), $blob = 0; $blob < count($this->blobs[$prepared_query]); $blob++, next($this->blobs[$prepared_query])) { $position = key($this->blobs[$prepared_query]); if (gettype($descriptors[$position] = @OCINewDescriptor($this->connection, OCI_D_LOB)) != 'object') { $success = $this->raiseError(MDB_ERROR, NULL, NULL, 'Do query: Could not create descriptor for blob parameter'); break; } $columns .= ($lobs == 0 ? ' RETURNING ' : ',') . $this->prepared_queries[$prepared_query - 1]['Fields'][$position - 1]; $variables .= ($lobs == 0 ? ' INTO ' : ',') . ':blob' . $position; $lobs++; } $query .= $columns . $variables; } } if (!MDB::isError($success)) { if ($statement = @OCIParse($this->connection, $query)) { if ($lobs) { for (reset($this->clobs[$prepared_query]), $clob = 0; $clob < count($this->clobs[$prepared_query]); $clob++, next($this->clobs[$prepared_query])) { $position = key($this->clobs[$prepared_query]); if (!@OCIBindByName($statement, ':clob' . $position, $descriptors[$position], -1, OCI_B_CLOB)) { $success = $this->oci8RaiseError(NULL, 'Do query: Could not bind clob upload descriptor'); break; } } if (!MDB::isError($success)) { for (reset($this->blobs[$prepared_query]), $blob = 0; $blob < count($this->blobs[$prepared_query]); $blob++, next($this->blobs[$prepared_query])) { $position = key($this->blobs[$prepared_query]); if (!@OCIBindByName($statement, ':blob' . $position, $descriptors[$position], -1, OCI_B_BLOB)) { $success = $this->oci8RaiseError(NULL, 'Do query: Could not bind blob upload descriptor'); break; } } } } if (!MDB::isError($success)) { if ($result = @OCIExecute($statement, $lobs == 0 && $this->auto_commit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT)) { if ($lobs) { for (reset($this->clobs[$prepared_query]), $clob = 0; $clob < count($this->clobs[$prepared_query]); $clob++, next($this->clobs[$prepared_query])) { $position = key($this->clobs[$prepared_query]); $clob_stream = $this->prepared_queries[$prepared_query - 1]['Values'][$position - 1]; for ($value = ''; !$this->endOfLOB($clob_stream);) { if ($this->readLOB($clob_stream, $data, $this->getOption('lob_buffer_length')) < 0) { $success = $this->raiseError(); break; } $value .= $data; } if (!MDB::isError($success) && !$descriptors[$position]->save($value)) { $success = $this->oci8RaiseError(NULL, 'Do query: Could not upload clob data'); } } if (!MDB::isError($success)) { for (reset($this->blobs[$prepared_query]), $blob = 0; $blob < count($this->blobs[$prepared_query]); $blob++, next($this->blobs[$prepared_query])) { $position = key($this->blobs[$prepared_query]); $blob_stream = $this->prepared_queries[$prepared_query - 1]['Values'][$position - 1]; for ($value = ''; !$this->endOfLOB($blob_stream);) { if ($this->readLOB($blob_stream, $data, $this->getOption('lob_buffer_length')) < 0) { $success = $this->raiseError(); break; } $value .= $data; } if (!MDB::isError($success) && !$descriptors[$position]->save($value)) { $success = $this->oci8RaiseError(NULL, 'Do query: Could not upload blob data'); } } } } if ($this->auto_commit) { if ($lobs) { if (MDB::isError($success)) { if (!@OCIRollback($this->connection)) { $success = $this->oci8RaiseError(NULL, 'Do query: ' . $success->getUserinfo() . ' and then could not rollback LOB updating transaction'); } } else { if (!@OCICommit($this->connection)) { $success = $this->oci8RaiseError(NULL, 'Do query: Could not commit pending LOB updating transaction'); } } } } else { $this->uncommitedqueries++; } if (!MDB::isError($success)) { switch (@OCIStatementType($statement)) { case 'SELECT': $result_value = intval($statement); $this->current_row[$result_value] = -1; if ($limit > 0) { $this->limits[$result_value] = array($first, $limit, 0); } $this->highest_fetched_row[$result_value] = -1; break; default: $this->affected_rows = @OCIRowCount($statement); @OCIFreeCursor($statement); break; } $result = $statement; } } else { return $this->oci8RaiseError($statement, 'Do query: Could not execute query'); } } } else { return $this->oci8RaiseError(NULL, 'Do query: Could not parse query'); } } for (reset($descriptors), $descriptor = 0; $descriptor < count($descriptors); $descriptor++, next($descriptors)) { @OCIFreeDesc($descriptors[key($descriptors)]); } return $result; }
/** * Execute a query * @param string $query the SQL query * @return mixed result identifier if query executed, else MDB2_error * @access private **/ function _doQuery($query, $ismanip = null, $prepared_query = 0) { $lobs = 0; $success = MDB2_OK; $result = 0; $descriptors = array(); if ($prepared_query) { $columns = ''; $variables = ''; for (reset($this->clobs[$prepared_query]), $clob = 0; $clob < count($this->clobs[$prepared_query]); $clob++, next($this->clobs[$prepared_query])) { $clob_stream = key($this->clobs[$prepared_query]); $descriptors[$clob_stream] = @OCINewDescriptor($this->connection, OCI_D_LOB); if (!is_object($descriptors[$clob_stream])) { $success = $this->raiseError(MDB2_ERROR, null, null, 'Could not create descriptor for clob parameter'); break; } $parameter = $GLOBALS['_MDB2_LOBs'][$clob_stream]->parameter; $columns .= ($lobs == 0 ? ' RETURNING ' : ',') . $this->prepared_queries[$prepared_query - 1]['fields'][$parameter - 1]; $variables .= ($lobs == 0 ? ' INTO ' : ',') . ':clob' . $parameter; ++$lobs; } if (!MDB2::isError($success)) { for (reset($this->blobs[$prepared_query]), $blob = 0; $blob < count($this->blobs[$prepared_query]); $blob++, next($this->blobs[$prepared_query])) { $blob_stream = key($this->blobs[$prepared_query]); $descriptors[$blob_stream] = @OCINewDescriptor($this->connection, OCI_D_LOB); if (!is_object($descriptors[$blob_stream])) { $success = $this->raiseError(MDB2_ERROR, null, null, 'Could not create descriptor for blob parameter'); break; } $parameter = $GLOBALS['_MDB2_LOBs'][$blob_stream]->parameter; $columns .= ($lobs == 0 ? ' RETURNING ' : ',') . $this->prepared_queries[$prepared_query - 1]['fields'][$parameter - 1]; $variables .= ($lobs == 0 ? ' INTO ' : ',') . ':blob' . $parameter; ++$lobs; } $query .= $columns . $variables; } } if (!MDB2::isError($success)) { if ($statement = @OCIParse($this->connection, $query)) { if ($lobs) { for (reset($this->clobs[$prepared_query]), $clob = 0; $clob < count($this->clobs[$prepared_query]); $clob++, next($this->clobs[$prepared_query])) { $clob_stream = key($this->clobs[$prepared_query]); $parameter = $GLOBALS['_MDB2_LOBs'][$clob_stream]->parameter; if (!OCIBindByName($statement, ':clob' . $parameter, $descriptors[$clob_stream], -1, OCI_B_CLOB)) { $success = $this->raiseError(); break; } } if (!MDB2::isError($success)) { for (reset($this->blobs[$prepared_query]), $blob = 0; $blob < count($this->blobs[$prepared_query]); $blob++, next($this->blobs[$prepared_query])) { $blob_stream = key($this->blobs[$prepared_query]); $parameter = $GLOBALS['_MDB2_LOBs'][$blob_stream]->parameter; if (!OCIBindByName($statement, ':blob' . $parameter, $descriptors[$blob_stream], -1, OCI_B_BLOB)) { $success = $this->raiseError(); break; } } } } if (!MDB2::isError($success)) { $mode = $lobs == 0 && $this->auto_commit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT; $result = @OCIExecute($statement, $mode); if ($result) { if ($lobs) { for (reset($this->clobs[$prepared_query]), $clob = 0; $clob < count($this->clobs[$prepared_query]); $clob++, next($this->clobs[$prepared_query])) { $clob_stream = key($this->clobs[$prepared_query]); for ($value = ''; !$this->datatype->endOfLOB($clob_stream);) { if ($this->datatype->readLOB($clob_stream, $data, $this->options['lob_buffer_length']) < 0) { $success = $this->raiseError(); break; } $value .= $data; } if (!MDB2::isError($success) && !$descriptors[$clob_stream]->save($value)) { $success = $this->raiseError(); } } if (!MDB2::isError($success)) { for (reset($this->blobs[$prepared_query]), $blob = 0; $blob < count($this->blobs[$prepared_query]); $blob++, next($this->blobs[$prepared_query])) { $blob_stream = key($this->blobs[$prepared_query]); for ($value = ''; !$this->datatype->endOfLOB($blob_stream);) { if ($this->datatype->readLOB($blob_stream, $data, $this->options['lob_buffer_length']) < 0) { $success = $this->raiseError(); break; } $value .= $data; } if (!MDB2::isError($success) && !$descriptors[$blob_stream]->save($value)) { $success = $this->raiseError(); } } } } if ($this->auto_commit) { if ($lobs) { if (MDB2::isError($success)) { if (!OCIRollback($this->connection)) { $success = $this->raiseError(); } } else { if (!OCICommit($this->connection)) { $success = $this->raiseError(); } } } } else { ++$this->uncommitedqueries; } if (!MDB2::isError($success)) { if (is_null($ismanip)) { $ismanip = MDB2::isManip($query); } if ($ismanip) { $this->affected_rows = @OCIRowCount($statement); @OCIFreeCursor($statement); } $result = $statement; } } else { return $this->raiseError($statement); } } } else { return $this->raiseError(); } } for (reset($descriptors), $descriptor = 0; $descriptor < count($descriptors); $descriptor++, next($descriptors)) { @$descriptors[key($descriptors)]->free(); } if (MDB2::isError($success)) { return $success; } return $result; }
function DoQuery($query, $first = 0, $limit = 0, $prepared_query = 0) { $lobs = 0; $success = 1; $result = 0; $descriptors = array(); if ($prepared_query) { $columns = ""; $variables = ""; for (Reset($this->clobs[$prepared_query]), $clob = 0; $clob < count($this->clobs[$prepared_query]); $clob++, Next($this->clobs[$prepared_query])) { $position = Key($this->clobs[$prepared_query]); if (GetType($descriptors[$position] = OCINewDescriptor($this->connection, OCI_D_LOB)) != "object") { $this->SetError("Do query", "Could not create descriptor for clob parameter"); $success = 0; break; } $columns .= ($lobs == 0 ? " RETURNING " : ",") . $this->prepared_queries[$prepared_query - 1]["Fields"][$position - 1]; $variables .= ($lobs == 0 ? " INTO " : ",") . ":clob" . $position; $lobs++; } if ($success) { for (Reset($this->blobs[$prepared_query]), $blob = 0; $blob < count($this->blobs[$prepared_query]); $blob++, Next($this->blobs[$prepared_query])) { $position = Key($this->blobs[$prepared_query]); if (GetType($descriptors[$position] = OCINewDescriptor($this->connection, OCI_D_LOB)) != "object") { $this->SetError("Do query", "Could not create descriptor for blob parameter"); $success = 0; break; } $columns .= ($lobs == 0 ? " RETURNING " : ",") . $this->prepared_queries[$prepared_query - 1]["Fields"][$position - 1]; $variables .= ($lobs == 0 ? " INTO " : ",") . ":blob" . $position; $lobs++; } $query .= $columns . $variables; } } if ($success) { if ($statement = OCIParse($this->connection, $query)) { if ($lobs) { for (Reset($this->clobs[$prepared_query]), $clob = 0; $clob < count($this->clobs[$prepared_query]); $clob++, Next($this->clobs[$prepared_query])) { $position = Key($this->clobs[$prepared_query]); if (!OCIBindByName($statement, ":clob" . $position, $descriptors[$position], -1, OCI_B_CLOB)) { $this->SetOCIError("Do query", "Could not bind clob upload descriptor", OCIError($statement)); $success = 0; break; } } if ($success) { for (Reset($this->blobs[$prepared_query]), $blob = 0; $blob < count($this->blobs[$prepared_query]); $blob++, Next($this->blobs[$prepared_query])) { $position = Key($this->blobs[$prepared_query]); if (!OCIBindByName($statement, ":blob" . $position, $descriptors[$position], -1, OCI_B_BLOB)) { $this->SetOCIError("Do query", "Could not bind blob upload descriptor", OCIError($statement)); $success = 0; break; } } } } if ($success) { if ($result = @OCIExecute($statement, $lobs == 0 && $this->auto_commit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT)) { if ($lobs) { for (Reset($this->clobs[$prepared_query]), $clob = 0; $clob < count($this->clobs[$prepared_query]); $clob++, Next($this->clobs[$prepared_query])) { $position = Key($this->clobs[$prepared_query]); $clob_stream = $this->prepared_queries[$prepared_query - 1]["Values"][$position - 1]; for ($value = ""; !MetabaseEndOfLOB($clob_stream);) { if (MetabaseReadLOB($clob_stream, $data, $this->lob_buffer_length) < 0) { $this->SetError("Do query", MetabaseLOBError($clob)); $success = 0; break; } $value .= $data; } if ($success && !$descriptors[$position]->save($value)) { $this->SetOCIError("Do query", "Could not upload clob data", OCIError($statement)); $success = 0; } } if ($success) { for (Reset($this->blobs[$prepared_query]), $blob = 0; $blob < count($this->blobs[$prepared_query]); $blob++, Next($this->blobs[$prepared_query])) { $position = Key($this->blobs[$prepared_query]); $blob_stream = $this->prepared_queries[$prepared_query - 1]["Values"][$position - 1]; for ($value = ""; !MetabaseEndOfLOB($blob_stream);) { if (MetabaseReadLOB($blob_stream, $data, $this->lob_buffer_length) < 0) { $this->SetError("Do query", MetabaseLOBError($blob)); $success = 0; break; } $value .= $data; } if ($success && !$descriptors[$position]->save($value)) { $this->SetOCIError("Do query", "Could not upload blob data", OCIError($statement)); $success = 0; } } } } if ($this->auto_commit) { if ($lobs) { if ($success) { if (!OCICommit($this->connection)) { $this->SetOCIError("Do query", "Could not commit pending LOB updating transaction", OCIError()); $success = 0; } } else { if (!OCIRollback($this->connection)) { $this->SetOCIError("Do query", $this->Error() . " and then could not rollback LOB updating transaction", OCIError()); } } } } else { $this->uncommitedqueries++; } if ($success) { switch (OCIStatementType($statement)) { case "SELECT": $result_value = intval($statement); $this->current_row[$result_value] = -1; if ($limit > 0) { $this->limits[$result_value] = array($first, $limit, 0); } $this->highest_fetched_row[$result_value] = -1; break; default: $this->affected_rows = OCIRowCount($statement); OCIFreeCursor($statement); break; } $result = $statement; } } else { $this->SetOCIError("Do query", "Could not execute query", OCIError($statement)); } } } else { $this->SetOCIError("Do query", "Could not parse query", OCIError($statement)); } } for (Reset($descriptors), $descriptor = 0; $descriptor < count($descriptors); $descriptor++, Next($descriptors)) { @OCIFreeDesc($descriptors[Key($descriptors)]); } return $result; }
/** * Execute a prepared query statement helper method. * * @param mixed $result_class string which specifies which result class to use * @param mixed $result_wrap_class string which specifies which class to wrap results in * * @return mixed MDB2_Result or integer (affected rows) on success, * a MDB2 error on failure * @access private */ function _execute($result_class = true, $result_wrap_class = false) { if (null === $this->statement) { return parent::_execute($result_class, $result_wrap_class); } $this->db->last_query = $this->query; $this->db->debug($this->query, 'execute', array('is_manip' => $this->is_manip, 'when' => 'pre', 'parameters' => $this->values)); if ($this->db->getOption('disable_query')) { $result = $this->is_manip ? 0 : null; return $result; } $connection = $this->db->getConnection(); if (PEAR::isError($connection)) { return $connection; } $result = MDB2_OK; $lobs = $quoted_values = array(); $i = 0; foreach ($this->positions as $parameter) { if (!array_key_exists($parameter, $this->values)) { return $this->db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'Unable to bind to missing placeholder: ' . $parameter, __FUNCTION__); } $type = array_key_exists($parameter, $this->types) ? $this->types[$parameter] : null; if ($type == 'clob' || $type == 'blob') { $lobs[$i]['file'] = false; if (is_resource($this->values[$parameter])) { $fp = $this->values[$parameter]; $this->values[$parameter] = ''; while (!feof($fp)) { $this->values[$parameter] .= fread($fp, 8192); } } elseif (is_a($this->values[$parameter], 'OCI-Lob')) { //do nothing } elseif ($this->db->getOption('lob_allow_url_include') && preg_match('/^(\\w+:\\/\\/)(.*)$/', $this->values[$parameter], $match)) { $lobs[$i]['file'] = true; if ($match[1] == 'file://') { $this->values[$parameter] = $match[2]; } } $lobs[$i]['value'] = $this->values[$parameter]; $lobs[$i]['descriptor'] =& $this->values[$parameter]; // Test to see if descriptor has already been created for this // variable (i.e. if it has been bound more than once): if (!is_a($this->values[$parameter], 'OCI-Lob')) { $this->values[$parameter] = @OCINewDescriptor($connection, OCI_D_LOB); if (false === $this->values[$parameter]) { $result = $this->db->raiseError(null, null, null, 'Unable to create descriptor for LOB in parameter: ' . $parameter, __FUNCTION__); break; } } $lob_type = $type == 'blob' ? OCI_B_BLOB : OCI_B_CLOB; if (!@OCIBindByName($this->statement, ':' . $parameter, $lobs[$i]['descriptor'], -1, $lob_type)) { $result = $this->db->raiseError($this->statement, null, null, 'could not bind LOB parameter', __FUNCTION__); break; } } else { if ($type == OCI_B_BFILE) { // Test to see if descriptor has already been created for this // variable (i.e. if it has been bound more than once): if (!is_a($this->values[$parameter], "OCI-Lob")) { $this->values[$parameter] = @OCINewDescriptor($connection, OCI_D_FILE); if (false === $this->values[$parameter]) { $result = $this->db->raiseError(null, null, null, 'Unable to create descriptor for BFILE in parameter: ' . $parameter, __FUNCTION__); break; } } if (!@OCIBindByName($this->statement, ':' . $parameter, $this->values[$parameter], -1, $type)) { $result = $this->db->raiseError($this->statement, null, null, 'Could not bind BFILE parameter', __FUNCTION__); break; } } else { if ($type == OCI_B_ROWID) { // Test to see if descriptor has already been created for this // variable (i.e. if it has been bound more than once): if (!is_a($this->values[$parameter], "OCI-Lob")) { $this->values[$parameter] = @OCINewDescriptor($connection, OCI_D_ROWID); if (false === $this->values[$parameter]) { $result = $this->db->raiseError(null, null, null, 'Unable to create descriptor for ROWID in parameter: ' . $parameter, __FUNCTION__); break; } } if (!@OCIBindByName($this->statement, ':' . $parameter, $this->values[$parameter], -1, $type)) { $result = $this->db->raiseError($this->statement, null, null, 'Could not bind ROWID parameter', __FUNCTION__); break; } } else { if ($type == OCI_B_CURSOR) { // Test to see if cursor has already been allocated for this // variable (i.e. if it has been bound more than once): if (!is_resource($this->values[$parameter]) || !get_resource_type($this->values[$parameter]) == "oci8 statement") { $this->values[$parameter] = @OCINewCursor($connection); if (false === $this->values[$parameter]) { $result = $this->db->raiseError(null, null, null, 'Unable to allocate cursor for parameter: ' . $parameter, __FUNCTION__); break; } } if (!@OCIBindByName($this->statement, ':' . $parameter, $this->values[$parameter], -1, $type)) { $result = $this->db->raiseError($this->statement, null, null, 'Could not bind CURSOR parameter', __FUNCTION__); break; } } else { $maxlength = array_key_exists($parameter, $this->type_maxlengths) ? $this->type_maxlengths[$parameter] : -1; $this->values[$parameter] = $this->db->quote($this->values[$parameter], $type, false); $quoted_values[$i] =& $this->values[$parameter]; if (PEAR::isError($quoted_values[$i])) { return $quoted_values[$i]; } if (!@OCIBindByName($this->statement, ':' . $parameter, $quoted_values[$i], $maxlength)) { $result = $this->db->raiseError($this->statement, null, null, 'could not bind non-abstract parameter', __FUNCTION__); break; } } } } } ++$i; } $lob_keys = array_keys($lobs); if (!PEAR::isError($result)) { $mode = !empty($lobs) || $this->db->in_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS; if (!@OCIExecute($this->statement, $mode)) { $err = $this->db->raiseError($this->statement, null, null, 'could not execute statement', __FUNCTION__); return $err; } if (!empty($lobs)) { foreach ($lob_keys as $i) { if (null !== $lobs[$i]['value'] && $lobs[$i]['value'] !== '') { if (is_object($lobs[$i]['value'])) { // Probably a NULL LOB // @see http://bugs.php.net/bug.php?id=27485 continue; } if ($lobs[$i]['file']) { $result = $lobs[$i]['descriptor']->savefile($lobs[$i]['value']); } else { $result = $lobs[$i]['descriptor']->save($lobs[$i]['value']); } if (!$result) { $result = $this->db->raiseError(null, null, null, 'Unable to save descriptor contents', __FUNCTION__); break; } } } if (!PEAR::isError($result)) { if (!$this->db->in_transaction) { if (!@OCICommit($connection)) { $result = $this->db->raiseError(null, null, null, 'Unable to commit transaction', __FUNCTION__); } } else { ++$this->db->uncommitedqueries; } } } } if (PEAR::isError($result)) { return $result; } if ($this->is_manip) { $affected_rows = $this->db->_affectedRows($connection, $this->statement); return $affected_rows; } $result = $this->db->_wrapResult($this->statement, $this->result_types, $result_class, $result_wrap_class, $this->limit, $this->offset); $this->db->debug($this->query, 'execute', array('is_manip' => $this->is_manip, 'when' => 'post', 'result' => $result)); return $result; }
/** * Usage: * Store BLOBs and CLOBs * * Example: to store $var in a blob * * $conn->Execute('insert into TABLE (id,ablobb) values(12,empty_blob())'); * $conn->UpdateBlob('TABLE', 'COLUMN', $var, 'ID=1', 'BLOB'); * * $blobtype supports 'BLOB' and 'CLOB' * * to get length of LOB: * select DBMS_LOB.GETLENGTH(ablob) from TABLE */ function UpdateBlob($table, $column, $val, $where, $blobtype = 'BLOB') { switch (strtoupper($blobtype)) { default: print "<b>UpdateBlob</b>: Unknown blobtype={$blobtype}<br>"; return false; case 'BLOB': $type = OCI_B_BLOB; break; case 'CLOB': $type = OCI_B_CLOB; break; } $sql = "UPDATE {$table} set {$column}=EMPTY_{$blobtype}() WHERE {$where} RETURNING {$column} INTO :blob"; $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB); $arr['blob'] = array($desc, -1, $type); $this->BeginTrans(); $rs = $this->Execute($sql, $arr); $rez = !empty($rs); $desc->save($val); $desc->free(); $this->CommitTrans(); if ($rez) { $rs->Close(); } return $rez; }
function Bind(&$stmt,&$var,$size=4000,$type=false,$name=false,$isOutput=false) { if (!is_array($stmt)) return false; if (($type == OCI_B_CURSOR) && sizeof($stmt) >= 5) { return OCIBindByName($stmt[1],":".$name,$stmt[4],$size,$type); } if ($name == false) { if ($type !== false) $rez = OCIBindByName($stmt[1],":".$stmt[2],$var,$size,$type); else $rez = OCIBindByName($stmt[1],":".$stmt[2],$var,$size); // +1 byte for null terminator $stmt[2] += 1; } elseif (oci_lob_desc($type)) { if ($this->debug) { ADOConnection::outp("<b>Bind</b>: name = $name"); } //we have to create a new Descriptor here $numlob = count($this->_refLOBs); $this->_refLOBs[$numlob]['LOB'] = OCINewDescriptor($this->_connectionID, oci_lob_desc($type)); $this->_refLOBs[$numlob]['TYPE'] = $isOutput; $tmp = $this->_refLOBs[$numlob]['LOB']; $rez = OCIBindByName($stmt[1], ":".$name, $tmp, -1, $type); if ($this->debug) { ADOConnection::outp("<b>Bind</b>: descriptor has been allocated, var (".$name.") binded"); } // if type is input then write data to lob now if ($isOutput == false) { $var = $this->BlobEncode($var); $tmp->WriteTemporary($var); $this->_refLOBs[$numlob]['VAR'] = $var; if ($this->debug) { ADOConnection::outp("<b>Bind</b>: LOB has been written to temp"); } } else { $this->_refLOBs[$numlob]['VAR'] = $var; } $rez = $tmp; } else { if ($this->debug) ADOConnection::outp("<b>Bind</b>: name = $name"); if ($type !== false) $rez = OCIBindByName($stmt[1],":".$name,$var,$size,$type); else $rez = OCIBindByName($stmt[1],":".$name,$var,$size); // +1 byte for null terminator } return $rez; }
/** * 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()) { set_error_handler("empty_error_handler"); $locs = array(); if (count($blobs)) { $idx = 1; $sql .= " returning "; $blobfields = ""; $blobvars = ""; foreach ($blobs as $ekey => $value) { if (count($locs)) { $blobfields .= ","; $blobvars .= ","; } $blobfields .= $ekey; $blobvars .= ":bnd" . $idx; $locs[$ekey] = OCINewDescriptor($this->conn, OCI_D_LOB); $idx++; } $sql .= $blobfields . " into " . $blobvars; } $stmt = OCIParse($this->conn, $sql); $idx = 1; foreach ($locs as $ekey => $value) { OCIBindByName($stmt, ":bnd" . $idx, $locs[$ekey], -1, OCI_B_BLOB); $idx++; } $result = OCIExecute($stmt, OCI_DEFAULT) !== false; foreach ($locs as $ekey => $value) { $locs[$ekey]->save($blobs[$ekey]); $locs[$ekey]->free(); } OCICommit($this->conn); OCIFreeStatement($stmt); set_error_handler("runner_error_handler"); return $result; }