循序渐进PostgreSQL: 学习pqxx C++ API访问

1. 简介习惯了C++来开发的人，可能更倾向于使用c++库来访问PostgreSQL。libpqxx很早以前就推出了。目前已经到了4.0版。本文就简单的介绍如何使用它，Linux平台编译相对简单，这里就介绍一下Windows平台下的使用。libpqxx的下载地址：直接上这里http://pqxx.org/development/libpqxx/wiki/DownloadPage由于libpqxx对libpq的C-API进行了很好的封装，从而可以节省大量的编码时间。2. 编译过程将libpqxx解压到一固定目录。你需要提前编译好或安装好的客户端库，通常为了方便，这里假定你已经有一个PG的安装版或者解压缩版，在c:pgsql里头。以PG9.x为例。1. 进入libpqxx源码目录，备份并修改win32common文件，将PGSQLSRC的值指向正确的地方：这里是PGSQLSRC="c:pgsql"后边还有很多目录位置需要调整，针对我们这种情况，通常都是保留安装版本的设置，如：LIBPQINC=$（PGSQLSRC）include, 而注释掉#LIBPQINC=$（PGSQLSRC）interfaceslibpq, 其它几个值都如此类推。2. 拷贝一些编译相关的头文件针对具体版本，9.0的，将configsample-headerslibpq9.0pqxx复制到include下边针对VS2008的，将configsample-headerscompilerVisualStudio2008pqxx复制到include目录下边, 如果是VS2005，以此类推。3. 编译program files->VS2008-->Visual studio tools->Visual Studio 2008 Command Prompt, 进入命令行，在此进入libpqxx的源代码根目录，执行：nmake /f win32vc-libpqxx.mak ALL,得到如下中间过程:link.exe kernel32.lib ws2_32.lib advapi32.lib /nologo /dll /machine:I386
shell32.lib secur32.lib wldap32.lib /libpath:"C:hisql-x86-2.0.1"lib libpq.lib
"ObjDllReleaseinarystring.obj" "ObjDllReleaseconnection.obj" "ObjDllRelea
seconnection_base.obj" "ObjDllReleasecursor.obj" "ObjDllReleasedbtransactio
n.obj" "ObjDllReleaseerrorhandler.obj" "ObjDllReleaseexcept.obj" "ObjDllRel
easefield.obj" "ObjDllReleaselargeobject.obj" "ObjDllRelease ontransaction.
obj" "ObjDllRelease otification.obj" "ObjDllRelease otify-listen.obj" "ObjD
llReleasepipeline.obj" "ObjDllReleaseprepared_statement.obj" "ObjDllRelease
result.obj" "ObjDllRelease obusttransaction.obj" "ObjDllReleasestatement_par
ameters.obj" "ObjDllReleasestrconv.obj" "ObjDllReleasesubtransaction.obj" "
ObjDllRelease ablereader.obj" "ObjDllRelease ablestream.obj" "ObjDllRelease
tablewriter.obj" "ObjDllRelease ransaction.obj" "ObjDllRelease ransaction_ba
se.obj" "ObjDllRelease uple.obj" "ObjDllReleaseutil.obj" "ObjDllReleaselib
pqxx.obj" /out:"liblibpqxx.dll" /implib:"liblibpqxx.lib"
Creating library liblibpqxx.lib and object liblibpqxx.exp [plain] view plaincopyprint？link.exe kernel32.lib ws2_32.lib advapi32.lib /nologo /dll /machine:I386shell32.lib secur32.lib wldap32.lib /libpath:"C:hisql-x86-2.0.1"lib libpq.lib"ObjDllReleaseinarystring.obj" "ObjDllReleaseconnection.obj" "ObjDllReleaseconnection_base.obj" "ObjDllReleasecursor.obj" "ObjDllReleasedbtransaction.obj" "ObjDllReleaseerrorhandler.obj" "ObjDllReleaseexcept.obj" "ObjDllReleasefield.obj" "ObjDllReleaselargeobject.obj" "ObjDllRelease ontransaction.obj" "ObjDllRelease otification.obj" "ObjDllRelease otify-listen.obj" "ObjDllReleasepipeline.obj" "ObjDllReleaseprepared_statement.obj" "ObjDllRelease esult.obj" "ObjDllRelease obusttransaction.obj" "ObjDllReleasestatement_parameters.obj" "ObjDllReleasestrconv.obj" "ObjDllReleasesubtransaction.obj" "ObjDllRelease ablereader.obj" "ObjDllRelease ablestream.obj" "ObjDllRelease ablewriter.obj" "ObjDllRelease ransaction.obj" "ObjDllRelease ransaction_base.obj" "ObjDllRelease uple.obj" "ObjDllReleaseutil.obj" "ObjDllReleaselibpqxx.obj" /out:"liblibpqxx.dll" /implib:"liblibpqxx.lib"Creating library liblibpqxx.lib and object liblibpqxx.explink.exe kernel32.lib ws2_32.lib advapi32.lib /nologo /dll /machine:I386shell32.lib secur32.lib wldap32.lib /libpath:"C:hisql-x86-2.0.1"lib libpq.lib"ObjDllReleaseinarystring.obj" "ObjDllReleaseconnection.obj" "ObjDllReleaseconnection_base.obj" "ObjDllReleasecursor.obj" "ObjDllReleasedbtransaction.obj" "ObjDllReleaseerrorhandler.obj" "ObjDllReleaseexcept.obj" "ObjDllReleasefield.obj" "ObjDllReleaselargeobject.obj" "ObjDllRelease ontransaction.obj" "ObjDllRelease otification.obj" "ObjDllRelease otify-listen.obj" "ObjDllReleasepipeline.obj" "ObjDllReleaseprepared_statement.obj" "ObjDllRelease esult.obj" "ObjDllRelease obusttransaction.obj" "ObjDllReleasestatement_parameters.obj" "ObjDllReleasestrconv.obj" "ObjDllReleasesubtransaction.obj" "ObjDllRelease ablereader.obj" "ObjDllRelease ablestream.obj" "ObjDllRelease ablewriter.obj" "ObjDllRelease ransaction.obj" "ObjDllRelease ransaction_base.obj" "ObjDllRelease uple.obj" "ObjDllReleaseutil.obj" "ObjDllReleaselibpqxx.obj" /out:"liblibpqxx.dll" /implib:"liblibpqxx.lib"Creating library liblibpqxx.lib and object liblibpqxx.exp 最终会在lib目录下生成：[plain] view plaincopyprint？D:Projectshisql.svn runklearninglibpqxx-4.0>dir/b/s libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpq.dllD:Projectshisql.svn runklearninglibpqxx-4.0liblibpq.libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx.dllD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx.dll.manifestD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx.expD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx.libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.dllD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.dll.manifestD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.expD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.ilkD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.pdbD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx_static.libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx_staticD.libD:Projectshisql.svn runklearninglibpqxx-4.0libmsdia80.dllD:Projectshisql.svn runklearninglibpqxx-4.0>dir/b/s libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpq.dllD:Projectshisql.svn runklearninglibpqxx-4.0liblibpq.libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx.dllD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx.dll.manifestD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx.expD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx.libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.dllD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.dll.manifestD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.expD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.ilkD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxxD.pdbD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx_static.libD:Projectshisql.svn runklearninglibpqxx-4.0liblibpqxx_staticD.libD:Projectshisql.svn runklearninglibpqxx-4.0libmsdia80.dll下边来看看最简单的示例：iihero=# create user foo password "foo1";CREATE ROLE[sql] view plaincopyprint？iihero=> create table t（id int primary key, col2 varchar（32））;注意: CREATE TABLE / PRIMARY KEY 将要为表 "t" 创建隐含索引 "t_pkey"CREATE TABLEiihero=> encodingGBKiihero=> insert into t values（1 ,"不同类型的列表"）;INSERT 0 1iihero=> select * from t;id | col2----+----------------1 | 不同类型的列表（1 行记录）iihero=> create table t（id int primary key, col2 varchar（32））;注意: CREATE TABLE / PRIMARY KEY 将要为表 "t" 创建隐含索引 "t_pkey"CREATE TABLEiihero=> encodingGBKiihero=> insert into t values（1 ,"不同类型的列表"）;INSERT 0 1iihero=> select * from t;id | col2----+----------------1 | 不同类型的列表（1 行记录）将编译出来后的include, lib目录提取到固定的目录，与你原来的postgresql的include和lib目录，可以合到一起。给开发使用。下边是一个最简单的示例：[cpp] view plaincopyprint？<PRE class=cpp name="code">#include <iostream>#include <pqxx/pqxx>int main（）{pqxx::connection conn（"dbname=iihero hostaddr=127.0.0.1 user=foo password=foo1" ）;if（conn.is_open（））{std::cout << "Connection succesful！" << std::endl;std::cout << conn.options（）<<std::endl;}else{std::cout << "Something went wrong... oops" << std::endl;}pqxx::work w（conn）;pqxx::result res = w.exec（"SELECT 1"）;w.commit（）;std::cout << res[0][0].as<int>（） << std::endl;}</PRE><BR>[cpp] view plaincopyprint？#include <iostream> #include <pqxx/pqxx> int main（） { pqxx::connection conn（"dbname=iihero hostaddr=127.0.0.1 user=foo password=foo1" ）; if（conn.is_open（）） { std::cout << "Connection succesful！" << std::endl; std::cout << conn.options（）<<std::endl; } else { std::cout << "Something went wrong... oops" << std::endl; } pqxx::work w（conn）; pqxx::result res = w.exec（"SELECT 1"）; w.commit（）; std::cout << res[0][0].as<int>（） << std::endl; } #include <iostream>#include <pqxx/pqxx>int main（）{pqxx::connection conn（"dbname=iihero hostaddr=127.0.0.1 user=foo password=foo1" ）;if（conn.is_open（））{std::cout << "Connection succesful！" << std::endl;std::cout << conn.options（）<<std::endl;}else{std::cout << "Something went wrong... oops" << std::endl;}pqxx::work w（conn）;pqxx::result res = w.exec（"SELECT 1"）;w.commit（）;std::cout << res[0][0].as<int>（） << std::endl;}需要注意的是，要将libpq.lib, libpqxx.lib, 以及头文件都设置上。如果动态库链接方式出现问题，可以尝试使用libpqxx的静态库。

Redis系列-安装部署维护篇查看到Oracle定义的隐含参数相关资讯 PostgreSQL教程 pqxx

PostgreSQL 列所有表结构的查询语（02/18/2013 08:37:22）
PostgreSQL模块——pg_stat_ （11/20/2012 19:27:31）
PostgreSQL集群方案hot standby初（07/10/2012 09:42:30）

PostgresQL中的NUlls first/last功（01/20/2013 11:55:23）
PostgreSQL 堆分配器mmgr详解（09/12/2012 09:33:05）
PostgreSQL数据库压力测试工具（07/09/2012 19:47:31）

本文评论查看全部评论（0）