Accessing: PostgreSql data base using libpq - C Library.

Download psql_sdba - 4.73 KB

Download vc2008_psql.zip - 9.58 KB

Audience

This article was written for beginners, developers that do not know how to access PostgreSql data base using libpq - C API encapsulated in set of C++ class´s.

1. Introduction

This article is continuation of my first article available at: sdba.aspx

But in it I will provide PostgreSql class.

PostgreSQL is a powerful relational database, it has more than 15 years of active development, and then it is very mature database system.

We will encapsulate the provided libpq - C API in a set of C++ classes.

Libpq is the C application programmer's interface to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries.

2. Get PostgreSQL

You can use this URL to get the last PostgreSQL binary/source:

http://www.postgresql.org/download/

PostgreSQL are available to many platforms, when I wrote this article the version 8 was available in pre-built binary to:

And others…

To updated list and direct download, please go to: http://www.postgresql.org/download/

5. C++ interface.

The interface set is composed by 5 classes as follow:

Physical file

Class

1

Exception.h

DataBaseError

2

DataBase.h

DataBase

3

ResultSet.h

ResultSet

4

PostgreSql.h

PostgreSql

Attention: The first 3 classes are the same of my first article, then to see implementation details, please refer to: sdba.aspx

5.1 PostgreSql

class PostgreSql
/// PostgreSql data base class
/// <a href="%22http://www.postgresql.org/docs/8.3/interactive/libpq.html%22">http://www.postgresql.org/docs/8.3/interactive/libpq.html</a>
{
   friend class DataBase<PostgreSql>;
 
   public: 
 

   PostgreSql()
      : _hasResult(false)
   {
   } 
 
   virtual ~PostgreSql()
   {
   }
  

   void connect(const std::string& server, const std::string& user, const std::string& password, const std::string& database)
   { 
      _connectionHandlerPtr = PQsetdbLogin(
      server.c_str(),
      NULL,
      NULL,
      NULL,
      database.c_str(),
      user.c_str(),
      password.c_str()
      );
 
      // Check to see that the backend connection was successfully made
      if (PQstatus(_connectionHandlerPtr) != CONNECTION_OK)
      {
         std::string msg("Failed to connect to database: Error: " + std::string(PQerrorMessage(_connectionHandlerPtr)));
         PQfinish(_connectionHandlerPtr);
         throw DataBaseError(msg);
      }
    }
 
    void execute(const std::string& sql)
    {
        std::cout << sql << std::endl;
 
        if(_hasResult) 
           PQclear(_resultPtr);
 
        _resultPtr = PQexec(_connectionHandlerPtr, sql.c_str());
        if(_resultPtr == NULL)
        {
           throw DataBaseError("Failed to execute sql: Error: " + std::string(PQerrorMessage(_connectionHandlerPtr)));
        }
 
        _status = PQresultStatus(_resultPtr);
 
        if ((_status == PGRES_EMPTY_QUERY) || (_status == PGRES_BAD_RESPONSE) || (_status == PGRES_FATAL_ERROR))
        {
            PQclear(_resultPtr);
            _hasResult = false;
            throw DataBaseError("Failed to execute sql: Error: " 
                  + std::string(PQresStatus(_status)) 
                  + " : " + std::string(PQerrorMessage(_connectionHandlerPtr)));
        }
 
        (_status != PGRES_TUPLES_OK) ? PQclear(_resultPtr) : _hasResult = true;
     }
 
     void populate(ResultSet& rs)
     {
         if(_status != PGRES_TUPLES_OK) 
            throw DataBaseError("This command don't support results");
 
         if(_hasResult == false)
            throw DataBaseError("Any results available");
 
         unsigned int num_tuples = PQntuples(_resultPtr);
         unsigned int num_fields = PQnfields(_resultPtr); 
 
         for(int i = 0; i < num_tuples; ++i)
         {
            std::vector<std::string> myRow;
 
            for(int j = 0; j < num_fields; ++j)
            {
               myRow.push_back(PQgetvalue(_resultPtr, i, j));
            }      
           
            rs.addRow(myRow);
         }
 

         PQclear(_resultPtr);
         _hasResult = false    ;
   }
 
protected:
 
   void close(void)
   /// close the connection to the database and cleanup
   {
       PQfinish(_connectionHandlerPtr);
   }
 

private:
 
   ExecStatusType _status;
   bool _hasResult;
   PGresult* _resultPtr;
   PGconn* _connectionHandlerPtr;
 
}; // PostgreSql

This class is PostgreSql implementation, in it that we use C API to manipulate/access data.

I will start be: PGconn* _connectionHandlerPtr; this is handle to database connection and it is used for almost all PostgreSQL functions.

In void connect(...) we use these API functions:

PQsetdbLogin(...)

PQstatus(...)

PQfinish(...)

http://www.postgresql.org/docs/8.3/interactive/libpq-connect.html

Note that all of these functions use: _connectionHandlerPtr;

In execute(..) we use these API functions:

PQclear(...)

Pqexec(...)

PqresultStatus(...)

http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html

In populate(...) that use these API functions:

PQntuples(...)

PQnfields(...)

PQgetvalue(...)

http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html

Finally we have close() that use:

PQfinish(...)

http://www.postgresql.org/docs/8.3/interactive/libpq-connect.html

That closes the connection to the server. Also frees memory used by the PGconn object.

Well, this is all, we use 10 function of PostgreSQL C API to build this set of class that provide simple access/manipulation to PostgreSQL data base.

6. Using interface.

Here I will show how use our set of classes, see:

int main(int argc, char** argv)
{
   try
   { 
      ResultSet rs1, rs2;
      std::vector<std::string> row;
 
      DataBase<PostgreSql /* or MySql */> dataBase("205.178.146.71", "dot", "Dot.Fidelity1", "dot");
 

      // table one
      // 
      dataBase << "DROP TABLE if exists tblTest1";
      dataBase << "CREATE TABLE if not exists tblTest1(test char(15) NOT NULL, testInt INT NULL, Constraint PK Primary Key(test))";
      dataBase << "DELETE FROM tblTest1";
      dataBase << "INSERT INTO tblTest1(test, testInt) VALUES('00', 1)" ;
      dataBase << "INSERT INTO tblTest1(test) VALUES('01')" ;
      dataBase << "INSERT INTO tblTest1(test) VALUES('02')" ;
      dataBase << "INSERT INTO tblTest1(test) VALUES('03')" ;
 

      // table two
      // 
      dataBase << "DROP TABLE tblTest2";
      dataBase << "CREATE TABLE if not exists tblTest2(test char(15) NOT NULL, dt DATE NULL, Constraint PK Primary Key(test))";
      dataBase << "DELETE FROM tblTest2";
      dataBase << "INSERT INTO tblTest2(test, dt) VALUES('01', '1979/11/14')" ;
      dataBase << "INSERT INTO tblTest2(test) VALUES('02')" ;
      dataBase << "INSERT INTO tblTest2(test) VALUES('03')" ;
      dataBase << "INSERT INTO tblTest2(test) VALUES('04')" ;
      dataBase << "SELECT * FROM tblTest1, tblTest2 WHERE tblTest1.test = tblTest2.test", rs1;
      dataBase << "SELECT * FROM tblTest2 WHERE test = '01'", rs2;
 
      std::string value;
 
      while(rs1.fetch(0, value))
      {
         std::cout << value << std::endl;
      }
 
      while(rs2.fetch(1, value))
      {
         std::cout << value << std::endl;
      }
 
      while(rs1.fetch(row))
      {
         for (size_t i = 0; i < row.size(); i++)
         {
            std::cout << row[i] << " | ";
         }
      }
 
      while(rs2.fetch(row))
      {
         for (size_t i = 0; i < row.size(); i++)
        {
           std::cout << row[i] << " | ";
        }
     }
 
     std::cout << rs1.get(0)[0] << " | " << rs1.get(0)[1] << std::endl;
     std::cout << rs2.get(0, 1) << std::endl;
   }
   catch (const DataBaseError& e)
   {
      std::cout << e.what() << std::endl;
   }
 
   return 0;
 
}

7. Conclusion.

Today PostgreSQL is one option for a powerful open source relational data base, it provide fast performance, high reliability, ease of use, cost savings and many access API to many languages is wide available and easy to use.