Postgresql 简明教程
PostgreSQL - C/C++ Interface
本教程将使用 libpqxx 库，它是 PostgreSQL 的官方 C++ 客户端 API。libpqxx 的源代码可在 BSD 许可证下获得，因此您可以自由下载、传递给他人、修改、出售，将其包含在自己的代码中，并与您选择的人员共享您的变更。
Installation
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如果您在没有使用以下命令的情况下运行/重新启动 postgres 服务器 -
[root@host]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]C/C++ Interface APIs
以下是重要的界面例程，可以满足您使用 C/C++ 程序处理 PostgreSQL 数据库的需求。如果您正在寻找更复杂的应用程序，那么可以查看 libpqxx 官方文档，或者可以使用市售的 API。
S. No. |
API & Description |
1 |
pqxx::connection C( const std::string & dbstring ) 这是一个将用于连接到数据库的 typedef。在此，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 创建事务对象的 typedef，最终将用于在事务模式中执行 SQL 语句。如果成功创建事务对象，则将其分配给变量 W，该变量将用于访问与事务对象相关的公共方法。 |
5 |
W.exec(const std::string & sql) 事务对象中的此公共方法将用于执行 SQL 语句。 |
6 |
W.commit() 事务对象中的此公共方法将用于提交事务。 |
7 |
W.abort() 事务对象中的此公共方法将用于回滚事务。 |
8 |
pqxx::nontransaction N( C ) 这是一个 typedef，它将用于使用连接 C 创建非事务对象，最终将用于在非事务模式下执行 SQL 语句。如果成功创建事务对象，则将其分配给变量 N，该变量将用于访问与非事务对象相关的公共方法。 |
9 |
N.exec(const std::string & sql) 非事务对象中的此公共方法将用于执行 SQL 语句，并返回结果对象，该对象实际上是包含所有返回记录的迭代器。 |
Connecting To Database
以下 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: testdbCreate a Table
将使用以下 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 successfullyINSERT Operation
以下 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 successfullySELECT Operation
以下 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 successfullyUPDATE Operation
以下 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 successfullyDELETE Operation
以下 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;
}
/* 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