Postgresql 简明教程

PostgreSQL - C/C++ Interface

本教程将使用 libpqxx 库，它是 PostgreSQL 的官方 C++ 客户端 API。libpqxx 的源代码可在 BSD 许可证下获得，因此您可以自由下载、传递给他人、修改、出售，将其包含在自己的代码中，并与您选择的人员共享您的变更。

This tutorial is going to use libpqxx library, which is the official C++ client API for PostgreSQL. The source code for libpqxx is available under the BSD license, so you are free to download it, pass it on to others, change it, sell it, include it in your own code, and share your changes with anyone you choose.

Installation

libpqxx 的最新版本可从链接 Download Libpqxx 下载。因此，下载最新版本并执行以下步骤 −

The the latest version of libpqxx is available to be downloaded from the link Download Libpqxx. So download the latest version and follow the following steps −

wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
tar xvfz libpqxx-4.0.tar.gz
cd libpqxx-4.0
./configure
make
make install

在开始使用 C/C++ PostgreSQL 界面之前，在您的 PostgreSQL 安装目录中找到 pg_hba.conf 文件并添加以下行 −

Before you start using C/C++ PostgreSQL interface, find the pg_hba.conf file in your PostgreSQL installation directory and add the following line −

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

如果您在没有使用以下命令的情况下运行/重新启动 postgres 服务器 -

You can start/restart postgres server in case it is not running using the following command −

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

C/C++ Interface APIs

以下是重要的界面例程，可以满足您使用 C/C++ 程序处理 PostgreSQL 数据库的需求。如果您正在寻找更复杂的应用程序，那么可以查看 libpqxx 官方文档，或者可以使用市售的 API。

The following are important interface routines which can sufice your requirement to work with PostgreSQL database from your C/C++ program. If you are looking for a more sophisticated application then you can look into the libpqxx official documentation, or you can use commercially available APIs.

S. No.	API & Description
1	pqxx::connection C( const std::string & dbstring ) This is a typedef which will be used to connect to the database. Here, dbstring provides required parameters to connect to the datbase, for example dbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432. If connection is setup successfully then it creates C with connection object which provides various useful function public function.
2	C.is_open() The method is_open() is a public method of connection object and returns boolean value. If 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 will be 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 transactional object.
5	W.exec(const std::string & sql) This public method from transactional object will be used to execute SQL statement.
6	W.commit() This public method from transactional object will be used to commit the transaction.
7	W.abort() This public method from transactional object will be used to rollback the transaction.
8	pqxx::nontransaction N( C ) This is a typedef which will be 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 non-transactional object.
9	N.exec(const std::string & sql) This public method from non-transactional object will be used to execute SQL statement and returns a result object which is actually an interator holding all the returned records.

Connecting To Database

以下 C 代码片段展示了如何连接到本地机器 5432 端口上运行的现有数据库。在此，我使用反斜杠 \ 换行。

The following C code segment shows how to connect to an existing database running 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 = testdb user = postgres 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;
   }
}

现在，让我们编译并运行上述程序以连接至我们的数据库 testdb ，此数据库已在您的模式中可用，并且可以使用用户 postgres 和密码 pass123 进行访问。

Now, let us compile and run the above program to connect to our database testdb, which is already available in your schema and can be accessed using user postgres and password pass123.

您可以根据数据库设置使用用户 ID 和密码。记住按给定的顺序使用 -lpqxx 和 -lpq！否则，链接器会针对以“PQ”开头的名称苦苦抱怨缺少函数。

You can use the 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 the missing functions with names starting with "PQ."

$g++ test.cpp -lpqxx -lpq
$./a.out
Opened database successfully: testdb

Create a Table

将使用以下 C 代码片段在之前创建的数据库中创建表 −

The following C code segment will be used to create a table in previously created database −

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

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres 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;
      }

      /* Create SQL statement */
      sql = "CREATE TABLE COMPANY("  \
      "ID INT PRIMARY KEY     NOT NULL," \
      "NAME           TEXT    NOT NULL," \
      "AGE            INT     NOT NULL," \
      "ADDRESS        CHAR(50)," \
      "SALARY         REAL );";

      /* Create a transactional object. */
      work W(C);

      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Table created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

编译并执行上述给定程序后，它会在 testdb 数据库中创建 COMPANY 表，并将显示以下语句 −

When the above given program is compiled and executed, it will create COMPANY table in your testdb database and will display the following statements −

Opened database successfully: testdb
Table created successfully

INSERT Operation

以下 C 代码片段显示了如何在上面示例中创建的 COMPANY 表中创建记录 −

The following C code segment shows how we can create records in our COMPANY table created in above example −

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

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres 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;
      }

      /* Create SQL statement */
      sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

      /* Create a transactional object. */
      work W(C);

      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

编译并执行上述给定程序后，它将在 COMPANY 表中创建给定记录，并将显示以下两行 −

When the above given program is compiled and executed, it will create given records in COMPANY table and will display the following two lines −

Opened database successfully: testdb
Records created successfully

SELECT Operation

以下 C 代码片段显示了如何从上面示例中创建的 COMPANY 表中获取并显示记录 −

The following C code segment shows how we can fetch and display records from our COMPANY table created in above example −

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

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres 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;
      }

      /* Create SQL statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);

      /* Execute SQL query */
      result R( N.exec( sql ));

      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

编译并执行上述给定程序后，它将产生以下结果 −

When the above given program is compiled and executed, it will produce the following result −

Opened database successfully: testdb
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 20000
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
Operation done successfully

UPDATE Operation

以下 C 代码片段显示了如何使用 UPDATE 语句更新任何记录，然后从 COMPANY 表中获取并显示更新后的记录 −

The following C code segment shows how we can use the UPDATE statement to update any record and then fetch and display updated records from our COMPANY table −

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

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres 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;
      }

      /* Create a transactional object. */
      work W(C);
      /* Create  SQL UPDATE statement */
      sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records updated successfully" << endl;

      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);

      /* Execute SQL query */
      result R( N.exec( sql ));

      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

编译并执行上述给定程序后，它将产生以下结果 −

When the above given program is compiled and executed, it will produce the following result −

Opened database successfully: testdb
Records updated successfully
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

DELETE Operation

以下 C 代码片段显示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余记录 −

The following C code segment shows how we can use the DELETE statement to delete any record and then fetch and display remaining records from our COMPANY table −

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

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres 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;
      }

      /* Create a transactional object. */
      work W(C);
      /* Create  SQL DELETE statement */
      sql = "DELETE from COMPANY where ID = 2";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records deleted successfully" << endl;

      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);

      /* Execute SQL query */
      result R( N.exec( sql ));

      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

编译并执行上述给定程序后，它将产生以下结果 −

When the above given program is compiled and executed, it will produce the following result −

Opened database successfully: testdb
Records deleted successfully
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully