wiki:WikiStart

Version 141 (modified by jtv, 8 years ago) (diff)

--

C++ connector for PostgreSQL

libpqxx is the official C++ client API for PostgreSQL, the enterprise-strength open-source relational database. (If "PostgreSQL" is too verbose, call it by its shorter name, postgres).

If you are writing software in C++ that needs to access databases managed by postgres—on just about any platform—then libpqxx is the library you use. It is the standard C++ language binding for the postgres RDBMS.

The source code for libpqxx is available under the BSD license, so you're free to download it, pass it on to others, change it, sell it, include it in your own code, and share your changes with anyone you choose. No charge, no catch. In most cases you'll want a pre-built package provided by a package maintainer for your platform, and distributed through your normal package management infrastructure.

News

2011-08-07: Bogged down in bug spam

Not getting much done for the upcoming 4.0 release. That's because all available "libpqxx time" goes into fighting spam at the moment. Has anyone else been getting a massive wave of spam lately? They're advertising some new drug every day, and when that starts hitting our bug tracker, it gets through our filters every single hour until we can update the filters. There'll be yet more work installing extra defenses, and making it easier to delete tickets.

Here's hoping we can beat it, or productivity is gone forever.

2011-04-24: Examples to the fore!

We should have done this ages ago. There's now a detailed, commented, instructive example right on the main page. I hope that will answer a lot of questions.

2011-04-14: Declaring victory over site outages

Last month we identified and made a configuration change that our hosting provider required for the server's last operating system upgrade. We haven't had any outages since. Phew!

Finding Everything

Where What
Sales Pitch Why this library should interest you
Using This Site The various services offered by this development site
Download Page Source archives (no binaries; those depend on your individual platform)
FAQ Frequently Asked Questions, and their answers
Online Documentation Wiki and copies of packaged documentation
Packagers Page Information for maintainers of libpqxx packages
Consulting? Where can I get professional development help?
Bug Tracker Known bugs and requests (as in View Tickets option in top button bar)
Reporting Bugs How to report a problem or request a new feature
Mailing Lists Hosted on pgFoundry site
Database Notes Notes and tips about postgres
Performance Tips Figure out and solve performance problems
Other Projects Other open-source development projects hosted here
libpqxx Elsewhere Sites where libpqxx is registered as a project
Author and Contributors Who made all this?

For issues not suitable for the mailing list or bug tickets, contact the author as jtv at xs4all.nl.

Also, you may want to have a look at the other open source projects hosted on this site.

Technical Overview

This library works on top of the C-level API library, libpq. It comes with postgres. You will link your program with both libpqxx and libpq, in that order.

Coding with libpqxx revolves around transactions. Transactions are a central concept in database management systems, but they are widely under-appreciated among application developers. In libpqxx, they're fundamental.

With conventional database APIs, you issue commands and queries to a database session or connection, and optionally create the occasional transaction. In libpqxx you start with a connection, but you do all your SQL work in transactions that you open in your connection. You commit each transaction when it's complete; if you don't, all changes made inside the transaction get rolled back.

There are several types of transactions with various "quality of service" properties; if you don't really want to use transactions at all, one of the available transaction types is called nontransaction. This transaction type provides basic non-transactional behaviour. (This is sometimes called "autocommit": it commits every command right away).

Every command or query returns a result. Your query fetches its result data immediately when you execute it, and stores it in the result. Don't check your result for errors; failures show up as regular C++ exceptions.

Result objects can be kept around for as long as they are needed, completely separate from the connections and transactions that originated them. You can access the rows in a result using standard iterators, or more like an array using numerical indexes. Inside each row you can access the fields by standard iterators, numerical indexes, or using column names.

Examples

Can't have a database example without an Employee table. Here are some things you might want to do with Employee.

For those in a real hurry:

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

int main(int, char *argv[])
{
  pqxx::connection c("dbname=company user=accounting");
  pqxx::work txn(c);
  pqxx::result r = txn.exec(
    "SELECT id FROM Employee WHERE name =" + std::string(argv[1]));
  int employee_id = r[0][0].as<int>();
  std::cout << "Updating employee #" << employee_id << std::endl;
  txn.exec(
    "UPDATE EMPLOYEE "
    "SET salary = salary + 1 "
    "WHERE id = " + txn.quote(employee_id));
  txn.commit();
}
#include <pqxx/pqxx>

pqxx::result get_all_employees(pqxx::transaction_base &txn)
{
  // Execute database query and immediately retrieve results.
  return txn.exec("SELECT id, name, department FROM Employee");
}

void add_employee(pqxx::transaction_base &txn, std::string name, int department)
{
  // Use quote() to escape and quote a value safely for use in a
  // query.  Avoid bugs and security holes when strings contain
  // backslashes, quotes, or other "weird" characters.
  // That's not very useful for department (an integer), but it
  // also represents the value as a string.  We don't want to add
  // an int to the query, we want to add a string that represents
  // the int value.
  txn.exec(
    "INSERT INTO Employee(name, department) "
    "VALUES (" + txn.quote(name) + ", " + txn.quote(department) + ")");
}

Once you have these functions, here's a simple program that prints all employees:

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

pqxx::result retrieve_employees()
{
  // Connect to the database.  Use the default database server,
  // port number, database, and password.  These can be changed
  // by passing a connection string here, or through environment
  // variables defined by libpq.
  pqxx::connection conn;

  // Start a transaction.  With libpqxx we're always working in
  // a transaction.
  pqxx::work txn(conn);

  // Query and return all employees.  Both our connection and our
  // transaction are local, so they'll be destroyed when we exit
  // this function.  That doesn't matter; the result object will
  // still be valid.
  return get_all_employees(txn);
}

int main()
{
  try
  {
    // Result objects are really just smart pointers inside, so
    // copying them around like this is very cheap.
    pqxx::result employees = retrieve_employees();

    // We'll be printing these fields:
    std::cout
      << "ID" << "\t"
      << "Name" << "\t"
      << "Dept." << std::endl;

    // Iterate over the rows in our result set.  Results objects
    // are containers similar to std::vector and such.
    for (
      pqxx::result::const_iterator row = employees.begin();
      row != employees.end();
      ++row)
    {
      // Fields within a row can be accessed by column name.
      // You can also iterate the fields in a row, or index the row
      // by column number just like an array.
      // Values are stored internally as plain strings.  You access
      // them by converting each to the desired C++ type using the
      // "as()" function template.
      std::cout
        << row["id"].as<int>() << "\t"
        << row["name"].as<std::string>() << "\t"
        << row["department"].as<int>()
        << std::endl;
    }

    // Like other C++ containers, pqxx::result has member functions
    // size() and empty().
    if (!employees.empty())
    {
      // If all we want to do is print the field values, without
      // locale formatting to make it suit local human tastes, we
      // could just read the fields as raw C strings using the
      // "c_str()" function, just like we have in std::string.
      // Results are a lot like std::vector, so we can access rows
      // through array indexing if an iterator is too much trouble.
      std::cout
        << "That first employee was:" << std::endl
        << employees[0]["id"].c_str() << "\t"
        << employees[0]["name"].c_str() << "\t"
        << employees[0]["department"].c_str()
        << std::endl;
    }

    // Our result object "employees" is local to this block, so
    // when we leave this scope without copying the object somewhere
    // else, the result's memory is freed up automatically.
  }
  catch (const std::exception &e)
  {
    // There's no need to check our database calls for errors.  If
    // any of them fails, it will throw a normal C++ exception.
    std::cerr << e.what() << std::endl;
    return 1;
  }
}

A program to enter a new employee into the database could look like this:

#include <iostream>
#include <pqxx/pqxx>
#include "my_functions"

int main(int argc, char *argv[])
{
  if (argc != 3)
  {
    std::cerr
      << "Give me an employee name and a department name.  No more, no less."
      << endl;
    return 2;
  }

  try
  {
    int department;

    // Use a libpqxx helper to interpret our department id string as an int.
    pqxx::from_string(argv[2], department);

    pqxx::connection conn;
    pqxx:: work txn(conn);
    add_employee(txn, argv[1], department);

    // Commit.  If we neglect to do this (e.g. because the program crashed)
    // then our changes will be rolled back.
    txn.commit();
  }
  catch (const std::exception &e)
  {
    // Our transaction's destructor has already been called, so our change
    // is implicitly rolled back before we get here.

    std::cerr << e.what() << std::endl;
    return 1;
  }
}

How do you build your program? That depends on your system and compiler. On a typical Unix-like system, you might do:

c++ add_employee.cxx -lpqxx -lpq

This should work on most GNU/Linux systems (Debian, Fedora, Gentoo, Red Hat, Slax, Ubuntu, etc.), BSD systems (FreeBSD, NetBSD, OpenBSD), and so on, as long as you have libpqxx, libpq, and a C++ compiler installed. (If your C++ compiler has a different name on the command line, use that instead of "c++"). It may also work on Apple OS X or Microsoft Windows.


Ohloh Metrics GTF Contributor