wiki:EmployeeExample

A nontrivial example: Employee table

Here are some functions that do practically useful things to employees:

#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;
  }
}
Last modified 6 years ago Last modified on Aug 8, 2011, 4:46:26 AM