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); }