/**
* 按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;
}
/**
* Function takes in an sql statement and returns data as an array. When calling, always alias
* resuts returned from stored function as 'dataArray'
*/
function executeQuery($SQLstatement, $parserFunction = "defaultFunction")
{
$conn = $GLOBALS['oracle_connection'];
if (!$conn) {
return array('error' => oci_error());
}
$preparedStatement = oci_parse($conn, $SQLstatement);
//Prepare statement
$success = oci_execute($preparedStatement);
//execute preparedStatement
if (!$success) {
return array('error' => oci_error($preparedStatement));
}
$arrayOfDataReturned = array();
//Array containing all data returned from result set
$currentRecord;
//temp user for each result set
while ($functionResults = oci_fetch_array($preparedStatement, OCI_ASSOC)) {
//Get first class in result set
/**Calls variable function; SEE: http://www.php.net/manual/en/functions.variable-functions.php**/
$currentRecord = $parserFunction($functionResults);
//Convert information array to class
array_push($arrayOfDataReturned, $currentRecord);
//push created object into all classes array
//echo($allStudentClasses[0]->term + "<br />");
}
oci_free_statement($preparedStatement);
return $arrayOfDataReturned;
}
/**
* Destructor - Checks for an oci statment resource and frees the resource if needed.
*/
public function __destruct()
{
if (strtolower(get_resource_type($this->stmt)) == 'oci8 statement') {
oci_free_statement($this->stmt);
}
//Also test for descriptors
}
function q5()
{
$dao = new DAO();
$q = "SELECT USERID FROM Q5 WHERE NUM = (SELECT MAX(NUM) FROM Q5)";
$stmt = oci_parse($dao->con, $q);
oci_execute($stmt, OCI_DEFAULT);
oci_fetch_all($stmt, $rs);
foreach ($rs as $rows) {
echo "";
foreach ($rows as $col_values) {
echo "User contributed most reviews: " . $dao->fetchUserName($col_values) . '<br>';
}
}
oci_free_statement($stmt);
$q = "SELECT USERID FROM Q5 WHERE NUM = (SELECT MAX(NUM) FROM Q5)";
$stmt = oci_parse($dao->con, $q);
oci_execute($stmt, OCI_DEFAULT);
oci_fetch_all($stmt, $rs);
foreach ($rs as $rows) {
echo "";
foreach ($rows as $col_values) {
echo "User contributed most comments on threads: " . $dao->fetchUserName($col_values) . '<br>';
}
}
}
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 endQuery($sqlQuery)
{
if (!oci_free_statement($this->queries[$sqlQuery])) {
echo "******** Unable to clean oci resource " . $this->queries[$sqlQuery];
}
unset($this->queries[$sqlQuery]);
}
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);
}
public function execute($query)
{
$q_result = oci_parse($this->connection, $query);
oci_execute($q_result);
//Gibt den Speicher wieder frei
oci_free_statement($q_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;
}
/**
* 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>";
}
public function __destruct()
{
if (!is_null($this->_cursor)) {
oci_free_statement($this->_cursor);
$this->_cursor = null;
}
}
function db_closequery($qhandle)
{
if(function_exists("oci_free_statement"))
oci_free_statement($qhandle);
else
ocifreestatement($qhandle);
}
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);
}
}
public function free_result()
{
if (is_resource($this->result_id)) {
oci_free_statement($this->result_id);
$this->result_id = FALSE;
}
}
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;
}
}
/**
* closes the cursor, allowing the statement to be executed again
*/
public function closeCursor()
{
if (!$this->_stmt) {
return false;
}
oci_free_statement($this->_stmt);
$this->_stmt = false;
}
public function close()
{
if ($this->stmt) {
oci_free_statement($this->stmt);
$this->stmt = null;
}
$this->current = null;
}
public function closeCursor()
{
if (!$this->_cursorOpen) {
return true;
}
$this->_cursorOpen = false;
return oci_free_statement($this->_sth);
}
/**
* This function frees the command reference.
*
* @access public
* @override
*/
public function free()
{
if ($this->command !== NULL) {
@oci_free_statement($this->command);
$this->command = NULL;
$this->record = FALSE;
}
}
function endQuery($sqlQuery)
{
// TODO : Check this return value
$OK = oci_free_statement($this->queries[$sqlQuery]);
if ($OK != true) {
echo "******** Unable to clean oci resource " . $this->queries[$sqlQuery];
}
unset($this->queries[$sqlQuery]);
}
function getRequetes($from, $conn)
{
$req = "SELECT distinct(count(*)) from {$from}";
$cur = PreparerRequete($conn, $req);
$r = ExecuterRequete($cur);
$return = oci_fetch_array($cur);
oci_free_statement($cur);
return $return;
}
protected function fetchDateFormat()
{
$sql = "select value from v\$nls_parameters where parameter = 'NLS_DATE_FORMAT'";
$query = oci_parse($this->conn->getResource(), $sql);
oci_execute($query);
$row = oci_fetch_assoc($query);
oci_free_statement($query);
return $row['VALUE'];
}
/**
* Destructor closes the statement and connection
*/
function __destruct()
{
if ($this->stid) {
oci_free_statement($this->stid);
}
if ($this->conn) {
oci_close($this->conn);
}
}
public function free()
{
if (!oci_free_statement($this->_cursor)) {
require_once 'Zend/Db/Cursor/Exception.php';
throw new Zend_Db_Cursor_Exception('Couldn\'t free the cursor.');
}
// Reset the cursor
$this->_cursor = null;
}
public function closeCursor()
{
if (empty($this->_sth)) {
return true;
}
$success = oci_free_statement($this->_sth);
$this->_sth = null;
return $success;
}
/**
*@package db_oracle
*@method close()
*@desc Close Oracle connection
*@since v0.1 beta
* */
public function close()
{
if (isset($this->resource)) {
oci_free_statement($this->resource);
}
if (self::$is_connected) {
oci_close(self::$conn);
self::$is_connected = false;
}
}
function getOneColumnAsArray()
{
$column = array();
$queryId = $this->connection->executeStatement($this->getStatement());
while (is_array($row = oci_fetch_array($queryId, OCI_NUM + OCI_RETURN_NULLS))) {
$column[] = $row[0];
}
oci_free_statement($queryId);
return $column;
}
/** Helper function for printing out core fulfillment options */
function printCoreSet($num)
{
global $conn;
$query = "SELECT core_code, description FROM vw_core_requirement WHERE core_set_id=" . $num . " ORDER BY report_order";
$data = oci_parse($conn, $query);
oci_execute($data);
while (oci_fetch($data)) {
echo "<option value='" . oci_result($data, 'CORE_CODE') . "'>" . oci_result($data, 'CORE_CODE') . "-" . oci_result($data, 'DESCRIPTION') . "</option>\n";
}
oci_free_statement($data);
}
function DriverOciExec($conn, $sql)
{
$stmt = oci_parse($conn, $sql);
if (!$stmt) {
throw new lmbDbException('OCI parse error happened', oci_error());
}
if (!oci_execute($stmt)) {
throw new lmbDbException('OCI execute error happened', oci_error($stmt));
}
oci_free_statement($stmt);
}
