How to use pqxx::stateless_cursor class from libpqxx?

Question

I'm learning libpqxx, the C++ API to PostgreSQL. I'd like to use the pqxx::stateless_cursor class, but 1) I find the Doxygen output unhelpful in this case, and 2) the pqxx.org website has been down for some time now.

Anyone know how to use it?

I believe this is how I construct one:

pqxx::stateless_cursor <pqxx::cursor_base::read_only, pqxx::cursor_base::owned>
    cursor( work, "SELECT * FROM mytable", ?, ? );

The last two parms are called cname and hold, but are not documented.

And once the cursor is created, how would I go about using it in a for() loop to get each row, one at a time?

Lase

Answer 1

Thanks @Eelke for the comments on cname and hold.

I figured out how to make pqxx::stateless_cursor work. I have no idea if there is a cleaner or more obvious way but here is an example:

pqxx::work work( conn );
pqxx::stateless_cursor<pqxx::cursor_base::read_only, pqxx::cursor_base::owned>
    cursor( work, "SELECT * FROM mytable", "mycursor", false );

for ( size_t idx = 0; true; idx ++ )
{
    pqxx::result result = cursor.retrieve( idx, idx + 1 );
    if ( result.empty() )
    {
        // nothing left to read
        break;
    }

    // Do something with "result" which contains a single
    // row in this example since we told the cursor to
    // retrieve row #idx (inclusive) to idx+1 (exclusive).
    std::cout << result[ 0 ][ "name" ].as<std::string>() << std::endl;
}

Lase

Answer 2

I do not know the pqxx library but based on the underlying DECLARE command of postgresql I would guess

That cname is the name of the cursor, so it can be anything postgresql normally accepts as a cursor name.

That hold refers to the WITH HOLD option of a cursor, from the docs:

WITH HOLD specifies that the cursor can continue to be used after the transaction that created it successfully commits. WITHOUT HOLD specifies that the cursor cannot be used outside of the transaction that created it. If neither WITHOUT HOLD nor WITH HOLD is specified, WITHOUT HOLD is the default.

Tajuanatak

Answer 3

Here's another cursor example, using a do-while() loop:

     const std::conStr("user=" + opt::dbUser + " password=" + opt::dbPasswd + " host=" + opt::dbHost + " dbname=" + opt::dbName);                                            

      pqxx::connection conn(connStr);
      pqxx::work txn(conn);
      std::string selectString = "SELECT id, name FROM table_name WHERE condition";

      pqxx::stateless_cursor<pqxx::cursor_base::read_only, pqxx::cursor_base::owned> 
      cursor(txn, selectString, "myCursor", false);

      //cursor variables
      size_t idx = 0;       //starting location
      size_t step = 10000;  //number of rows for each chunk
      pqxx::result result;
      do{
        //get next cursor chunk and update the index
        result = cursor.retrieve( idx, idx + step );
        idx += step;

        size_t records = result.size();
        cout << idx << ": records pulled = " << records << endl;

        for( pqxx::result::const_iterator row : result ){
          //iterate over cursor rows
        }
      }
      while( result.size() == step ); //if the result.size() != step, we're on our last loop
      cout << "Done!" << endl;

Rolph