Click here to Skip to main content
Click here to Skip to main content

Accessing: PostgreSql data base using libpq - C Library.

, 18 May 2009
Rate this:
Please Sign up or sign in to vote.
A set of class to access and manipule PostgreSql data base using libpq - C Library.

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.

Acknowledgements

Bibliography

PostgreSQL

Web Site

http://www.postgresql.org/

http://www.postgresql.org/docs/

C++ Books (list)

http://www.amazon.com/C%2B%2B-Books/lm/R1FWITBSD4E82F/ref=cm_lm_byauthor_title_full/103-7510116-1265448

The Web Links listed here may not be valid in the future.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Renato Tegon Forti
Systems Engineer
Brazil Brazil
No Biography provided

Comments and Discussions

 
GeneralJust use libpqxx PinmemberSternocera27-Sep-09 23:44 
GeneralRe: Just use libpqxx PinmemberRenato Tegon Forti29-Sep-09 12:51 
GeneralMy vote of 1 PinmemberSternocera27-Sep-09 23:42 
You'd have to be insane to use this rather than libpqxx
GeneralCan't build Pinmemberdavorin_zg10-Apr-09 7:37 
GeneralRe: Can't build PinmemberRenato Tegon Forti10-Apr-09 11:31 
QuestionRe: Can't build PinmemberMr_Dark18-May-09 3:10 
AnswerRe: Can't build PinmemberRenato Tegon Forti18-May-09 7:06 
GeneralRe: Can't build PinmemberMr_Dark18-May-09 21:41 
GeneralRe: Can't build PinmemberRenato Tegon Forti19-May-09 4:20 
GeneralRe: Can't build [modified] PinmemberJack_Archer9-Jun-09 9:11 
GeneralRe: Can't build PinmemberJack_Archer9-Jun-09 10:04 
GeneralRe: Can't build PinmemberMatthew Carrington20-Aug-09 7:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 18 May 2009
Article Copyright 2009 by Renato Tegon Forti
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid