[Libpqxx-general] Prepared statements, cursors, and quoting

[Scott Gifford]

Hello,

I've been using libpqxx for a few weeks now with good success.  I'm
working on converting some queries which have been getting results in
a pqxx::result to use pqxx::icursorstream::icursorstream instead, so
they can handle large result sets (millions of records) without
running out of memory.

When possible, I like to use SQL prepared statements, mostly because
they make seperation between SQL code and data clearer, which helps
protect against mistakes that could lead to SQL injection from
user-provided strings.

With pqxx::result, I've been using prepared statements like this:

      pqxx::connection pqconn("dbname=lx_historical"),
      pqxx::work xact(pqconn, "SelectCells");
      pqxx::result qresults = xact.prepared("sel_command")(arg1)(arg2);

I wasn't able to find a way to use a prepared statement to create a
cursor.  pqxx::prepare::invocation doesn't seem to have a method to
return a cursor instead of a pqxx::result object, and
pqxx::icursorstream::icursorstream doesn't seem to have a constructor
that takes any kind of prepared statement.

Is it possible to use a prepared statement with a cursor?

Instead, I've been constructing the SQL queries as PGSTD::string
objects.  I'm then passing these strings to the the constructor of a
locally defined class, which creates a transaction and then passes the
string to the cursor's constructor.  It's convenient for objects of
this class to "own" the cursor object, so it is automatically
destroyed when they are destroyed, which is why it's created inside
the object instead of passed in.

However, I'm having some trouble figuring out how to quote strings
like this.  The string escaping mechanism seems to be the "esc" method
of pqxx::transaction_base, but since I'm constructing the query string
before starting the transaction I don't have a way to access this.  Is
there another way?

Thanks!

----ScottG.