function sql_query($sqltype, $query, $con)
{
if ($sqltype == 'mysql') {
if (class_exists('mysqli')) {
return $con->query($query);
} elseif (function_exists('mysql_query')) {
return mysql_query($query);
}
} elseif ($sqltype == 'mssql') {
if (function_exists('sqlsrv_query')) {
return sqlsrv_query($con, $query);
} elseif (function_exists('mssql_query')) {
return mssql_query($query);
}
} elseif ($sqltype == 'pgsql') {
return pg_query($query);
} elseif ($sqltype == 'oracle') {
return oci_execute(oci_parse($con, $query));
} elseif ($sqltype == 'sqlite3') {
return $con->query($query);
} elseif ($sqltype == 'sqlite') {
return sqlite_query($con, $query);
} elseif ($sqltype == 'odbc') {
return odbc_exec($con, $query);
} elseif ($sqltype == 'pdo') {
return $con->query($query);
}
}
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;
}
}
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();
}
protected function _execute($sql)
{
$this->stmt_id = FALSE;
$this->_set_stmt_id($sql);
oci_set_prefetch($this->stmt_id, 1000);
return @oci_execute($this->stmt_id, $this->_commit);
}
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);
}
* -- fetch sql result one by one.
* -- $sql_result sql result variable. in future you will use $sql_result[..] to load result.
* -- $type 0 - use ibase_fetch_row to fetch data.
* 1 - use ibase_fetch_array to fetch data.
*
* function goheader( &$sql_result, $type)
* -- move db result pointer into correct position. -- it will be called inside function fetchrow.
* -- $sql_result sql result variable. in future you will use $sql_result[..] to load result.
* -- $type 0 - use ibase_fetch_row to fetch data.
* 1 - use ibase_fetch_array to fetch data.
* Attention :
* 1. display_count(), display_links() can't be appeared inside "<form></form>" tags because there are "<form>" tags inside.
* 2. page number are specified on URL, e.g. http://www.yourcompany.com/display.php?page=10, page number = 10
* 3. At present it can't work very well when your sql statement includes keyword 'DISTINCT'.
*
* @version 0.1
* @author Kelvin Ma - Span International
* @email : kma@spanltd.com
*
* Some codes are from osCommerce, Open Source E-Commerce Solutions, http://www.oscommerce.com
*
* Released under the GNU General Public License
******************************************************************************/
/*
Here is an example to show how to use this class.
$sql = 'SELECT FID, FACTORYNAME FROM FACTORY';
$page_split = new SplitPageResults($sql);
echo "<table width=100% BORDER=0 CELLPADDING=0 CELLSPACING=0><tr><td align=left>".$page_split->display_count()."</td><td align=right>".$page_split->display_links()."</td></tr></table>";
echo "<TABLE BGCOLOR=2A66AA BORDER=0 CELLPADDING=0 CELLSPACING=0><TR><TD><TABLE BORDER=0 CELLPADDING=1 CELLSPACING=1><TR BGCOLOR=#3388EE><TD COLSPAN=5><FONT SIZE=2 COLOR=WHITE><CENTER><B>Factory list</B></CENTER></FONT></TD></TR><TR BGCOLOR=#6BAAEE><TD>FACTORY ID</TD><TD>FACTORY NAME</TD></TR>";
while ($page_split->fetchrow($ibrow, 0)) {
echo "<tr><td>".$ibrow[0]."</td><td>".$ibrow[1]."</td></tr>";
function obtain_roadnet_xml_all()
{
$sql = "select * from node_info";
$r = oci_parse($dbconn, $query);
oci_execute($r, OCI_DEFAULT);
return get_Roadnet_xml($r);
}
public function crearActividad($actividad)
{
session_start();
$sqltxt = "insert into s_actividad values(14,'" . $actividad->getNombre_actividad() . "','" . $actividad->getDescripcion() . "')";
$stid = oci_parse($_SESSION['sesion_logueado'], $sqltxt);
oci_execute($stid);
}
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);
}
}
function afficher_produit($is_fruit)
{
if ($is_fruit) {
$requete = "SELECT * FROM Produits WHERE `fruit`=1";
} else {
$requete = "SELECT * FROM Produits WHERE `fruit`=0";
}
$idcom = connex("Myparam");
$stmt = oci_parse($idcom, $requete);
$result = oci_execute($result, OCI_DEFAULT);
if (!$result) {
echo "Lecture impossible";
} else {
echo "<table cellspacing='0' cellpadding='5'>";
echo "<tr>";
echo "<th>Produit</th><th>Description</th><th>Stock</th><th>Prix</th><th>Quantité</th><th></th>";
echo "</tr>";
while ($ligne = oci_fetch_assoc($result)) {
echo "<tr id='" . $ligne[0] . "'>";
echo "<td>" . $ligne[1] . "</td>";
echo "<td>" . $ligne[2] . "</td>";
echo "<td>" . $ligne[3] . "</td>";
echo "<td>" . $ligne[4] . "</td>";
echo "<td><select name='qte_v[]'>";
for ($i = 1; $i <= $ligne[3]; $i++) {
echo "<option>" . $i . "</option>";
}
echo "</select></td>";
echo '<td><button onclick="addProduit($(this));" class=bouton_produit>Ajouter au panier</button></td>';
echo "</tr>";
}
echo "</table>";
}
}
function cargarArray($sentencia)
{
include dirname(__FILE__) . '/conectar_ORACLE.php';
$array = array();
$sentenciaExec = oci_parse($c, $sentencia);
oci_execute($sentenciaExec);
$error = 0;
$k = 0;
$ncols = oci_num_fields($sentenciaExec);
for ($i = 1; $i <= $ncols; ++$i) {
$colname = oci_field_name($sentenciaExec, $i);
$array[0][$k] = $colname;
$k++;
}
$cont = 0;
$j = 1;
$k = 0;
while ($row = oci_fetch_array($sentenciaExec, OCI_BOTH + OCI_RETURN_NULLS)) {
while ($cont < $ncols) {
$array[$j][$cont] = $row[$cont];
$cont++;
}
$cont = 0;
$k = 0;
$j++;
}
if (oci_num_rows($sentenciaExec) == 0) {
oci_free_statement($sentenciaExec);
return false;
} else {
oci_free_statement($sentenciaExec);
return $array;
}
}
public function asignarDiaSolicitud($iddia, $idsolicitud)
{
$dia = new DiaSolicitud();
$sqltxt = "insert into s_diasolicitud values(" . $idsolicitud . "," . $iddia . ")";
$stid = oci_parse($_SESSION['sesion_logueado'], $sqltxt);
oci_execute($stid);
}
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 function consultar()
{
//$prub = Conexion::getInstancia("system", "admin");
//$p=new Persona();
$persina = new PersonaDAO();
$aa = new ConexionDB("apoyo_alimentario", "apoyo_alimentario");
if ($aa->conectarDB()) {
$sesion = $aa->getConn();
$facultades = array();
$i = 0;
$sqltxt = "select * from facultad";
$stid = oci_parse($sesion, $sqltxt);
oci_execute($stid);
while (oci_fetch($stid)) {
$facultad = new Facultad();
$facultad->setIdfacultad(oci_result($stid, 'ID_FACULTAD'));
$facultad->setNombre_facultad(oci_result($stid, 'NOMBRE_FACULTAD'));
$facultades[$i] = $facultad;
$i += 1;
}
return $facultades;
//oci_parse($a, $sql_text);
//echo $a;
}
}
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;
}
public function execute($mode = OCI_COMMIT_ON_SUCCESS)
{
set_error_handler(static::getErrorHandler());
$isSuccess = oci_execute($this->resource, $mode);
restore_error_handler();
return $isSuccess;
}
function fetchAndUpdateFacultyInfo($query, $conn, $nextPage)
{
$stid = oci_parse($conn, $query);
var_dump($query);
oci_execute($stid);
echo "<form action=\"{$nextPage}?user=FACULTY\">";
echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
$i = 1;
foreach ($row as $item) {
echo "<tr>\n";
$column_name = oci_field_name($stid, $i);
echo "<td> {$column_name} </td>\n";
if ($column_name == "UnityId" || $column_name == "FacultyNo" || $column_name == "Balance" || $column_name == "Type" || $column_name == "Category" || $column_name == "isHeld" || $column_name == "Department") {
echo " <td>" . "<input type=\"text\" name=\"{$column_name}\" value=\"{$item}\" readonly>" . "</td>\n";
} else {
echo " <td>" . "<input type=\"text\" name=\"{$column_name}\" value=\"{$item}\" >" . "</td>\n";
}
$i++;
echo "</tr>\n";
}
}
echo "</table>\n";
echo "<input type=\"submit\" value=\"Update\">";
echo "</form>";
}
function cargarArray($FUPE_CD_PROMOTOR, $FUPE_FE_ESTADO_DESDE, $FUPE_FE_ESTADO_HASTA, $queries)
{
include dirname(__FILE__) . '/conectar_ORACLE.php';
//echo $queries.";<br /><br />";
$array = array();
$query = oci_parse($c, $queries);
oci_execute($query);
$error = 0;
$ncols = oci_num_fields($query);
$cont = 0;
$j = 0;
$k = 0;
while ($row = oci_fetch_array($query, OCI_BOTH + OCI_RETURN_NULLS)) {
while ($cont < $ncols) {
$array[$j][$cont] = $row[$cont];
$cont++;
}
$cont = 0;
$k = 0;
$j++;
}
if (oci_num_rows($query) == 0) {
return false;
} else {
return $array;
}
}
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 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;
}
}
public function _query($sql)
{
$this->stid = oci_parse($this->link, $sql);
if ($this->stid != false) {
// parsing empty query != false
if (oci_execute($this->stid)) {
return $this->stid;
// executing empty query != false
/*
if (oci_fetch_all($this->stid, $data, 0, -1, OCI_FETCHSTATEMENT_BY_ROW) == false) {
// but fetching executed empty query results in error (ORA-24338: statement handle not executed)
$e = oci_error($this->stid);
echo $e['message'];
} */
} else {
$e = oci_error($this->stid);
echo $e['message'];
return false;
}
} else {
$e = oci_error($this->link);
echo $e['message'];
return false;
}
//$stid = oci_parse();
//oci_execute($stid);
return $this->stid;
}
function createAndExecuteQuery($sqlQuery)
{
// echo "$sqlQuery \n";
$oracleSQLQuery = oci_parse($this->dbConnection, $sqlQuery);
oci_execute($oracleSQLQuery);
$this->queries[$sqlQuery] = $oracleSQLQuery;
}
protected function query($query, $returnresult = false)
{
if (!$this->isConnected()) {
$this->connect();
}
if ($this->isConnected()) {
$this->myLog->log(LOG_DEBUG, 'DB query is: ' . $query);
# OCI mode
$result = oci_parse($this->dbh, $query);
if (!oci_execute($result)) {
$this->myLog->log(LOG_INFO, 'Database query error: ' . preg_replace('/\\n/', ' ', print_r(oci_error($result), true)));
$this->dbh = Null;
return false;
}
$this->result = $result;
if ($returnresult) {
return $this->result;
} else {
return true;
}
} else {
$this->myLog->log(LOG_CRIT, 'No database connection');
return false;
}
}
public function Query($query_string, $params = null)
{
++self::$query_count;
$this->qid = $this->prepareSQL($query_string, $params);
$result = oci_execute($this->qid);
$this->row = 0;
$this->selected_rows = null;
$err = oci_error($this->qid);
$this->error = $err['message'];
$this->errno = $err['code'];
if (oci_statement_type($this->qid) == 'SELECT') {
$this->records = array();
$this->selected_rows = 0;
$this->current_row = 0;
while ($row = @oci_fetch_assoc($this->qid)) {
$this->records[] = $row;
++$this->selected_rows;
}
}
if (!$result) {
throw new Exception('Invalid query: ' . $query_string . ': ' . $this->error, $this->errno);
}
//if
return $result;
}
/**
* 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;
}
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 ejecutarListaBeneficiados($convocatoria)
{
$sqltxt = "BEGIN PK_PROCESOS.PR_LISTADO(" . $convocatoria . ");END;";
echo $sqltxt;
$stid = oci_parse($_SESSION['sesion_logueado'], $sqltxt);
oci_execute($stid);
}
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);
}
}
public function testRunOracle()
{
$writerData = $this->prepareConfig('oracle');
$testing = $this->container->getParameter('testing');
$this->configuration->updateTable($this->writerId, $testing['table']['id'], ['dbName' => 'keboola.dummy']);
$dbParams = $testing['oracle']['db'];
$dbString = '//' . $dbParams['host'] . ':' . $dbParams['port'] . '/' . $dbParams['database'];
$conn = oci_connect($dbParams['user'], $dbParams['password'], $dbString, 'AL32UTF8');
try {
oci_execute("DROP TABLE keboola.dummy");
} catch (\Exception $e) {
// table doesn't exist
}
$this->write($writerData['id']);
$stid = oci_parse($conn, "SELECT * FROM keboola.dummy");
oci_execute($stid);
$result = [];
while ($res = oci_fetch_assoc($stid)) {
$result[] = array_values($res);
}
$sapiData = $this->storageApi->exportTable($testing['table']['id']);
$sapiDataArr = Table::csvStringToArray($sapiData);
$sapiDataArrFinal = [];
unset($sapiDataArr[0]);
foreach ($sapiDataArr as $row) {
unset($row[3]);
$sapiDataArrFinal[] = array_values($row);
}
$this->assertEquals($sapiDataArrFinal, $result);
}