本教程讲解C/C++连接PostgreSQL,使用libpqxx库,这是官方的C++客户端API用于连接PostgreSQL。libpqxx源代码在BSD许可下,可以免费下载,传递给他人,改变它或出售,它包括在你自己的代码,并分享你的代码更改。
安装
libpqxx最新版本的可供下载链接下载libpqxx。所以下载的最新版本,并遵循以下步骤:Download 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
可以start/restart Postgres的服务器的情况下,它没有运行使用下面的命令:
[root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]
C/C++ APIs
以下是重要接口例程可满足工作需求与PostgreSQL数据库的C/C + +程序。如果正在寻找一个更复杂的应用程序,那么可以寻找到libpqxx官方文档,或者可以使用商用的API。
S.N. | API & 描述 |
---|---|
1 |
pqxx::connection C( const std::string & dbstring ) 这是将用于连接到数据库一个类型定义。这里dbstring提供所需要的参数,例如连接到数据库 dbname=testdb user=postgres password=pass123 hostaddr=127.0.0.1 port=5432. 如果连接设置成功,那么它创建C与连接对象提供各种有用的函数公共函数。 |
2 |
C.is_open() is_open()是一个连接对象的公共方法,并返回布尔值。如果连接处于活动状态,则此方法返回true,否则返回false。 |
3 |
C.disconnect() 使用此方法打开的数据库连接断开。 |
4 |
pqxx::work W( C ) 这是一个类型定义将用于创建一个事务对象,使用C连接方式,最终将被用于执行SQL语句的事务模式。 如果交易对象被创建成功,那么它被分配到变量W,这将被用来访问相关的事务性对象的公共方法。 |
5 |
W.exec(const std::string & sql) 这种从事务对象的公共方法将被用于执行SQL语句。 |
6 |
W.commit() 这种从事务对象的公共方法将用于提交事务。 |
7 |
W.abort() 这种从事务对象的公共方法将用于回滚事务。 |
8 |
pqxx::nontransaction N( C ) 这是一个类型定义,被用来创建使用C连接方式,最终将被用于在非事务性模式下执行SQL语句的非事务性对象。 如果交易对象被创建成功,那么它被分配到变量N将用于访问相关的非事务性对象的公共方法。 |
9 |
N.exec(const std::string & sql) 从非事务性对象的公共方法将被用于执行SQL语句,并返回一个结果对象,这实际上是一个的迭代器返回的记录。 |
连接到数据库
以下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; } }
现在,让我们编译和运行上面的程序,使用用户postgres和密码pass123访问连接到我们的数据库testdb。可以使用基于数据库设置用户ID和密码。在给定的顺序,记住要保持使用-lpqxx和-plq!否则,链接器将提示缺少以"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; } /* 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 表会显示下面的语句:
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; } /* 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: testdb Records created successfully
#p#副标题#e#
SELECT操作符
下面的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; } /* 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; }
上述程序编译和执行时,它会产生以下结果:
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 操作
下面的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; } /* 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; }
上述程序编译和执行时,它会产生以下结果:
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 操作
#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; }
上述程序编译和执行时,它会产生以下结果:
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