$thumb = createThumbnail($image_dir, $image_dir . '_thumb.jpg', 50, 50);
if (!$thumb) {
echo "Sorry, an error has occurred while creating thumbnail";
$uploadOk = 0;
}
//Attempt to put image into database
//Code stolen and adapted from https://stackoverflow.com/questions/11970258/upload-images-as-blobs-in-oracle-using-php
$conn = connect();
//RECOREDED DATA VS. RECOREDED DATA!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
$sql = 'INSERT INTO images(image_id, sensor_id, date_created, description, thumbnail, recorded_data)
VALUES (\'' . $_POST['image_id'] . '\', \'' . $_POST['sensor_id'] . '\', to_date(\'' . $_POST['date_created'] . '\',
\'dd/mm/yyyy HH24:Mi:SS\'), \'' . $_POST['description'] . '\', empty_blob(), empty_blob())
RETURNING thumbnail, recorded_data INTO :thumbnail, :recorded_data';
$stid = oci_parse($conn, $sql);
$tblob = oci_new_descriptor($conn, OCI_D_LOB);
$iblob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stid, ':thumbnail', $tblob, -1, OCI_B_BLOB);
oci_bind_by_name($stid, ':recorded_data', $iblob, -1, OCI_B_BLOB);
oci_execute($stid, OCI_DEFAULT) or die("Unable to execute query");
if (!$tblob->save($thumb) || !$iblob->save($image)) {
oci_rollback($conn);
echo "Aborted.";
} else {
$res = oci_commit($conn);
if (!$res) {
$err = oci_error($conn);
trigger_error(htmlentities($err['message']), E_USER_ERROR);
} else {
echo "Image File Successfully Uploaded.";
//Show view of the image.
//Stolen from Gordon♦'s comment at https://stackoverflow.com/questions/3385982/the-image-cannot-be-displayed-because-it-contains-errors
/**
* Insert image data to database (recoreded_data and thumbnail).
* First generate an unique image id, then insert image to recoreded_data and resized image to thubnail along with
* other given data
*/
function uploadImage($conn, $sensor_id, $date_created, $description)
{
$image_id = generateId($conn, "images");
if ($image_id == 0) {
return;
}
$image2 = file_get_contents($_FILES['file_image']['tmp_name']);
$image2tmp = resizeImage($_FILES['file_image']);
$image2Thumbnail = file_get_contents($image2tmp['tmp_name']);
// encode the stream
$image = base64_encode($image2);
$imageThumbnail = base64_encode($image2Thumbnail);
$sql = "INSERT INTO images (image_id, sensor_id, date_created, description, thumbnail, recoreded_data)\n VALUES(" . $image_id . ", " . $sensor_id . ", TO_DATE('" . $date_created . "', 'DD/MM/YYYY hh24:mi:ss'), '" . $description . "', empty_blob(), empty_blob())\n RETURNING thumbnail, recoreded_data INTO :thumbnail, :recoreded_data";
$result = oci_parse($conn, $sql);
$recoreded_dataBlob = oci_new_descriptor($conn, OCI_D_LOB);
$thumbnailBlob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($result, ":recoreded_data", $recoreded_dataBlob, -1, OCI_B_BLOB);
oci_bind_by_name($result, ":thumbnail", $thumbnailBlob, -1, OCI_B_BLOB);
$res = oci_execute($result, OCI_DEFAULT) or die("Unable to execute query");
if ($recoreded_dataBlob->save($image) && $thumbnailBlob->save($imageThumbnail)) {
oci_commit($conn);
} else {
oci_rollback($conn);
}
oci_free_statement($result);
$recoreded_dataBlob->free();
$thumbnailBlob->free();
echo "New image is added with image_id ->" . $image_id . "<br>";
}
function insertImage($conn, $photo_id, $owner_name, $descriptive_info, $thumbnail, $photo)
{
$photo_blob = oci_new_descriptor($conn, OCI_D_LOB);
$thumbnail_blob = oci_new_descriptor($conn, OCI_D_LOB);
$subject = $descriptive_info[0];
$place = $descriptive_info[1];
$date_time = $descriptive_info[2];
$description = $descriptive_info[3];
$permitted = $descriptive_info[4];
$sql = 'INSERT INTO images (photo_id, owner_name, permitted, subject, place,
timing, description, thumbnail, photo) VALUES (:photoid, :ownername,
:permitted, :subject, :place, TO_DATE(:datetime, \'MM/DD/YYYY\'), :description, empty_blob(),
empty_blob()) returning thumbnail, photo into :thumbnail, :photo';
$stid = oci_parse($conn, $sql);
oci_bind_by_name($stid, ':photoid', $photo_id);
oci_bind_by_name($stid, ':ownername', $owner_name);
oci_bind_by_name($stid, ':permitted', $permitted);
oci_bind_by_name($stid, ':subject', $subject);
oci_bind_by_name($stid, ':place', $place);
oci_bind_by_name($stid, ':datetime', $date_time);
oci_bind_by_name($stid, ':description', $description);
oci_bind_by_name($stid, ':thumbnail', $thumbnail_blob, -1, OCI_B_BLOB);
oci_bind_by_name($stid, ':photo', $photo_blob, -1, OCI_B_BLOB);
$res = oci_execute($stid, OCI_DEFAULT);
if ($thumbnail_blob->save($thumbnail) && $photo_blob->save($photo)) {
oci_commit($conn);
} else {
oci_rollback($conn);
}
oci_free_statement($stid);
$photo_blob->free();
$thumbnail_blob->free();
}
public function prep_row_id()
{
if ($new_id = @oci_new_descriptor($this->db_handle, OCI_D_ROWID)) {
return $new_id;
} else {
$this->handle_error('new_descriptor', oci_error($this->db_handle));
return false;
}
}
public function execute($sql, array $data = null)
{
$this->connect();
if (!is_array($data)) {
$data = array();
}
$data = array_values($data);
$lob = null;
$ldt = null;
foreach ($data as $i => $v) {
switch (gettype($v)) {
case 'boolean':
case 'integer':
$data[$i] = (int) $v;
oci_bind_by_name($sql, 'f' . $i, $data[$i], -1, SQLT_INT);
break;
default:
// keep in mind oracle needs a transaction when inserting LOBs, aside from the specific syntax:
// INSERT INTO table (column, lobcolumn) VALUES (?, ?, EMPTY_BLOB()) RETURNING lobcolumn INTO ?
if (is_resource($v) && get_resource_type($v) === 'stream') {
$ldt = $v;
$lob = oci_new_descriptor($this->lnk, OCI_D_LOB);
oci_bind_by_name($sql, 'f' . $i, $lob, -1, OCI_B_BLOB);
continue;
}
if (!is_string($data[$i]) && !is_null($data[$i])) {
$data[$i] = serialize($data[$i]);
}
oci_bind_by_name($sql, 'f' . $i, $data[$i]);
break;
}
}
$temp = oci_execute($sql, $this->transaction ? OCI_NO_AUTO_COMMIT : OCI_COMMIT_ON_SUCCESS);
if (!$temp) {
throw new DatabaseException('Could not execute query : ' . oci_error($sql));
}
if ($lob) {
while (!feof($ldt) && ($ltmp = fread($ldt, 8192)) !== false) {
$lob->write($ltmp);
$lob->flush();
}
$lob->free();
}
$this->aff = oci_num_rows($sql);
return $sql;
}
/**
* Binds a value
*
* @param mixed $param param (column)
* @param mixed $value value for param
* @param mixed $type optional data type
*
* @return bool bound
*/
public function bindValue($param, $value, $type = null)
{
$ok = false;
try {
$param = $this->_getBindVar($param);
if (PDO::PARAM_LOB == $type) {
$lob = \oci_new_descriptor($this->_con, \OCI_D_LOB);
$ok = \oci_bind_by_name($this->_stmt, $param, $lob, -1, \OCI_B_BLOB);
$this->_bindsLob[$param] = array('lob' => $lob, 'value' => $value);
} else {
$ok = \oci_bind_by_name($this->_stmt, $param, $value);
$this->_binds[$param] = $value;
}
} catch (\Exception $e) {
throw new \PDOException($e->getMessage());
}
return $ok;
}
public function ajouterContenu($id_fichier, $contenu)
{
$sql = "INSERT INTO CONTENU(ID_FICHIER,CONTENU) VALUES ({$id_fichier}, EMPTY_CLOB())\n RETURNING CONTENU INTO :CONTENU_loc";
$conn = oci_connect("DBA_PARAPHEUR", "12345678", "XE");
$stmt = oci_parse($conn, $sql);
// Creates an "empty" OCI-Lob object to bind to the locator
$clob = oci_new_descriptor($conn, OCI_D_LOB);
// Bind the returned Oracle LOB locator to the PHP LOB object
oci_bind_by_name($stmt, ":CONTENU_loc", $clob, -1, OCI_B_CLOB);
// Execute the statement using , OCI_DEFAULT - as a transaction
oci_execute($stmt, OCI_DEFAULT) or die("Unable to execute query\n");
// Now save a value to the clob
if (!$clob->save($contenu)) {
// On error, rollback the transaction
oci_rollback($conn);
} else {
// On success, commit the transaction
oci_commit($conn);
}
}
public function bindParam($parameter, &$variable, $data_type = -1, $length = 0, $driver_options = null)
{
if ($parameter[0] != ':' && !is_numeric($parameter)) {
$parameter = ':' . $parameter;
}
if (!$length) {
$length = -1;
}
$params_info =& $this->_params_info;
switch ($data_type) {
case PDO::PARAM_INT:
$type = SQLT_INT;
break;
case PDO::PARAM_LOB:
if (isset($params_info[$parameter])) {
$p = $params_info[$parameter];
$lob = oci_new_descriptor($this->_link, OCI_DTYPE_LOB);
if (oci_bind_by_name($this->_result, $p, $lob, -1, SQLT_BLOB)) {
$this->_bound_params[$p] = 1;
$this->lobs[$p] = array(&$lob, &$variable);
return true;
}
oci_free_descriptor($lob);
}
return false;
break;
default:
$type = SQLT_CHR;
break;
}
if (isset($params_info[$parameter]) && oci_bind_by_name($this->_result, $params_info[$parameter], $variable, $length, $type)) {
$this->_bound_params[$params_info[$parameter]] = 1;
return true;
}
return false;
}
/**
* 新建生产线
*/
public function set_productline($showxml)
{
$this->load->helper('url');
$this->load->helper('oracledb');
$id = getCurSequeens("SEQ_PPC_PRODUCTLINE");
$codes = $this->input->post('selectcodes');
$staid = $this->input->post('staid');
$devcode = $this->input->post('devcode');
$isload = $this->input->post('isload');
$linetype = $this->input->post('linetype');
$caption = "pl_" . $id . "_" . $staid;
$alias = $this->input->post('alias');
$data = array('ID' => $id, 'Code' => $codes, 'SatCode' => $staid, 'Caption' => $caption, 'Alias' => $alias, 'DevCode' => $devcode, 'LINETYPE' => $linetype, 'ISLOAD' => $isload, 'Version' => 'a0', 'RawVer' => '-1', 'Status' => "run", 'Author' => "", 'ISVIEW' => 1);
//以下是直接使用oci做clob的插入
$sid = $this->config->item('dbusrsid');
$password = $this->config->item('dbusrpassword');
$dburl = $this->config->item('dbusrurl');
$conn = oci_connect($sid, $password, $dburl, "AL32UTF8");
$sql = "INSERT INTO\n \"T_PPC_ProductLine\"\n (\n ID, \"Code\",\"SatCode\",\"Caption\",\"Alias\",\"RawVer\",\"Status\",\"DevCode\",LINETYPE,\"Author\",\"Version\",ISVIEW,ISLOAD,\n PLXML,\"ShowXml\"\n )\n VALUES\n ( " . $id . ",'" . $codes . "','" . $staid . "','" . $caption . "','" . $alias . "',-1,'run','" . $devcode . "','" . $linetype . "','','a0',1," . $isload . ",EMPTY_CLOB(), EMPTY_CLOB()\n )\n RETURNING\n PLXML,\"ShowXml\" INTO :pllob,:showlob";
// echo $sql;
$stmt = oci_parse($conn, $sql);
$PlLOB = oci_new_descriptor($conn, OCI_D_LOB);
$showLOB = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stmt, ":pllob", $PlLOB, -1, OCI_B_CLOB);
oci_bind_by_name($stmt, ":showlob", $showLOB, -1, OCI_B_CLOB);
oci_execute($stmt, OCI_DEFAULT) or die("Unable to execute query\n");
if (!$PlLOB->save('') || !$showLOB->save('')) {
oci_rollback($conn);
} else {
oci_commit($conn);
}
// Free resources
oci_free_statement($stmt);
$PlLOB->free();
$showLOB->free();
// $showxml = $this->input->post('formxml');
updateShowCLOB($id, $showxml);
$cdate = updateTime($id, 'T_PPC_ProductLine', 'CreateTime');
$data['CreateTime'] = $cdate;
return $data;
}
function DBSaveLob($connection, $sql, $blobParamName, $data, $lobType)
{
// Guarda datos en un clob..
global $dbError;
$lob = oci_new_descriptor($connection, OCI_D_LOB);
$stmt = oci_parse($connection, $sql);
oci_bind_by_name($stmt, ":" . $blobParamName, $lob, -1, $lobType);
$error = !oci_execute($stmt, OCI_DEFAULT);
$result = $lob->write($data);
if ($result) {
oci_commit($connection);
}
if ($error) {
$dbError = oci_error($stmt);
if (isset($dbError["offset"])) {
throw new Exception($dbError["message"]);
}
}
return $result;
}
function insertOneRow($table, $row, $fname)
{
// "INSERT INTO tables (a, b, c)"
$sql = "INSERT INTO " . $this->tableName($table) . " (" . join(',', array_keys($row)) . ')';
$sql .= " VALUES (";
// for each value, append ":key"
$first = true;
$returning = '';
foreach ($row as $col => $val) {
if (is_object($val)) {
$what = "EMPTY_BLOB()";
assert($returning === '');
$returning = " RETURNING {$col} INTO :bval";
$blobcol = $col;
} else {
$what = ":{$col}";
}
if ($first) {
$sql .= "{$what}";
} else {
$sql .= ", {$what}";
}
$first = false;
}
$sql .= ") {$returning}";
$stmt = oci_parse($this->mConn, $sql);
foreach ($row as $col => $val) {
if (!is_object($val)) {
if (oci_bind_by_name($stmt, ":{$col}", $row[$col]) === false) {
$this->reportQueryError($this->lastErrno(), $this->lastError(), $sql, __METHOD__);
}
}
}
if (($bval = oci_new_descriptor($this->mConn, OCI_D_LOB)) === false) {
$e = oci_error($stmt);
throw new DBUnexpectedError($this, "Cannot create LOB descriptor: " . $e['message']);
}
if (strlen($returning)) {
oci_bind_by_name($stmt, ":bval", $bval, -1, SQLT_BLOB);
}
if (oci_execute($stmt, OCI_DEFAULT) === false) {
$e = oci_error($stmt);
$this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__);
}
if (strlen($returning)) {
$bval->save($row[$blobcol]->getData());
$bval->free();
}
if (!$this->mTrxLevel) {
oci_commit($this->mConn);
}
oci_free_statement($stmt);
}
/**
* @param string $paramIndex
* @param mixed $clob Clob object or string containing data.
* @return void
*/
function setClob($paramIndex, $clob)
{
require_once CREOLE_ROOT . 'util/Clob.php';
if (!$clob instanceof Clob) {
$c = new Clob();
$c->setContents($clob);
$clob = $c;
}
$this->lobDescriptors[$paramIndex] = oci_new_descriptor($this->conn->getResource(), OCI_D_LOB);
$this->lobs[$paramIndex] = $clob;
}
/**
* Build and execute a database update query from an array of keys/values.
*
* @param string The table to insert into.
* @param array Associative array containing key/value pairs to update.
* @param string The where clause to apply to the update
* @param bool TRUE to interpret NULL as being database NULL, FALSE to mean an empty string
*
* @return boolean True on success, false on error.
*/
function UpdateQuery($table, $values, $where = "", $useNullValues = false)
{
$fields = array();
$lobVals = array();
$lobDesc = array();
foreach ($values as $k => $v) {
if (strlen($v) > 4000) {
$lobVals[':' . $k] = $v;
$lobDesc[':' . $k] = oci_new_descriptor($this->connection, OCI_D_LOB);
$v = ':' . $k;
} else {
if ($useNullValues) {
if (is_null($v)) {
$v = " ";
} else {
$v = "'" . $this->Quote($v) . "'";
}
} else {
$v = "'" . $this->Quote($v) . "'";
}
}
$fields[] = sprintf("%s=%s", $k, $v);
}
$fields = implode(",", $fields);
if ($where != "") {
$fields .= sprintf(" WHERE %s", $where);
}
$query = sprintf('UPDATE [|PREFIX|]%s SET %s', $table, $fields);
if ($this->TablePrefix !== null) {
$query = str_replace("[|PREFIX|]", $this->TablePrefix, $query);
} else {
$query = str_replace("[|PREFIX|]", '', $query);
}
$resource = oci_parse($this->connection, $query);
if (!$resource) {
$e = oci_error($this->connection);
$this->SetError($e['message']);
return false;
}
foreach ($lobDesc as $k => $v) {
oci_bind_by_name($resource, $k, $lobDesc[$k], -1, OCI_B_CLOB);
$lobDesc[$k]->WriteTemporary($lobVals[$k]);
}
$result = oci_execute($resource);
foreach ($lobDesc as $k => $v) {
$lobDesc[$k]->close();
$lobDesc[$k]->free();
}
if (!$result) {
$e = oci_error($resource);
$this->SetError($e['message']);
return false;
}
return oci_commit($this->connection);
}
// description
$description = $_POST['desc_audio'];
// Audio Type Check
$audioType = $_FILES['file_audio']['type'];
if ($audioType != "audio/wav") {
echo "File Not Found/Extension not allowed, please choose a wav file";
} else {
$audio_id = generateId($conn, "audio_recordings");
if ($audio_id == 0) {
return;
}
$audio2 = file_get_contents($_FILES['file_audio']['tmp_name']);
$audio = base64_encode($audio2);
$sql = "INSERT INTO audio_recordings(recording_id, sensor_id, date_created, length, description, recorded_data)\n VALUES(" . $audio_id . "," . $sensor_id . ", TO_DATE('" . $date_created . "', 'DD/MM/YYYY hh24:mi:ss')," . $length . ",'" . $description . "',empty_blob())\n RETURNING recorded_data INTO :recorded_data";
$result = oci_parse($conn, $sql);
$recorded_dataBlob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($result, ":recorded_data", $recorded_dataBlob, -1, OCI_B_BLOB);
$res = oci_execute($result, OCI_DEFAULT) or die("Unable to execute query");
if ($recorded_dataBlob->save($audio)) {
oci_commit($conn);
} else {
oci_rollback($conn);
}
oci_free_statement($result);
$recorded_dataBlob->free();
echo "New audio is added with image_id ->" . $audio_id . "<br>";
}
}
// ----Upload Image----
// TO DO: thumbnail -> resize the image and update the database
if (isset($_POST["submit_image"])) {
public function __construct($conn, $binary)
{
$this->conn = $conn;
$this->binary = $binary;
$this->lob = oci_new_descriptor($conn, OCI_D_LOB);
}
/**
* @param $sql
* @param array|null $arBinds
* @param $offset
* @param $limit
* @param \Bitrix\Main\Diag\SqlTrackerQuery|null $trackerQuery
* @return resource
* @throws SqlException|\Bitrix\Main\ArgumentException
*/
protected function queryInternal($sql, array $arBinds = null, $offset = 0, $limit = 0, \Bitrix\Main\Diag\SqlTrackerQuery $trackerQuery = null)
{
$this->connectInternal();
$offset = intval($offset);
$limit = intval($limit);
if ($offset > 0 && $limit <= 0) {
throw new \Bitrix\Main\ArgumentException("Limit should be set if offset is set");
}
$bindsKeys = array();
if (!empty($arBinds)) {
$binds1 = $binds2 = "";
foreach ($arBinds as $key => $value) {
if (strlen($value) > 0) {
if ($binds1 != "") {
$binds1 .= ",";
$binds2 .= ",";
}
$bindsKeys[] = $key;
$binds1 .= $key;
$binds2 .= ":" . $key;
}
}
if ($binds1 != "") {
$sql .= " RETURNING " . $binds1 . " INTO " . $binds2;
}
}
if ($limit > 0) {
if (preg_match("#\\sROWNUM\\W#i", $sql)) {
throw new \Bitrix\Main\ArgumentException("Duplicate limit settings");
}
if ($offset <= 0) {
$sql = "SELECT * " . "FROM (" . $sql . ") " . "WHERE ROWNUM <= " . $limit . "";
} else {
$sql = "SELECT * " . "FROM (" . " SELECT rownum_query_alias.*, ROWNUM rownum_alias " . " FROM (" . $sql . ") rownum_query_alias " . " WHERE ROWNUM <= " . ($offset + $limit - 1) . " " . ") " . "WHERE rownum_alias >= " . $offset . "";
}
}
if ($trackerQuery != null) {
$trackerQuery->startQuery($sql, $arBinds);
}
$result = oci_parse($this->connectionResource, $sql);
if (!$result) {
if ($trackerQuery != null) {
$trackerQuery->finishQuery();
}
throw new SqlException("", $this->getErrorMessage());
}
$executionMode = $this->transaction;
$clob = array();
if (!empty($arBinds)) {
$executionMode = OCI_DEFAULT;
foreach ($bindsKeys as $key) {
$clob[$key] = oci_new_descriptor($this->connectionResource, OCI_D_LOB);
oci_bind_by_name($result, ":" . $key, $clob[$key], -1, OCI_B_CLOB);
}
}
if (!oci_execute($result, $executionMode)) {
if ($trackerQuery != null) {
$trackerQuery->finishQuery();
}
throw new SqlException("", $this->getErrorMessage());
}
if (!empty($arBinds)) {
if (oci_num_rows($result) > 0) {
foreach ($bindsKeys as $key) {
$clob[$key]->save($arBinds[$key]);
}
}
if ($this->transaction == OCI_COMMIT_ON_SUCCESS) {
oci_commit($this->connectionResource);
}
foreach ($bindsKeys as $key) {
$clob[$key]->free();
}
}
if ($trackerQuery != null) {
$trackerQuery->finishQuery();
}
$this->lastQueryResult = $result;
return $result;
}
/**
* Special non PDO function used to start descriptor in the database
* Remember to call oci_free_statement() on your cursor
*
* @access public
*
* @param int $type One of OCI_DTYPE_FILE, OCI_DTYPE_LOB or OCI_DTYPE_ROWID.
* @return mixed New LOB or FILE descriptor on success, FALSE on error.
*/
public function getNewDescriptor($type = OCI_D_LOB)
{
return oci_new_descriptor($this->_dbh, $type);
}
private function insertOneRow($table, $row, $fname)
{
global $wgLang;
$table = $this->tableName($table);
// "INSERT INTO tables (a, b, c)"
$sql = "INSERT INTO " . $table . " (" . join(',', array_keys($row)) . ')';
$sql .= " VALUES (";
// for each value, append ":key"
$first = true;
foreach ($row as $col => $val) {
if ($first) {
$sql .= $val !== null ? ':' . $col : 'NULL';
} else {
$sql .= $val !== null ? ', :' . $col : ', NULL';
}
$first = false;
}
$sql .= ')';
$stmt = oci_parse($this->mConn, $sql);
foreach ($row as $col => &$val) {
$col_info = $this->fieldInfoMulti($table, $col);
$col_type = $col_info != false ? $col_info->type() : 'CONSTANT';
if ($val === null) {
// do nothing ... null was inserted in statement creation
} elseif ($col_type != 'BLOB' && $col_type != 'CLOB') {
if (is_object($val)) {
$val = $val->getData();
}
if (preg_match('/^timestamp.*/i', $col_type) == 1 && strtolower($val) == 'infinity') {
$val = '31-12-2030 12:00:00.000000';
}
$val = $wgLang != null ? $wgLang->checkTitleEncoding($val) : $val;
if (oci_bind_by_name($stmt, ":{$col}", $val) === false) {
$this->reportQueryError($this->lastErrno(), $this->lastError(), $sql, __METHOD__);
return false;
}
} else {
if (($lob[$col] = oci_new_descriptor($this->mConn, OCI_D_LOB)) === false) {
$e = oci_error($stmt);
throw new DBUnexpectedError($this, "Cannot create LOB descriptor: " . $e['message']);
}
if ($col_type == 'BLOB') {
// is_object($val)) {
$lob[$col]->writeTemporary($val);
// ->getData());
oci_bind_by_name($stmt, ":{$col}", $lob[$col], -1, SQLT_BLOB);
} else {
$lob[$col]->writeTemporary($val);
oci_bind_by_name($stmt, ":{$col}", $lob[$col], -1, OCI_B_CLOB);
}
}
}
wfSuppressWarnings();
if (oci_execute($stmt, OCI_DEFAULT) === false) {
$e = oci_error($stmt);
if (!$this->ignore_DUP_VAL_ON_INDEX || $e['code'] != '1') {
$this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__);
return false;
} else {
$this->mAffectedRows = oci_num_rows($stmt);
}
} else {
$this->mAffectedRows = oci_num_rows($stmt);
}
wfRestoreWarnings();
if (isset($lob)) {
foreach ($lob as $lob_i => $lob_v) {
$lob_v->free();
}
}
if (!$this->mTrxLevel) {
oci_commit($this->mConn);
}
oci_free_statement($stmt);
}
public function zalozUmowaKarta($tabDane, $strProdSymbol)
{
Kis_Logic_Debuger::wyswietlDane($tabDane);
$strModulo = $tabDane['modulo'];
//
$intDlugoscOkresu = 999;
//karta 999 - karta
$intTypOkresu = 2;
//typ okresu 2 - karta
$strDestination = '';
$strDestinationDesc = 'Konsumpcyjny gotówkowy';
$intKwota = $tabDane['kwota'];
$strWaluta = $tabDane['waluta'];
$strData = date("Ymd", $tabDane['data']);
//
$strUlica = $tabDane['ulica'];
//
$strNumerDomu = $tabDane['nr_domu'];
//
$strNumerMieszkania = $tabDane['nr_mieszkania'];
//
$strKodPocztowy = $tabDane['kod_pocztowy'];
//
$strMiasto = $tabDane['miasto'];
//
$strKraj = $tabDane['kraj'];
//
$strNazwaKlient = $tabDane['klient'];
//
$strEmeil = $tabDane['mail'];
//
$strOkres = $tabDane['okres'];
$xmlUmowaKredytowa = "<CONTRACT>\n <PROD_SYMBOL>{$strProdSymbol}</PROD_SYMBOL>\n <ROOT_NO>{$strModulo}</ROOT_NO>\n <CONTRACT_NO></CONTRACT_NO>\n <PERIOD>{$intDlugoscOkresu}</PERIOD>\n <TIMESCALE>{$intTypOkresu}</TIMESCALE>\n <AMOUNT>{$intKwota}</AMOUNT>\n <CURRENCY>{$strWaluta}</CURRENCY>\n <START_DATE></START_DATE>\n <VARIANT_TYPE></VARIANT_TYPE>\n <INT_TYPE>2</INT_TYPE>\n <TRANCHE_AVAILABLE></TRANCHE_AVAILABLE>\n <INT_IN_TRANS_TYPE>1</INT_IN_TRANS_TYPE>\n <SETTLEMENT_CYCLE>{$strOkres}</SETTLEMENT_CYCLE>\n </CONTRACT>\n <CORRESP_ADDRESS_DATA>\n <STREET_PREF>1</STREET_PREF>\n <STREET>{$strUlica}</STREET>\n <HOUSE_NO>{$strNumerDomu}</HOUSE_NO>\n <APARTMENTS_NO>{$strNumerMieszkania}</APARTMENTS_NO>\n <CORRESP_POST_CODE>{$strKodPocztowy}</CORRESP_POST_CODE>\n <CORRESP_TOWN>{$strMiasto}</CORRESP_TOWN>\n <CORRESP_COUNTRY>{$strKraj}</CORRESP_COUNTRY>\n <CUST_CORRESP_NAME>{$strNazwaKlient}</CUST_CORRESP_NAME>\n <EMAIL>{$strEmeil}</EMAIL>\n </CORRESP_ADDRESS_DATA>\n ";
$xmlWyjsciowy = oci_new_descriptor($this->oracle_link, OCI_D_LOB);
$zap = oci_parse($this->oracle_link, "begin :errr := NBL_API.INSUPD_CONTRACT(:head,:xml_wejsciowy,:xml_wyjsciowy); end;");
oci_define_by_name($zap, ':xml_wyjsciowy', $xmlWyjsciowy, SQLT_CLOB);
oci_bind_by_name($zap, ":head", $this->strNaglowekXML);
oci_bind_by_name($zap, ":errr", $error, 2000, SQLT_CHR);
oci_bind_by_name($zap, ":xml_wejsciowy", $xmlUmowaKredytowa);
oci_bind_by_name($zap, ":xml_wyjsciowy", $xmlWyjsciowy, -1, SQLT_CLOB);
$wynik = oci_execute($zap);
if (!$wynik) {
$this->intCzyBledy++;
$this->debuger("Nie udana próba założenie umowy kredytowej");
}
Kis_Logic_Debuger::wyswietlDane($error);
if (!is_object($xmlWyjsciowy)) {
throw new Kis_Logic_DefException("Nie otrzymano xml zwrotnego {$error}");
}
$clobXml = $xmlWyjsciowy->size();
$xmlWynik = $xmlWyjsciowy->read($clobXml);
if ($xmlWynik !== '') {
// $xmlWynik = iconv("ISO-8859-2","UTF-8",$xmlWynik);
$domXml = new DOMDocument();
$domXml->loadXML($xmlWynik);
$docElem = $domXml->documentElement;
$strIdDef = $docElem->getElementsByTagName("ID_CONTRACT")->item(0)->nodeValue;
$strRachunekWplata = $docElem->getElementsByTagName("CONTRACT_NRB")->item(0)->nodeValue;
$RefNo = $docElem->getElementsByTagName("REF_NO")->item(0)->nodeValue;
$tabDaneZwracane = array();
$tabDaneZwracane['id_kontrakt'] = $strIdDef;
$tabDaneZwracane['rachunek_nrb'] = $strRachunekWplata;
$tabDaneZwracane['ref_no'] = addslashes($RefNo);
return $tabDaneZwracane;
} else {
$this->intCzyBledy++;
$this->debuger($error);
return 0;
}
}
protected function bind_params($stmt, array &$params = null, $tablename = null, array &$descriptors = null)
{
if ($params) {
$columns = array();
if ($tablename) {
$columns = $this->get_columns($tablename);
}
foreach ($params as $key => $value) {
// Decouple column name and param name as far as sometimes they aren't the same
if ($key == 'o_newfieldtoset') {
// found case where column and key diverge, handle that
$columnname = key($value);
// columnname is the key of the array
$params[$key] = $value[$columnname];
// set the proper value in the $params array and
$value = $value[$columnname];
// set the proper value in the $value variable
} else {
$columnname = preg_replace('/^o_/', '', $key);
// Default columnname (for DB introspecting is key), but...
}
// Continue processing
// Now, handle already detected LOBs
if (is_array($value)) {
// Let's go to bind special cases (lob descriptors)
if (isset($value['clob'])) {
$lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB);
if ($descriptors === null) {
throw new coding_exception('moodle_database::bind_params() $descriptors not specified for clob');
}
$descriptors[] = $lob;
oci_bind_by_name($stmt, $key, $lob, -1, SQLT_CLOB);
$lob->writeTemporary($this->oracle_dirty_hack($tablename, $columnname, $params[$key]['clob']), OCI_TEMP_CLOB);
continue;
// Column binding finished, go to next one
} else {
if (isset($value['blob'])) {
$lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB);
if ($descriptors === null) {
throw new coding_exception('moodle_database::bind_params() $descriptors not specified for clob');
}
$descriptors[] = $lob;
oci_bind_by_name($stmt, $key, $lob, -1, SQLT_BLOB);
$lob->writeTemporary($params[$key]['blob'], OCI_TEMP_BLOB);
continue;
// Column binding finished, go to next one
}
}
} else {
// If, at this point, the param value > 4000 (bytes), let's assume it's a clob
// passed in an arbitrary sql (not processed by normalise_value() ever,
// and let's handle it as such. This will provide proper binding of CLOBs in
// conditions and other raw SQLs not covered by the above function.
if (strlen($value) > 4000) {
$lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB);
if ($descriptors === null) {
throw new coding_exception('moodle_database::bind_params() $descriptors not specified for clob');
}
$descriptors[] = $lob;
oci_bind_by_name($stmt, $key, $lob, -1, SQLT_CLOB);
$lob->writeTemporary($this->oracle_dirty_hack($tablename, $columnname, $params[$key]), OCI_TEMP_CLOB);
continue;
// Param binding finished, go to next one.
}
}
// TODO: Put proper types and length is possible (enormous speedup)
// Arrived here, continue with standard processing, using metadata if possible
if (isset($columns[$columnname])) {
$type = $columns[$columnname]->meta_type;
$maxlength = $columns[$columnname]->max_length;
} else {
$type = '?';
$maxlength = -1;
}
switch ($type) {
case 'I':
case 'R':
// TODO: Optimise
oci_bind_by_name($stmt, $key, $params[$key]);
break;
case 'N':
case 'F':
// TODO: Optimise
oci_bind_by_name($stmt, $key, $params[$key]);
break;
case 'B':
// TODO: Only arrive here if BLOB is null: Bind if so, else exception!
// don't break here
// TODO: Only arrive here if BLOB is null: Bind if so, else exception!
// don't break here
case 'X':
// TODO: Only arrive here if CLOB is null or <= 4000 cc, else exception
// don't break here
// TODO: Only arrive here if CLOB is null or <= 4000 cc, else exception
// don't break here
default:
// Bind as CHAR (applying dirty hack)
// TODO: Optimise
$params[$key] = $this->oracle_dirty_hack($tablename, $columnname, $params[$key]);
// Because of PHP7 bug (https://bugs.php.net/bug.php?id=72524) it seems that it's
// impossible to bind NULL values in a reliable way, let's use empty string
// instead in the mean time.
if ($params[$key] === null && version_compare(PHP_VERSION, '7.0.0', '>=')) {
$params[$key] = '';
}
oci_bind_by_name($stmt, $key, $params[$key]);
}
}
}
return $descriptors;
}
function publish($conn, $id, $name, $picture)
{
$image = file_get_contents($picture);
$stid = oci_parse($conn, 'INSERT INTO PUBLICATIONS (ID, OWNER, PICTURE, NAME, PDATE) VALUES(SPUBLICATIONS.nextval, :1, empty_blob(), :2, SYSDATE) RETURNING PICTURE INTO :PICTURE');
oci_bind_by_name($stid, ':1', $_SESSION['user_id']);
oci_bind_by_name($stid, ':2', $_POST['name']);
$blob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stid, ":PICTURE", $blob, -1, OCI_B_BLOB);
oci_execute($stid, OCI_DEFAULT) or die("Unable to execute query");
if (!$blob->save($image)) {
oci_rollback($conn);
} else {
oci_commit($conn);
}
oci_free_statement($stid);
$blob->free();
}
/**
* Executes a prepared statement.
*
* @param array $params OPTIONAL Values to bind to parameter placeholders.
* @return bool
* @throws Zend_Db_Statement_Exception
*/
public function _execute(array $params = null)
{
$output_arr = array();
$connection = $this->_adapter->getConnection();
if (!$this->_stmt) {
return false;
}
if ($params !== null) {
if (!is_array($params)) {
$params = array($params);
}
$error = false;
$output_arr = array();
foreach ($params as $key => &$variable) {
$pos1 = strpos($key, '(');
$pos2 = strpos($key, ')');
$pos3 = strpos($key, '{');
$pos4 = strpos($key, '}');
$type = null;
$output = null;
if ($pos1 !== false || $pos2 !== false) {
$type = trim(substr($key, $pos1 + 1, $pos2 - $pos1 - 1));
}
if ($pos3 !== false || $pos4 !== false) {
$output = trim(substr($key, $pos3 + 1, $pos4 - $pos3 - 1));
}
$key = preg_replace('!\\{.*?\\}!sim', '', $key);
$key = preg_replace('!\\(.*?\\)!sim', '', $key);
$key = trim($key);
$bindReturn = false;
if (strtolower(trim($output)) == 'output') {
if ($type != 'SQLT_INT' && $type != 'SQLT_CHR') {
$variable = oci_new_descriptor($connection, OCI_DTYPE_LOB);
}
$output_arr[$key] =& $variable;
if (!is_null($type)) {
$bindReturn = @oci_bind_by_name($this->_stmt, $key, $variable, -1, constant($type));
} else {
$bindReturn = @oci_bind_by_name($this->_stmt, $key, $variable, -1, SQLT_CLOB);
}
} else {
if (!is_null($type)) {
$bindReturn = @oci_bind_by_name($this->_stmt, $key, $variable, -1, constant($type));
} else {
$bindReturn = @oci_bind_by_name($this->_stmt, $key, $variable);
}
}
if (!$bindReturn) {
$error = true;
break;
}
}
if ($error) {
/**
* @see Zend_Db_Adapter_Oracle_Exception
*/
require_once 'Zend/Db/Statement/Oracle/Exception.php';
throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));
}
}
$retval = @oci_execute($this->_stmt, $this->_adapter->_getExecuteMode());
if ($retval === false) {
/**
* @see Zend_Db_Adapter_Oracle_Exception
*/
require_once 'Zend/Db/Statement/Oracle/Exception.php';
throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));
}
foreach ($output_arr as $key => &$_var) {
if (is_object($_var)) {
$size = $_var->size();
$_var = $_var->read($size);
}
}
$this->_keys = array();
if ($field_num = oci_num_fields($this->_stmt)) {
for ($i = 1; $i <= $field_num; $i++) {
$name = oci_field_name($this->_stmt, $i);
$this->_keys[] = $name;
}
}
$this->_values = array();
if ($this->_keys) {
$this->_values = array_fill(0, count($this->_keys), null);
}
return $retval;
}
/**
* Executes a query against connected database.
* Rises SqlQueryException on any database error.
* <p>
* When object $trackerQuery passed then calls its startQuery and finishQuery
* methods before and after query execution.
*
* @param string $sql Sql query.
* @param array $binds Array of binds.
* @param \Bitrix\Main\Diag\SqlTrackerQuery $trackerQuery Debug collector object.
*
* @return resource
* @throws \Bitrix\Main\Db\SqlQueryException
*/
protected function queryInternal($sql, array $binds = null, \Bitrix\Main\Diag\SqlTrackerQuery $trackerQuery = null)
{
$this->connectInternal();
if ($trackerQuery != null) {
$trackerQuery->startQuery($sql, $binds);
}
$result = oci_parse($this->resource, $sql);
if (!$result) {
if ($trackerQuery != null) {
$trackerQuery->finishQuery();
}
throw new SqlQueryException("", $this->getErrorMessage($this->resource), $sql);
}
$executionMode = $this->transaction;
/** @var \OCI_Lob[] $clob */
$clob = array();
if (!empty($binds)) {
$executionMode = OCI_DEFAULT;
foreach ($binds as $key => $val) {
$clob[$key] = oci_new_descriptor($this->resource, OCI_DTYPE_LOB);
oci_bind_by_name($result, ":" . $key, $clob[$key], -1, OCI_B_CLOB);
}
}
if (!oci_execute($result, $executionMode)) {
if ($trackerQuery != null) {
$trackerQuery->finishQuery();
}
throw new SqlQueryException("", $this->getErrorMessage($result), $sql);
}
if (!empty($binds)) {
if (oci_num_rows($result) > 0) {
foreach ($binds as $key => $val) {
if ($clob[$key]) {
$clob[$key]->save($binds[$key]);
}
}
}
if ($this->transaction == OCI_COMMIT_ON_SUCCESS) {
oci_commit($this->resource);
}
foreach ($binds as $key => $val) {
if ($clob[$key]) {
$clob[$key]->free();
}
}
}
if ($trackerQuery != null) {
$trackerQuery->finishQuery();
}
$this->lastQueryResult = $result;
return $result;
}
function update($table, $values, $conds, $fname = 'DatabaseOracle::update', $options = array())
{
global $wgContLang;
$table = $this->tableName($table);
$opts = $this->makeUpdateOptions($options);
$sql = "UPDATE {$opts} {$table} SET ";
$first = true;
foreach ($values as $col => &$val) {
$sqlSet = $this->fieldBindStatement($table, $col, $val, true);
if (!$first) {
$sqlSet = ', ' . $sqlSet;
} else {
$first = false;
}
$sql .= $sqlSet;
}
if ($conds !== array() && $conds !== '*') {
$conds = $this->wrapConditionsForWhere($table, $conds);
$sql .= ' WHERE ' . $this->makeList($conds, LIST_AND);
}
if (($this->mLastResult = $stmt = oci_parse($this->mConn, $sql)) === false) {
$e = oci_error($this->mConn);
$this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__);
return false;
}
foreach ($values as $col => &$val) {
$col_info = $this->fieldInfoMulti($table, $col);
$col_type = $col_info != false ? $col_info->type() : 'CONSTANT';
if ($val === null) {
// do nothing ... null was inserted in statement creation
} elseif ($col_type != 'BLOB' && $col_type != 'CLOB') {
if (is_object($val)) {
$val = $val->getData();
}
if (preg_match('/^timestamp.*/i', $col_type) == 1 && strtolower($val) == 'infinity') {
$val = '31-12-2030 12:00:00.000000';
}
$val = $wgContLang != null ? $wgContLang->checkTitleEncoding($val) : $val;
if (oci_bind_by_name($stmt, ":{$col}", $val) === false) {
$e = oci_error($stmt);
$this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__);
return false;
}
} else {
if (($lob[$col] = oci_new_descriptor($this->mConn, OCI_D_LOB)) === false) {
$e = oci_error($stmt);
throw new DBUnexpectedError($this, "Cannot create LOB descriptor: " . $e['message']);
}
if ($col_type == 'BLOB') {
$lob[$col]->writeTemporary($val);
oci_bind_by_name($stmt, ":{$col}", $lob[$col], -1, SQLT_BLOB);
} else {
$lob[$col]->writeTemporary($val);
oci_bind_by_name($stmt, ":{$col}", $lob[$col], -1, OCI_B_CLOB);
}
}
}
wfSuppressWarnings();
if (oci_execute($stmt, $this->execFlags()) === false) {
$e = oci_error($stmt);
if (!$this->ignore_DUP_VAL_ON_INDEX || $e['code'] != '1') {
$this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__);
return false;
} else {
$this->mAffectedRows = oci_num_rows($stmt);
}
} else {
$this->mAffectedRows = oci_num_rows($stmt);
}
wfRestoreWarnings();
if (isset($lob)) {
foreach ($lob as $lob_v) {
$lob_v->free();
}
}
if (!$this->mTrxLevel) {
oci_commit($this->mConn);
}
oci_free_statement($stmt);
}
/**
* Bind a variable -- very, very fast for executing repeated statements in oracle.
*
* Better than using
* for ($i = 0; $i < $max; $i++) {
* $p1 = ?; $p2 = ?; $p3 = ?;
* $this->Execute("insert into table (col0, col1, col2) values (:0, :1, :2)", array($p1,$p2,$p3));
* }
*
* Usage:
* $stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)");
* $DB->Bind($stmt, $p1);
* $DB->Bind($stmt, $p2);
* $DB->Bind($stmt, $p3);
* for ($i = 0; $i < $max; $i++) {
* $p1 = ?; $p2 = ?; $p3 = ?;
* $DB->Execute($stmt);
* }
*
* Some timings to insert 1000 records, test table has 3 cols, and 1 index.
* - Time 0.6081s (1644.60 inserts/sec) with direct oci_parse/oci_execute
* - Time 0.6341s (1577.16 inserts/sec) with ADOdb Prepare/Bind/Execute
* - Time 1.5533s ( 643.77 inserts/sec) with pure SQL using Execute
*
* Now if PHP only had batch/bulk updating like Java or PL/SQL...
*
* Note that the order of parameters differs from oci_bind_by_name,
* because we default the names to :0, :1, :2
*/
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 oci_bind_by_name($stmt[1], ":" . $name, $stmt[4], $size, $type);
}
if ($name == false) {
if ($type !== false) {
$rez = oci_bind_by_name($stmt[1], ":" . $stmt[2], $var, $size, $type);
} else {
$rez = oci_bind_by_name($stmt[1], ":" . $stmt[2], $var, $size);
// +1 byte for null terminator
}
$stmt[2] += 1;
} else {
if (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'] = oci_new_descriptor($this->_connectionID, oci_lob_desc($type));
$this->_refLOBs[$numlob]['TYPE'] = $isOutput;
$tmp = $this->_refLOBs[$numlob]['LOB'];
$rez = oci_bind_by_name($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 = oci_bind_by_name($stmt[1], ":" . $name, $var, $size, $type);
} else {
$rez = oci_bind_by_name($stmt[1], ":" . $name, $var, $size);
// +1 byte for null terminator
}
}
}
return $rez;
}
/**
* {@inheritdoc}
*/
public function bindParam($column, &$variable, $type = null, $length = null)
{
$column = isset($this->_paramMap[$column]) ? $this->_paramMap[$column] : $column;
if ($type == \PDO::PARAM_LOB) {
$lob = oci_new_descriptor($this->_dbh, OCI_D_LOB);
$lob->writeTemporary($variable, OCI_TEMP_BLOB);
return oci_bind_by_name($this->_sth, $column, $lob, -1, OCI_B_BLOB);
} elseif ($length !== null) {
return oci_bind_by_name($this->_sth, $column, $variable, $length);
}
return oci_bind_by_name($this->_sth, $column, $variable);
}
/**
* @param $sql
* @param array|null $arBinds
* @param $offset
* @param $limit
* @param \Bitrix\Main\Diag\SqlTrackerQuery|null $trackerQuery
* @return resource
* @throws SqlException|\Bitrix\Main\ArgumentException
*/
protected function queryInternal($sql, array $arBinds = null, $offset = 0, $limit = 0, \Bitrix\Main\Diag\SqlTrackerQuery $trackerQuery = null)
{
$this->connectInternal();
if ($limit > 0) {
$sql = $this->getSqlHelper()->getTopSql($sql, $limit, $offset);
}
$bindsKeys = array();
if (!empty($arBinds)) {
$binds1 = $binds2 = "";
foreach ($arBinds as $key => $value) {
if (strlen($value) > 0) {
if ($binds1 != "") {
$binds1 .= ",";
$binds2 .= ",";
}
$bindsKeys[] = $key;
$binds1 .= $key;
$binds2 .= ":" . $key;
}
}
if ($binds1 != "") {
$sql .= " RETURNING " . $binds1 . " INTO " . $binds2;
}
}
if ($trackerQuery != null) {
$trackerQuery->startQuery($sql, $arBinds);
}
$result = oci_parse($this->resource, $sql);
if (!$result) {
if ($trackerQuery != null) {
$trackerQuery->finishQuery();
}
throw new SqlQueryException("", $this->getErrorMessage(), $sql);
}
$executionMode = $this->transaction;
$clob = array();
if (!empty($arBinds)) {
$executionMode = OCI_DEFAULT;
foreach ($bindsKeys as $key) {
$clob[$key] = oci_new_descriptor($this->resource, OCI_D_LOB);
oci_bind_by_name($result, ":" . $key, $clob[$key], -1, OCI_B_CLOB);
}
}
if (!oci_execute($result, $executionMode)) {
if ($trackerQuery != null) {
$trackerQuery->finishQuery();
}
throw new SqlQueryException("", $this->getErrorMessage(), $sql);
}
if (!empty($arBinds)) {
if (oci_num_rows($result) > 0) {
foreach ($bindsKeys as $key) {
$clob[$key]->save($arBinds[$key]);
}
}
if ($this->transaction == OCI_COMMIT_ON_SUCCESS) {
oci_commit($this->resource);
}
foreach ($bindsKeys as $key) {
$clob[$key]->free();
}
}
if ($trackerQuery != null) {
$trackerQuery->finishQuery();
}
$this->lastQueryResult = $result;
return $result;
}
protected function _handleBindedLobs($sql)
{
if (!$this->lobs) {
return $sql;
}
$holder_to_field_map = array();
foreach ($this->lobs as $name => $lob) {
if (array_key_exists($name, $this->parameters)) {
unset($this->parameters[$name]);
}
if (strpos($sql, ":p_{$name}") !== false) {
$holder_to_field_map[$name] = $this->_mapHolderToField($name, $sql);
}
}
$sql .= " RETURNING ";
foreach ($this->lobs as $name => $lob) {
$sql = str_replace(":p_{$name}", $lob->getEmptyExpression(), $sql);
$this->lobDescriptors[$name] = oci_new_descriptor($this->connection->getConnectionId(), $lob->getDescriptorType());
$sql .= "{$holder_to_field_map[$name]},";
}
$sql = rtrim($sql, ',');
$sql .= " INTO ";
foreach (array_keys($this->lobs) as $name) {
$sql .= ":p_{$name},";
}
return rtrim($sql, ',');
}
/**
* Saves changes to module's audit table
*
* @param object $bean Sugarbean instance
* @param array $changes changes
* @see DBHelper::getDataChanges()
*/
public function save_audit_records(SugarBean $bean, $changes)
{
global $current_user;
$sql = "INSERT INTO " . $bean->get_audit_table_name();
//get field defs for the audit table.
require 'metadata/audit_templateMetaData.php';
$fieldDefs = $dictionary['audit']['fields'];
$values = array();
$values['id'] = $this->massageValue(create_guid(), $fieldDefs['id']);
$values['parent_id'] = $bean->dbManager->getHelper()->massageValue($bean->id, $fieldDefs['parent_id']);
$values['field_name'] = $bean->dbManager->getHelper()->massageValue($changes['field_name'], $fieldDefs['field_name']);
$values['data_type'] = $bean->dbManager->getHelper()->massageValue($changes['data_type'], $fieldDefs['data_type']);
if ($changes['data_type'] == 'text') {
$bean->fetched_row[$changes['field_name']] = $changes['after'];
$values['before_value_text'] = $bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_text']);
$values['after_value_text'] = $bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_text']);
} else {
$bean->fetched_row[$changes['field_name']] = $changes['after'];
$values['before_value_string'] = $bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_string']);
$values['after_value_string'] = $bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_string']);
}
$values['date_created'] = $bean->dbManager->getHelper()->massageValue(TimeDate::getInstance()->nowDb(), $fieldDefs['date_created']);
$values['created_by'] = $bean->dbManager->getHelper()->massageValue($current_user->id, $fieldDefs['created_by']);
$sql .= "(" . implode(",", array_keys($values)) . ") ";
$sql .= "VALUES(" . implode(",", $values) . ")";
if ($this->db->dbType == 'oci8' && $changes['data_type'] == 'text') {
$sql .= " RETURNING before_value_text, after_value_text INTO :before_value_text, :after_value_text";
$stmt = oci_parse($this->db->getDatabase(), $sql);
$err = oci_error($this->db->getDatabase());
if ($err != false) {
$GLOBALS['log']->fatal($sql . ">>" . $err['code'] . ":" . $err['message']);
return false;
}
$before_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
oci_bind_by_name($stmt, ":before_value_text", $before_value_text_LOB, -1, OCI_B_CLOB);
$after_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
oci_bind_by_name($stmt, ":after_value_text", $after_value_text_LOB, -1, OCI_B_CLOB);
oci_execute($stmt, OCI_DEFAULT);
$err = oci_error($this->db->getDatabase());
if ($err != false) {
$GLOBALS['log']->fatal($sql . ">>" . $err['code'] . ":" . $err['message']);
return false;
}
$before_value_text_LOB->save($changes['before']);
$after_value_text_LOB->save($changes['after']);
oci_commit($this->db->getDatabase());
$before_value_text_LOB->free();
$after_value_text_LOB->free();
oci_free_statement($stmt);
} else {
$bean->db->query($sql);
}
}
protected function bind_params($stmt, array $params = null, $tablename = null)
{
$descriptors = array();
if ($params) {
$columns = array();
if ($tablename) {
$columns = $this->get_columns($tablename);
}
foreach ($params as $key => $value) {
// Decouple column name and param name as far as sometimes they aren't the same
if ($key == 'o_newfieldtoset') {
// found case where column and key diverge, handle that
$columnname = key($value);
// columnname is the key of the array
$params[$key] = $value[$columnname];
// set the proper value in the $params array and
$value = $value[$columnname];
// set the proper value in the $value variable
} else {
$columnname = preg_replace('/^o_/', '', $key);
// Default columnname (for DB introspecting is key), but...
}
// Continue processing
// Now, handle already detected LOBs
if (is_array($value)) {
// Let's go to bind special cases (lob descriptors)
if (isset($value['clob'])) {
$lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB);
oci_bind_by_name($stmt, $key, $lob, -1, SQLT_CLOB);
$lob->writeTemporary($this->oracle_dirty_hack($tablename, $columnname, $params[$key]['clob']), OCI_TEMP_CLOB);
$descriptors[] = $lob;
continue;
// Column binding finished, go to next one
} else {
if (isset($value['blob'])) {
$lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB);
oci_bind_by_name($stmt, $key, $lob, -1, SQLT_BLOB);
$lob->writeTemporary($params[$key]['blob'], OCI_TEMP_BLOB);
$descriptors[] = $lob;
continue;
// Column binding finished, go to next one
}
}
}
// TODO: Put proper types and length is possible (enormous speedup)
// Arrived here, continue with standard processing, using metadata if possible
if (isset($columns[$columnname])) {
$type = $columns[$columnname]->meta_type;
$maxlength = $columns[$columnname]->max_length;
} else {
$type = '?';
$maxlength = -1;
}
switch ($type) {
case 'I':
case 'R':
// TODO: Optimise
oci_bind_by_name($stmt, $key, $params[$key]);
break;
case 'N':
case 'F':
// TODO: Optimise
oci_bind_by_name($stmt, $key, $params[$key]);
break;
case 'B':
// TODO: Only arrive here if BLOB is null: Bind if so, else exception!
// don't break here
// TODO: Only arrive here if BLOB is null: Bind if so, else exception!
// don't break here
case 'X':
// TODO: Only arrive here if CLOB is null or <= 4000 cc, else exception
// don't break here
// TODO: Only arrive here if CLOB is null or <= 4000 cc, else exception
// don't break here
default:
// Bind as CHAR (applying dirty hack)
// TODO: Optimise
oci_bind_by_name($stmt, $key, $this->oracle_dirty_hack($tablename, $columnname, $params[$key]));
}
}
}
return $descriptors;
}
