root/trunk/README @ 1665

Introduction

Welcome to libpqxx, the C++ API to the PostgreSQL database management system.

This package requires PostgreSQL to be installed--including the C headers for
client development.  The library builds on top of PostgreSQL's standard C API,
libpq, though this fact is almost completely hidden from programs that use
libpqxx.

Further information, as well as updates, a mailing list, and a bug reporting
system can be found at http://pqxx.org/

There are also ready-made libpqxx packages available for several systems: Debian
packages are available on any Debian download mirror, Mark Round maintains a
Blastwave package for Solaris, and Arjen Baart has made RPM packages (source and
i386 binaries) available on

        http://www.andromeda.nl/UNIX/packages/

You may want to check these before going to the trouble of building libpqxx
yourself.


Getting Started

All of this applies only to operating systems that are either part of, or
closely resemble, or support the standard interfaces of, the Unix family.  This
includes not just the "real" Unices (AIX, HP-UX, Irix, Solaris) but also MacOS X
(starting with 10.2, a.k.a. Jaguar) as well as GNU/Linux, the various BSD
operating systems, and Microsoft Windows with a Unix-like environment such as
Cygwin or MinGW installed.  There is a separate section below for Windows users
without such an environment.

For the Unix-like systems the procedure is essentially the standard "configure,
make, make install" sequence, except that in most cases some extra work needs to
be done for the optional "make check" step.

Let's just step through the full procedure for a Unix-like system.  You may be
familiar with most of it:


./configure     # (plus suitable preparation, to set up the build environment)
make            # (to build the library)

# Run library's self-test suite; in this example, connect to DBMS running on
# local Unix socket in /tmp, and use database pqxx as a testing ground.  See
# below for more details on how to set test parameters.

make PGHOST=/tmp PGDATABASE=pqxx check
make install    # (with superuser privileges, to install the library; see below)


Once this has succeeded, you should be able to link your own programs with
libpqxx and run them with confidence.

If it hasn't succeeded, or isn't quite what you want, it's time to move on to
the fineprint we hinted at.


1. Configure

A word on that "suitable preparation."  In older versions of libpqxx, you would
need to specify the location of the PostgreSQL header and library files in some
cases; when left alone, the script had a builtin list of usual locations to
check.  This is no longer necessary; the configure script will use another
script provided by Postgres to find them automatically.

In the new setup, however, the configure script must be able to find and run
this new script.  It's called pg_config, and it should be in the bin/
subdirectory of wherever Postgres is installed on your system.  Make sure this
directory is represented in your executable path before you run the configure
script, or it will fail with a message like:

configure: error: PostgreSQL configuration script pg_config was not found.

Or if you don't want to have pg_config in your path for whatever reason, or you
have multiple PostgreSQL installations on your system (each with their own copy
of pg_config, naturally) and wish to override the default version, add an option
like

        PG_CONFIG=/home/me/postgres/bin/pg_config

to your "configure" command line, where /home/me/postgres/bin/pg_config is an
example of where your preferred copy of pg_config might be.  This would indicate
to the configure script that you wish to build a libpqxx based on the postgres
version found in /home/me/postgres.

Finally, if you wish to install the libpqxx you're going to build in a custom
location, such as your home directory /home/me, you can specify this to the
configure script by calling it with the --prefix option, e.g.: 

        ./configure --prefix=/home/me

This can be handy if you don't have administrator rights on the machine you're
working on!

The configure scripts supports many other options to tweak how and where libpqxx
is to be built and installed; try the --help option to get an overview if you're
interested.

If configuration just absolutely plain won't work for whatever reason: take a
look in the config/sample-headers/ directory.  Here you will find configuration
headers for various compilers and libpq versions.  Pick the config-internal-*.h
and config-public-*.h headers for the compiler and libpq version most closely
matching your own, and see if they work for you.


2. Make

One problem some people have run into at this stage is that the header files for
PostgreSQL need the OpenSSL header files to work.  If this happens to you, make
sure openssl is installed and try again.

Otherwise, if the "make" part of the build procedure fails, you're probably
using a different compiler or compiler version than I am.  If your C++ compiler
is more than a few years old, and it's not one of the highly standard-compliant
ones like gcc or Comeau, just forget it.  It won't work until you get an
up-to-date compiler.

If, on the other hand, you're using a shiny new compiler and you're getting
errors or warnings, please let me know about them so I can fix them.


3. Make Check

This part is optional.  It is recommended that you do this just to make sure
that you've got a working library, but there is one Very Important Caveat that
could affect your existing data.  See below.

"Make check" is where you compile and run the regression test that typically
exercises all public methods in the library.  Obviously something or other will
have to go wrong at this point, right?

Indeed.  The "make check" procedure needs a database to play with.  In this
database, it will create two tables "pqxxevents" and "pqxxorgevents," as well as
some tables for its own use.  All have names prefixed with pqxx.  CAUTION: if
this database already contains tables with any of these names, either the check
will fail, or worse, your original data will be erased.

So choose your test database with at least a little caution.  Obviously the
safest thing to do is to set up a separate database for this.

To direct the regression test to the right database, set some or all of the
following environment variables as needed before running "make check" (see
description above):

        PGDATABASE      (name of database; defaults to your user name)
        PGHOST          (database server; defaults to local machine)
        PGPORT          (PostgreSQL port to connect to; default is 5432)
        PGUSER          (your PostgreSQL user ID; defaults to your login name)
        PGPASSWORD      (your PostgreSQL password, if needed)

Further environment variables that may be of use to you are documented in the
libpq documentation and in the manpage for Postgres' command-line client, psql.

Setting environment variables works differently depending on your shell, but
try (preferably in this order):
    export VARIABLE=value
or
    VARIABLE=value
    export VARIABLE
or
    set VARIABLE=value

Try printing the variable afterwards to make sure.  The command is normally
    echo $VARIABLE
and, if you set the variable successfully, should print the value you assigned.
It will print nothing if you failed to set the variable.

Should you have trouble finding the socket for a database running locally on a
Unix socket, try locating the socket in the filesystem.  It should appear as a
"file" called .s.PGSQL.5432 (if you are new to Unix-like systems, be warned that
files whose name start with a dot are not displayed by default).  Set PGHOST to
the directory where this "file" resides, as an absolute path--e.g. "/tmp".  On
most Unix-like systems, it will be located in /tmp, /var/run, or if your system
adheres to the FHS (the Filesystem Hierarchy Standard for GNU/Linux systems), in
/var/run/postgresql.  Be sure to use the absolute path; the leading slash tells
libpq that this is not a network address but a local Unix socket.

If you've taken care of all this, it should get you through the regression test
to ensure that everything's working properly.  If it isn't, and it's not
something you can fix by tweaking your PostgreSQL setup, let me know about it
and I'll try to fix it.


4. Make Install

This is supposed to install the libpqxx library and header files to your
system.  It took me and many others some time to get this to actually work, so
please take care to check that it really does work and that especially the
headers are really installed to your system.

The library and headers are installed to /usr/local/ by default, in their
respective subdirectories include/ and lib/.  The default installation location
has changed in release 2.2.0; it was /usr/local/pqxx before.

Assuming this succeeds, you should now be able to build your own programs by
adding /usr/local/pqxx/include to your include path, and /usr/local/pqxx/lib to
your library search path.  See the documentation and the test programs for more
information on using libpqxx.

One other thing here that may not work is that, if you link with the dynamic
version of the library, your program may fail to run because the run-time
loader cannot find the library.  You can get around this by (i) linking to the
static version of the library, or (ii) adding a link in /usr/local/lib to the
dynamic libpqxx library, or (iii) adding libpqxx's lib/ directory to your
loader's search path before running your program.  This is in decreasing order
of preference, so try (i) first, and only resort to (iii) if both (i) and (ii)
fail.  On Unix-like systems including GNU/Linux, the loader's search path can be
extended by setting the LD_LIBRARY_PATH variable.

Enjoy!


Building on Windows

Project files for Visual C++ are provided in the win32 directory, along with
some other Windows-specific material.  You'll need at least version 7 of the
VC compiler (also called VC.NET), earlier versions being too severely flawed to
build serious post-1996 C++ code.  Some of the 7.x versions will work; others
may not.  Versions of the library have also been built with Intel's compiler
running as a Visual C++ plugin.

Instead of going this route, you may want to try if the Unix build procedure
works for you instead, e.g. using the Cygwin tools.  If you don't, or it
doesn't, you'll need to copy the most appropriate compile-time configuration
files from various subdirectories in config/example-headers/ to include/pqxx.
You may need to tweak them manually to define the exact features your system,
compiler, and PostgreSQL versions support.  Normally the configure script would
do this for you; in theory it should be possible to run the configure script
with e.g. Visual C++ as your compiler.

Before trying to compile with Visual C++, you'll at least need to copy the file
win32/common-sample to win32/common, and edit the latter to reflect the proper
paths to your PostgreSQL headers and the libpq library.  See the win32
subdirectory for more documentation.


Manual Configuration: config-*-*.h

Normally, on any vaguely Unix-like system, the configuration headers (called
config-internal-*.h for the library's internal use, config-public-*.h for
both the library and client programs) are generated from config.h.in.  All these
files, once generated, are situated in the include/pqxx/ directory.

The configitems file lists all configuration items and where they go; but see
win32/INSTALL.txt for a detailed description of how these files work.

Getting the compiler-related configuration right can take several stages of
trying to build, looking at error messages, looking for configuration items that
may be related, changing them, and building again.  If nothing seems to help,
try the libpqxx mailing list or register a bug report or support request on the
website.  Be sure to read the FAQ though, because there are some known problems
with various compilers.


Windows-Specific Build Problems

If you're using Microsoft's compiler, you may find that some features of the
library (such as reverse iterators) are left out.  This is because Visual C++ is
not able to compile all of the library's code, and so the preprocessor was used
to disable such code if use of this compiler is detected.  Several other
workarounds for compiler bugs are automatically switched on for Visual C++,
regardless of whether you use the configure script.

Another problem specific to Windows is that apparently it doesn't let you free
memory in a DLL that was allocated in the main program or in another DLL, or
vice versa.  This can cause trouble when setting Noticers to process error or
warning output; which is one reason why recommended practice is to build libpqxx
as a static library, not a DLL.  When using Windows you must also take care to
set all Noticers from the same context where the Connection is created.


Documentation

The best information on programming with libpqxx can be found in the header
files themselves, in the include/pqxx/ directory.  Comments and declarations
from these headers are automatically extracted, using a program called Doxygen,
to form the HTML reference documentation that can be found in the
doc/html/Reference/ directory.  You'll want to start off by reading about the
connection class and the transaction_base class (though in practice you'll
mostly use the convenience typedef "work").

To get a good start in programming libpqxx, however, you may prefer to have some
introductory explanation and code examples.  The former can be found in the
tutorial, doc/html/Tutorial which currently is not being actively maintained but
does cover the basics accurately.  The tutorial is generated from the input
file doc/libpqxx.sgml at release time.

For some quick examples, take a look at the test programs in the test/
directory.  If you don't know how a certain function or class is used, try
searching the test programs for that name.  This may be the quickest way to a
working example.

When reading the test programs, please keep in mind that these do a lot of
checking and verifying to see that the library works correctly.  This is done
at test time precisely so that you won't need to do that in your own code.  You
are not meant to imitate all those safety checks yourself; libpqxx encourages a
relatively care-free programming style.  Rely on the exception mechanism for
error handling and concentrate on your goals, not the plumbing.


Programming with libpqxx

Your first program will involve the libpqxx classes connection (see headers
"pqxx/connection_base.hxx" and "pqxx/connection.hxx"), and work (a convenience
typedef for transaction<> which conforms to the interface defined in
"pqxx/transaction_base.hxx").

These "*.hxx" headers are not the ones you include in your program.  Instead,
include the versions without filename suffix (i.e. "pqxx/connection_base" etc.)
and they will include the .hxx files for you.  This was done so that includes
are in standard C++ style (as in <iostream> etc.), but an editor will still
recognize them as files containing C++ code.

Continuing the list of classes, you will most likely also need the result class
("pqxx/result.hxx").  In a nutshell, you create a "connection" based on a
Postgres connection string (see below), create a "work" in the context of that
connection, and run one or more queries on the work which return "result"
objects.  The results are containers of rows of data, each of which you can
treat as an array of strings: one for each field in the row.  It's that simple.

Here is a simple example program to get you going, with full error handling:

        #include <iostream>
        #include <pqxx/pqxx>

        using namespace std;
        using namespace pqxx;

        int main()
        {
                try
                {
                        connection C;
                        cout << "Connected to " << C.dbname() << endl;
                        work W(C);

                        result R = W.exec("SELECT name FROM employee");

                        cout << "Found " << R.size() << "employees:" << endl;
                        for (result::const_iterator r = R.begin();
                             r != R.end();
                             ++r)
                        {
                                cout << r[0].c_str() << endl;
                        }

                        cout << "Doubling all employees' salaries..." << endl;
                        W.exec("UPDATE employee SET salary=salary*2");

                        cout << "Making changes definite: ";
                        W.commit();
                        cout << "ok." << endl;
                }
                catch (const exception &e)
                {
                        cerr << e.what() << endl;
                        return 1;
                }
                return 0;
        }


Connection strings

Postgres connection strings state which database server you wish to connect to,
under which username, using which password, and so on.  Their format is defined
in the documentation for libpq, the C client interface for PostgreSQL.
Alternatively, these values may be defined by setting certain environment
variables as documented in e.g. the manual for psql, the command line interface
to PostgreSQL.  Again the definitions are the same for libpqxx-based programs.

The connection strings and variables are not fully and definitively documented
here; this document will tell you just enough to get going.  Check the
PostgreSQL documentation for authoritative information.

The connection string consists of attribute=value pairs separated by spaces,
e.g. "user=john password=1x2y3z4".  The valid attributes include:

host
        Name of server to connect to, or the full file path (beginning with a
        slash) to a Unix-domain socket on the local machine.  Defaults to
        "/tmp".  Equivalent to (but overrides) environment variable PGHOST.

hostaddr
        IP address of a server to connect to; mutually exclusive with "host".

port
        Port number at the server host to connect to, or socket file name
        extension for Unix-domain connections.  Equivalent to (but overrides)
        environment variable PGPORT.

dbname
        Name of the database to connect to.  A single server may host multiple
        databases.  Defaults to the same name as the current user's name.
        Equivalent to (but overrides) environment variable PGDATABASE.

user
        User name to connect under.  This defaults to the name of the current
        user, although PostgreSQL users are not necessarily the same thing as
        system users.

requiressl
        If set to 1, demands an encrypted SSL connection (and fails if no SSL
        connection can be created).

Settings in the connection strings override the environment variables, which in
turn override the default, on a variable-by-variable basis.  You only need to
define those variables that require non-default values.

Linking with libpqxx

To link your final program, make sure you link to both the C-level libpq library
and the actual C++ library, libpqxx.  On most Unix-style compilers, this can be
done using the options

        -lpq -lpqxx

while linking.  Note that both libraries must be in your link path, so the
linker knows where to find them.  Any dynamic libraries you use must also be in
a place where the loader can find them when loading your program at runtime.

Some users have reported problems using the above syntax, however, particularly
when multiple versions of libpqxx are partially or incorrectly installed on the
system.  If you get massive link errors, try removing the "-lpqxx" argument from
the command line and replacing it with the name of the libpqxx library binary
instead.  That's typically libpqxx.a, but you'll have to add the path to its
location as well, e.g. /usr/local/pqxx/lib/libpqxx.a.  This will ensure that the
linker will use that exact version of the library rather than one found
elsewhere on the system, and eliminate worries about the exact right version of
the library being installed with your program..


APPENDIX A - Links

Apple MacOS X   http://www.apple.com/macosx/
BSD             http://www.bsd.org/
C++             http://www.cs.rpi.edu/~musser/stl-book/
Cygwin          http://cygwin.com/
Doxygen         http://www.stack.nl/~dimitri/doxygen/
gcc             http://gcc.gnu.org/
Google          http://www.google.com/
libpq           http://candle.pha.pa.us/main/writings/pgsql/sgml/libpq.html
libpqxx         http://pqxx.org/
Linux           http://www.linux.org/
MinGW           http://www.mingw.org/
OpenSSL         http://www.openssl.org/
PostgreSQL      http://www.postgresql.org/
zlib            http://www.zlib.org/


APPENDIX B - Projects Using libpqxx

This list is far from complete.  It is known that there are many other projects
using libpqxx that are not included here.  Some of them may be proprietary, or
even have no names.

Inclusion does not imply endorsement.  For all I know, the people running the
Google projects may be unhappy with libpqxx--or perhaps they may have stopped
using it by the time you read this!  But obviously I'll do my best to ensure
that this does not happen.  On to the list:

As found on Google:

DocConversion           http://docconversion.sourceforge.net/
Genea                   http://savannah.nongnu.org/projects/genea/
Gnucomo                 http://www.gnucomo.org/
MapServer               http://mapserver.gis.umn.edu/
QHacc                   http://qhacc.sourceforge.net/
Vocal / Mascarpone      http://www.vovida.org/


Confirmed by authors:

OKE                     http://www.liacs.nl/home/bsamwel/oke/prerelease-0.10/
KOffice / Kexi          http://www.kexi-project.org/
KPoGre                  http://kpogre.sourceforge.net/
Once MMORPG             http://sourceforge.net/projects/once/
Scippy                  http://dicomlib.swri.ca/scippy.html