libpqxx - postgresql c++ library 基本教學
- 簡介
libpqxx是postgrsql 官方所推出的函式庫，基本上包裝得很好，用起來十分簡便。
libpqxx官網：http://pqxx.org/
- linux上安裝方式
請先安裝postgresql，方便起見可安裝php,phppgadmin和apache以利測試。
link:https://wiki.archlinux.org/index.php/PostgreSQL
libray link:http://pqxx.org/download/software/libpqxx/
$ wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
$ tar -zxvf libpqxx-4.0-tar.gz
$ cd libpqxx-4.0
$ ./configure
$ make
$ make install
arch linux上安裝方式，libpqxx為Extra package。
link:https://www.archlinux.org/packages/extra/i686/libpqxx/
$ sudo pacman -Sy libpqxx
安裝完成後，於postgresql安裝目錄下。
$ cp pg_hba.conf.sample pg_hba.conf
於pg_hba.conf新增
# IPv4 local connections:
host all all 127.0.0.1/32 md5
啟動與停止postgresql，基本上使用systemd的linux distro都可用以下方式來處理
$ sudo systemctl start postgresql.service
$ sudo systemctl stop postgresql.service
- 基本用法
libpqxx通常需要透過pqxx::connection來建立連線，接著以transaction物件pqxx::work執行sql操作。以下為基本範例
#include<iostream>
#include<pqxx/pqxx>
#include<string>
using namespace std;
using namespace pqxx;
int main()
{
string sql;
try{
connection C("dbname=testdb user=postgres password=pqsswd \
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 USERTEST";
/* Create a transactional object. */
work W(C);
/* Execute SQL query */
pqxx::result r = W.exec( sql );
W.commit();
const int num_rows = r.size();
for (int rownum=0; rownum < num_rows; ++rownum)
{
const pqxx::tuple row = r[rownum];
const int num_cols = row.size();
for (int colnum=0; colnum < num_cols; ++colnum)
{
const pqxx::field field = row[colnum];
std::cout << field.c_str() << '\t';
}
std::cout << std::endl;
}
for (pqxx::result::const_iterator row = r.begin();row != r.end();++row)
{
for (pqxx::tuple::const_iterator field = row->begin();field != row->end();++field)
std::cout << field->c_str() << '\t';
std::cout << std::endl;
}
cout << "table"<<endl;
for (int rownum = 0 ; rownum < num_rows ; rownum++){
cout << r[rownum]["UID"].c_str() << " " << r[rownum]["TRUE_NAME"].c_str() << " " << r[rownum]["GENDER"].c_str()
<< " "<< r[rownum]["NICK_NAME"].c_str() <<endl;
}
C.disconnect ();
}catch (const std::exception &e){
cerr << e.what() << std::endl;
return 1;
}
}
這個範例使用了三種parse資料的方法，分別是使用pqxx::tuple的方式、使用內建iterator的方式與使用類似C++ STL map的key-value方式。
編譯方式：
$ g++ -o test filename.cpp -lpqxx -lpq