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:
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
{
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()
);
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)
{
PQfinish(_connectionHandlerPtr);
}
private:
ExecStatusType _status;
bool _hasResult;
PGresult* _resultPtr;
PGconn* _connectionHandlerPtr;
};
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 > dataBase("205.178.146.71", "dot", "Dot.Fidelity1", "dot");
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')" ;
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.