postgresql c/c 接口——迹忆客-ag捕鱼王app官网

本教程将使用libpqxx库，它是 postgresql 的官方 c 客户端 api。libpqxx 的源代码在 bsd 许可下可用。

安装

最新版本的 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 
#include  
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
   try {
      connection c("dbname = jiyik_db 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;
   }
}

现在，让我们编译并运行上述程序以连接到我们的数据库 jiyik_db，该数据库已在您的架构中可用，可以使用用户postgres和密码pass123访问。

您可以根据您的数据库设置使用用户 id 和密码。请记住按照给定的顺序保留 -lpqxx 和 -lpq！否则，链接器会报缺少名称以“pq”开头的函数的错误。

$g   test.cpp -lpqxx -lpq
$./a.out
opened database successfully: jiyik_db

创建表

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

#include 
#include  
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection c("dbname = jiyik_db 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;
}

当上述给定的程序被编译和执行时，它将在您的 jiyik_db 数据库中创建 company 表并显示以下语句 -

opened database successfully: jiyik_db
table created successfully

插入操作

以下 c 代码段显示了我们如何在上面示例中创建的 company 表中创建记录 -

#include 
#include  
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection c("dbname = jiyik_db 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 表中创建给定的记录，并显示以下两行 -

opened database successfully: jiyik_db
records created successfully

选择操作

以下 c 代码段显示了我们如何从上面示例中创建的 company 表中获取和显示记录

#include 
#include  
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection c("dbname = jiyik_db 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() << endl;
         cout << "name = " << c[1].as() << endl;
         cout << "age = " << c[2].as() << endl;
         cout << "address = " << c[3].as() << endl;
         cout << "salary = " << c[4].as() << 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: jiyik_db
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

更新操作

下面的 c 代码段展示了我们如何使用 update 语句来更新任何记录，然后从我们的 company 表中获取和显示更新的记录

#include 
#include  
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection c("dbname = jiyik_db 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() << endl;
         cout << "name = " << c[1].as() << endl;
         cout << "age = " << c[2].as() << endl;
         cout << "address = " << c[3].as() << endl;
         cout << "salary = " << c[4].as() << 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: jiyik_db
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

删除操作

以下 c 代码段显示了我们如何使用 delete 语句删除任何记录，然后从我们的 company 表中获取并显示剩余的记录

#include 
#include  
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection c("dbname = jiyik_db 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() << endl;
         cout << "name = " << c[1].as() << endl;
         cout << "age = " << c[2].as() << endl;
         cout << "address = " << c[3].as() << endl;
         cout << "salary = " << c[4].as() << 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