Program/C & C++

C++ PostgreSQL Example

너구리V 2013. 6. 10. 16:36

출처 : http://www.askyb.com/cpp/c-postgresql-example/

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

반응형
저작자표시 비영리 변경금지

'Program > C & C++' 카테고리의 다른 글

[CMake] 초심자를 위한 예제  (0) 2013.06.10
CMake 정의/개념  (0) 2013.06.10
[boost] boost 동기화 객체 boost::shared_mutex  (0) 2013.03.28
[boost] boost::asio::deadline_timer 를 이용한 timeout 설정  (0) 2013.03.28
boost::asio::ip::tcp::socket 에서 IP address 추출하기  (0) 2013.03.26

'Program/C & C++'의 다른글

  • 현재글C++ PostgreSQL Example

관련글

티스토리툴바