PstgreSQL database connectivity with C++

 

Libpqxx library is required for database connectivity with C++.

C/C++ Interface APIs

Some of the important interface routines required to work with PostgreSQL database along with C++ code are explained below:

  1. pqxx::connection C( const std::string & dbstring ): This is a typedef which is used to connect to the database. Here, dbstring provides required parameters to connect to the database, for example dbname=testdb user=postgres password=hello hostaddr=127.0.0.1 port=5432.If the connection is setup successfully then it creates C with connection object which provides various useful functions public function.
  2. C.is_open(): The method is_open() is a public method of connection object and returns a boolean value. If the connection is active, then this method returns true otherwise, it returns false.
  3. C.disconnect(): This method is used to disconnect an opened database connection.
  4. pqxx::work W(C): This is a typedef which is used to create a transactional object using connection C, which ultimately will be used to execute SQL statements in transactional mode. If transaction object gets created successfully, then it is assigned to variable W which will be used to access public methods related to a transactional object.
  5. W.exec(const std::string & sql): This public method from transactional object is used to execute SQL statement.
  6. W.commit(): This is used to commit the transaction.
  7. W.abort(): This is used to rollback the transaction.
  8. pqxx::nontransaction N(C): This is a typedef which is used to create a non-transactional object using connection C, which ultimately will be used to execute SQL statements in non-transactional mode.If transaction object gets created successfully, then it is assigned to variable N which will be used to access public methods related to a non-transactional object.
  9. N.exec(const std::string & sql): This public method from a non-transactional object is used to execute SQL statement and returns a result object which is actually an interator holding all the returned records.

Create Database in PostgreSQL:

sudo -u postgres -i
createuser $USER # answer yes for superuser (although this isn't strictly) necessary)
createdb -E UTF8 -O $USER simran # simran is a name of database
exit

Connectivity to Database:
Here, I’m sharing a simple program of database connectivity in C++ on local machine at port 5432. Here, I used backslash \ for line continuation.

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[])
{
   try{
      connection C("dbname=simran user=simran password=cohondob \
      hostaddr=127.0.0.1 port=5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      C.disconnect ();
   }catch (const std::exception &e){
      cerr << e.what() << std::endl;
      return 1;
   }
}

Now, let’s compile and run above program to connect to our database simran(or any you created), which is already available in your schema and can be accessed using user postgres and password sim. You can use user ID and password based on your database setting. Remember to keep the -lpqxx and -lpq in the given order! Otherwise, the linker will complain bitterly about missing functions with names starting with “PQ.”

$g++ check.cpp -lpqxx -lpq
$./a.out
Opened database successfully: simran

Similarly, you can create table, insert, delete or update the values and more. You can practice some basic programs, I shared on my Github account. To check what’s happening in the database, you may use the following:

sudo -u postgres psql
\c simran
# You may check everything you want, Refere to the following examples.
\q
exit

Check Examples:
mysql: SHOW TABLES
postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

mysql: SHOW DATABASES
postgresql: \l
postgresql: SELECT datname FROM pg_database;

mysql: SHOW COLUMNS
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

mysql: DESCRIBE TABLE
postgresql: \d+ table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

I hope, you get the initial idea about working with PostgreSQL Database.

Advertisements

Published by

kaurdavinder

I am the one who discover myself daily through reading, writing, interacting with people and expressing my thoughts to inspire them if I would be able to bring a little change to the society through my work. I write blogs, participate in various social activities and want to be a Professional Speaker. Currently, I am pursuing my post graduation in Computer Applications from Guru Nanak Dev Engineering College, Ludhiana and I’m very much active in many technical & social communities.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s