<?php
/**
* This file contains class that handles connection with oracle database
*
*
* USAGE:
* $dbs['TESTDB'] = array("login", "password", "test_db");
* $db = new database($dbs['TESTDB']);
* $sql = "select * from xyz";
* $ret = $db->_select($sql);
* print_r($ret);
*
*
* @author Peter Pivarc <hide@address.com>
* @version v1.0
*/
if(!function_exists(_err)){
/**
* Function that is used to display error messages
*
* @param string $text
* @param integer|string $endscript if integer = 1, script will be terminated, if integer = 2 script will call history.go(-1), if variable contains string, it is supposed to be valid link that page will be redirected to
* @param integer $showalert 0 do not show alert, 1 show alert
*/
function _err ($text, $endscript=0, $showalert=1)
{
$text=preg_replace("/'/is","\'",$text); #ak retazec obsahuje znaky ' zmen ich na \', aby sa v alerte dobre vypisali
$text=preg_replace("/\n/is","\\n",$text); #ak retazec obsahuje znak noveho riadku \n, zmen ho na \\n, aby sa v alerte dobre vypisal
if($showalert)
echo "\n<script>window.alert('$text');</script>\n";
if ($endscript==1)
exit();
elseif ($endscript ==2)
{
echo "\n<script>history.back();</script>\n";
exit();
}
elseif (!is_numeric($endscript))
echo "<script>location.href='" .$endscript. "'</script>\n";
}
}
/**
* Class will do some basic operations over oracle database
*
* available methods (will do exactly what they are called):
* - _insert
* - _select
* - _update
* - _delete
* - _truncate
*/
class database
{
/**
* database connection description in array (username, password, database_name)
*
* @access public
* @var array
*/
var $dbs;
/**
* database query
*
* @access public
* @var string
*/
var $query;
/**
* oracle connection handler
*
* @access protected
* @var int
*/
var $connection;
/**
* Error level display
*
* Levels:
* - 0 - do not show errors and continue if errors occure
* - 1 - show error but continue in script
* - 2 - show error and stop script execution
* - 3 - do not show error and stop execution
*
* @access public
* @var int
*/
var $err_level;
/**
* @access protected
* @var int
*/
var $p;
/**
* @access protected
* @var int
*/
var $s;
/**
* Log all sql queries to file
* @access public
* @var bool
*/
var $log_all = false;
/**
* Class constructor
*
* @param array $dbs connection description
* @param integer $dispaly_error_level
* @return database
*/
function database($dbs, $dispaly_error_level = 1, $email_notification = true, $log_all = false){
$this->dbs = $dbs;
$this->err_level = $dispaly_error_level;
$this->email_notification = $email_notification;
$this->log_all = $log_all;
switch ($this->err_level){
case 0:
$this->s = 0; //continue
$this->p = 0; //display
break;
case 1:
$this->s = 0;
$this->p = 1;
break;
case 2:
$this->s = 1;
$this->p = 1;
break;
case 3:
$this->s = 1;
$this->p = 0;
break;
}
if(!$this->connection = $this->_connect($this->dbs)){
$err = ocierror();
error_log(date("H:i:s d.m.Y") . "-> Error" . $err[code] .". ".$err[message]." Unable to connect to database.\r\n", 3,"log/db_oracle_err_log.log");
_err("Error. Unable to connect to database.",$this->s,$this->p);
exit;
}
}
/**
* Class destructor
*
*/
function __destruct(){
$this->_disconnect($this->connection);
}
/**
* Database connection trought ocilogon
*
* @access protected
* @param array $dbs
* @return boolean|integer false on error, connection handler otherwise
*/
function _connect($dbs){
$c = ocilogon($dbs[0], $dbs[1], $dbs[2]);
if(!$c){
$err = ocierror();
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _connect error.\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] .". Function _connect error.",$this->s,$this->p);
return false;
}
else {
if($this->log_all)
error_log(date("H:i:s d.m.Y") . "-> (ConnID:$this->connection)(user:$this->uid) Connection to database successfuly established.\r\n", 3,"log/db_oracle_log.log");
return $c;
}
}
/**
* Terminate connection
*
* @access protected
* @param integer $connection
* @return boolean false on error, else true
*/
function _disconnect($connection){
$r = ocilogoff($connection);
if(!$r){
$err = ocierror($res);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _disconnect error 01.\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] .". Function _disconnect error 01.", $this->s,$this->p);
return false;
}
else {
if($this->log_all)
error_log(date("H:i:s d.m.Y") . "-> (ConnID:$this->connection) Successfuly disconected from database.\r\n", 3,"log/db_oracle_log.log");
return true;
}
}
/**
* Use this function to select data from oracle database.
*
* @access public
* @param string $query sql query
* @param integer $skip optional - number of record to skip (like mysql limit) default do not skip anything
* @param integer $maxrows optional - number of records to select (like mysql limit) default select all
* @return array|boolean associative array on succeed, FALSE on error.
*/
function _select($query, $skip = 0, $maxrows = -1){
$res = $this->_parse($this->connection, $query);
if(!$this->_execute($res)){
$err = ocierror($res);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _select error. $query\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] .". Function _select error 01. " . $query,$this->s,$this->p);
return false;
}
$pole = array();
if(ocifetchstatement($res, $pole, $skip, $maxrows) === false){
$err = ocierror($res);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _select error 02. $query\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] . ". Function _select error 02.", $this->s,$this->p);
return false;
}
else {
if($this->log_all)
error_log(date("H:i:s d.m.Y") . " (ConnID:$this->connection) $query\r\n", 3,"log/db_oracle_log.log");
return $pole;
}
}
/**
* Use this function to insert data into oracle database
*
* @access public
* @param string $query
* @return boolean TRUE on succeed, FALSE on error.
*/
function _insert($query){
$res = $this->_parse($this->connection, $query);
if(!$this->_execute($res)){
$err = ocierror($res);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _insert error 01. $query\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] .". Function _insert error 01. " . $query, $this->s,$this->p);
return false;
}
else {
if($this->log_all)
error_log(date("H:i:s d.m.Y") . " (ConnID:$this->connection) $query\r\n", 3,"log/db_oracle_log.log");
return true;
}
}
/**
* Use this function to update data in oracle database.
*
* @access public
* @param string $query
* @return boolean TRUE on succeed, FALSE on error.
*/
function _update($query){
$res = $this->_parse($this->connection, $query);
if(!$this->_execute($res)){
$err = ocierror($res);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _update error 01. $query\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] .". Function _update error 01. " . $query, $this->s,$this->p);
return false;
}
else {
if($this->log_all)
error_log(date("H:i:s d.m.Y") . " (ConnID:$this->connection) $query\r\n", 3,"log/db_oracle_log.log");
return true;
}
}
/**
* Use this function to truncate table in oracle database
*
* @access public
* @param string $query
* @return boolean TRUE on succeed, FALSE on error.
*/
function _truncate($query){
$res = $this->_parse($this->connection, $query);
if(!$this->_execute($res)){
$err = ocierror($res);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _truncate error 01. $query\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] .". Function _truncate error 01. " . $query, $this->s,$this->p);
return false;
}
else {
if($this->log_all)
error_log(date("H:i:s d.m.Y") . " (ConnID:$this->connection) $query\r\n", 3,"log/db_oracle_log.log");
return true;
}
}
/**
* Use this function to delete rows from table in oracle database
*
* @access public
* @param string $query
* @return boolean TRUE on succeed, FALSE on error.
*/
function _delete($query){
$res = $this->_parse($this->connection, $query);
if(!$this->_execute($res)){
$err = ocierror($res);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _delete error 01. $query\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] .". Function _delete error 01. " . $query, $this->s,$this->p);
return false;
}
else {
if($this->log_all)
error_log(date("H:i:s d.m.Y") . " (ConnID:$this->connection) $query\r\n", 3,"log/db_oracle_log.log");
return true;
}
}
/**
* Use this function to parse data
*
* @access protected
* @param integer $connection
* @param string $sql
* @return integer|boolean statement handler on succeed, FALSE on error.
*/
function _parse($connection, $sql){
$res = ociparse($connection,$sql);
if(!$res){
$err = ocierror($res);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _parse error. $sql\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] . ". Function _parse error.",$this->s,$this->p);
return false;
}
else {
return $res;
}
}
/**
* ociexecute
*
* @access protected
* @param integer $res statement handler
* @return boolean false on error, else true
*/
function _execute($res){
if(!ociexecute($res)){
$err = ocierror($res);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Function _execute error.\r\n", 3,"log/db_oracle_err_log.log");
_err("Error " . $err[code] . ". Function _execute error.",$this->s,$this->p);
return false;
}
else {
$committed = ocicommit($this->connection);
// Test whether commit was successful. If error occurred, return error message
if (!$committed) {
$err = oci_error($this->connection);
error_log(date("H:i:s d.m.Y") . "-> Error " . $err[code] .". Commit error.\r\n", 3,"log/db_oracle_err_log.log");
}
else {
if($this->log_all)
error_log(date("H:i:s d.m.Y") . "-> (ConnID:$this->connection) Commited.\r\n", 3,"log/db_oracle_log.log");
}
return true;
}
}
}
?>