Gattaca-ngin: libpqxx - postgresql c++ library 基本教學

libpqxx是postgrsql 官方所推出的函式庫，基本上包裝得很好，用起來十分簡便。
libpqxx官網：http://pqxx.org/

請先安裝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

view raw gistfile1.txt hosted with ❤ by GitHub

arch linux上安裝方式，libpqxx為Extra package。<br /> link:https://www.archlinux.org/packages/extra/i686/libpqxx/

sudo pacman -Sy libpqxx

view raw gistfile1.txt hosted with ❤ by GitHub

安裝完成後，於postgresql安裝目錄下。

cp pg_hba.conf.sample pg_hba.conf

view raw gistfile1.txt hosted with ❤ by GitHub

於pg_hba.conf新增

	# IPv4 local connections:
	host all all 127.0.0.1/32 md5

view raw gistfile1.txt hosted with ❤ by GitHub

啟動與停止postgresql，基本上使用systemd的linux distro都可用以下方式來處理

	sudo systemctl start postgresql.service
	sudo systemctl stop postgresql.service

view raw gistfile1.txt hosted with ❤ by GitHub

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

view raw bash hosted with ❤ by GitHub

Gattaca-ngin