public function readCursor($storedProcedure, $binds) { // // This function needs two parameters: // // $storedProcedure - the name of the stored procedure to call a chamar. Ex: // my_schema.my_package.my_proc(:param) // // $binds - receives an array of associative arrays with: parameter names, // values and sizes // // WARNING: The first parameter must be consistent with the second one $conn = oci_connect('SECMAN', 'SECMAN', '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.10.24)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = cisqa)))'); if ($conn) { // Create the statement and bind the variables (parameter, value, size) $stid = oci_parse($conn, 'begin :cursor := ' . $storedProcedure . '; end;'); foreach ($binds as $variable) { oci_bind_by_name($stid, $variable["parameter"], $variable["value"], $variable["size"]); } // Create the cursor and bind it $p_cursor = oci_new_cursor($conn); oci_bind_by_name($stid, ':cursor', $p_cursor, -1, OCI_B_CURSOR); // Execute the Statement and fetch the data oci_execute($stid); oci_execute($p_cursor, OCI_DEFAULT); oci_fetch_all($p_cursor, $data, null, null, OCI_FETCHSTATEMENT_BY_ROW); // Return the data return $data; } }
public function getDetalle() { $sql = "SELECT trigger_name, trigger_type, triggering_event, table_name, status, description, trigger_body FROM user_triggers WHERE trigger_name = UPPER(:v_trigger_name)"; $stmt = oci_parse($this->getConnection(), $sql); oci_bind_by_name($stmt, ":v_trigger_name", $this->objectName); if (!@oci_execute($stmt)) { $e = oci_error($stmt); $this->setMensaje("Error al obtener los datos del trigger '{$this->objectName}' de la tabla user_triggers - {$e['message']}"); return false; } $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS); if (empty($row)) { $this->setMensaje("No se pudo encontrar el trigger especificado en la tabla user_triggers"); return false; } $this->triggerType = $row['TRIGGER_TYPE']; $this->triggeringEvent = $row['TRIGGERING_EVENT']; $this->affectedTable = $row['TABLE_NAME']; $this->triggerStatus = $row['STATUS']; $this->description = $row['DESCRIPTION']; $this->triggerSql = $row['TRIGGER_BODY']; if ($this->triggerStatus != 'ENABLED') { $this->setMensaje("El trigger se encuentra inhabilitado"); return false; } return true; }
/** * Executes a prepared statement. * * @param array $args an array of values with as many elements as there are bound parameters in the statement * @return boolean returns true on success or false on failure */ public function execute($args = array()) { foreach ($args as $key => $value) { oci_bind_by_name($this->statement, ":" . $key, $args[$key]); } return oci_execute($this->statement, OCI_DEFAULT); }
/** * Obtiene el SQL de la funcion especificada * @return String or false */ protected function getObjectSql() { if ($this->remote) { $sql = "select line, text from all_source where name = UPPER(:v_function_name) and type = :v_object_type order by name, type, line"; } else { $sql = "select line, text from user_source where name = UPPER(:v_function_name) and type = :v_object_type order by name, type, line"; } $stmt = oci_parse($this->getConnection(), $sql); oci_bind_by_name($stmt, ":v_function_name", $this->objectName); oci_bind_by_name($stmt, ":v_object_type", $this->objectType); if (!@oci_execute($stmt)) { $e = oci_error($stmt); $this->setMensaje("Error al obtener el SQL del objeto {$this->objectType} '{$this->objectName}' - {$e['message']}"); return false; } $sqlResult = ''; while ($row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS)) { $sqlResult .= $row['TEXT']; } $this->sourceSql = $sqlResult; if (empty($sqlResult)) { $this->setMensaje("No se pudo obtener el SQL del objeto {$this->objectType} '{$this->objectName}'"); return false; } return $this->sourceSql; }
function get_filteredGames($data) { // The connection string is loooooooong. It's easiest to copy/paste this line. Remember to replace 'username' and 'password'! $conn = oci_connect('malz', '1Qaz2wsx', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=db1.chpc.ndsu.nodak.edu)(Port=1521)))(CONNECT_DATA=(SID=cs)))'); if ($data === 'all') { $results = array(); $query = 'select * from Game'; $stid = oci_parse($conn, $query); oci_bind_by_name($stid, ':data', $data); oci_execute($stid); //iterate through each row while ($row = oci_fetch_array($stid, OCI_ASSOC)) { $results[] = $row; } echo json_encode($results); oci_free_statement($stid); oci_close($conn); } else { $results = array(); $data = $data . '%'; $query = 'select * from Game where gameName like :data'; $stid = oci_parse($conn, $query); oci_bind_by_name($stid, ':data', $data); oci_execute($stid); //iterate through each row while ($row = oci_fetch_array($stid, OCI_ASSOC)) { $results[] = $row; } echo json_encode($results); oci_free_statement($stid); oci_close($conn); } }
protected function getDetalle() { $sql = "SELECT synonym_name, table_owner, table_name, db_link FROM user_synonyms WHERE synonym_name = UPPER(:v_synonym_name)"; $stmt = oci_parse($this->getConnection(), $sql); oci_bind_by_name($stmt, ":v_synonym_name", $this->objectName); if (!@oci_execute($stmt)) { $e = oci_error($stmt); $this->setMensaje("Error al obtener los datos del sinónimo '{$this->objectName}' de la tabla user_synonyms - {$e['message']}"); $this->setEstado(false); return false; } $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS); if (empty($row)) { $sqlPublic = "SELECT * FROM all_synonyms WHERE synonym_name = UPPER(:v_synonym_name) AND owner = 'PUBLIC'"; $stmt2 = oci_parse($this->getConnection(), $sqlPublic); oci_bind_by_name($stmt2, ":v_synonym_name", $this->objectName); if (!@oci_execute($stmt2)) { $e = oci_error($stmt2); $this->setMensaje("Error al obtener los datos del sinónimo '{$this->objectName}' de la tabla all_synonyms - {$e['message']}"); $this->setEstado(false); return false; } $row = oci_fetch_array($stmt2, OCI_ASSOC | OCI_RETURN_NULLS); if (empty($row)) { $this->setMensaje("No se encontró el sinónimo '{$this->objectName}' en la tabla user_synonyms"); $this->setEstado(false); return false; } } $this->tableOwner = $row['TABLE_OWNER']; $this->tableName = $row['TABLE_NAME']; $this->dbLinkName = $row['DB_LINK']; $this->setEstado(true); return true; }
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(); }
function dispCart() { $cartLen = count($_SESSION['cart']); if ($cartLen < 1) { echo 'You have no items in your cart.<br><a href="main.php">Keep shopping</a><br>'; die; } //for loop to iterate through cart items for ($i = 0; $i < $cartLen; $i++) { if ($cartLen > 0) { $newconn = conndb(); //sql $s = oci_parse($newconn, "select * from PRODUCT where PRODUCTID=:pid_prefix"); $plook = $_SESSION['cart'][$i]['productid']; oci_bind_by_name($s, ':pid_prefix', $plook); oci_execute($s); //fetch a single row depending on product id $res = oci_fetch_assoc($s); echo "Product name: ", $res['PRODUCTNAME'], " Price: ", $res['PRODUCTPRICE']; echo '<a href="cart.php?del=' . $i . '"> Remove item</a><br>'; } } if ($cartLen > 0) { echo '<a href="checkout.php">Proceed to checkout</a><br>'; } }
public static function bindValues($stmt, $binds, $types) { foreach ($binds as $key => $field) { switch ($types[$key]) { case 'numeric': case 'string': case 'date': case 'time': case 'datetime': oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1); break; case 'int': oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1, SQLT_INT); break; case 'boolean': oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1); break; case 'blob': oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1, SQLT_BLOB); break; case 'custom': oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1); break; } } return true; }
public function buscarPorProcedimiento() { $sql = "SELECT job, to_char(last_date, 'DD/MM/YYYY') last_date, last_sec, to_char(next_date, 'DD/MM/YYYY') next_date, next_sec, interval, failures, what from user_jobs WHERE UPPER(what) LIKE UPPER('%' || :v_procedure_name || '%')"; $stmt = oci_parse($this->getConnection(), $sql); oci_bind_by_name($stmt, ":v_procedure_name", $this->objectName); if (!@oci_execute($stmt)) { $e = oci_error($stmt); $this->setMensaje("Error al obtener los datos del job que ejecuta el proceso '{$this->objectName}' de la tabla user_jobs - {$e['message']}"); $this->setEstado(false); } $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS); if (empty($row)) { $this->setEstado(false); } $this->jobId = $row['JOB']; $this->lastDate = $row['LAST_DATE']; $this->lastSec = $row['LAST_SEC']; $this->nextDate = $row['NEXT_DATE']; $this->nextSec = $row['NEXT_SEC']; $this->interval = $row['INTERVAL']; $this->failures = $row['FAILURES']; $this->jobSql = $row['WHAT']; $this->setEstado(true); return $this->getEstado(); }
function actualizarPassword($newpassword, $token) { $conex = DataBase::getInstance(); $stid = oci_parse($conex, "UPDATE FISC_USERS SET \n\t\t\t\t\t\tpassword=:newpassword\n\t\t\t\t WHERE token=:token"); if (!$stid) { oci_free_statement($stid); oci_close($conex); return false; } // Realizar la lógica de la consulta oci_bind_by_name($stid, ':token', $token); oci_bind_by_name($stid, ':newpassword', $newpassword); $r = oci_execute($stid, OCI_NO_AUTO_COMMIT); if (!$r) { oci_free_statement($stid); oci_close($conex); return false; } $r = oci_commit($conex); if (!$r) { oci_free_statement($stid); oci_close($conex); return false; } oci_free_statement($stid); // Cierra la conexión Oracle oci_close($conex); return true; }
public function bindByName($bvName, &$variable, $maxLength = -1, $type = SQLT_CHR) { set_error_handler(static::getErrorHandler()); $isSuccess = oci_bind_by_name($this->resource, $bvName, $variable, $maxLength, $type); restore_error_handler(); return $isSuccess; }
/** * Excecutes a statement * * @return boolean */ public function query($sql, array $params = array()) { $this->numRows = 0; $this->numFields = 0; $this->rowsAffected = 0; $this->arrayResult = null; $this->result = $stid = oci_parse($this->dbconn, $sql); # Bound variables if (count($params)) { foreach ($params as $var => $value) { oci_bind_by_name($stid, $var, $value); } } $r = $this->transac_mode ? @oci_execute($stid, OCI_NO_AUTO_COMMIT) : @oci_execute($stid, OCI_COMMIT_ON_SUCCESS); if (!$r) { $error = oci_error($this->result); $this->error($error["code"], $error["message"]); if (count($this->errors)) { throw new Exception($error["message"], $error["code"]); } else { throw new Exception("Unknown error!"); } } # This should be before of getArrayResult() because oci_fetch() is incremental. $this->rowsAffected = oci_num_rows($stid); $rows = $this->getArrayResult(); $this->numRows = count($rows); $this->numFields = oci_num_fields($stid); if ($this->transac_mode) { $this->transac_result = is_null($this->transac_result) ? $this->result : $this->transac_result && $this->result; } return $this->result; }
public function getById($id) { $this->conex = DataBase::getInstance(); $stid = oci_parse($this->conex, "SELECT *\n\t\t\tFROM FISC_CIUDADANO WHERE ID_CIUDADANO=:id"); if (!$stid) { $e = oci_error($this->conex); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } // Realizar la lógica de la consulta oci_bind_by_name($stid, ':id', $id); $r = oci_execute($stid); if (!$r) { $e = oci_error($stid); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } // Obtener los resultados de la consulta $alm = new FiscCiudadano(); while ($fila = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) { $it = new ArrayIterator($fila); while ($it->valid()) { $alm->__SET(strtolower($it->key()), $it->current()); $it->next(); } } //Libera los recursos oci_free_statement($stid); // Cierra la conexión Oracle oci_close($this->conex); //retorna el resultado de la consulta return $alm; }
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { /* init */ $this->hasAutoincrement = false; /* parent prep */ $result = parent::prepareStatement($adapter, $statementContainer); /* oci8 with autoincrement */ if ($statementContainer instanceof \Zend\Db\Adapter\Driver\Oci8\Statement && $this->autoincrement !== null) { /* get sequence */ if ($this->sequence === null) { $this->sequence = 'SEQ_' . $this->table; } /* replace ai field with sequence & move ai field binding to the end with returning */ $count = 0; $sql = preg_replace('/:' . $this->autoincrement . '\\s*/', $this->sequence . '.NEXTVAL', $statementContainer->getSql(), 1, $count) . ' RETURNING "' . $this->autoincrement . '" INTO :' . $this->autoincrement; /* anything replaced? */ if ($count > 0) { /* prep statement to prep resource */ $statementContainer->setSql($sql); $statementContainer->prepare(); /* unset ai field */ $statementContainer->getParameterContainer()->offsetUnset($this->autoincrement); /* get ai field position on values */ $position = array_search($this->autoincrement, $this->columns); $this->values[$position] = 0; $this->hasAutoincrement = true; oci_bind_by_name($statementContainer->getResource(), $this->autoincrement, $this->values[$position], -1, SQLT_INT); } } //oci8 AI return $result; }
public function getUserObjectData() { $sql = "SELECT object_name, object_type, TO_CHAR(created, 'DD/MM/YYYY') AS created, TO_CHAR(last_ddl_time, 'DD/MM/YYYY') AS last_ddl_time, status FROM user_objects WHERE object_name = UPPER(:v_obj_name) AND object_type = UPPER(:v_obj_type)"; $stmt = oci_parse($this->getConnection(), $sql); oci_bind_by_name($stmt, ":v_obj_name", $this->objectName); oci_bind_by_name($stmt, ":v_obj_type", $this->objectType); if (!@oci_execute($stmt)) { $e = oci_error($stmt); $this->setMensaje("Error al obtener los datos del objeto '{$this->objectName}' de la tabla user_objects - {$e['message']}"); return false; } $row = @oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS); if (empty($row)) { $this->setMensaje("No se pudo encontrar el objeto '{$this->objectName}' en la tabla user_objects"); return false; } $this->fechaCreacion = $row['CREATED']; $this->fechaModificacion = $row['LAST_DDL_TIME']; $this->oracleStatus = $row['STATUS']; if ($this->oracleStatus != 'VALID') { $this->setMensaje("El objeto '{$this->objectName}' tiene el estado '{$this->oracleStatus}' en la tabla user_objects"); return false; } return true; }
/** * 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 get_list($data) { $results = array(); $games = array(); $conn = oci_connect('malz', '1Qaz2wsx', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=db1.chpc.ndsu.nodak.edu)(Port=1521)))(CONNECT_DATA=(SID=cs)))'); //Select customer with last name from field $userQuery = 'select userName from Account where listId = :data'; $listQuery = 'select * from ListGame, Game where listId = :data and ListGame.gameId = Game.gameId'; $stid = oci_parse($conn, $userQuery); $stid2 = oci_parse($conn, $listQuery); oci_bind_by_name($stid, ':data', $data); oci_bind_by_name($stid2, ':data', $data); oci_execute($stid, OCI_DEFAULT); //iterate through each row while ($row = oci_fetch_array($stid, OCI_ASSOC)) { $results[] = $row; } oci_execute($stid2, OCI_DEFAULT); while ($row = oci_fetch_array($stid2, OCI_ASSOC)) { $games[] = $row; } $results[] = $games; echo json_encode($results); oci_free_statement($stid); oci_free_statement($stid2); oci_close($conn); }
function query($query) { if (!$this->link) { $this->connect(); } $matches = array(); preg_match_all('/\' ?<<::(.*?)::>> *\'/', $query, $matches, PREG_SET_ORDER); $replacements = array(); foreach ($matches as $idx => $match) { $query = str_replace($match[0], ':' . $idx, $query); $replacements[':' . $idx] = str_replace(array('<|<::', '::>|>'), array('<<::', '::>>'), $match[1]); } $result = oci_parse($this->link, $query); foreach ($replacements as $key => $replacement) { oci_bind_by_name($result, $key, $replacements[$key]); } if (!$result) { return false; } $success = @oci_execute($result); if (!$success) { return false; } return $result; }
function loginChk($conn) { isset($_POST["email"]); isset($_POST["pass1"]); global $username, $password, $password_enc; /* *** A1 - Injection attacks, converted all SQL statments to include binding/placeholders to prevent injection attacks. * */ //check password in database $s = oci_parse($conn, "SELECT username FROM tblusers WHERE username=:username_prefix AND password=:pw"); oci_bind_by_name($s, ':username_prefix', $username); oci_bind_by_name($s, ':pw', $password_enc); oci_execute($s); //evaluate based on db information $res = oci_fetch_row($s); if ($res) { oci_free_statement($s); oci_close($conn); return true; } else { oci_free_statement($s); oci_close($conn); echo "Username or password were incorrect.</br> Please try to login again, <a href='login.html'>click to return to login page</a>."; return false; } }
function actualizarQuejas($quejas) { //$ids = transformarArray($denuncias,","); $conex = DataBase::getInstance(); foreach ($quejas as $key => $value) { $conex = DataBase::getInstance(); $stid = oci_parse($conex, "UPDATE FISC_DENUNCIAS_JURIDICAS SET \n\t\t\t\tASIGNADA=1 , ASIGNADOPOR=:sesion_cod\n\t\t\t\tWHERE ID_DENUNCIA=:ID"); if (!$stid) { oci_free_statement($stid); oci_close($conex); return false; } // Realizar la lógica de la consulta oci_bind_by_name($stid, ':ID', $value); oci_bind_by_name($stid, ':sesion_cod', $_SESSION['USUARIO']['codigo_usuario']); $r = oci_execute($stid, OCI_NO_AUTO_COMMIT); if (!$r) { oci_free_statement($stid); oci_close($conex); return false; } $r = oci_commit($conex); if (!$r) { oci_free_statement($stid); oci_close($conex); return false; } oci_free_statement($stid); // Cierra la conexión Oracle oci_close($conex); } return true; }
/** Does the oracle-dependent work to bind the parameters to the statement. @param $aParameters The parameters to bind. */ protected function doBind($aParameters) { foreach ($aParameters as $sName => $mValue) { if (isset($this->aParameters[$sName])) { // Don't use $mValue here because oci_bind_by_name binds by reference. oci_bind_by_name($this->rStatement, ':' . $sName, $aParameters[$sName]) or burn('DatabaseException', sprintf(_WT("Failed to bind the parameter \"%s\" with the following error:\n%s"), $sName, array_value(oci_error($this->rStatement), 'message'))); } } }
function getUltimaRevisioVehicle($codi_vehicle) { $oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']); $req = "SELECT \r\n\t\t\t\t\t\tdata_l, \r\n\t\t\t\t\t\tkms \r\n\t\t\t\t\tFROM \r\n\t\t\t\t\t\trevisio \r\n\t\t\t\t\tWHERE codi IN ( \r\n\t\t\t\t\t\tselect max(rev.codi) \r\n\t\t\t\t\t\tfrom revisio rev \r\n\t\t\t\t\t\twhere rev.codi_vehicle = :codi_vehicle \r\n\t\t\t\t\t\tgroup by rev.codi_vehicle \r\n\t\t\t\t\t)"; $stid = oci_parse($oci->getConnection(), $req); oci_bind_by_name($stid, ":codi_vehicle", $codi_vehicle); oci_execute($stid); $ultima = oci_fetch_object($stid); return $ultima; }
function HakAksesUser($username, $nm_field, $conn) { $sql = "SELECT {$nm_field} FROM WELTES_SEC_ADMIN.WELTES_AUTHENTICATION AUT LEFT OUTER JOIN WELTES_SEC_ADMIN.WELTES_AUTH_LEVEL LEV ON LEV.APP_USR_CODE=AUT.APP_USR_CODE WHERE APP_USERNAME = :UN_BV "; $sqlSqry = oci_parse($conn, $sql); oci_bind_by_name($sqlSqry, ":UN_BV", $username); oci_execute($sqlSqry); $rowSqry = oci_fetch_array($sqlSqry); // echo $rowSqry[0]; return $rowSqry[0]; }
function show_foto($id) { $conn = oci_connect('mmAdmin', 'mmAdmin', '//localhost/MATCHMEDB'); $stid = oci_parse($conn, "begin :result := get_foto(person_id => :codigo); end;"); oci_bind_by_name($stid, ":result", $resul, 200); oci_bind_by_name($stid, ":codigo", $id); oci_execute($stid); oci_close($conn); return $resul; }
/** * Run a SQL or PL/SQL statement * * Call like: * Db::execute("insert into mytab values (:c1, :c2)", * [[":c1", $c1, -1], * [":c2", $c2, -1]]) * * For returned bind values: * Db::execute("begin :r := myfunc(:p); end", * [[":r", &$r, 20], * [":p", $p, -1]]) * * Note: this performs a commit. * * @param string $sql The statement to run * @param array $bindvars Binds. An array of (bv_name, php_variable, length) */ public function execute($sql, $bindvars = []) { $this->stid = oci_parse($this->_connection, $sql); foreach ($bindvars as $bv) { // oci_bind_by_name(resource, bv_name, php_variable, length) oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]); } //oci_set_action($this->_connection, $action); oci_execute($this->stid); // will auto commit }
private function buscarMesJove() { $codi_delegacio = $this->_infos->CODI_DELEGACIO; $oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']); $req = "SELECT codi_delegacio, min(data_alta), max(codi) as CODI FROM venedor WHERE codi_delegacio = :codi_delegacio group by codi_delegacio"; $stid = oci_parse($oci->getConnection(), $req); oci_bind_by_name($stid, ":codi_delegacio", $codi_delegacio); oci_execute($stid); $jove = oci_fetch_object($stid); return $jove->CODI; }
/** * @param $query * @param $params * @param $action */ public function execute($query, $action, $params = array()) { $this->stid = oci_parse($this->conn, $query); if ($this->prefetch >= 0) { oci_set_prefetch($this->stid, $this->prefetch); } foreach ($params as $bv) { oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]); } oci_set_action($this->conn, $action); oci_execute($this->stid); }
function db_execute($sql, $bind = null) { $c = db_connect(); $res = array(); $s = oci_parse($c, $sql); if ($bind != null) { foreach ($bind as $key => $value) { oci_bind_by_name($s, ":".$key, htmlentities($value,ENT_QUOTES)); } } $res = oci_execute($s); return $res; }
public function exec_proc($proc, $param, $exportReq) { $cursor = oci_new_cursor($this->conn); for ($i = 0; $i < count($param); $i++) { $this->proc_param_list .= "p{$i}=>:param{$i}, "; } $sql = "begin\n\t\t\t\t{$proc} ({$this->proc_param_list} ref_cur_out=>:result); \n\t\t\t\tend;"; $parse = oci_parse($this->conn, $sql); oci_bind_by_name($parse, ':result', $cursor, -1, OCI_B_CURSOR); # Bind input parameters to stored procedure call ($param) for ($i = 0; $i < count($param); $i++) { oci_bind_by_name($parse, ':param' . $i, $param[$i]); } oci_execute($parse); oci_execute($cursor); if ($exportReq) { // CSV export oci_fetch_all($cursor, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM); foreach ($res as $key => $value) { // Remove 'ORDERING' from resultset array_pop($res[$key]); } return $res; } else { oci_fetch_all($cursor, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC); array_shift($res); // Remove 'header' row foreach entry on json req foreach ($res as $key => $value) { // Remove 'ORDERING' from resultset unset($res[$key]['ORDERING']); } return json_encode($res, JSON_PRETTY_PRINT); } }