PHP Database Wrapper Class

Many organizations use multi database software. For example an organisation’s HRD system using SQL Server and on its ERP they use DB2.

To develop an application that can connect to DB2 and SQL Server using PHP, we need database-independent connection object.

Here’s a database wrapper class sample code:

class DBType {
   const db2 = 0;
   const sqlserver = 1;
}

class db_connection {
		private $conn, $query_stmt;
		private $dbprod_var;
		function __construct($Database_var, $host, $dbname, $user, $passwd) {
			$this->dbprod_var = $Database_var;
			switch ($this->dbprod_var) {
				case DBType::db2:
					$this->conn = db2_connect($dbname,$user,$passwd);
					break;
				case DBType::sqlserver:
					$this->conn = mssql_connect($host,$user,$passwd);
					mssql_select_db($dbname, $this->conn);
					break;
			}

		}

		function loadrecords($sql) {
			if (strtoupper(substr(trim($sql), 0, 6))=="SELECT") {
				try {
					switch ($this->dbprod_var) {
						case DBType::db2:
							$this->query_stmt = db2_prepare($this->conn, $sql);
							$result = db2_execute($this->query_stmt);
							break;
						case DBType::sqlserver:
							$this->query_stmt = mssql_query($sql, $this->conn);
							$result =$this->query_stmt;
							break;
					}

					if ($result) {
						return new db_recordset($this->query_stmt,$this->dbprod_var);

					} else {
						return false;
					}
				} catch (Exception $ex) {
					die($sql."</pre>

<hr />

<pre>
".$ex->getMessage());
				}
			} else {
				die($sql."</pre>

<hr />

<pre>
loadrecords error: No 'select' sql statement");
			}
		}

		function execute($sql) {
			if (strtoupper(substr(trim($sql), 0, 6))<>"SELECT") {
				try {
					switch ($this->dbprod_var) {
						case DBType::db2:
							$this->query_stmt = db2_prepare($this->conn, $sql);
							$result = db2_execute($this->query_stmt);
							break;
						case DBType::sqlserver:
							$result = mssql_query($sql, $this->conn);
							break;
					}

					return $result;
				} catch (Exception $ex) {
					die($sql."</pre>

<hr />

<pre>
".$ex->getMessage());
				}
			} else {
				die($sql."</pre>

<hr />

<pre>
execute error: Can not start sql statement with 'select'");
			}
		}

		function Close() {
			switch ($this->dbprod_var) {
				case DBType::db2:
					db2_close($this->conn);
					break;
				case DBType::sqlserver:
					mssql_close($this->conn);
					break;
			}
		}
	}

class db_recordset {
		private $query_stmt, $row;
		private $dbprod_var;
		function __construct($query_stmt, $dbprod_var) {
			$this->dbprod_var = $dbprod_var;
			$this->query_stmt = $query_stmt;
		}

		function ReadArray($bool_as_struct=false) {
			switch ($this->dbprod_var) {
				case DBType::db2:
					$this->row = db2_fetch_array($this->query_stmt);
					break;
				case DBType::sqlserver:
					$this->row = mssql_fetch_array($this->query_stmt, MSSQL_NUM);
					break;
			}

			if ($this->row) {
				if (!$bool_as_struct) {
					return $this->row;
				} else {
					return row_on_struct();
				}
			} else {
				return false;
			}
		}

		function Close() {
			unset($this->row);
		}
	}

Explanation:
Class DBTypeis an enumeration constants that define DB2 and SQL Server.
Class db_connection connects, load records with ‘select’ query and executes add, edit, delete to database objects.
Class db_recordset reads recordset data.

PHP Example of DB2 Implementation:

//include the database wrapper class
include_once "connect_db_wrap.php";

$sql = "select DEPTNO, DEPTNAME from DEPARTMENT";

//Open DB2 connection with related enumeration
$dbconn = new db_connection(DBType::db2,"127.0.0.1", "SAMPLE", "user", "pwd");
$rs = $dbconn->loadrecords($sql);
while ($row = $rs->ReadArray()) {
   echo $row[0]." ".$row[1]."--
";
}
$rs->Close();

$dbconn->Close();

PHP Example of SQL Server Implementation:

//include the database wrapper class
include_once "connect_db_wrap.php";

$sql = "select tester, division from Testers";
//Open DB2 connection with related enumeration
$dbconn = new db_connection(DBType::sqlserver, "127.0.0.1", "TestDB", "sa", "");
$rs = $dbconn->loadrecords($sql);
while ($row = $rs->ReadArray()) {
	echo $row[0]." ".$row[1]."--
";
}
$rs->Close();

$dbconn->execute("insert into Testers (tester, division) values ('Rin tintin', 'ERP report')");

$dbconn->Close();

Both of implementation php file has same structure and codes, except that on db_connection’s constructor. It choose between DB2 or SQL Server.

Cheers,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s