Step-by-Step PostgreSQL: Learn about pqxx C ++ API access

1. Introduction

People who are used to C ++ development may prefer to use the C ++ library to access pg. Libpqxx was launched a long time ago. It is now in version 4.0. This article briefly introduces how to use it. compiling on the Linux platform is relatively simple. Here we will introduce how to use it on the Windows platform.

Libpqxx: directly on the http://pqxx.org/development/libpqxx/wiki/DownloadPage

Because libpqxx has encapsulated libpq C-API well, it can save a lot of coding time.

For help documentation, see the libpqxx manual http://pqxx.org/devprojects/libpqxx/doc/4.0/html/Tutorial.

Libpqxx class library help: http://pqxx.org/devprojects/libpqxx/doc/4.0/html/Reference/

2. compilation process

Extract libpqxx to a fixed directory. You need to compile or install the client library in advance. Generally, for convenience, assume that you already have an install or decompress version of PG, which is in c: \ pgsql. Take pg9.x as an example.

1. Go to the libpqxx source code directory, back up and modify the Win32 \ common file, and point the pgsqlsrc value to the correct place:

Here is pgsqlsrc = "C: \ pgsql"

There are still many directory locations to be adjusted. In this case, we usually keep the installation version settings, such as libpqinc =$ (pgsqlsrc) \ include, comment out # libpqinc =$ (pgsqlsrc) \ Interfaces \ libpq, and so on.

2. copy some compilation-related header files.

For specific versions, copy config \ sample-headers \ libpq \ 9.0 \ pqxx To the bottom of include

For vs2008, copy config \ sample-headers \ compiler \ visualstudio2008 \ pqxx to the include directory. For vs2005, and so on.

3. Compile

Program Files-> vs2008 --> Visual Studio Tools-> Visual Studio 2008 command prompt, enter the command line, enter the source code root directory of libpqxx, and execute:

Nmake/F Win32 \ vc-libpqxx.mak all,

The intermediate process is as follows:

        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  "ObjDllRelease\binarystring.obj"  "ObjDllRelease\connection.obj"  "ObjDllRelease\connection_base.obj"  "ObjDllRelease\cursor.obj"  "ObjDllRelease\dbtransaction.obj"  "ObjDllRelease\errorhandler.obj"  "ObjDllRelease\except.obj"  "ObjDllRelease\field.obj"  "ObjDllRelease\largeobject.obj"  "ObjDllRelease\nontransaction.obj"  "ObjDllRelease\notification.obj"  "ObjDllRelease\notify-listen.obj"  "ObjDllRelease\pipeline.obj"  "ObjDllRelease\prepared_statement.obj"  "ObjDllRelease\result.obj"  "ObjDllRelease\robusttransaction.obj"  "ObjDllRelease\statement_parameters.obj"  "ObjDllRelease\strconv.obj"  "ObjDllRelease\subtransaction.obj"  "ObjDllRelease\tablereader.obj"  "ObjDllRelease\tablestream.obj"  "ObjDllRelease\tablewriter.obj"  "ObjDllRelease\transaction.obj"  "ObjDllRelease\transaction_base.obj"  "ObjDllRelease\tuple.obj"  "ObjDllRelease\util.obj"  "ObjDllRelease\libpqxx.obj" /out:"lib\libpqxx.dll" /implib:"lib\libpqxx.lib"   Creating library lib\libpqxx.lib and object lib\libpqxx.exp

It will be generated in the lib directory:

D:\Projects\hisql.svn\trunk\learning\libpqxx-4.0>dir/b/s libD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpq.dllD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpq.libD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxx.dllD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxx.dll.manifestD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxx.expD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxx.libD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxxD.dllD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxxD.dll.manifestD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxxD.expD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxxD.ilkD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxxD.libD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxxD.pdbD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxx_static.libD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\libpqxx_staticD.libD:\Projects\hisql.svn\trunk\learning\libpqxx-4.0\lib\msdia80.dll

3. Simple Example

Let's take a look at the simplest example. First, create the example user Foo with the password foo1, create a simple table t (ID, col), and insert a record containing Chinese characters, it can be seen that the client is GBK.

Iihero = # create user Foo password 'foo1 ';
Create role

Iihero => Create Table T (ID int primary key, col2 varchar (32); note: create Table/primary key: Create implicit index for table "T" "t_pkey" create tableiihero => \ encodinggbkiihero => insert into T values (1, 'list of different type '); insert 0 1 iihero => select * from t; Id | col2 ---- + ---------------- 1 | list of different types (1 row record)

Extract the compiled include and Lib directories to a fixed Directory, which can be combined with your original PostgreSQL include and Lib directories. For development and use.

The following is a simple sample code, which is used only to understand the connection and obtain the simplest result of a row (1 ):

#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;}
Note that you must set libpq. Lib, libpqxx. Lib, and header files. If there is a problem with the dynamic library connection method, you can try to use the static library of libpqxx.
In the preceding pqxx: connection object, a string must be input as the connection string, which must be separated by spaces. This is consistent with the libpq connection string.
Dbname = <database> hostaddr = <IP address or Host Name> User = <usrename> Password = <password>
Keyword cannot be mistaken. Otherwise, an exception is reported. Of course, the above code is very nonstandard, just as a demonstration. Under normal circumstances, you need to add exception handling.
Okay. Let's make some improvements to the code below.
4. Improved code
# Include <iostream> # include <pqxx/pqxx> int main () {try {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.exe C (" select 1 "); // W. commit (); STD: cout <res [0] [0]. as <int> () <STD: Endl; // access the data in Table T in the example. First, set the character set encoding at the client side. Otherwise, garbled conn occurs. set_client_encoding ("GBK"); pqxx: result R = w.exe C ("select * from t"); 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 ;}} catch (STD: exception & E) {STD: cerr <E. what () <STD: Endl; return 1;} return 0 ;}
As we can see above, we need to set the client encoding. You can use an iterator to access data in a query.
It should be noted that libpqxx's online documents are poorly written, and the sample code is incorrect. Inside,
pqxx::result::tuple::const_iterator
It cannot be compiled at all. It should be pqxx: tuple.