本教程將使用libpqxx庫(kù)，它是PostgreSQL的官方C ++客戶端API。 libpqxx的源代碼在BSD許可下可用，因此您可以免費(fèi)下載，將其傳遞給他人，進(jìn)行更改，出售，將其包含在自己的代碼中，并與選擇的任何人共享您的更改。
最新版本的libpqxx可以從下載libpqxx鏈接下載(Libpqxx下載)。因此，請(qǐng)下載最新版本并按照以下步驟-
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
在開(kāi)始使用C / C ++ PostgreSQL接口之前，請(qǐng)?jiān)赑ostgreSQL安裝目錄中找到 pg_hba.conf 文件，并添加以下行-
# IPv4 local connections: host all all 127.0.0.1/32 md5
如果postgres服務(wù)器沒(méi)有運(yùn)行，可以使用以下命令啟動(dòng)/重新啟動(dòng)postgres服務(wù)器-
[root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]
下面是重要的接口例程，它可以滿足您從C/C++程序中使用PostgreSQL數(shù)據(jù)庫(kù)的需求。如果您正在尋找一個(gè)更復(fù)雜的應(yīng)用程序，那么您可以查看libpqxx官方文檔，也可以使用商業(yè)上可用的api。
| S. No. | API & 描述 |
|---|---|
| 1 | pqxx::connection C( const std::string & dbstring ) 這是一個(gè)typedef，將用于連接到數(shù)據(jù)庫(kù)。這里，dbstring提供連接到數(shù)據(jù)庫(kù)所需的參數(shù),例如: dbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432. 如果連接成功建立，那么它創(chuàng)建了C與連接對(duì)象，提供各種有用的函數(shù)公共函數(shù)。 |
| 2 | C.is_open() is_open()方法是連接對(duì)象的公共方法，并返回布爾值。如果連接是活動(dòng)的，則此方法返回true，否則返回false。 |
| 3 | C.disconnect() 此方法用于斷開(kāi)已打開(kāi)的數(shù)據(jù)庫(kù)連接。 |
| 4 | pqxx::work W( C ) 這是一個(gè) typedef，用于使用連接 C 創(chuàng)建事務(wù)對(duì)象，最終用于在事務(wù)模式下執(zhí)行 SQL 語(yǔ)句。 如果事務(wù)對(duì)象創(chuàng)建成功，那么它將被分配給變量W，該變量將用于訪問(wèn)與事務(wù)對(duì)象相關(guān)的公共方法。 |
| 5 | W.exec(const std::string & sql) 這個(gè)來(lái)自事務(wù)對(duì)象的公共方法將用于執(zhí)行SQL語(yǔ)句。 |
| 6 | W.commit() 來(lái)自事務(wù)對(duì)象的這個(gè)公共方法將用于提交事務(wù)。 |
| 7 | W.abort() 事務(wù)對(duì)象的這個(gè)公共方法將用于回滾事務(wù)。 |
| 8 | pqxx::nontransaction N( C ) 這是一個(gè)typedef，它將用于使用連接C創(chuàng)建非事務(wù)性對(duì)象，最終將用于在非事務(wù)模式下執(zhí)行SQL語(yǔ)句。 如果事務(wù)對(duì)象創(chuàng)建成功，那么它將被分配給變量N，該變量將用于訪問(wèn)與非事務(wù)對(duì)象相關(guān)的公共方法。 |
| 9 | N.exec(const std::string & sql) 這個(gè)來(lái)自非事務(wù)對(duì)象的公共方法將用于執(zhí)行SQL語(yǔ)句并返回一個(gè)result對(duì)象，它實(shí)際上是一個(gè)保存所有返回記錄的interator。 |
下面的C代碼段顯示了如何連接到在端口5432的本地計(jì)算機(jī)上運(yùn)行的現(xiàn)有數(shù)據(jù)庫(kù)。這里，我用反斜杠\表示行的延續(xù)。
#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;
}
}現(xiàn)在，讓我們編譯并運(yùn)行上面的程序，以連接到我們的數(shù)據(jù)庫(kù) testdb，該數(shù)據(jù)庫(kù)已經(jīng)在您的模式中可用，可以使用用戶 postgres 和密碼 pass123訪問(wèn)。
您可以根據(jù)數(shù)據(jù)庫(kù)設(shè)置使用用戶ID和密碼。記住保持-lpqxx和-lpq的給定順序!否則，連接器將會(huì)認(rèn)為缺少名稱而以“PQ”開(kāi)頭的函數(shù)。
$g++ test.cpp -lpqxx -lpq $./a.out Opened database successfully: testdb
以下C代碼段將用于在先前創(chuàng)建的數(shù)據(jù)庫(kù)中創(chuàng)建表-
#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;
}
/* 創(chuàng)建SQL語(yǔ)句 */
sql = "CREATE TABLE COMPANY(" \
"ID INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"ADDRESS CHAR(50)," \
"SALARY REAL );";
/* 創(chuàng)建事務(wù)對(duì)象. */
work W(C);
/* 執(zhí)行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;
}當(dāng)編譯并執(zhí)行上述給定程序時(shí)，它將在testdb數(shù)據(jù)庫(kù)中創(chuàng)建COMPANY表，并顯示以下語(yǔ)句-
Opened database successfully: testdb Table created successfully
下面的C代碼段顯示了如何在上面示例中創(chuàng)建的company表中創(chuàng)建記錄-
#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;
}
/* 創(chuàng)建 SQL 語(yǔ)句 */
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 );";
/* 創(chuàng)建事務(wù)對(duì)象. */
work W(C);
/* 執(zhí)行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;
}當(dāng)編譯并執(zhí)行上述給定程序時(shí)，它將在COMPANY表中創(chuàng)建給定的記錄，并顯示以下兩行-
Opened database successfully: testdb Records created successfully
下面的C代碼段展示了我們?nèi)绾螐纳厦媸纠袆?chuàng)建的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;
}
/* 創(chuàng)建 SQL 語(yǔ)句 */
sql = "SELECT * from COMPANY";
/* 創(chuàng)建非事務(wù)對(duì)象. */
nontransaction N(C);
/* 執(zhí)行 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;
}當(dāng)編譯并執(zhí)行上述給定程序時(shí)，它將產(chǎn)生以下結(jié)果-
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
下面的 c 代碼段顯示了如何使用 UPDATE 語(yǔ)句更新任何記錄，然后從 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;
}
/* 創(chuàng)建事務(wù)對(duì)象. */
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;
/* 創(chuàng)建 SQL SELECT 語(yǔ)句 */
sql = "SELECT * from COMPANY";
/* 創(chuàng)建一個(gè)非事務(wù)對(duì)象. */
nontransaction N(C);
/* 執(zhí)行 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;
}當(dāng)上述給定的程序被編譯和執(zhí)行時(shí)，它將產(chǎn)生以下結(jié)果-
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
下面的 C 代碼段顯示了如何使用 DELETE 語(yǔ)句刪除任何記錄，然后從 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;
}
/* 創(chuàng)建事務(wù)對(duì)象. */
work W(C);
/* 創(chuàng)建 SQL DELETE 語(yǔ)句 */
sql = "DELETE from COMPANY where ID = 2";
/* 執(zhí)行 SQL 查詢 */
W.exec( sql );
W.commit();
cout << "Records deleted successfully" << endl;
/* Create SQL SELECT statement */
sql = "SELECT * from COMPANY";
/* 創(chuàng)建非事務(wù)對(duì)象. */
nontransaction N(C);
/* 執(zhí)行 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;
}當(dāng)編譯并執(zhí)行上述給定程序時(shí)，它將產(chǎn)生以下結(jié)果-
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