- type ?? The database engine name, such as ORACLE or MYSQL. This should coincide with your dbTransTable identifiers.
- name ?? The unique identifier assigned to a specific connection.
- database ?? The database instance name. In Microsoft SQL Server and MySQL, this will be the database name. In Oracle, this will be the service name.
- username ?? The username of the user that will be connecting to the database.
- password ?? The user??s password.
- portnum ?? The port number to connect to the database server.
- ipaddress ?? The IP address or hostname of the server you will connect to.
class myUDBI
{
function addDatabase($type, $name, $database, $username, $password, $portnum, $ipaddress)
{
switch($type)
{
case "ORACLE":
$tns = "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP)(HOST = ".$ipaddress.")
(PORT = ".$portnum.")))(CONNECT_DATA =
(SERVICE_NAME = ".$database." )))";
$res = ocilogon($username,$password,$tns);
if( $res !== false )
{
$this->dbConnection[$name] = array('RES'=>$res,'TYPE'=>$type);
return true;
}
$this->dbErrorBuffer[count($this->dbErrorBuffer)] = oci_error();
break;
case "MSSQL":
$res = odbc_connect($database,$username,$password);
if( $res !== false )
{
$this->dbConnection[$name] = array('RES'=>$res,'TYPE'=>$type);
return true;
}
$this->dbErrorBuffer[count($this->dbErrorBuffer)] = odbc_errormsg();
break;
case "MYSQL":
$res = mysqli_connect($ipaddress,$username,$password,$database,$port);
if( $res !== false )
{
$this->dbConnection[$name] = array('RES'=>$res,'TYPE'=>$type);
return true;
}
$this->dbErrorBuffer[count($this->dbErrorBuffer)] = mysqli_connect_error();
break;
}
return false;
}
}
Terminating All Database Connections
When the class instance is closed or destroyed, the function, closeDatabase, is called. This function is designed to iterate through all of the database connections listed in dbConnection and properly terminate each session. Alternatively, this is a public function and can be used with an optional parameter, the database unique identifier. This will terminate the specified instance and remove it from dbConnection.
class myUDBI
{
public function closeDatabase($name = null)
{
$status = true;
$keys = array_keys($this->dbConnection);
for( $i = 0; $i < count($keys); $i++ )
{
if( $name == null || array_key_exists($name,$this->dbConnection) )
{
switch( $this->dbConnection[$keys[$i]]['TYPE'] )
{
case "ORACLE":
if( oci_close($this->dbConnection[$keys[$i]]['RES']) )
{
unset($this->dbConnection[$keys[$i]]);
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
oci_error($this->dbConnection[$keys[$i]]['RES']);
$status = false;
}
break;
case "MSSQL":
odbc_close($this->dbConnection[$keys[$i]]['RES']);
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
odbc_errormsg($this->dbErrorBuffer[$keys[$i]]['RES']);
unset($this->dbConnection[$keys[$i]]);
break;
case "MYSQL":
if( !mysqli_close($this->dbConnection[$keys[$i]]['RES']) )
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
mysqli_error($this->dbErrorBuffer[$keys[$i]]['RES']);
$status = false;
}
else
{
unset($this->dbConnection[$keys[$i]]);
}
break;
}
}
}
return $status;
}
}
Error Handling
As a rule of thumb when we develop this class, each function should return false if the function did not complete successfully. In the case that an error has occurred, each function should write the corresponding error message to our class variable, dbErrorBuffer. In addition, we shall include two functions to view the dbErrorBuffer error messages. First, we will create a function called viewAllErrors, which will return the dbErrorBuffer array. Secondly, we will create another function that returns only the last error message encountered, aptly named viewLastError.
class myUDBI
{
public function viewAllErrors()
{
return $this->dbErrorBuffer;
}
public function viewLastError()
{
return $this->dbErrorBuffer[count($this->dbErrorBuffer)];
}
}
Querying and Returning Results
Naturally, we will want to develop a function to query the database and return results, or just update existing data. In our class, there are several factors to consider. First, we must remember that we have created a replication flag called dbReplicate. This class variable determines whether or not all databases in the dbConnection array will be replicated. In other words, if you send an INSERT, UPDATE, CREATE, or DROP query, does it affect a single database or all of the databases. If you decide that you want to replicate databases, you must also decide which database you will SELECT from. This will become your master database. The sendQuery function will apply this logic. In our example, if dbReplicate is set to true, then the optional argument, name, must be assigned to the master database. Otherwise, sendQuery will commit your query but only a boolean result will be returned.
class myUDBI
{
public function sendQuery($query, $name = null)
{
if( $name == null || $this->dbReplicate )
{
$keys = array_keys($this->dbConnection);
for( $i = 0; $i < count($keys); $i++ )
{
if( $name == $keys[$i] )
{
$t = $this->runQuery($query,$keys[$i]);
if( $t === false )
{
return false;
}
else
{
$retData = $t;
}
}
else
{
if( $this->runQuery($query,$keys[$i]) === false )return false;
}
}
return ( $this->dbReplicate && $retData !== true )? $retData:true;
}
else
{
return $this->runQuery($query,$name);
}
}
}
As you may have noticed in the above example, sendQuery does not perform the actual query to the database, but only processes the request. Instead, sendQuery calls another class function named, runQuery. This function performs the actual query to each database connection. The function will process the results from your query, returning false on error or the result set in an array.
class myUDBI
{
private function runQuery($query, $name)
{
switch( $this->dbConnection[$name]['TYPE'] )
{
case "ORACLE":
$res = oci_parse($this->dbConnection[$name]['RES'],
$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
if( $res !== false && oci_execute($res) )
{
$data = array();
while( $data[count($data)] = oci_fetch_array($res) );
return ( count($data) == 0 )? true:$data;
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
oci_error($this->dbConnection[$name]['RES']);
return false;
}
break;
case "MSSQL":
$res = odbc_prepare($this->dbConnection[$name]['RES'],
$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
if( $res !== false && odbc_execute($res) )
{
$data = array();
while( $data[count($data)] = odbc_fetch_array($res) );
return ( count($data) == 0 )? true:$data;
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
odbc_errormsg($this->dbErrorBuffer[$name]['RES']);
return false;
}
break;
case "MYSQL":
$res = mysqli_prepare($this->dbConnection[$name]['RES'],
$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
if( $res !== false && mysqli_execute($res) )
{
$data = array();
while( $data[count($data)] = mysqli_fetch_array($res) );
return ( count($data) == 0 )? true:$data;
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
mysqli_error($this->dbErrorBuffer[$name]['RES']);
return false;
}
break;
default:
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
"Unrecognized database type - ".$this->dbConnection[$name]['TYPE'];
return false;
}
}
}
There are several points to address about this function before proceeding. First, you may notice that an argument can be passed to a rule, using %s. This will allow us to pass a string within the brackets. Also, because the ??%?? symbol is used to process arguments, we must allow the symbol to be escaped. In this case, we use the text, ??[p]??, to escape the ??%?? symbol.
Final Considerations
Because requirements tend to evolve, so should your class. In some instances you may need a function to test whether a value exists in a table rather than returning any values (see example below). In other cases, you may want to upload a file within the database. In addition, you may want to execute database procedures. Whatever the case, your new class can evolve to your programming needs and yet remain universal, cross-platform independent.
class myUDBI
{
public function testValue($query, $name)
{
switch( $this->dbConnection[$name]['TYPE'] )
{
case "ORACLE":
$res = oci_parse($this->dbConnection[$name]['RES'],
$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
if( $res !== false && oci_execute($res) )
{
return oci_fetch($res);
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
oci_error($this->dbConnection[$name]['RES']);
return false;
}
break;
case "MSSQL":
$res = odbc_prepare($this->dbConnection[$name]['RES'],
$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
if( $res !== false && odbc_execute($res) )
{
return ( odbc_num_rows($res) > 0 )? true:false;
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
odbc_errormsg($this->dbErrorBuffer[$name]['RES']);
return false;
}
break;
case "MYSQL":
$res = mysqli_prepare($this->dbConnection[$name]['RES'],
$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
if( $res !== false && mysqli_execute($res) )
{
return mysqli_stmt_fetch($res);
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
mysqli_error($this->dbErrorBuffer[$name]['RES']);
return false;
}
break;
default:
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
"Unrecognized database type - ".$this->dbConnection[$name]['TYPE'];
return false;
}
}
}
Final Considerations
Because requirements tend to evolve, so should your class. In some instances you may need a function to test whether a value exists in a table rather than returning any values (see example below). In other cases, you may want to upload a file within the database. In addition, you may want to execute database procedures. Whatever the case, your new class can evolve to your programming needs and yet remain universal, cross-platform independent.
class myUDBI
{
public function testValue($query, $name)
{
switch( $this->dbConnection[$name]['TYPE'] )
{
case "ORACLE":
$res = oci_parse($this->dbConnection[$name]['RES'],
$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
if( $res !== false && oci_execute($res) )
{
return oci_fetch($res);
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
oci_error($this->dbConnection[$name]['RES']);
return false;
}
break;
case "MSSQL":
$res = odbc_prepare($this->dbConnection[$name]['RES'],
$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
if( $res !== false && odbc_execute($res) )
{
return ( odbc_num_rows($res) > 0 )? true:false;
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
odbc_errormsg($this->dbErrorBuffer[$name]['RES']);
return false;
}
break;
case "MYSQL":
$res = mysqli_prepare($this->dbConnection[$name]['RES'],
$this->mutateQuery($query,$this->dbConnection[$name]['TYPE']));
if( $res !== false && mysqli_execute($res) )
{
return mysqli_stmt_fetch($res);
}
else
{
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
mysqli_error($this->dbErrorBuffer[$name]['RES']);
return false;
}
break;
default:
$this->dbErrorBuffer[count($this->dbErrorBuffer)] =
"Unrecognized database type - ".$this->dbConnection[$name]['TYPE'];
return false;
}
}
}
Using the Class
Now that we have completed our class, we can now use it in other scripts. In the example below, I will create three database connections, set the replication flag to true, and process incoming data. The data will be saved in three different databases for various uses.
<?php
// Database connections
$db = new myUDBI(true);
// Master database
$db->addDatabase("ORACLE",
"ORA",
"master.foo.com",
"UserA",
"abc123",
1521,
"192.168.0.10");
// Marketing database
$db->addDatabase("MYSQL",
"MRKTG",
"marketing ",
"UserB",
"abc456",
3306,
"192.168.0.20");
// Assembly database
$db->addDatabase("MSSQL",
"ASSEM",
"assembly ",
"UserC",
"abc789",
null,
"192.168.0.30");
// incoming data variables
$client = Company A’;
$expense = 1299.99;
$dateProcess = 20071201’;
// update databases
$qry = INSERT INTO TBL_CUST
(CUSTOMER,EXPNSE,ACTIONDATE)
VALUES
(‘’.$client.’´,’.$expense.’,DATE[.$dateProcess.’])’;
If( $db->sendQuery($qry) !== false )
{
echo Data Updated!’;
}
else
{
echo Error Encountered: .$db->viewLastError();
}
?>
