In this article, I’ll show you an example on how to integrate the PostgreSQL C++ library into your C++ project solution. The PostgreSQL version that i am using for this example is PostgreSQL for Windows version 8.4.4. In case you don’t have PostgreSQL installed on your machine, you can download it at postgresql-8.4.4-1-windows.exe. You may need to provide some details first before start to download. Make sure that you have installed PostgreSQL successfully and please REMEMBER your password to login to PostgreSQL through its command console windows in later. Let’s start to create a database for this example now. Run the psql in the command windows and create a new database named “testdb” as the following screen shot. Once the database has been created, we are now ready for the coding part. Let’s create a Win32 console application now (I am using visual studio 2005 for this example). The first task we need to do right now is to include the PostgreSQL C++ interface header file and lib to our project. You can do that by right click your project solution and click properties. Note 1: You might need to change the following file path accordingly. [C/C++ -> General -> Additional Include Directories] C:\Program Files\PostgreSQL\8.4\include [Linker -> General -> Additional Library Directories] C:\Program Files\PostgreSQL\8.4\lib [Linker -> Input -> Additional Dependencies] libpq.lib Note 2:At the end of this example, you will need to copy your project output (EXE) to ..\PostgreSQL\8.4\bin. Alternately, you can copy all DLLs located at ..\PostgreSQL\8.4\bin\ to you project output folder. We will start the coding part in our cpp file now. Make sure that you include the following header accordingly. libpq-fe.h must be included.
1
2
#include <string>
#include "libpq-fe.h"
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
7
/* Close connection to database */
void CloseConn(PGconn *conn)
{
    PQfinish(conn);
  getchar();
    exit(1);
}
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)
    {
        printf("Connection to database failed");
        CloseConn(conn);
    }

  printf("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)
    {
        printf("Create employee table failed");
        PQclear(res);
        CloseConn(conn);
    }

  printf("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, char * fname, 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)
    {
        printf("Insert employee record failed");
        PQclear(res);
        CloseConn(conn);
    }

  printf("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)
    {
        printf("BEGIN command failed");
        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)
    {
        printf("DECLARE CURSOR failed");
        PQclear(res);
        CloseConn(conn);
    }

  // Clear result
    PQclear(res);

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

    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        printf("FETCH ALL failed");
        PQclear(res);
        CloseConn(conn);
    }

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

  // Prepare the header with employee table field name
  printf("\nFetch employee record:");
  printf("\n********************************************************************\n");
    for (int i = 0; i < nFields; i++)
        printf("%-30s", PQfname(res, i));
    printf("\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)
    {
        printf("Delete employees record failed.");
        PQclear(res);
        CloseConn(conn);
    }

  printf("\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)
    {
        printf("Drop employee table failed.");
        PQclear(res);
        CloseConn(conn);
    }

  printf("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
int _tmain(int argc, _TCHAR* argv[])
{
  PGconn     *conn = NULL;

  conn = ConnectDB();
  CreateEmployeeTable(conn);
  InsertEmployeeRec(conn, "Mario", "Hewardt");
  InsertEmployeeRec(conn, "Daniel", "Pravat");
  FetchEmployeeRec(conn);

  printf("\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 screen shot. At this point, if you are running the SELECT statment from the PostgreSQL command console, you will see the same data being display on your C++ win32 console windows. sample source code: c++ postgresql example