Location: PHPKode > scripts > Oracle database > db.class.php
<?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;
		}
	}
}
?>
Return current item: Oracle database