Chapter 3. Executing SQL statements
Prev Part I. Core SQL classes Next

Chapter 3. Executing SQL statements

Index

Logging executed SQL
Combining prepare() with execute()
Vector parameters
Fetching SELECT results
Limiting the number of rows fetched
Bulk SELECT fetches
Creating scrollable cursors
Using scrollable cursors
Modifying fetched rows
Batches and row counts
Using mpf_class values from GMP library
#include <x/sql/connection.H>
#include <x/sql/statement.H>

x::sql::connection conn=henv->connect("DSN=dev").first;

x::sql::statement stmt=conn->prepare("INSERT INTO books(name, price) VALUES(?, ?)");

stmt->execute("Around the world in 80 days", 9.99);
stmt->execute("Moby Dick", 9.99);

std::string s="War and Peace";

stmt->execute(s, nullptr);

Invoking a connection object's prepare() creates and returns an x::sql::statement, which is a reference to a reference-counted object, that represents an SQL statement. prepare() does not execute the statement, but may (depending on the database driver) throw an exception if the SQL statement is not acceptable.

execute() executes the SQL statement. Pass any values for placeholders in the SQL statement as additional arguments to execute(). The number of additional parameters to execute() must match the number of parameters in the SQL statement.

The additional parameters can be a mixture of different types: natural integer types, floats, doubles, x::ymd and x::hms (which are converted to equivalent SQL data types), and text strings given either as std::strings or as literal character strings. A nullptr indicates a NULL value for the corresponding placeholder. There's also optional support for passing mpf_class parameters, from the GMP library. 

std::list<std::pair<std::string, x::sql::bitflag>> args;

args.emplace_back({"Through the Looking-Glass", 0});
args.emplace_back({"", 1});

stmt->execute(args);

args.emplace_back({"Alice in Wonderland", 0});
args.emplace_back({"9.99", 0});

stmt->execute(args);

Alternatively, construct a std::list that contains a std::pair of any type that can otherwise be passed to execute(), and a x::sql::bitflag. Setting the x::sql::bitflag value to a non-zero value specifies a NULL; a value of zero in the x::sql::bitflag takes the value of the corresponding parameter from std::pair type.

The above example is equivalent to “stmt->execute("Through the Looking-Glass", nullptr);” and “stmt->execute("Alice in Wonderland", "9.99");”. This approach, of course, is naturally restricted to a parameters of the same type, however:

The std::pair is optional when NULL values are not needed. Just declare a std::list with the parameter type. The previous example is equivalent to: 

std::list<std::string> args;

args.emplace_back("Through the Looking-Glass");

stmt->execute(args, 9.99);

In all cases, the number of parameters passed as standalone values, or in std::lists must be equal to the number of placeholders in the SQL statement.

An SQL error results in prepare() or execute() throwing an exception. 

size_t n=stmt->num_params();

std::vector<x::sql::statement::base::parameter>
    params=stmt->get_parameters();

for (const auto &param:params)
{
    std::cout << param.data_type << "(" << param.parameter_size;

    if (param.decimal_digits)
        std::cout << "," << param.decimal_digits;
    std::cout << ")";

    if (param.nullable)
        std::cout << " nullable";
    std::cout << std::endl;
}

num_params() returns the number of parameters the SQL statement requires for execution. get_parameters() returns a vector of x::sql::statement::base::parameters that describe the statement's parameters.

Note

Not all database drivers implement get_parameters().

An x::sql::statement saves an internal reference on its x::sql::connection. If the x::sql::connection is no longer needed, it can go out of the application's scope. When the last reference on the x::sql::statement goes out of scope and it gets destroyed, the last reference to the x::sql::connection goes out of scope, and the connection to the database gets automatically closed. 

#include <x/sql/connection.H>
#include <x/sql/statement.H>

x::sql::connection conn=henv->connect("DSN=dev").first;

x::sql::statement stmt=conn->execute_directly("INSERT INTO books(name, price) VALUES('Around the world in 80 days', 9.99)");

execute_directly() executes a literal SQL statement without any parameters, returning a x::sql::statement only for the purpose of invoking row_count() and more(). Although it's possible to prepare() and execute() skipping all parameter-related setup, some database drivers may be problematic with this approach. At press time, for example, the Postgres ODBC connector encounters an internal error if a SAVEPOINT command gets execute(). It is necessary to execute_directly() all SAVEPOINT commands.

Logging executed SQL

All executed SQL is logged by the x::sql::execute pseudo-class at the debug level. Setting the “x::sql::execute::@log::level” LIBCXX application property logs literal SQL according to the application's logging configuration. If the application uses LIBCXX's option parsing classes, passing the “--set-property x::sql::execute::@log::level=debug” parameter logs all SQL (by default, to standard error).

Prev Up Next
Determining SQL database driver capabilities Table Of Contents Combining prepare() with execute()