Click here to Skip to main content
15,860,859 members
Articles / Programming Languages / C

Accessing: PostgreSql data base using libpq - C Library.

Rate me:
Please Sign up or sign in to vote.
4.65/5 (12 votes)
18 May 2009CPOL2 min read 67.9K   2K   26   12
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)


Written By
Systems Engineer
Brazil Brazil
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralJust use libpqxx Pin
Sternocera27-Sep-09 23:44
Sternocera27-Sep-09 23:44 
GeneralRe: Just use libpqxx Pin
Renato Tegon Forti29-Sep-09 12:51
Renato Tegon Forti29-Sep-09 12:51 
GeneralMy vote of 1 Pin
Sternocera27-Sep-09 23:42
Sternocera27-Sep-09 23:42 
GeneralCan't build Pin
davorin_zg10-Apr-09 7:37
davorin_zg10-Apr-09 7:37 
GeneralRe: Can't build Pin
Renato Tegon Forti10-Apr-09 11:31
Renato Tegon Forti10-Apr-09 11:31 
QuestionRe: Can't build Pin
Mr_Dark18-May-09 3:10
Mr_Dark18-May-09 3:10 
AnswerRe: Can't build Pin
Renato Tegon Forti18-May-09 7:06
Renato Tegon Forti18-May-09 7:06 
GeneralRe: Can't build Pin
Mr_Dark18-May-09 21:41
Mr_Dark18-May-09 21:41 
Thanks it's working now. I had to copy 8 DLL files from the PostgreSQL bin folder to my debug folder tho.
GeneralRe: Can't build Pin
Renato Tegon Forti19-May-09 4:20
Renato Tegon Forti19-May-09 4:20 
GeneralRe: Can't build [modified] Pin
Jack_Archer9-Jun-09 9:11
Jack_Archer9-Jun-09 9:11 
GeneralRe: Can't build Pin
Jack_Archer9-Jun-09 10:04
Jack_Archer9-Jun-09 10:04 
GeneralRe: Can't build Pin
Matthew Carrington20-Aug-09 7:23
Matthew Carrington20-Aug-09 7:23 

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

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