In this tutorial, i’ll demonstrate an example on how to connect to PostgreSQL database using gcc in linux. Also, i’ll demonstrate some basic SQL execution in the C++ example so that it give better understading to you how all of these are working. If you are looking a tutorial for windows platform, you could visit the following link which demonstrate the tutorial for windows platform. C++ PostgreSQL Example in Windows To begin this tutorial, let’s make sure that you have the PostgreSQL install into your linux box. I’m using Ubuntu linux in this demo. Use the following command to download & intstall PostgreSQL if it’s not exists in your linux box.
$ sudo apt-get install postgresql
Let’s turn on the psql command console with the following command:
$ sudo -u postgres psql template1
Let’s create a password for this by using the following command in psql console:
# \password postgres
You will need to key in you password now. I use password “test123″ in this demo. Let’s create the database now by using the following command in psql console. Our database name in this demo called “testdb”.
# create database testdb;
At this point, we’ve the PostgreSQL database created and we can now writing some C++ code to establish connection to this database. We will start the coding part in our cpp file now. I called the cpp file – “PSQLTest.cpp”. Make sure that you include the following header accordingly. libpq-fe.h must be included.
1
2
3
4
#include <iostream>
#include "libpq-fe.h"

using namespace std;
Now we start our very first functon named CloseConn(). This function will make sure that we close the database connection accordingly.
1
2
3
4
5
6
/* Close connection to database */
void CloseConn(PGconn *conn)
{
  PQfinish(conn);
  getchar();
}
Next, we create a function named ConnectDB(). This function will establish a connection to PostgreSQL server. Please be sure that you provide the correct parameters in PostgreSQL(). Prior to this example, i have setup a database called testdb with user password of test123 during the installation. You might need to modify it accordingly in order to make sure the compilation success.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/* Establish connection to database */
PGconn *ConnectDB()
{
  PGconn *conn = NULL;

  // Make a connection to the database
  conn = PQconnectdb("user=postgres password=test123 dbname=testdb hostaddr=127.0.0.1 port=5432");

  // Check to see that the backend connection was successfully made
  if (PQstatus(conn) != CONNECTION_OK)
  {
    cout << "Connection to database failed.\n";
    CloseConn(conn);
  }

  cout << "Connection to database - OK\n";

  return conn;
}
Next, we create a function named CreateEmployeeTable(). This function will create an employee table in our testdb database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/* Create employee table */
void CreateEmployeeTable(PGconn *conn)
{
  // Execute with sql statement
  PGresult *res = PQexec(conn, "CREATE TABLE employee (Fname char(30), Lname char(30))");
   
  if (PQresultStatus(res) != PGRES_COMMAND_OK)
  {
    cout << "Create employee table failed\n";
    PQclear(res);
    CloseConn(conn);
  }

  cout << "Create employee table - OK\n";

  // Clear result
  PQclear(res);
}
Next, we create a function named InsertEmployeeRec(). This function will take 2 parameters, fname and lname in char pointer type, to form a SQL statement. It then will be executed in order to store the record into the employee table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/* Append SQL statement and insert record into employee table */
void InsertEmployeeRec(PGconn *conn, const char * fname, const char * lname)
{
  // Append the SQL statment
  std::string sSQL;
  sSQL.append("INSERT INTO employee VALUES ('");
  sSQL.append(fname);
  sSQL.append("', '");
  sSQL.append(lname);
  sSQL.append("')");
 
  // Execute with sql statement
  PGresult *res = PQexec(conn, sSQL.c_str());

  if (PQresultStatus(res) != PGRES_COMMAND_OK)
  {
    cout << "Insert employee record failed\n";
    PQclear(res);
    CloseConn(conn);
  }

  cout << "Insert employee record - OK\n";

  // Clear result
  PQclear(res);
}
Next, we create a function named FetchEmployeeRec(). This function will fetch all the record in employee table and display it on the console windows.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
/* Fetch employee record and display it on screen */
void FetchEmployeeRec(PGconn *conn)
{
  // Will hold the number of field in employee table
  int nFields;

  // Start a transaction block
  PGresult *res  = PQexec(conn, "BEGIN");

  if (PQresultStatus(res) != PGRES_COMMAND_OK)
  {
    cout << "BEGIN command failed\n";
    PQclear(res);
    CloseConn(conn);
  }

  // Clear result
  PQclear(res);

  // Fetch rows from employee table
  res = PQexec(conn, "DECLARE emprec CURSOR FOR select * from employee");
  if (PQresultStatus(res) != PGRES_COMMAND_OK)
  {
    cout << "DECLARE CURSOR failed\n";
    PQclear(res);
    CloseConn(conn);
  }

  // Clear result
  PQclear(res);

  res = PQexec(conn, "FETCH ALL in emprec");

  if (PQresultStatus(res) != PGRES_TUPLES_OK)
  {
    cout << "FETCH ALL failed\n";
    PQclear(res);
    CloseConn(conn);
  }

  // Get the field name
  nFields = PQnfields(res);

  // Prepare the header with employee table field name
  cout << "\nFetch employee record:";
  cout << "\n********************************************************************\n";
    for (int i = 0; i < nFields; i++)
        printf("%-30s", PQfname(res, i));
    cout << "\n********************************************************************\n";

    // Next, print out the employee record for each row
    for (int i = 0; i < PQntuples(res); i++)
    {
        for (int j = 0; j < nFields; j++)
            printf("%-30s", PQgetvalue(res, i, j));
        printf("\n");
    }
 
    PQclear(res);

    // Close the emprec
    res = PQexec(conn, "CLOSE emprec");
    PQclear(res);

    // End the transaction
    res = PQexec(conn, "END");

  // Clear result
    PQclear(res);
}
Next, we create a function named RemoveAllEmployeeRec(). This function will remove all record in employee table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/* Erase all record in employee table */
void RemoveAllEmployeeRec(PGconn *conn)
{
  // Execute with sql statement
  PGresult *res = PQexec(conn, "DELETE FROM employee");

  if (PQresultStatus(res) != PGRES_COMMAND_OK)
  {
    cout << "Delete employees record failed.\n";
    PQclear(res);
    CloseConn(conn);
  }

  cout << "\nDelete employees record - OK\n";

  // Clear result
  PQclear(res);
}
Next, we create a function named DropEmployeeTable(). This function will drop or remove the employee from the testdb database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/* Drop employee table from the database*/
void DropEmployeeTable(PGconn *conn)
{
  // Execute with sql statement
  PGresult *res = PQexec(conn, "DROP TABLE employee");

  if (PQresultStatus(res) != PGRES_COMMAND_OK)
  {
    cout << "Drop employee table failed.\n";
    PQclear(res);
    CloseConn(conn);
  }

  cout << "Drop employee table - OK\n";

  // Clear result
  PQclear(res);
}
Finally, we update the main entry point function so that it call all the functions that we have created to demonstrate what we intend to show in this example.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
int main()
{
  PGconn *conn = NULL;
 
  conn = ConnectDB();
 
  if (conn != NULL)
  {
    CreateEmployeeTable(conn);
    InsertEmployeeRec(conn, "Mario", "Hewardt");
    InsertEmployeeRec(conn, "Daniel", "Pravat");
    FetchEmployeeRec(conn);

    cout << "\nPress ENTER to remove all records & table.....\n";
    getchar();
   
    RemoveAllEmployeeRec(conn);
   
    DropEmployeeTable(conn);
   
    CloseConn(conn);
  }

  return 0;
}
Try to compile and run the application now. You should see the following screenshot. REMEMBER, in order to make sure that the compilation can be done successfully, you must tell the gcc compiler where to find postgres include file. Also, you must include postgres library into your c++ project as well. All this can be done by supplying the following argument in the gcc command. If you are new to C++ compilation in Linux, please visit to my previous post which should guide you on how to compile C++ in Linux. Compiling C++ code by using g++ in Ubuntu
$ g++ PSQLTest.cpp -I /usr/include/postgresql/include -l pq -o PSQLTest
$ ./PSQLTest
If you try to break the C++ program before it remove and drop the employee table. Enter to psql command console and you should be able to read you table data as the following screenshot. Download sample source code: PSQLTest.zip