Creating scrollable cursors

auto capabilities=conn->config_get_static_cursor_attributes1();

bool has_absolute_positioning=capabilities.count("SQL_CA1_ABSOLUTE");
bool has_relative_positioning=capabilities.count("SQL_CA1_RELATIVE");
bool has_bookmarks=capabilities.count("SQL_CA1_BOOKMARK");

x::sql::newstament newstatement=conn->create_newstatement();

newstatement->option("CURSOR_TYPE", "STATIC");

if (has_bookmarks)
    newstatement->option("BOOKMARKS", "ON");

x::sql::stament statement=newstatement->prepare("SELECT title, price from books order by stock_id");

statement->execute();

Not all database drivers support all types of cursors, and cursor positioning capabilities. Use config_get_dynamic_cursor_attributes1(), config_get_forward_only_cursor_attributes1(), config_get_keyset_cursor_attributes1(), or config_get_static_cursor_attributes1() to determine which cursor types are supported to which extent. The SQL_CA1_ABSOLUTE flag indicates that x::sql::fetch::first, x::sql::fetch::last, and x::sql::fetch::absolute scrolling directions are supported. The SQL_CA1_RELATIVE flag indicates that x::sql::fetch::prior, and x::sql::fetch::relative scrolling directions are supported. The SQL_CA1_BOOKMARKS flag indicates that bookmarks can be enabled, and that the x::sql::fetch::atbookmark scrolling direction is supported.

Cursor are enabled by calling a connection object's create_newstatement() which returns an x::sql::newstatement. This is an intermediate object that sets non-default statement options before preparing or executing a new x::sql::statement. It can be considered as a statement factory. option() sets non-default statement options, and prepare() prepares a new statement, using the previously-set options.

x::sql::statement statement=newstatement->execute("SELECT title, price from books where title like ? order by stock_id",
    "War%");

execute() and execute_vector() is equivalent to calling prepare(), then execute() or execute_vector() on the new x::sql::statement object.

Calling x::sql::connection's prepare(), execute(), or execute_vector() is equivalent to calling create_newstatement(), followed by prepare(), execute(), or execute_vector() without setting any non-default options.

std::map<std::string, std::string> options;

if (has_bookmarks)
    options["BOOKMARKS"]="ON";

x::sql::statement statement=
    conn->create_newstatement("CURSOR_TYPE", "STATIC", options)
        ->prepare("SELECT title, price from books order by stock_id");

create_newstatement() takes an optional list of explicit settings. This is equivalent to constructing a x::sql::newstatement, then using option() to set the given options. create_newstatement() takes a variadic list of settings either as an explicit name/value tuple, or as a string std::map, whose key/value tuples correspond to option names and values.

The options are:

BOOKMARKS

Setting this option to ON enables bookmarks.

CURSOR_TYPE

Specifies the type of the SELECT cursor. See the ODBC documentation for a more complete description of each cursor type. The possible values are:

FORWARD

The default FORWARD cursor is an ordinary SELECT that returns a non-scrollable row sequence.

STATIC

A scrollable, static resultset of rows.

DYNAMIC

A scrollable resultset that reflects any concurrent changes to the underlying rows, during scrolling.

KEYSET(n)

A combination of a static and a dynamic cursor. n specifies the number of rows in the keyset.

newstatement->option("CURSOR_TYPE", "KEYSET(100)");

This example creates a cursor with a 100 row keyset. If the keyset size is equal or greater than the number of rows in the entire resultset, a keyset cursor becomes equivalent of a static cursor. When the keyset's size is less than the resultset's size, the keyset cursor becomes equivalent to a static cursor for the number of rows in the keyset. When the cursor gets scrolled beyond the end of the keyset, a new keyset gets created, to cover the next subset of rows in the resultset.