<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN"
	"http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd">
<book id="manual" lang="en">
  <bookinfo>
    <title>libpqxx tutorial</title>

    <authorgroup>
      <author>
	<firstname>Jeroen</firstname>
	<othername>T.</othername>
	<surname>Vermeulen</surname>
	<affiliation>
	  <address>
	    <email>jtv@xs4all.nl</email>
	  </address>
	</affiliation>
      </author>
    </authorgroup>

    <copyright>
      <year>2002—2008</year>
      <holder>
        Jeroen T. Vermeulen
      </holder>
    </copyright>
  </bookinfo>

  <chapter id="preface">
    <title>About libpqxx</title>

    <para>
      <productname>libpqxx</productname> is a C++ <acronym>API</acronym> to
      <productname>PostgreSQL</productname>.  It replaces the earlier
      <productname>libpq++</productname> with a more Standard Template Library
      (<acronym>STL</acronym>)-conformant interface and makes more extensive use
      of C++ language features such as exceptions, templates, and strings.
    </para>

    <para>
      Since <productname>libpqxx</productname> builds on top of the C frontend
      <productname>libpq</productname>, you will need a working
      <productname>libpq</productname> library on your system prior to getting
      started with <productname>libpqxx</productname>.  This is similar to the
      situation with the older <productname>libpq++</productname> interface.
    </para>

    <para>
      See the <ulink url="http://pqxx.org/"><productname>libpqxx</productname>
      website</ulink> for more information.
    </para>

    <section id="whyanother">
      <title>Why another C++ SQL library?</title>

      <para>
        Several open-source C++ wrapper libraries are available for various
        databases, as well as some database-independent ones; there's
        <productname>libpql++</productname>, <productname>libpq++</productname>,
        <productname>libsqlxx</productname> and so on.
      </para>

      <para>
        So was <productname>libpqxx</productname> really necessary?
      </para>

      <para>
        I think it is.  The C++ Standard, and its standard library in
        particular, have stabilized over the mid-1990s; compilers and standard
        library implementations are still following suit at the time of writing.
        As a result, most older third-party libraries suffer from symptoms such
        as:
      </para>

      <itemizedlist>
        <listitem>
	  <para>
	    Not using exceptions.  Many libraries return a status code and
            expect callers to check for errors "if they're interested."
            <emphasis>Of course</emphasis> they're interested.  But checking
            each and every call is tedious and error prone.
	  </para>
        </listitem>

        <listitem>
	  <para>
	    Ad-hoc exception classes.  Programs using C++ libraries need to deal
	    with various incompatible exception class hierarchies, and error
	    handling becomes a total nightmare with multiple essentially
            identical <literal>catch</literal> clauses for every
            <literal>try</literal> block.
	  </para>
        </listitem>

        <listitem>
	  <para>
	    Ad-hoc string classes.  As one member of the C++ standards committee
	    put it, if the C++ wasn't going to have a standard string class,
            "there will be blood in the streets."  Luckily the committee saw
            the wisdom in this, and civilization was spared.
	  </para>
        </listitem>

        <listitem>
	  <para>
	    "A Better C."  Using only the oldest features of C++ as extensions
            to the C language really only complicates things, but there was a
            time when this was the only way to write portable C++.
	  </para>
        </listitem>

        <listitem>
	  <para>
	    Ad-hoc containers and algorithms.  Inventing your own container
            APIs, interesting as it may be, in most cases is wasted development
            effort.  Besides that, there's also the redundant effort of having
            to learn how they work.
	  </para>
        </listitem>
      </itemizedlist>

      <para>
        For these reasons, third-party libraries have tended to grow into
        intrusive frameworks.  Third-party code, and class libraries in
        particular, need to be rewritten to replace such ad-hockery with the
        rich and standardized functionality now provided by C++.  This is what
        <productname>libpqxx</productname> intends to do for
        <productname>PostgreSQL</productname>.  The library was designed to be a
        forward-looking one, which means that ports to vendor X's compiler Y may
        have to wait until X improves its support of the Standard.
      </para>

      <para>
        But there were other reasons to design a new SQL frontend from scratch.
        C++ is a complex language, and the only things stopping library writers
        from shooting themselves in the feet are years of experience and an
        extensive idiom of good practices.  To name a few typical symptoms of
        foot-shooting:
      </para>

      <itemizedlist>
        <listitem>
	  <para>
	    Poor language integration.  Users need to learn myriads of function
	    names when standard C++ operators can be overloaded instead to make
	    code fit in more naturally with the core language.
	  </para>
        </listitem>

        <listitem>
	  <para>
	    Overdoing overloading.  It's also easy to go overboard with operator
	    or function overloading, and many classes do.  Some programmers use
	    identical function names to mean very different things, or define
	    operators whose semantics are not clearly derived from their
	    conventional meanings.
	  </para>
        </listitem>

        <listitem>
	  <para>
	    Resource management.  In the complex world of C++, what the user
	    really needs from a library is a little help in ensuring that
            objects are deleted at the proper time, pointers don't go off into
            never-never land, and so on.  Perhaps the most compelling aspect of
            C++ is the "resource acquisition is initialization" paradigm, but
            too few libraries apply it as yet.
	  </para>
        </listitem>

        <listitem>
	  <para>
	    Convoluted family trees.  Class derivation is a beautiful thing, but
	    it can be overused.  Some libraries construe far-fetched relations
	    between classes, or needlessly expose "convenience" relations using
	    public inheritance.
	  </para>
        </listitem>

        <listitem>
	  <para>
	    Overweight classes.  Some classes lump together different pieces of
	    functionality with different lifetimes.  For instance, some C++ SQL
	    libraries integrate transaction bracketing into a connection class.
	    Yet it is not unreasonable for an application to perform multiple
	    consecutive transactions using the same connection.
	  </para>
        </listitem>

        <listitem>
	  <para>
	   Exposure of low-level interfaces.  Some C++ wrappers for C-level SQL
	   interface libraries (including <productname>libpq++</productname>)
	   don't hide the ugly details of the underlying API.  Worse, some only
	   provide "utility" classes that the user can mix in freely with
           C-level function calls.
	  </para>
        </listitem>
      </itemizedlist>

      <para>
        Again, <productname>libpqxx</productname> is an attempt to do better.
        Hopefully this has resulted in a library that is easier to use than most
        alternatives while giving you more flexibility, that helps you avoid
        common pitfalls and reduces the need for debugging.
      </para>
    </section>
  </chapter>

  <chapter id="classoverview">
    <title>Class overview</title>

    <para>
      To work with <productname>libpqxx</productname>, you need to be aware of
      at least the following classes.  These are all introduced in a dedicated
      namespace, <literal>pqxx</literal>:
    </para>

    <itemizedlist>
      <listitem>
	<para>
	  <literal>connection</literal> represents a connection from your
	  program to a <productname>PostgreSQL</productname> back-end server.
	  Naturally your program may open multiple connections, simultaneous or
	  not, to any number of databases.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>work</literal>, actually a typedef for the
	  <literal>transaction</literal> class template, represents a
	  transaction being executed in the context of a
	  <literal>connection</literal>. This is a unit of work that must be
	  completed as a whole by the database backend.  If execution fails,
	  everything done within that <literal>transaction</literal> is undone
	  by the backend as if it had never happened.

	  <footnote>
	    <para>
	      Actually <productname>libpqxx</productname> provides three classes
	      doing this at various levels of reliability, called
	      <literal>nontransaction</literal>, <literal>transaction</literal>,
	      and <literal>robusttransaction</literal> for no, standard, and
	      best reliability respectively.  You probably shouldn't mess with
              <literal>robusttransaction</literal> though, because it comes with
              complications.
	    </para>
	  </footnote>

	  You may execute any number of transactions on a single
	  <literal>connection</literal>, but only one at a time.
	</para>

	<para>
	  I recommend that you do not set up your own
	  <literal>transaction</literal>s however, but have them managed for you
	  by a <literal>transactor</literal> instead (see below).
	</para>
      </listitem>

      <listitem>
	<para>
	  A <literal>result</literal> is a container holding the resulting data
	  coming out of a query or command executed in a
	  <literal>transaction</literal>.  It behaves just like a standard C++
	  container as found in the STL, although its contents can't be modified
          in any way.  (You can assign or copy it cheaply though; it's basically
          a smart pointer).  The tuples held in the result in turn behave much
          like non-modifiable containers themselves.
	</para>
      </listitem>

    </itemizedlist>

    <para>
      I would also like to use this opportunity to plug the invaluable
      <function>esc</function> function, which you should use whenever you want
      to include a variable as a string in your SQL (eg. insert it into a text
      field in a table).  See below.
    </para>

    <para>
      There are other classes that may be of interest to you; these are either
      not essential to writing a simple program using
      <productname>libpqxx</productname>, or are used in such a way that
      knowledge of them is not immediately necessary.  You will probably want to
      look them up at some point when it becomes necessary to understand complex
      compiler warnings.  Unfortunately C++ compilers are not yet very good at
      unravelling template-related errors and making them readable, so you may
      find some long and strange names when they happen.
    </para>

    <para>
      Some of the classes you may become interested in fairly quickly are:
    </para>
    <itemizedlist>
      <listitem>
	<para>
	  <literal>broken_connection</literal> is an exception class that is
	  thrown if <productname>libpqxx</productname> loses its connection to
	  the back-end.  It is derived from the standard C++ exception
	  <literal>std::runtime_error</literal>, and can generally be treated as
	  such.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>sql_error</literal> is an exception class that is thrown if
	  a query to the back-end fails.  Like
	  <literal>broken_connection</literal> it
	  is derived from <literal>std::runtime_error</literal>, but it also
	  carries information about the failed query.  This may be useful for
	  debugging.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>icursorstream</literal> is a way of iterating over a query
	  result using an SQL cursor.  This can be useful for selectively
	  retrieving parts of a query, or for retrieving and processing results
	  in chunks so your program can do useful work on one chunk while the
	  next is still coming in over a network connection.  Or you may want to
	  use this to give progress reports to the user between chunk transfers.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>in_doubt_error</literal> is an exception class to indicate a
	  rare and difficult error condition.  Explaining this is a bit painful,
	  since it invalidates some of what was said above (and is commonly said
	  in the database world).
	</para>
	<para>
	  In theory, a "transaction" is an integral, atomic unit of work that
	  either fails or succeeds.  This is a beautiful idea, but in practice
	  there is nothing that cannot be broken.  In practice there is a tiny
	  risk that, like Schroedinger's Cat, the transaction ends up in a
	  third, indeterminate state.  This happens when the connection to the
	  backend is lost just when you were waiting for confirmation after
	  completing your transaction.  Your transaction may have succeeded (in
	  fact it probably has), but if the connection is lost during just this
	  small time window, it has no way to confirm this to your application.
	</para>

	<para>
	  When this happens, libpqxx throws an <literal>in_doubt_error</literal>
	  on the client side, which you may want to <literal>catch</literal> as
	  a special case.  If you do, make sure all alarm bells go off!
	</para>

	<para>
	  See also the description for <literal>robusttransaction</literal>,
	  which sacrifices some performance to minimize this risk.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>nontransaction</literal> is just like a regular
	  <literal>transaction</literal> as far your code is concerned (except
	  that it's not a template).  The big difference is that where the
	  latter opens a back-end transaction to keep your view and
	  modifications of the database atomic, the former does
	  <emphasis>nothing</emphasis> to maintain integrity.  It just passes
	  your queries directly to the database, with no enclosing transaction.
	  This difference is expressed by the fact that the other transaction
	  classes are derived from <literal>dbtransaction</literal>, an
	  abstract class that expresses the presence of a real backend
	  transaction, whereas <literal>nontransaction</literal> is not.
	</para>
	<para>
	  This may give you better performance if your transaction does not
	  modify the database, but if it does, may have unforeseen implications
	  if you expect certain levels of consistency in the
	  database--especially if other programs (perhaps even other instances
	  of your own) may be modifying the database at the same time.  You'll
	  probably only want to use this in the following cases:
	</para>

	<itemizedlist>
	  <listitem>
	    <para>
	      If you only want to read from the database, not modify it, and
	      you know that the data is not going to be updated while your
	      program is running.
	    </para>
	  </listitem>

	  <listitem>
	    <para>
	      If you are interested in always getting the very latest
	      information out of your database, even if that means that you may
	      not get a temporally consistent view of the database.
	    </para>
	  </listitem>

	  <listitem>
	    <para>
	      When making changes to the database schema; some operations may
	      not be supported by the backend inside backend transactions.
	    </para>
	  </listitem>
	</itemizedlist>
      </listitem>

      <listitem>
	<para>
	  <literal>robusttransaction</literal> (a template just like
	  <literal>transaction</literal>) is essentially the same as a
	  <literal>transaction</literal>, ie. an atomic unit of work, except
	  that it works harder to prevent <literal>in_doubt_error</literal>s
	  from happening.  This comes at the cost of some performance, which is
	  one reason why this behaviour isn't the default.  It also creates
	  some extra tables in your database to keep track of what's happening.
	</para>

	<para>
	  Use this class instead of plain old <literal>transaction</literal> if
	  you get <literal>in_doubt_error</literal>s in your application--but
	  only <emphasis>after</emphasis> thoroughly inspecting your code for
	  bugs that might be causing the problem, or you'll only be hiding
	  glitches that should be fixed instead.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>transaction_base</literal> defines the common public
	  interface for the <literal>transaction</literal>-like classes:
	  <literal>nontransaction</literal>,
	  <literal>robusttransaction</literal>, and
	  <literal>transaction</literal> itself.  To look up methods available
	  in these classes, see the definition (or documentation) for
	  <literal>transaction_base</literal> instead.
	</para>
	<para>
	  You'll usually want to refer to the default
	  <literal>transaction</literal> type as <literal>work</literal>, a
	  typedef that means "<literal>transaction</literal> with default
	  (read-committed) isolation level."
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>transactor&lt;&gt;</literal> provides a framework that makes
	  it easier for you to write correct, robust transaction code to be
	  executed in a transaction.  You should generally try to write your
	  code as a class derived from <literal>transactor&lt;&gt;</literal>,
	  but this is not required.
	</para>

	<para>
	  A <literal>transactor</literal>-derived class may select its own
	  "quality of service" in transactional integrity by choosing a
	  transaction type as its template argument, e.g.
	  <literal>transactor&lt;robusttransaction&lt;&gt; &gt;</literal>.  The
	  default <literal>transactor&lt;&gt;</literal> is equivalent to
	  <literal>transactor&lt;work&gt;</literal>.  Within your class you can
	  refer to the transaction type used as
	  <literal>argument_type</literal>.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>notification_receiver</literal> is an instance of the
	  Observer design pattern.  Any code connected to a backend may flag an
	  event using the <productname>PostgreSQL</productname>
	  <literal>NOTIFY</literal> command.  Frontends connected to that same
	  backend database may be listening for an event of that name to occur.
	  A frontend using <productname>libpqxx</productname> does this by
	  registering an observer object derived from
	  <literal>notification_receiver</literal>, which will be invoked by
	  <productname>libpqxx</productname> to handle the event when it occurs.
	</para>

	<para>
	  Something every programmer using notification receivers in PostgreSQL
	  should know, by the way, is that notifications are not delivered to
	  your program while it is inside a back-end transaction.  Nor are they
	  sent until the notifying process has committed any transaction it may
	  be in.  This makes sense from a correctness point of view (otherwise
	  your processing of the event might get rolled back just because the
	  transaction you're in happens to fail), but may be a little confusing
	  to the unaware programmer.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>result::tuple</literal> is a nested class defined within
	  <literal>result</literal>.  It refers to a tuple of data within a
	  <literal>result</literal> object.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>result::field</literal>, also nested within
	  <literal>result</literal>, refers to one field within a
	  <literal>tuple</literal>.  A <literal>field</literal> contains one
	  retrieved value such as a number or a string, and can be read into
	  variables of various types such as <literal>int</literal>s or
	  <literal>string</literal>s.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>result::const_iterator</literal> allows you to enumerate the
	  <literal>tuple</literal>s in a <literal>result</literal> in a similar
	  way to the iterators defined by any standard C++ container.
	</para>
      </listitem>

      <listitem>
	<para>
	  <literal>result::tuple::const_iterator</literal> similarly iterates
	  over the <literal>field</literal>s in a <literal>tuple</literal>.
	</para>
      </listitem>
    </itemizedlist>

    <para>
      Finally, there are also some functions that you may want to know about,
      that live alone in the <literal>pqxx</literal> namespace without being
      part of any class:
    </para>

    <itemizedlist>
      <listitem>
	<para>
	  <function>esc</function>, found in the transaction classes, is the
	  canonical way to use strings in your SQL.  This function escapes any
	  quotes, backslashes, and other characters in your input string that
	  would otherwise cause syntax errors (or worse, security holes) in your
	  queries when included directly.
	</para>

	<para>
	  <emphasis>Use these functions</emphasis> whenever you want to have a
	  variable string in your SQL queries.  If you don't, you'll be left
	  with annoying bugs or possibly even security holes in your program.
	  Use this function, use this function, use this function.  Please.
	  Make me proud.
	</para>
      </listitem>

      <listitem>
	<para>
	  <function>to_string</function> knows how to render many types of
	  values as strings.  This is used internally by
	  <literal>libpqxx</literal> to convert values coming out of or going
	  into the backend, and doesn't respect locale settings.
	</para>
      </listitem>

      <listitem>
	<para>
	  <function>from_string</function> is the inverse of
	  <function>to_string</function>.  It is used by eg.
	  <function>result::field::to()</function> to convert the incoming
	  field data (which is in text format) to the desired C++ type.
	</para>
      </listitem>
    </itemizedlist>
  </chapter>

  <chapter id="tutorial">
    <title>Tutorial</title>

    <section id="before">
      <title>Before we begin programming</title>

      <section id="abouttutorial">
	<title>This document</title>

	<para>
	  This frontend library is built on top of the
	  <filename>libpq</filename> frontend library, which defines
	  <productname>PostgreSQL</productname>'s C <acronym>API</acronym>.
	  Therefore this manual will sometimes refer to the
	  <filename>libpq</filename> documentation rather than repeat what is
	  said there.  This was not done to annoy you, but to ensure that the
	  <productname>libpqxx</productname> documentation remains up-to-date
	  with any changes in the C frontend.  Please bear with us.
	</para>

	<para>
	  Furthermore, if you're ever in doubt about how to use the basic
	  features of <productname>libpqxx</productname>, take a look at the
	  example programs provided; they're called
	  <filename>test000</filename>, <filename>test001</filename> etc.
	  Those should help give you an idea of how simple the library can be
	  in practice.  Don't let that keep you from submitting a request for
	  improved documentation though; your questions define what this
	  tutorial needs to say!
	</para>
      </section>

      <section id="cpp-conventions">
	<title>C++ conventions</title>

	<para>
	  All definitions made by <productname>libpqxx</productname> reside
	  in a namespace <literal>pqxx</literal>.  If you're not familiar
	  with C++ namespaces, you have two options:
	</para>

	<itemizedlist>
	  <listitem>
	    <para>
	      Always import the whole of the <literal>pqxx</literal>
	      namespace in your programs.
	    </para>

	    <para>
	      This tells the compiler to always look in the
	      <literal>pqxx</literal> namespace when trying to resolve a name
	      that doesn't occur in the global (ie. the "default") namespace).
	      To do this, include the directive
	      <programlisting>
		using namespace pqxx;
	      </programlisting>
	      near the top of each source file that uses
	      <productname>libpqxx</productname>.  This is typically best for
	      your source files (you're probably doing it already for the
	      <literal>std</literal> namespace), though it is not considered
	      good practice in header files.
	    </para>
	  </listitem>

	  <listitem>
	    <para>
	      Explicitly qualify each <productname>libpqxx</productname> name
	      with the <literal>pqxx</literal> namespace.
	    </para>

	    <para>
	      This will tell the compiler exactly which names are supposed to
	      be in the <literal>pqxx</literal> namespace, avoiding any
	      confusion over multiple classes or functions used in your
	      program possibly having the same name.  As long as they're in
	      different namespaces, the compiler will be able to tell them
	      apart without trouble.  To do this, write
	      <literal>pqxx::connection</literal> instead of
	      <literal>connection</literal>,
	      <literal>pqxx::result::const_iterator</literal> instead of
	      <literal>result::const_iterator</literal>, and so on.
	    </para>

	    <para>
	      This is the mode of address you will typically want to use in
	      header files, to avoid polluting the namespaces of the actual
	      source files with the definitions of the extra namespace.
	    </para>
	  </listitem>
	</itemizedlist>
      </section>
    </section>

    <section id="connectionsetup">
      <title>Setting up a connection</title>

      <para>
	The very first thing to do before we can begin working with a
	database, is actually connect to one.  We do this by creating a
	<literal>connection</literal> object that will serve as our "handle"
	on the connection:
      </para>

      <programlisting>
	connection Conn("dbname=test");
      </programlisting>

      <para>
        This gives us a connection object called <literal>Conn</literal>.
      </para>

      <para>
	The <literal>connection</literal> constructor here takes one argument,
	the "connect string."  This string may be used to specify which host
	on the network runs the database backend we wish to connect to, which
	database we're interested in, which user name we'll be using to log
	in, etc.  Refer to the libpq <function>connect</function> call for a
	complete definition of what may go into the connect string.  In this
	case we're connecting to a database <literal>test</literal> residing
	on the local machine.  By default the client will try to connect to a
        server running on the local machine.
      </para>

      <para>
	If no connection could be established, the <literal>connection</literal>
	constructor may throw an exception immediately; or it may decide to
	retry the connection later in case the problem is only temporary.  In
	the latter case, an exception may occur at some later point if the
	problem turns out not to be so temporary after all.
      </para>

      <para>
	The <literal>connection</literal> can now act as a "service counter"
	for our database; your client will use it to perform one or more
	transactions related to the database.
      </para>

      <para>
	Connections cannot be copied or assigned.  Any attempt to do so will
	be met with a compiler error.
      </para>

      <caution>
	<para>
	  As a matter of design, <productname>libpqxx</productname> will not
	  allow you to perform queries on the <literal>connection</literal>
	  directly.  You will need to open a transaction instead.
	</para>

	<para>
	  See the section on transactions below.
	</para>
      </caution>
    </section>

    <section id="trans-performing">
      <title>Performing a transaction</title>

      <section id="trans-whatis">
	<title>What's a Transaction?</title>

	<para>
	  A transaction demarcates a "unit of work" operating on the database,
	  ie.  a series of operations that must either complete successfully, or
	  if it is aborted at any stage, must not affect the data in the
	  database at all.  Hence the process of completing a transaction is
	  called the "commit" operation, and the process of aborting it and
	  undoing any changes it may have made is called "rollback."
	</para>

	<para>
	  All access to the database in <productname>libpqxx</productname>
	  must go through a transaction object, so familiarity with this
	  class family's interface is essential.
	</para>
      </section>

      <section id="trans-creating">
	<title>Creating a transaction object</title>

	<para>
	  Creating a transaction object is not normally something you will
	  have to do.  The <literal>transactor</literal> class, discussed
	  below, will take care of that and some other red tape besides.
	  Still, there are cases where creating the object is unavoidable
	  (when acting on multiple database connections simultaneously) or
	  just plain easier to do.  Please consider using a
	  <literal>transactor</literal> whereever possible.
	</para>

	<para>
	  When creating a transaction, pass it the <literal>connection</literal>
	  object it is to act on, and optionally an identifying name for your
	  transaction.  The name, which need not be unique but should begin with
	  a letter and may contain only letters, digits and underscores, can be
	  used by <productname>libpqxx</productname> to make some error messages
	  more specific.

	  <programlisting>
	    transaction&lt;&gt; Xaction(Conn, "DemoTransaction");
	  </programlisting>

	  Or, alternatively (once you get fed up with typing
	  <literal>transaction&lt;&gt;</literal>):

	  <programlisting>
	    work Xaction(Conn, "DemoTransaction");
	  </programlisting>
	</para>

	<para>
	  The lifetime of the transaction object demarcates the unit of work.
	  Its construction marks the beginning of the transaction, and its
	  destruction means that the transaction is ended--whether through
	  commit or rollback.
	</para>

	<para>
	  The transaction class hierarchy is built on the principle of "explicit
	  commit," ie. the commit operation must always be explicit in the code.
	  If the transaction is destroyed before a commit was given, the
	  transaction is implicitly aborted (rolled back).  If your transaction
	  makes no changes to the database, however, there is nothing to commit
	  or roll back and the commit may safely be omitted.
	</para>

	<para>
	  Destroying the connection object while the transaction still exists
	  is an error that may result in a program crash, although the
	  library will try to log an error message.  Transactions cannot be
	  copied, nor assigned, nor constructed without a connection
	  (default-constructed); attempts to do any of these will result in
	  compile errors.
	</para>
      </section>
    </section>

    <section id="trans-ending">
      <title>Ending a transaction</title>
      <para>
	A transaction ends either successfully through an explicit commit
	command, or unsuccessfully in any of a number of ways.  The following
	are the ways to end a transaction:
      </para>

      <itemizedlist>
	<listitem>
	  <para>
	    The transaction may be committed through its
	    <literal>commit</literal> member function:
	  </para>
	  <programlisting>
	    Xaction.commit();
	  </programlisting>

	  <para>
	    The commit operation is sent to the backend at the point where
	    the <function>commit</function> call occurs.  Any exceptions
	    generated by the database transaction will be thrown from here at
	    the latest.  The only exceptions that may be generated by
	    <literal>Xaction</literal> beyond this point are related to
	    incorrect handling of the transaction object, eg. if an attempt
	    is made to abort <literal>Xaction</literal> after it has been
	    committed, or runtime errors such as memory running out.
	  </para>

	  <para>
	    As a consequence, any streams or cursors nested within the
	    transaction (to be discussed later) must have been closed before
	    the <literal>commit()</literal>.  To do otherwise could possibly
	    allow a transaction to be committed before all related actions
	    had completed.  The library will throw an exception if any
	    streams are still open when the transaction is ended.
	  </para>
	</listitem>

	<listitem>
	  <para>
	    A transaction is aborted if it is destroyed without having been
	    explicitly committed:
	  </para>
	  <itemizedlist>
	    <listitem>
	      <programlisting>
		{
		work Xaction(Conn, "DemoTransaction");

		// (Queries)

		} // Xaction destroyed here
	      </programlisting>
	    </listitem>

	    <listitem>
	      <programlisting>
		work *XactionP = new work(Conn, "DemoTransaction");

		// (Queries)

		delete XactionP; // Xaction destroyed here
	      </programlisting>
	    </listitem>

	    <listitem>
	      <programlisting>
		try
		{
		work Xaction(Conn, "DemoTransaction");

		// (Queries)

		Xaction.commit();
		// If we get here, Xaction is committed
		}
		catch (...)
		{
		// If we get here, Xaction has been rolled back
		}
	      </programlisting>
	    </listitem>

	  </itemizedlist>
	  <para>
	    No matter where exactly the decision to abort is made, the actual
	    abort operation is sent to the backend when the transaction's
	    destructor is called.  If the abort fails, eg. because the
	    network connection has been lost, no error is reported
	    <footnote>
	      <para>
		Throwing an exception from a destructor to report the error
		would have serious effects on program correctness.
		<emphasis>Never throw exceptions from a destructor.</emphasis>
	      </para>
	    </footnote>
	    and the transaction will die of natural causes (either it has been
	    closed by the backend already, or it soon will be if the connection
	    is lost).
	  </para>
	</listitem>

	<listitem>
	  <para>
	    If a database error occurs during the transaction, such as an SQL
	    syntax error or lost connection to the backend, the transaction
	    is aborted.
	  </para>

	  <programlisting>
	    work Xaction(Conn, "DemoTransaction");
	    try
	    {
	    // (Queries)
	    Xaction.exec("SELECT !?^H^H^H^H");	// Fails: SQL syntax error
	    }
	    catch (...)
	    {
	    }
	    Xaction.commit(); // ERROR: Xaction has already aborted!
	  </programlisting>

	  <para>
	    For this reason, it is recommended always to include the "commit"
	    operation inside the <literal>try</literal> block (if any)
	    surrounding the transaction code, <emphasis>not</emphasis> after
	    the <literal>catch</literal> block.
	  </para>

	  <para>
	    Think of it as a natural extension of structural programming: the
	    transaction is "nested" within the connection, and the
	    transaction code can be "nested" in a
	    <literal>try</literal>/<literal>catch</literal> block.
	  </para>
	</listitem>

      </itemizedlist>
      <para>
	No more queries may be issued to the transaction regardless of how it
	ended; an exception will be thrown if the application attempts to
	continue the transaction after that time.  Ending a transaction more
	than once is an error, except that aborting it multiple times is
	tolerated to facilitate error handling.
      </para>
    </section>

    <section id="queries">
      <title>Executing queries</title>
      <para>
	So let's execute an actual query.  There is no "query class" in
	<productname>libpqxx</productname>; we really do try to keep your life
	simple.
      </para>

      <para>
	A query is executed within a transaction by passing the query string to
	the transaction object's <function>exec</function> method.  If the query
	fails to complete successfully, this method will throw the appropriate
	exception.
      </para>

      <para>
	The query itself is a standard C string in this case, ie. a
	<literal>const char *</literal> but you'll frequently want to use a C++
	<literal>string</literal> to make it easy to include variables:
      </para>

      <programlisting>
	void DeleteEntry(work &amp;T, string Table, long ID)
	{
	T.exec("DELETE FROM " + Table + " WHERE ID=" + to_string(ID));
	}
      </programlisting>

      <para>
        In some places, even <literal>stringstream</literal>s will work, so
	you can use the full stream formatting capabilities, locales etc. in
	the standard C++ library to compose your SQL queries.
      </para>

    </section>

    <section id="queryresults">
      <title>Getting query results</title>

      <para>
	Obviously not all queries are commands--the more common kind actually
	returns useful data.  Result data in
	<productname>libpqxx</productname> are encapsulated in a
	<literal>result</literal> object, which acts as a container similar
	to the STL's <literal>vector</literal> template.
      </para>

      <programlisting>
	result R = T.exec("SELECT firstname FROM employee WHERE lastname='Ng'");
      </programlisting>

      <para>
	This executes a query on the database, collects all matching data, and
	stores it in the form of a <literal>result</literal>.
      </para>

      <para>
	Two ways are provided to get at individual rows in a
	<literal>result</literal>: first, through indexing with the array
	index operator <literal>[]</literal> or the <function>at</function>
	member function, and second, through random-access iterators.  Either
	will give you a <literal>result::tuple</literal> object that in turn
	can be addressed with the array index operator (or the
	<function>at</function> member function) or using iterators to get at
	its individual fields

	<footnote>
	  <para>
	    The difference between <literal>[]</literal> and
	    <function>at</function> is that the latter is guaranteed to perform
	    bounds-checking, throwing an exception if you pass it an illegal
	    index value.  With the array index operator you may get slightly
	    better performance, but attempting to address a nonexistent row or
	    field will result in undefined behaviour such as program crashes or
	    inexplicably strange results.
	  </para>
	</footnote>.
      </para>

      <para>
	Thus, <literal>R[0]</literal> will return the first ("zeroth") tuple
	in R.  You won't normally want to bother with
	<literal>tuple</literal>s though; they don't contain the actual data,
	but rather serve as placeholders to later tell
	<literal>result</literal> which fields to fetch when field values
	from the tuple are requested.  The class exists mostly for technical
	reasons related to the array index operators
	<footnote>
	  <para>
	    This is an instance of the <wordasword>Proxy</wordasword>
	    implementation pattern, needed to allow a <literal>result</literal>
	    to be indexed as if it were a two-dimensional array.  C++'s array
	    index operator doesn't allow this usage directly, but the operator
	    can be applied to the result of a previous use of the same operator.
	    The "result in the middle" needs to have a type of its own, though,
	    and that's what <literal>tuple</literal> is for.
	  </para>
	</footnote>.

	What you'll usually do is index the row directly to get at the field
	you want, e.g. <literal>R[0][0]</literal> to get the first field of
	the first row.
      </para>

      <para>
	Array indexing of tuples also works with the fields' names instead of
	their numbers, eg.:
      </para>

      <programlisting>
	// Process employees' names one by one.  ProcessNames() doesn't know exactly
	// what columns are going to be in R, but there must be one called "lastname".
	void ProcessNames(result R)
	{
	for (result::size_type i = 0; i != R.size(); ++i)
	Process(R[i]["lastname"]);
	}
      </programlisting>
      <para>
	As for the alternative, accessing the result tuples through an iterator,
	only <literal>const</literal> iterators are provided so the contents of
	the result cannot be modified.  Use these iterators as random-access
	iterators like with any STL-like container:
      </para>
      <programlisting>
	for (result::const_iterator i = R.begin(); i != R.end(); ++i)
	Process(*i);
      </programlisting>

      <para>
	Iterators may be incremented or decremented (whether pre- or post-),
	they may be added to or subtracted from to jump through the result
	tuples; their positions may be compared (provided they point into the
	same <literal>result</literal>), and they may be dereferenced through
	the <literal>*</literal> or <literal>-></literal> operators.
      </para>

      <para>
	Finally, the iterated tuples' fields may be addressed using the array
	index operator on the iterator directly, eg.
	<literal>R.begin()[0]</literal> gets you the first field of
	<literal>R</literal>'s first row, just like
	<literal>R[0][0]</literal> would
	<footnote>
	  <para>
	    Or <literal>(*R.begin())[0]</literal>.  It may seem quirky to
	    have <literal>R.begin()</literal> and
	    <literal>*R.begin()</literal> mean the same thing, but it makes
	    practical use of iterators a lot easier.  In fact it matches how
	    arrays and pointers work in C and C++.
	  </para>
	</footnote>.
      </para>

      <para>
	Either way, once you've indexed the <literal>result::tuple</literal>
	you get a <literal>result::field</literal>--which is another
	placeholder, but this time encapsulates an actual field value in our
	query result.  A <literal>field</literal> <literal>F</literal> also
	knows its column name, which can be obtained as
	<literal>F.Name()</literal>.
      </para>

      <para>
	Again, there is more than one way to read the field's value.  Let's
	start out with the easy one, <function>c_str</function>, which reads
	the value as a C string:
      </para>

      <programlisting>
	cout &lt;&lt; "Name: " &lt;&lt; F.c_str() &lt;&lt; endl;
      </programlisting>

      <para>
	This will return the empty string (<literal>""</literal>) if field F
	has the null value.  Use <function>is_null</function> to see if this
	is the case:
      </para>

      <programlisting>
	if (!F.is_null())
	cout &lt;&lt; "Name: " &lt;&lt; F.c_str() &lt;&lt; endl;
      </programlisting>

      <para>
	In practice of course, not all data is going to consist of strings.
	Many fields will be integer values, or decimals, or Booleans.  To
	convert the field's value to one of these, use its
	<function>to</function> method.  This adapts itself to the type of
	variable you pass it, expecting the field value to be of an
	appropriate form for that type.  For convenience,
	<function>to</function> returns <literal>false</literal> if the field
	had the null value, and <literal>true</literal> otherwise.  In the
	former case, the variable will retain the value it had before the
	call.
      </para>

      <programlisting>
	// Pay an employee his salary.  Add bonus for managers.
	// The employee tuple must contain the fields
	void PaySalary(result::const_iterator empl)
	{
	long id;
	float salary;
	bool is_manager=false;

	// Get id.  Must never be null.
	if (!empl[0].to(id)) throw runtime_error("No id!");

	// Get salary.  If this employee has no salary, skip payment.
	if (!empl[1].to(salary)) return;

	// Get manager status.  If field is null, variable's original value (false)
	// will be preserved.
	empl[2].to(is_manager);

	if (is_manager) salary += Bonus;

	TransferMoney(id, salary);
	}
      </programlisting>

      <para>
	If conversion fails, e.g. when trying to convert a floating-point
	value to an integer variable, <function>to</function> will throw a
	<literal>runtime_error</literal> reporting the problem in its
	<function>what</function> message.
      </para>
    </section>

    <section id="transactors">
      <title>Doing Transactions Right: Transactors</title>

      <para>
	Writing database code can be tricky.  One of the most complicated
	areas is dealing with unexpected error conditions, such as losing
	one's connection to the database server.  For long-running processes
	you'll frequently find yourself rewriting code for a simple
	transaction to make it:
      </para>

      <itemizedlist>
	<listitem>
	  <para>
	    Attempt to perform the transaction.
	  </para>
	</listitem>

	<listitem>
	  <para>
	    Check for "connection lost" errors.
	  </para>
	</listitem>

	<listitem>
	  <para>
	    Attempt to restore the connection.
	  </para>
	</listitem>

	<listitem>
	  <para>
	    Repeat until the transaction succeeds.
	  </para>
	</listitem>
      </itemizedlist>

      <para>
	This is bad for the heart, and clutters up your code besides.  The
	<literal>transactor</literal> framework will take this work out of
	your hands if you let it.
      </para>

      <section id="functors">
	<title>Functors</title>

	<para>
	  The mechanism is based on the concept of
	  <emphasis>Functors</emphasis>, a powerful object-oriented design
	  pattern that replaces the older practice of passing callback
	  functions (or hooks, as they're sometimes called, or exits) to
	  foreign code.  Unlike classic callback functions, Functors provide
	  an elegant way of maintaining custom state in your callback code,
	  when the exact form or size of that state was not known in advance
	  to the writer of the foreign code that will eventually invoke your
	  callback.
	</para>

	<para>
	  Functors in C++ are simple objects that can be invoked just like
	  functions or function pointers can, by virtue of providing the
	  function invocation operator, <function>operator()</function>.
	</para>

	<para>
	  A simple functor could look like this:
	</para>

	<programlisting>
	  struct HelloFunctor
	  {
	  void operator()() { cout &lt;&lt; "Hello World" &lt;&lt; endl; }
	  };
	</programlisting>

	<para>
	  ...And once an object of this functor type has been created, it can
	  be "invoked" just as if it were a function:
	</para>

	<programlisting>
	  HelloFunctor Hi;
	  Hi();
	</programlisting>

	<para>
	  But the invoking code may also be foreign code knowing nothing
	  about the type of your functor.  The foreign code is usually a
	  template, so it automatically becomes "specialized" to your type of
	  functor when the one meets the other:
	</para>

	<programlisting>
	  template&lt;typename FUNCTOR&gt; void DoFunctor(const FUNCTOR &amp;Hi)
	  {
	  Hi();
	  }
	</programlisting>

	<para>
	  The great thing about functors is that they can carry state.  This
	  is most useful when you need to pass a functor object to foreign
	  code like <function>DoFunctor</function> above, but you need
	  certain extra parameters to be passed to your functor that the
	  foreign code isn't going to pass.  The classic C solution to this
	  problem is to let you pass both a function pointer and a
	  pointer-to-<literal>void</literal> as a method of letting you
	  provide any type of data of your liking to it.  This generally
	  makes programs a little harder to read, and is not very safe or
	  convenient.
	</para>

	<para>
	  With functors, there is a better way to do the same thing.  Let's
	  say you want to adapt the <literal>HelloFunctor</literal> class to
	  print its output to a different output stream.  What you'd really
	  like to do is add a parameter to your <literal>()</literal>
	  operator to indicate which stream to print to:
	</para>
	<programlisting>
	  void operator()(ostream &amp;Stream)
	  {
	  Stream &lt;&lt; "Hello World" &lt;&lt; endl;
	  }
	</programlisting>

	<para>
	  Unfortunately, <function>DoFunctor</function> doesn't know about
	  this new parameter, let alone what argument to pass!  So instead,
	  let's make this
	  <literal>Stream</literal> a class member:
	</para>
	<programlisting>
	  struct HelloFunctor
	  {
	  ostream &amp;Stream;

	  // Set Stream when creating a HelloFunctor
	  explicit HelloFunctor(ostream &amp;S) : Stream(S) { }

	  // Print to output stream selected at construction time
	  void operator()()
	  {
	  Stream &lt;&lt; "Hello World" &lt;&lt; endl;
	  }
	  };
	</programlisting>

	<para>
	  We can now provide the necessary information (ie., which stream to
	  print to) to our <literal>HelloFunctor</literal> before we pass it
	  to <function>DoFunctor</function>:
	</para>

	<programlisting>
	  HelloFunctor Hi1(cout), Hi2(cerr);

	  DoFunctor(Hi1);  // Print to cout
	  DoFunctor(Hi2);  // Print to cerr
	</programlisting>

	<para>
	  Naturally a functor's <literal>()</literal> operator may also return
	  some other type than <literal>void</literal>, and it may take
	  arguments just like any other function.  This mechanism is used
	  extensively by the STL to sort containers, to find items in sorted
	  containers like <literal>set</literal> or <literal>map</literal>, and
	  so on.
	</para>
      </section>

      <section id="transactor-working">
	<title>How Transactors Work</title>

	<para>
	  A transactor is a functor derived from an instantiation of
	  <filename>libpqxx</filename>'s <literal>transactor</literal> class
	  template.  Instead of writing your database transaction inline with
	  the rest of your code, you encapsulate it in your functor's
	  <literal>()</literal> operator.  When the time comes to execute
	  your transaction, you create an object of your functor type and
	  pass it to the <function>perform</function> method of your
	  <literal>connection</literal> to the database.
	</para>

	<para>
	  <function>perform</function> will make a copy of your transactor
	  (which means it needs to be copy-constructible, by the way).  It
	  will also create a transaction object and invoke your transactor's
	  <literal>()</literal> operator, passing the transaction object to
	  it.  All your transactor needs to do is perform its queries on this
	  object and return, after which <function>perform</function> will
	  commit the transaction.  If your <literal>()</literal> operator
	  throws an exception instead, <function>perform</function> will
	  discard the copy of your original transactor, and try again with a
	  new copy until it either succeeds or eventually gives up
	  <footnote>
	    <para>
	      <function>perform</function> gives up if the transaction fails
	      too many times in succession.  The maximum number of attempts
	      that are made can be passed to <function>perform</function> as
	      an optional second argument.
	    </para>
	  </footnote>.
	  If the connection is lost, the transaction will fail but
	  <function>perform</function> will restore it transparently and
	  simply try again.
	</para>

	<para>
	  To make all this work, your transactor's <literal>()</literal>
	  operator must make <emphasis>no</emphasis> changes to the rest of
	  your program's state.  Any intermediate results, data to be
	  processed, and so on, must stay within your transactor, and be
	  destroyed when the transactor is.  That is the magic trick that
	  allows <function>perform</function> to create copies of your
	  transactor and use them to rerun the transaction as
	  many times as needed, without your program noticing.
	</para>

	<para>
	  So how does your transactor pass query results back to the outside
	  world once it's done?  For this purpose, you may redefine
	  <literal>transactor</literal>'s <function>on_commit</function>
	  member function to pass any data back to the rest of your program.
	  This member function will be called only if your transaction
	  succeeded.
	</para>

	<para>
	  (You may also wish to go the other route, storing data to variables
	  outside the transactor right away, and override the
	  <function>on_abort</function> and <function>on_doubt</function>
	  functions to remove the data again if the transaction failed, but
	  this is much more likely to cause subtle bugs.)
	</para>

	<para>
	  Please refer to the reference manual, the source code, and the test
	  programs that come with <filename>libpqxx</filename> to learn more
	  about how transactors work.
	</para>
      </section>
    </section>
  </chapter>
</book>
<!-- vi: sw=2
  -->