public function execQry()
{
$recordString = false;
switch ($this->connectorStructure['dbType']) {
case "ORA":
$connectorString = '(DESCRIPTION = (CONNECT_TIMEOUT=5) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =' . $this->connectorStructure['dbIP'] . ')(PORT = ' . $this->connectorStructure['dbPort'] . ')))(CONNECT_DATA=(SID= ' . $this->connectorStructure['dbName'] . ')))';
$dbConnector = oci_connect($this->connectorStructure['dbUser'], $this->connectorStructure['dbPassword'], $connectorString);
if (!$dbConnector) {
echo 'Failed connection.......';
} else {
$oraQuery = oci_parse($dbConnector, $this->queryStructure['dbQuery']);
}
oci_execute($oraQuery);
$rowsnum = oci_fetch_all($oraQuery, $recordString);
if ($rowsnum == 0) {
$recordString = array();
} else {
if (!(substr($this->queryStructure['dbQuery'], 1, 6) == 'select')) {
$oraQuery = oci_parse($dbConnector, 'commit');
oci_execute($oraQuery);
$rowsnum = oci_fetch_all($oraQuery, $recordString);
}
}
oci_close($dbConnector);
break;
case "PGS":
$connectorString = "host=" . $this->connectorStructure['dbIP'] . " port=" . $this->connectorStructure['dbPort'] . " dbname=" . $this->connectorStructure['dbName'] . " user="******" password=" . $this->connectorStructure['dbPassword'];
$dbConnector = pg_connect($connectorString);
if (!$dbConnector) {
echo 'Failed connection.......';
} else {
pg_prepare($dbConnector, $this->queryStructure['qryName'], $this->queryStructure['dbQuery']);
}
$queryResult = pg_execute($dbConnector, $this->queryStructure['qryName'], $this->queryStructure['qryParameters']);
if (!$queryResult) {
$recordString = array();
} else {
$recordString = pg_fetch_all($queryResult);
if (!(substr($this->queryStructure['dbQuery'], 1, 6) == 'select')) {
pg_prepare($dbConnector, 'commit', 'commit');
pg_exec($dbConnector, 'commit');
}
}
pg_close($dbConnector);
break;
default:
$recordString = false;
break;
}
return $recordString;
}
function getThreadContent($TID)
{
$dao = new DAO();
$q = "SELECT * FROM (SELECT ID FROM THREAD WHERE PARENTTHREADID = {$TID} order by id DESC) WHERE ROWNUM <=3 ";
$stid = oci_parse($dao->con, $q);
oci_execute($stid, OCI_DEFAULT);
oci_fetch_all($stid, $rs);
return $rs;
}
/**
Execute an SQL query.
@param $sQueryString The query string
@return weeDatabaseDummyResult Only with SELECT queries: an object for results handling
*/
protected function doQuery($sQueryString)
{
$rStatement = oci_parse($this->rLink, $sQueryString);
$rStatement !== false or burn('DatabaseException', sprintf(_WT("Failed to parse the query with the following error:\n%s"), array_value(oci_error($this->rLink), 'message')));
// oci_execute triggers a warning when the statement could not be executed.
@oci_execute($rStatement, OCI_DEFAULT) or burn('DatabaseException', sprintf(_WT("Failed to execute the query with the following error:\n%s"), array_value(oci_error($rStatement), 'message')));
$this->iNumAffectedRows = oci_num_rows($rStatement);
if (oci_num_fields($rStatement) > 0) {
// TODO: Check whether the silence operator is really required here.
@oci_fetch_all($rStatement, $aRows, 0, -1, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW);
return new weeDatabaseDummyResult($aRows);
}
}
/**
* Метод класса. Получть содержимое запроса количеством $limit строк
*
* @param string $query строка
* @param array|string $vars массив
* @param int|string $length число
* @param string $limit число
*
* @param int $keys
* @return array
* @throws \Exception
*/
public function ociExecute($query, $vars = array(), $length = -1, $limit = null, $keys = OCI_ASSOC)
{
$parse = oci_parse($this->getDbLink(), $query);
if (!empty($vars)) {
foreach ($vars as $key => $value) {
if (is_array($value)) {
oci_bind_by_name($parse, $key, $vars[$key]['value'], $value['length'], $value['type']);
} else {
oci_bind_by_name($parse, $key, $vars[$key], $length);
}
}
}
$execute = oci_execute($parse, OCI_DEFAULT);
if ($execute) {
$array = false;
if ($keys === OCI_ASSOC) {
$result = oci_fetch_all($parse, $array, null, $limit, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC + OCI_RETURN_LOBS + OCI_RETURN_NULLS);
} else {
$result = oci_fetch_all($parse, $array, null, $limit, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM + OCI_RETURN_LOBS + OCI_RETURN_NULLS);
}
oci_free_statement($parse);
return $array;
} else {
$error_info = oci_error($parse);
oci_rollback($this->getDbLink());
oci_free_statement($parse);
throw new \Exception($error_info['message'], $error_info['code']);
}
}
/**
* 按SQL语句获取记录结果，返回数组
*
* @param sql 执行的SQL语句
*/
public function getArray($sql)
{
$result = $this->exec($sql);
oci_fetch_all($result, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
oci_free_statement($result);
return $res;
}
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;
}
}
/**
* Run a query and return all rows.
*
* @param string $sql A query to run and return all rows
* @param array $bindvars Binds. An array of (bv_name, php_variable, length)
* @return array An array of rows
*/
public function execFetchAll($sql, $bindvars = [])
{
$this->execute($sql, $bindvars);
oci_fetch_all($this->stid, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
$this->stid = null;
// free the statement resource
return $res;
}
function retrieveHash($conn, $username)
{
$sql = 'SELECT U.PASSWORD FROM User_Tbl U WHERE Username = :v_user';
$stid = oci_parse($conn, $sql);
oci_bind_by_name($stid, ':v_user', $username);
oci_execute($stid);
oci_fetch_all($stid, $res);
return $res['PASSWORD'][0];
}
public function fetchAll(&$output, $skip = 0, $maxRows = -1, $flags = 0)
{
if (empty($flags)) {
$flags = OCI_FETCHSTATEMENT_BY_COLUMN | OCI_ASSOC;
}
set_error_handler(static::getErrorHandler());
$numRows = oci_fetch_all($this->resource, $output, $skip, $maxRows, $flags);
restore_error_handler();
return $numRows;
}
public function o_fetch_all($query, $action, $params = array(), $f_mode)
{
$result = array();
$this->execute($query, $action, $params);
$result["count"] = oci_num_fields($this->stid);
oci_fetch_all($this->stid, $res, 0, -1, $f_mode);
$result["data"] = $res;
$this->stid = null;
return $result;
}
function __construct(&$db, $stmt)
{
$this->db =& $db;
if (($this->nrows = oci_fetch_all($stmt, $this->rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW | OCI_NUM)) === false) {
$e = oci_error($stmt);
$db->reportQueryError($e['message'], $e['code'], '', __FUNCTION__);
return;
}
$this->cursor = 0;
$this->stmt = $stmt;
}
/**
* Modify permis's informations
*
* @param PK_LECON, permis's id
* @return 0 without errors, exception message any others cases
*/
public function has_marque($PK_LECON)
{
try {
$qry = oci_parse($this->db, 'SELECT AUTO.EXAMEN FROM EXAMEN WHERE EXAMEN.PK_EXAMEN =?');
$qry->bindValue(1, $PK_LECON, \PDO::PARAM_STR);
$nrows = oci_fetch_all($qry, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
oci_close($this->db);
return $res;
} catch (Exception $e) {
return $e->getMessage();
}
}
/**
* Modify permis's informations
*
* @param PK_PERMIS, permis's id
* @return 0 without errors, exception message any others cases
*/
public function has_Permis($PK_PERMIS)
{
try {
$qry = oci_parse($this->db, 'SELECT AUTO.PERMIS FROM PERMIS WHERE PERMIS.PK_PERMIS =?');
$qry->bindValue(1, $PK_PERMIS, \PDO::PARAM_STR);
$nrows = oci_fetch_all($qry, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
oci_close($this->db);
return $res;
} catch (Exception $e) {
return $e->getMessage();
}
}
/**
* Modify permis's informations
*
* @param PK_VEHICULE, permis's id
* @return 0 without errors, exception message any others cases
*/
public function has_vehicule($PK_VEHICULE)
{
try {
$qry = oci_parse($this->db, 'SELECT * FROM VEHICULE WHERE VEHICULE.PK_VEHICULE =?');
$qry->bindValue(1, $PK_VEHICULE, \PDO::PARAM_STR);
$nrows = oci_fetch_all($qry, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
oci_close($this->db);
return $res;
} catch (Exception $e) {
return $e->getMessage();
}
}
/**
* Modify permis's informations
*
* @param PK_FACTURATION, permis's id
* @return 0 without errors, exception message any others cases
*/
public function has_facturation($PK_FACTURATION)
{
try {
$qry = oci_parse($this->db, 'SELECT AUTO.FACTURATION FROM FACTURATION WHERE FACTURATION.PK_FACTURATION =?');
$qry->bindValue(1, $PK_FACTURATION, \PDO::PARAM_STR);
$nrows = oci_fetch_all($qry, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
oci_close($this->db);
return $res;
} catch (Exception $e) {
return $e->getMessage();
}
}
/**
* Modify all customer's informations from one customer
* @param TYPE_FACTURE , customer's lasttName
*/
public function add_marque($TYPE_FACTURE)
{
try {
$qry = oci_parse($this->db, 'INSERT INTO AUTO.MARQUE (NOM) VALUES (?)');
$qry->bindValue(1, $TYPE_FACTURE, \PDO::PARAM_STR);
$nrows = oci_fetch_all($qry, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
oci_close($this->db);
return $res;
} catch (Exception $e) {
return $e->getMessage();
}
}
function query($SQLCmd)
{
if (!$this->isConnect) {
return null;
}
$stmt = ociparse($this->connection, $SQLCmd);
ociexecute($stmt, OCI_DEFAULT);
$results = array();
oci_fetch_all($stmt, $results, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN);
$rs = new ResultSet();
$rs->setHolder($results);
oci_free_statement($stmt);
return $rs;
}
public function exec()
{
// IDEA:--------------------------------------------------
// Se me ocurre que esta parte de la aplicacion haga los
// binds antes de lacer la ejecucion, y que tome un array
// de binds de una variables del objeto.
// -------------------------------------------------------
if (oci_execute($this->statement)) {
$this->state = TRUE;
$this->rows = oci_fetch_all($this->statement, $this->results, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
} else {
$this->state = FALSE;
$this->oci_error_message = oci_error($this->statement);
}
}
/**
* Returns an array containing all of the result set rows.
*
* @param int $fetchStyle a pdo fetch syle constant
* @return array
*/
public function fetchAll($fetchStyle = PDO::FETCH_BOTH)
{
switch ($fetchStyle) {
case PDO::FETCH_BOTH:
$flags = OCI_FETCHSTATEMENT_BY_ROW + OCI_BOTH;
break;
case PDO::FETCH_ASSOC:
$flags = OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC;
default:
$flags = 0;
break;
}
oci_fetch_all($this->statement, $rows, null, null, $flags);
return $rows;
}
function run_sql($conn, $sql)
{
var_dump($sql);
$query = oci_parse($conn, $sql);
oci_execute($query);
$nrows = oci_fetch_all($query, $result, null, null, OCI_FETCHSTATEMENT_BY_ROW);
echo "{$nrows} rows fetched<br>\n";
var_dump($result);
if (!$result) {
echo oci_error();
return null;
} else {
return $result;
}
}
function getMoviesBasedOnZipOrTheatreName($searchText, $conn)
{
$inside = "false";
$searchQuery = "select * from theatres where upper(theatrename) like '%" . strtoupper($searchText) . "%'";
if (is_numeric($searchText)) {
$searchQuery = "select * from theatres where zip = " . $searchText;
}
$theatreids = oci_parse($conn, $searchQuery);
oci_execute($theatreids);
while (($row = oci_fetch_array($theatreids, OCI_BOTH)) != false) {
$movieshows = oci_parse($conn, "select showid,movieid,screenid,theatreid,to_char(starttime, 'yyyy-mm-dd hh24:mi:ss') as starttime, price from movieshow where starttime >=sysdate and theatreid = " . $row['THEATREID'] . "order by starttime");
$uniqueMovies = oci_parse($conn, "select movieid,moviename,rating from movies where movieid in (select unique movieid from movieshow where THEATREID =" . $row['THEATREID'] . ")");
oci_execute($uniqueMovies);
oci_fetch_all($uniqueMovies, $result, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
oci_execute($movieshows);
while (($movieshowRow = oci_fetch_array($movieshows, OCI_BOTH)) != false) {
$inside = "true";
$starttime = date_format(date_create($movieshowRow['STARTTIME']), 'Y-m-d H:i:s');
$movieid = searchForMovieId($movieshowRow['MOVIEID'], $result);
echo "<br>";
echo "<div class='movie'>";
echo "<form action='/ticket-booking/ticket-selection.php' method='post'>";
echo "<a class='review' href= '/ticket-booking/view-reviews.php?type=theatre&theatreid=" . $movieshowRow['THEATREID'] . "'>View Review</a>";
if (isset($_SESSION['username'])) {
echo " <a class='review' href='/ticket-booking/reviews.php?type=theatre&theatreid=" . $movieshowRow['THEATREID'] . "'>Write Review</a>";
}
echo "<br>";
echo "<span class='title'>Theatre: </span><span class='titleValue'>" . $row['THEATRENAME'] . "</span>";
echo "<br>";
echo "<span class='title'>Movie: </span><span class='titleValue'>" . $result[$movieid]['MOVIENAME'] . "</span><span class='rating'> <img src='includes/likes.png'/ title='Users Rating'>" . number_format($result[$movieid]['RATING'] / 10 * 100, 0) . "%</span>";
echo "<br>";
echo "<span class='title'>Show Start Time: </span><span class='titleValue'>" . $movieshowRow['STARTTIME'] . "</span>";
echo "<span class='movieOrder'><input type='submit' value='Select'></span>";
echo "<input type=hidden name='showid' value=\"" . $movieshowRow['SHOWID'] . "\">";
echo "<input type=hidden name='theatreid' value=\"" . $movieshowRow['THEATREID'] . "\">";
echo "<input type=hidden name='movieid' value=\"" . $movieshowRow['MOVIEID'] . "\">";
echo "<input type=hidden name='screenid' value=\"" . $movieshowRow['SCREENID'] . "\">";
echo "<input type=hidden name='theatrename' value=\"" . $row['THEATRENAME'] . "\">";
echo "<input type=hidden name='moviename' value=\"" . $result[$movieid]['MOVIENAME'] . "\">";
echo "<input type=hidden name='moviestarttime' value=\"" . $movieshowRow['STARTTIME'] . "\">";
echo "<input type=hidden name='ticketprice' value=\"" . $movieshowRow['PRICE'] . "\">";
echo "<input type=hidden name='movierating' value=\"" . number_format($result[$movieid]['RATING'] / 10 * 100, 0) . "\">";
echo "</form>";
echo "</div>";
}
}
return $inside;
}
/**
* Modify permis's informations
*
* @param PK_TYPE_FACTURE, permis's id
* @return 0 without errors, exception message any others cases
*/
public function has_Type_Facture($PK_TYPE_FACTURE)
{
try {
$qry = oci_parse($this->db, 'SELECT AUTO.TYPE_FACTURE FROM TYPE_FACTURE WHERE TYPE_FACTURE.PK_TYPE_FACTURE =?');
$qry->bindValue(1, $PK_TYPE_FACTURE, \PDO::PARAM_STR);
$nrows = oci_fetch_all($qry, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
oci_close($this->db);
return $res;
} catch (Exception $e) {
return $e->getMessage();
}
}
public function get_cursor_data($name = '')
{
$temp = array();
$temp[$name] = array();
oci_fetch_all($this->curs_id[$name], $temp[$name]);
oci_free_statement($this->curs_id[$name]);
$this->result_array[$name] = array();
/* FIX Para guardar el resultado como un array clásico */
foreach ($temp[$name] as $fieldName => $aField) {
//oracle return the values in caps lock, and we use lower case.
$fieldName = strtolower($fieldName);
foreach ($aField as $index => $aValue) {
$this->result_array[$name][$index]->{$fieldName} = $aValue;
}
}
return $this->result_array[$name];
}
/**
* Display all caravans's informations
*
* @return return_qry : result into an object, exception message any others cases
*/
public function display_caravans()
{
try {
$qry = oci_parse($this->db, 'SELECT * FROM MARQUE');
oci_execute($qry);
//$qry = $this->db->prepare('SELECT * FROM caravan ORDER BY caravan.car_id');
//
//$qry->execute();
//get customer's ID put the result into an object
//$return_qry = $qry->fetchAll();
$nrows = oci_fetch_all($qry, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
//var_dump($res);
oci_close($this->db);
return $res;
} catch (Exception $e) {
return $e->getMessage();
}
}
public function executeStatementAlt($sql_statement)
{
//Prepare sql using conn and returns the statement identifier
$stid = oci_parse($this->conn, $sql_statement);
//Execute a statement returned from oci_parse()
$res = oci_execute($stid);
//if error, retrieve the error using the oci_error() function & output an error message
if (!$res) {
$err = oci_error($stid);
echo htmlentities($err['message']);
} else {
// Fetch ALL results
$data = oci_fetch_all($stid, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM);
// Free all resources associated with the oracle statement/cursor
oci_free_statement($stid);
return $res;
}
}
public function get($query, $column_or_row = 'column')
{
global $conn;
if ($column_or_row == 'row') {
$fetchas = OCI_FETCHSTATEMENT_BY_ROW;
} else {
if ($column_or_row == 'column') {
$fetchas = OCI_FETCHSTATEMENT_BY_COLUMN;
} else {
$fetchas = OCI_ASSOC + OCI_RETURN_NULLS;
}
}
set_error_handler(function ($errno, $errstr, $errfile, $errline, array $errcontext) {
// error was suppressed with the @-operator
if (0 === error_reporting()) {
return false;
}
throw new ErrorException($errstr, 0, $errno, $errfile, $errline);
});
try {
$stid = oci_parse($conn, $query);
if ($stid) {
oci_execute($stid);
$nrows = oci_fetch_all($stid, $res, null, null, $fetchas);
return $res;
}
} catch (Exception $e) {
$this->errors[] = $e->getMessage();
}
restore_error_handler();
}
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);
}
}
function isle($sql)
{
$conn = oci_connect("yeni3", "yeni3", "//10.6.30.11/mykdb");
$s = oci_parse($conn, $sql);
$res = oci_execute($s);
oci_fetch_all($s, $res);
return $res;
oci_close($conn);
}
function fetchAllRowsNum($result)
{
$output = array();
$rows = oci_fetch_all($result, $output, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM);
if ($rows === false) {
return array();
}
return $output;
}
public function testInit()
{
$config['host'] = 'localhost';
$config['username'] = '******';
$config['password'] = '******';
$config['port'] = 1521;
$config['instance_name'] = 'xe';
$config['charset'] = 'UTF8';
// The user should log in
$db = new Oracle($config);
$this->assertTrue($db->enabled());
// simple query
$sql = "SELECT * FROM HR.JOBS WHERE JOB_ID = 'AD_PRES'";
$stid = $db->query($sql);
oci_fetch_all($stid, $res);
$this->assertNotEmpty($res);
}
