Database Code
  Home arrow Database Code arrow PostgreSQL Abstraction Layer
Codewalker Forums 
  Tutorials  
Database Articles  
Miscellaneous  
Navigation Usability  
PEAR Articles  
Programming Basics  
Server Administration  
XML Tutorials  
  Reviews  
Database Book Reviews  
Linux Book Reviews  
Miscellaneous Reviews  
PHP Book Reviews  
PHP Software Reviews  
Server Admin Reviews  
SQL Tool Reviews  
  Code Gallery  
Content Management Code  
Contest Code  
Counters Code  
Database Code  
Date Time Code  
Discussion Board Code  
Email Code  
File Manipulation Code  
GUI Code  
Link Farm Code  
Miscellaneous Code  
Search Code  
Site Navigation Code  
User Management Code  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
Download TestComplete 
Forums Sitemap 
Weekly Newsletter 
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE CODE

PostgreSQL Abstraction Layer
By: Codewalkers
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 1
    2002-01-18

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Class that provides an abstraction layer above the PHP PostgreSQL API. This centralizes all the database connection info such as host, port, username and password. Same class can be used to connect to multiple databases, local and remote, on different ports.
    In addition to "normal" stuff that you'd expect from a db abstraction layer, it also supports transactions and comprehensive error checking/management.

    By : tmchow

    <?php
    ///////////////////////
    // Author: Trevin Chow
    // Email: t1@mail.com
    // Date: February 21, 2000
    // Last Updated: August 14, 2001
    //
    // Description:
    // Abstracts both the php function calls and the server information to POSTGRES
    // databases. Utilizes class variables to maintain connection information such
    // as number of rows, result id of last operation, etc.
    //
    // Sample Usage:
    // include("include/dblib.php");
    // $db = new phpDB();
    // $db->connect("foobar");
    // $db->exec("SELECT * from TREVIN");
    // while ($db->nextRow()) {
    // $rs = $db->fobject();
    // echo "$rs->description : $rs->color : $rs->price <br>\n";
    // }
    //
    // Modification History:
    // Modification History:
    // - v1.04b, 08/11/2001, Trevin Chow, t1@mail.com
    //
    // Fixed following bugs:
    // * added in call to moveFirst() in exec() to ensure that on subsequent calls to exec
    // we are moving row pointer to beginning again. Previously, row pointer wasn't being
    // updated.
    //
    // - v1.04a, 08/09/2001, Trevin Chow, t1@mail.com
    //
    // Fixed following bugs introduced in v1.03:
    // * connect() function had problem with setting password. Instead of using $this->password, was using $this->userName again for some reason.
    //
    // - v1.04, 07/29/2001, Trevin Chow, t1@mail.com
    //
    // Added Following function(s):
    // * currRow() - return current row
    //
    // - v1.03, 05/18/2001, Lee Pang, wleepang@hotmail.com
    //
    // Added the following functions:
    // * moveNext() - same as nextRow(), better syntax for VB/ASP converts like myself.
    // * movePrevious() - like nextRow(), just in the opposite direction.
    // * recordCount() - same as numRows(), better syntax for VB/ASP converts
    // * columnCount() - same as numFields, better syntax
    // * querySafe() - removes "\r" and "\n" and replaces "\'" with "'" in input query
    // * sqlSafe() - replaces "\'" with "\'\'"
    //
    // Added more comprehensive error handling:
    // * internal error code $errorCode
    // * in connect()
    // * in errorMsg()
    //
    // Modified following functions:
    // * connect() - generates connection string based on available data
    //
    // Fixed the following bugs:
    // * Syntax error in numAffected() - if ($this->result = null) ... to if ($this->result == null) ...
    ///////////////////////

    class phpDB {

    // set when connect() is called, defined in set_db_info()
    var $hostName = '';
    var $port = '';
    var $userName = '';
    var $password = '';
    var $databaseName = '';
    var $connectionID = -1;
    var $row = -1; // a row counter, needed to loop through records in postgres.
    var $result = null; // point to result set.
    var $errorCode = 0; // internal error code

    ////////////////////////////////////////////
    // Core primary connection/database function
    ////////////////////////////////////////////

    // Set appropriate parameters for database connection
    function set_db_info($DataBaseReference){
    switch ($DataBaseReference){
    case "someName":
    $this->hostName = "localhost";
    $this->port = "5432";
    $this->userName = "nobody" ;
    $this->password = "";
    $this->databaseName = "someDBname";
    break;
    case "test":
    $this->hostName = "";
    $this->port = "";
    $this->userName = "";
    $this->password = "" ;
    $this->databaseName = "test";
    break;
    default:
    // FATAL ERROR - DB REFERENCE UNDEFINED
    }
    }

    // connection function
    function connect($DataBaseReference){
    if (isset($DataBaseReference)) {
    $this->set_db_info($DataBaseReference);

    // build connection string based on internal settings.
    $connStr = '';
    ($this->hostName != '') ? ($connStr .= "host=" . $this->hostName . " ") : ($connStr = $connStr);
    ($this->port != '') ? ($connStr .= "port=" . $this->port . " ") : ($connStr = $connStr);
    ($this->databaseName != '') ? ($connStr .= "dbname=" . $this->databaseName . " ") : ($connStr = $connStr);
    ($this->userName != '') ? ($connStr .= "user=" . $this->userName . " ") : ($connStr = $connStr);
    ($this->password != '') ? ($connStr .= "password=" . $this->password . " ") : ($connStr = $connStr);
    $connStr = trim($connStr);

    $connID = @pg_connect($connStr);
    if ($connID != "") {
    $this->connectionID = $connID;
    $this->exec("set datestyle='ISO'");
    $this->exec("set time zone 'PST'");
    return $this->connectionID ;
    } else {
    // FATAL ERROR - CONNECTI0N ERROR
    $this->errorCode = -1;
    $this->connectionID = -1;
    return 0;
    }
    } else {
    // FATAL ERROR - FUNCTION CALLED WITH NO PARAMETERS
    $this->connectionID = -1;
    return 0;
    }
    }

    // standard method to close connection
    function close() {
    if ($this->connectionID != "-1") {
    $this->RollbackTrans(); // rollback transaction before closing
    $closed = pg_close($this->connectionID);
    return $closed;
    } else {
    // connection does not exist
    return null;
    }
    }

    // function to execute sql queries
    function exec($query){
    if ($this->connectionID != "-1") {
    $this->result = @pg_exec($this->connectionID, $query);
    if ($this->numRows() > 0) $this->moveFirst();
    return $this->result;
    }
    else return 0;
    }

    // get last error message for db connection
    function errorMsg() {
    if ($this->connectionID == "-1") {
    switch ($this->errorCode) {
    case -1:
    return "FATAL ERROR - CONNECTION ERROR: RESOURCE NOT FOUND";
    break;
    case -2:
    return "FATAL ERROR - CLASS ERROR: FUNCTION CALLED WITHOUT PARAMETERS";
    break;
    default:
    return null;
    }
    } else {
    return pg_errormessage($this->connectionID);
    }
    }

    ////////////////////
    // Cursor movement
    ////////////////////

    // move pointer to first row of result set
    function moveFirst() {
    if ($this->result == null) return false;
    else {
    $this->setRow(0);
    return true;
    }
    }

    // move pointer to last row of result set
    function moveLast() {
    if ($this->result == null) return false;
    else {
    $this->setRow($this->numRows()-1);
    return true;
    }
    }

    // point to the next row, return false if no next row
    function moveNext() {
    // If more rows, then advance row pointer
    if ($this->row < $this->numRows()-1) {
    $this->setRow($this->row +1);
    return true;
    }
    else return false;
    }

    // point to the previous row, return false if no previous row
    function movePrevious() {
    // If not first row, then advance row pointer
    if ($this->row > 0) {
    $this->setRow($this->row -1);
    return true;
    }
    else return false;
    }

    // point to the next row, return false if no next row
    function nextRow() {
    // If more rows, then advance row pointer
    if ($this->row < $this->numRows()-1) {
    $this->setRow($this->row +1);
    return true;
    }
    else return false;
    }

    // can be used to set a pointer to a perticular row
    function setRow($row){
    $this->row = $row;
    }

    ///////////////////////
    // Result set related
    ///////////////////////

    // used to pull the results back
    function fobject() {
    if ($this->result == null || $this->row == "-1") return null;
    else {
    $object = pg_fetch_object($this->result,$this->row);
    return $object;
    }
    }

    // another method to obtain results
    function farray(){
    if ($this->result == null || $this->row == "-1") return null;
    else {
    $arr = pg_fetch_array($this->result,$this->row);
    return $arr;
    }
    }

    // return number of affected rows by a DELETE, UPDATE, INSERT
    function numAffected() {
    if ($this->result == null) return 0; // no result to return result from!
    else return pg_cmdtuples ($this->result);
    }

    // get the number of rows in a result
    function numRows(){
    if ($this->result == null) return 0;
    else {
    $this->numrows = pg_numrows($this->result);
    return $this->numrows;
    }
    }

    // return current row
    function currRow(){
    return $this->row;
    }

    function recordCount() {
    return $this->numRows();
    }

    // get the number of fields in a result
    function numFields() {
    if ($this->result == null) return 0;
    else return pg_numfields ($this->result);
    }

    function columnCount() {
    return $this->numFields();
    }

    // get last OID (object identifier) of last INSERT statement
    function lastOID() {
    if ($this->result == null) return null;
    else return pg_getlastoid ($this->result);
    }

    // get result field name
    function fieldname($fieldnum) {
    if ($this->result == null) return null;
    else return pg_FieldName($this->result, $fieldnum);
    }

    ////////////////////////
    // Transaction related
    ////////////////////////

    function beginTrans() {
    return @pg_exec($this->connectionID, "begin");
    }

    function commitTrans() {
    return @pg_exec($this->connectionID, "commit");
    }

    // returns true/false
    function rollbackTrans() {
    return @pg_exec($this->connectionID, "rollback");
    }

    ////////////////////////
    // SQL String Related
    ////////////////////////
    function querySafe($string) {
    // replace \' with '
    $string = str_replace("\'", "'", $string);

    // replace line-break characters
    $string = str_replace("\n", "", $string);
    $string = str_replace("\r", "", $string);

    return $string;
    }

    function sqlSafe($string) {
    // replace \' with \'\'
    // use this function only for text fields that may contain "'"'s
    $string = str_replace("\'", "\'\'", $string);
    return $string;
    }
    } // end class phpDB

    ?>
    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

    More Database Code Articles
    More By Codewalkers

     

    IBM® developerWorks developerWorks - FREE Tools!


    NEW! Addressing software-as-a-service challenges using Tivoli security and WebSphere solutions

    Building a software-as-a-service solution requires addressing a few key technical challenges. In this webcast, we'll focus on the role of IBM Tivoli Directory Server and WebSphere Portlet Factory in creating a Software as a Service solution. We will demonstrate how to use Tivoli Directory Server to prevent the user population of one tenant from accessing the virtual portal and portlet components of another tenant. We will also use the dynamic profile capability of WebSphere Portlet Factory to create multiple highly customized applications from one code base.
    FREE! Go There Now!


    NEW! Evaluate IBM Rational Developer for System i V7.1

    Download a free trial version of IBM Rational Developer for System i V7.1, which provides a complete development environment for traditional i5/OS application development. IBM Rational Developer for System i is a new eclipse-based workstation offering for i5/OS application development that provides a comprehensive Integrated Development Environment for edit/compile/debug of traditional RPG/COBOL/C/C++ i5/OS applications.
    FREE! Go There Now!


    NEW! Hello World: WebSphere Service Registry and Repository

    Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services.
    FREE! Go There Now!


    NEW! Improve your build process with IBM Rational Build Forge, Part 1: Create a continuous build and integration environment

    Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code.
    FREE! Go There Now!


    NEW! Rational Talks to You: Grady Booch on Architecture

    Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered!
    FREE! Go There Now!


    NEW! Rational Testing eKits

    Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing.
    FREE! Go There Now!


    NEW! Try the IBM SOA Sandbox for People

    Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity.
    FREE! Go There Now!


    NEW! Using Rational Business Developer to enhance your developer productivity

    Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components.
    FREE! Go There Now!


    NEW! Webcast: Calling All Testers! Find Application Vulnerabilities Early in the Development Process Where they are Easier to Fix and Less Risky to your Business

    In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information.
    FREE! Go There Now!


    NEW! Whitepaper: Delivering SOA solutions: service lifecycle management

    The unprecedented scope of a service-oriented architecture (SOA) initiative brings to the forefront a number of management and governance issues that were sidestepped in the past. The key to a successful SOA implementation is managing and governing activities throughout the entire SOA delivery lifecycle by ensuring that services conform to the needs of all of the business’s stakeholders. Learn how service lifecycle management allows the business to ensure that the process by which services are defined, created, tested, deployed, optimized and retired is manageable, repeatable and auditable.
    FREE! Go There Now!



    All FREE IBM® developerWorks Tools!

    DATABASE CODE ARTICLES

    - Examples and Tools for Database Design
    - Relationships, Entities and Database Design
    - Modeling and Designing Databases
    - Data extract to Excel
    - Oracle database class 0.76
    - The opposite of mysql_fetch_assoc
    - On line Thermal Transmitance Calculation
    - pjjTextBase
    - PHP Object Generator
    - FastMySQL
    - RC4PHP
    - SQL function with integrated sprintf()
    - DB Interaction Classes v1.1
    - deeMySQLParser
    - CSV to SQL convertor





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 6 Hosted by Hostway
    Stay green...Green IT