PostgreSQL - 连接C/C++

本教程将使用 libpqxx 库，该库是PostgreSQL的官方C ++客户端API。 libpqxx的源代码在BSD许可下可用，因此您可以免费下载，将其传递给他人，进行更改，出售，将其包含在自己的代码中，并与选择的任何人共享您的更改。

安装Libpqxx

可以从以下链接下载最新版本的libpqxx:下载Libpqxx 。因此,下载最新版本并按照以下步骤操作:

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 文件，并添加以下行-

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

您可以使用以下命令启动/重新启动postgres服务器，以防其未运行-

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

C/C ++接口API

以下是重要的接口例程，这些例程可以满足您从C/C ++程序使用PostgreSQL数据库的要求。如果您正在寻找更复杂的应用程序，则可以查阅libpqxx官方文档，也可以使用市售的API。

连接到数据库

以下C代码段显示了如何连接到端口5432上本地计算机上运行的现有数据库。在这里，无涯教程使用反斜杠\进行行继续。

#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 。

您可以根据数据库设置使用用户ID和密码。切记按给定顺序保留-lpqxx和-lpq!否则，链接器将对缺少以" PQ"开头的名称的功能进行抱怨。

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

创建表

以下C代码段将用于在先前创建的数据库中创建表-

#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;
      }

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

      /* 创建事务对象。 */
      work W(C);
      
      /* 执行 SQL 查询*/
      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表，并显示以下语句-

Opened database successfully: testdb
Table created successfully

插入操作

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

#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;
      }

      /*创建 SQL 语句*/
      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, 'Learnfk', 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 );";

      /* 创建事务对象。 */
      work W(C);
      
      /* 执行 SQL 查询 */
      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表中创建给定记录，并显示以下两行-

Opened database successfully: testdb
Records created successfully

选择操作

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

#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;
      }

      /* 创建 SQL 语句 */
      sql = "SELECT * from COMPANY";

      /* 创建一个非事务对象。 */
      nontransaction N(C);
      
      /* 执行 SQL 查询*/
      result R( N.exec( sql ));
      
      /* 列出所有记录 */
      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;
}

编译并执行上述给定程序时，将产生以下输出-

Opened database successfully: testdb
ID=1
Name=Paul
Age=32
Address=California
Salary=20000
ID=2
Name=Learnfk
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

更新操作

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

#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;
      }
      
      /* 创建事务对象。 */
      work W(C);
      /* 创建 SQL UPDATE 语句*/
      sql = "UPDATE COMPANY set SALARY=25000.00 where ID=1";
      /* 执行 SQL 查询 */
      W.exec( sql );
      W.commit();
      cout << "Records updated successfully" << endl;
      
      /* 创建 SQL SELECT 语句 */
      sql = "SELECT * from COMPANY";

      /* 创建一个非事务对象。*/
      nontransaction N(C);
      
      /* 执行 SQL 查询*/
      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;
}

编译并执行上述给定程序时，将产生以下输出-

Opened database successfully: testdb
Records updated successfully
ID=2
Name=Learnfk
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

删除操作

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

#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;
      }
      
      /* 创建事务对象。 */
      work W(C);
      /* 创建 SQL DELETE 语句 */
      sql = "DELETE from COMPANY where ID=2";
      /* 执行 SQL 查询 */
      W.exec( sql );
      W.commit();
      cout << "Records deleted successfully" << endl;
      
      /* 创建 SQL SELECT 语句 */
      sql = "SELECT * from COMPANY";

      /* 创建一个非事务对象。 */
      nontransaction N(C);
      
      /* 执行 SQL 查询 */
      result R( N.exec( sql ));
      
      /* 列出所有记录 */
      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;
}

编译并执行上述给定程序时，将产生以下输出-

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