/**
* 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;
}
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;
}
/**
* @see ResultSet::next()
*/
function next()
{
// no specific result position available
// Returns an array, which corresponds to the next result row or FALSE
// in case of error or there is no more rows in the result.
$this->fields = oci_fetch_array($this->result, $this->fetchmode + OCI_RETURN_NULLS + OCI_RETURN_LOBS);
if (!$this->fields) {
// grab error via array
$error = oci_error($this->result);
if (!$error) {
// end of recordset
$this->afterLast();
return false;
} else {
throw new SQLException('Error fetching result', $error['code'] . ': ' . $error['message']);
}
}
// Oracle returns all field names in uppercase and associative indices
// in the result array will be uppercased too.
if ($this->fetchmode === ResultSet::FETCHMODE_ASSOC && $this->lowerAssocCase) {
$this->fields = array_change_key_case($this->fields, CASE_LOWER);
}
// Advance cursor position
$this->cursorPos++;
return true;
}
public function testConnection($db_info)
{
if (!$this->isValid($db_info)) {
return false;
}
foreach ($db_info as $key => $value) {
if (empty($db_info[$key])) {
unset($db_info[$key]);
}
}
if ($db_info['db_oracle_type'] == 'tns') {
$connect = @oci_connect($db_info['db_user'], $db_info['db_password'], $db_info['db_net_service_name']);
if (!$connect) {
$error = oci_error();
throw new \Exception($error['message'], $error['code']);
}
} else {
$dsn = $db_info['db_host'];
$dsn .= ':' . $db_info['db_port'];
$dsn .= '/' . $db_info['db_service_name'];
$connect = @oci_connect($db_info['db_user'], $db_info['db_password'], $dsn);
if (!$connect) {
$error = oci_error();
throw new \Exception($error['message'], $error['code']);
}
}
return true;
}
/**
* 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 executePlainSQL($cmdstr)
{
//takes a plain (no bound variables) SQL command and executes it
//echo "<br>running ".$cmdstr."<br>";
global $db_conn, $success;
$statement = OCIParse($db_conn, $cmdstr);
//There is a set of comments at the end of the file that describe some of the OCI specific functions and how they work
if (!$statement) {
$e = OCI_Error($db_conn);
// For OCIParse errors pass the
// connection handle
echo "<div class='alert alert-danger' role='alert'>";
echo "<span class='glyphicon glyphicon-exclamation-sign' aria-hidden='true'></span>";
echo "<span class='sr-only'>Error:</span>";
echo htmlentities($e['message']);
echo "<br>Cannot parse the following command: " . $cmdstr . "<br>";
echo "</div>";
$success = False;
}
$r = OCIExecute($statement, OCI_DEFAULT);
if (!$r) {
#echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
$e = oci_error($statement);
// For OCIExecute errors pass the statementhandle
echo "<div class='alert alert-danger' role='alert'>";
echo "<span class='glyphicon glyphicon-exclamation-sign' aria-hidden='true'></span>";
echo "<span class='sr-only'>Error:</span>";
echo htmlentities($e['message']);
echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
echo "</div>";
$success = False;
} else {
}
return $statement;
}
public function getError()
{
set_error_handler($this->getErrorHandler());
$error = oci_error($this->resource);
restore_error_handler();
return $error;
}
/**
* 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;
}
function executePlainSQL($cmdstr)
{
//takes a plain (no bound variables) SQL command and executes it
//echo "<br>running ".$cmdstr."<br>";
global $db_conn, $success;
$statement = OCIParse($db_conn, $cmdstr);
//There is a set of comments at the end of the file that describe some of the OCI specific functions and how they work
if (!$statement) {
echo "<br>Cannot parse the following command: " . $cmdstr . "<br>";
$e = OCI_Error($db_conn);
// For OCIParse errors pass the
// connection handle
echo htmlentities($e['message']);
$success = False;
}
$r = OCIExecute($statement, OCI_DEFAULT);
if (!$r) {
echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
$e = oci_error($statement);
// For OCIExecute errors pass the statementhandle
echo htmlentities($e['message']);
$success = False;
} else {
}
return $statement;
}
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 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();
}
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 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 open_connection()
{
$connection_string = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)\n (HOST = " . DB_SERVER . ")(PORT = " . DB_PORT . ")))(CONNECT_DATA=(SID=" . DB_NAME . ")))";
$this->connection = oci_connect(DB_USER, DB_PASS, $connection_string);
if (!$this->connection) {
die("Database connection failed: " . oci_error());
}
}
function log_if_error($resource)
{
if (!$resource) {
$error = oci_error();
echo $error['message'];
exit;
}
}
protected static function oci_error($resource = null) {
$error = isset($resource) ? oci_error($resource) : oci_error();
if ($error === FALSE) {
$error = array('message' => 'No error is found');
}
return $error;
}
private function __construct()
{
$this->con = oci_connect($this->user, $this->pass);
if (!$this->con) {
$m = oci_error();
exit('Connect Error ' . $m['message']);
}
}
public function __construct(array $config)
{
$this->_connection = oci_connect($config['username'], $config['password'], $config['host'] . ":" . $config['port'] . "/" . $config['instance_name'], $config['charset']);
if (!$this->_connection) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
}
public function get_ams_student($student_id = NULL)
{
$conn = oci_connect('AMS_QUERIES', 'Oo_Hecha1_rohm3', '//192.168.170.171:1522/ACADEMIC');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
} else {
if ($student_id == NULL) {
$sql = "SELECT * FROM GAOWNER.VIEW_STUDENT_DETAILS";
} else {
$sql = 'SELECT * FROM GAOWNER.VIEW_STUDENT_DETAILS WHERE STUDENT_NO LIKE \'%' . $student_id . '%\'';
}
$rs4 = oci_parse($conn, $sql);
oci_execute($rs4);
$rows = oci_num_rows($rs4);
$t = 0;
while (OCIFetch($rs4)) {
$t++;
$name1 = ociresult($rs4, "SURNAME");
$dob = ociresult($rs4, "DOB");
$gender = ociresult($rs4, "GENDER");
$oname1 = ociresult($rs4, "OTHER_NAMES");
$STUDENT_NO = ociresult($rs4, "STUDENT_NO");
$COURSES = ociresult($rs4, "COURSES");
$GUARDIAN_NAME1 = ociresult($rs4, "GUARDIAN_NAME");
$MOBILE_NO = ociresult($rs4, "MOBILE_NO");
$EMAIL = ociresult($rs4, "EMAIL");
$FACULTIES = ociresult($rs4, "FACULTIES");
// details to be saved
$name = str_replace("'", "", "{$name1}");
$oname = str_replace("'", "", "{$oname1}");
$GUARDIAN_NAME = str_replace("'", "", "{$GUARDIAN_NAME1}");
if (!empty($STUDENT_NO)) {
$exists = $this->student_exists($STUDENT_NO);
$data = array('title' => '', 'Surname' => $name, 'Other_names' => $oname, 'DOB' => $dob, 'contact' => $MOBILE_NO, 'gender' => $gender, 'student_Number' => $STUDENT_NO, 'courses' => $FACULTIES, 'GUARDIAN_NAME' => $GUARDIAN_NAME, 'faculty' => $FACULTIES);
if (!$exists) {
$this->db->insert('student', $data);
} else {
$this->db->where('student_Number', $STUDENT_NO);
$this->db->update('student', $data);
}
$date = date("Y-m-d H:i:s");
// data for patients patient date, visit type, strath number created by and modified by fields
if ($student_id != NULL) {
$patient_data = array('patient_number' => $this->create_patient_number(), 'patient_date' => $date, 'visit_type_id' => 1, 'strath_no' => $STUDENT_NO, 'created_by' => $this->session->userdata('personnel_id'), 'modified_by' => $this->session->userdata('personnel_id'));
$this->db->insert('patients', $patient_data);
return $this->db->insert_id();
}
} else {
$this->session->set_userdata("error_message", "Student could not be found");
return FALSE;
}
}
if ($student_id != NULL) {
return TRUE;
}
}
}
function connect()
{
$conn = oci_connect('olexson', 'yohatdog4');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
return $conn;
}
/**
* Construct method where configuration is applied
* @param string $dbIdent Identifier of connection parameters section in config file
*/
public function __construct($dbIdent)
{
$connData = Pokelio_Global::getConfig('DATABASES', 'Pokelio')->{$dbIdent};
$this->connection = oci_connect($connData->USER, $connData->PASSWORD, $connData->DSN);
if (!$this->connection) {
$e = oci_error();
trigger_error("Error connecting to database." . NL . htmlentities($e['message'], ENT_QUOTES) . NL);
}
}
/**
* Méthode de connection à la base Oracle
*
* @return void
*/
private final function _connect()
{
trigger_error('start', E_USER_NOTICE);
trigger_error('service ' . $this->getInput('servicename'), E_USER_NOTICE);
if (!($this->handle = oci_connect($this->getSpecialProperty('login'), $this->getSpecialProperty('password'), $this->getInput('servicename')))) {
trigger_error('Erreur connexion oracle ' . serialize(oci_error()), E_USER_ERROR);
}
trigger_error('end', E_USER_NOTICE);
}
function connect()
{
$conn = oci_connect('username', 'password');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
return $conn;
}
public function connect()
{
$conf = c("db.write");
$conn_str = sprintf("(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = %s)(PORT = %s)) (CONNECT_DATA= (SID = %s)))", $conf['dbhost'], $conf['dbport'], $conf['dbname']);
self::$link = oci_pconnect($con_user, $con_pwd, $conn_str, str_replace("-", "", $con_charset));
if (!self::$link) {
throw_exception(oci_error());
}
}
function connect()
{
$conn = oci_connect('yunita', 'yenchi12');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
return $conn;
}
function connect()
{
//please enter your oracle login info
$conn = oci_connect('bwcheung', 'bc4784387');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
return $conn;
}
function ExecuterRequete($cur)
{
$r = oci_execute($cur);
if (!$r) {
$e = oci_error($r);
echo htmlentities($e['message']);
exit;
}
return $r;
}
public function connect($params)
{
$conn = @oci_connect($params["username"], $params["password"], $params["server"], 'UTF8');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$this->connect_shared($params);
return $conn;
}
public function InsertInto($conn, $sql)
{
$execute = oci_execute($parse);
if ($execute) {
$this->resultQuery .= "SUKSES";
} else {
$this->resultQuery .= "GAGAL" . oci_error();
}
return $this->resultQuery;
}
function __construct()
{
$this->con = oci_connect(DB_USER, DB_PASS, DB_HOST);
if (!$this->con) {
$e = oci_error();
// For oci_connect errors do not pass a handle
trigger_error(htmlentities($e['message']), E_USER_ERROR);
}
$this->stmt = "very simple";
}
