qdb_stmt_exec()

Execute a precompiled statement

Synopsis:

#include <qdb/qdb.h>

int qdb_stmt_exec( qdb_hdl_t *hdl,
                   int stmtid,
                   qdb_binding_t *bindings,
                   uint8_t binding_count );

Arguments:

hdl: A pointer to the database handle.
stmtid: The ID of a precompiled statement, returned by qdb_stmt_init().
bindings: An array of qdb_binding_t structures filled in with pointers to data that will be bound in to the variable parameters in the precompiled statement (see "The qdb_binding_t structure").
binding_count: The number of items in bindings.

Library:

qdb

Description:

This function executes a precompiled statement that was previously prepared with qdb_stmt_init(). If the SQL string that was passed to qdb_stmt_init() earlier contains variable parameters, you must bind data to these parameters by placing the data values in one or more qdb_binding_t structures that are passed in through the bindings argument.

The qdb_binding_t structure

The qdb_binding_t structure contains a reference to a data value and a description of the data. This structure has these members:

int index: The index of the variable parameter in the precompiled statement that this data should be bound to. The placeholder is in the form of ?n, where n is a number between 1 and 999.
int type: The type of the data. Can be one of: QDB_NULL, QDB_BLOB, QDB_TEXT, QDB_INTEGER, or QDB_REAL.
int len: The length of the data argument. This number excludes '\0' for QDB_TEXT, and is set to sizeof(double) for QDB_REAL and sizeof(int64_t) for QDB_INTEGER.
void* data: The data to be bound. The data must be the same size as what's set in len, because len bytes are read during the binding. For example, for the QDB_REAL type, whose length is sizeof(double), if you assign a float to data, only half the bytes will be read, resulting in unknown behavior. To correct this, use a double for data.
unsigned long long intcopy: A 64-bit field for holding a copy of integer values. This field may or may not be used, depending on how you initialize the structure.

Binding macros

You should always initialize an instance of qdb_binding_t with one of the convenience macros. It is not recommended to manually set the fields in the binding structure one by one.

In the macro prototypes, bind is the address of the qdb_binding_t structure, i is the index member, which references the statement variable you're binding the data to, t is the type member, l is the len member, and d is the data member:

QDB_SETBIND(bind, i, t, l, d): Bind in any specified data type.
QDB_SETBIND_INT(bind, i, d): Bind in a 64-bit integer; subsequent changes to the same integer variable alter the bound data.
QDB_SETBIND_NULL(bind, i): Bind in NULL.
QDB_SETBIND_TEXT(bind, i, d): Bind in text.
QDB_SETBIND_INTCOPY(bind, i, d): Bind in a copy of an integer; subsequent changes to the same integer variable do not alter the bound data. Also, with this macro only, you can bind data of varying sizes because the intcopy field is used to store a copy of the integer value, and this field can accept assignments from narrower variable types; for example, 32- or even 16-bit integers.
QDB_SETBIND_BLOB(bind, i, d): Bind in a blob.
QDB_SETBIND_REAL(bind, i, d): Bind in a real number.

Array macros

You can create an array of qdb_binding_t structures and use the array convenience macros to fill in the structures with data for the variable parameters in the precompiled statement.

These macros have names of the form QDB_SETARRAYBIND_*, and there is a matching array macro for each non-array macro, with identical parameters. For example, QDB_SETARRAYBIND accepts the same five parameters as QDB_SETBIND, and binds any specified data type. The only semantic difference is that for the array macros, the i argument acts as an index not only for the parameter variable being bound, but also for the array of qdb_binding_t structures.

Suppose you have multiple variable parameters in the statement you prepared with qdb_stmt_init(), and are using an array of structures to bind the data. To bind the first parameter, call one of the macro arrays with the index i set to 1 to fill in the first structure in the array. For the second parameter, use an index of 2, and so on.

Note: There is a limit to the amount of data that can be sent to a database when using qdb_stmt_exec(). This limit is the lesser of the following values:

the limits set by the database
x = 2³¹ - ( binding_count + 1 ) × 12, where x is the data limit, in bytes

Returns:

>0: Success.
-1: An error occurred (errno is set).

Examples:

The following code excerpt highlights the difference between the QDB_SETBIND_INT and QDB_SETBIND_INTCOPY macros:

qdb_binding_t qbind[2];
int64_t i = 17;
QDB_SETARRAYBIND_INT(qbind, 1, i);
QDB_SETARRAYBIND_INTCOPY(qbind, 2, i);
int stmtid = qdb_stmt_init(
           "INSERT INTO testtable (val1, val2) \
                               VALUES (?1, ?2);");

for (i=0; i<10; i++) {
	qdb_stmt_exec(stmtid, qbind, 2);
}

Both bound parameters refer to the local variable i initially; however, the loop uses this variable as the index, causing a different value to be inserted in the first column each time qdb_stmt_exec() runs. The first parameter changes because QDB_SETBIND_INT only stores the variable address (and not the value itself) in the binding structure, so modifying the variable modifies the bound value as well. The second parameter remains unchanged because QDB_SETBIND_INTCOPY makes a copy of the passed-in value. So, the resulting table values are:

val1 | val2
===========
  0  |  17
  1  |  17
  2  |  17
...
  9  |  17

See qdb_stmt_init() for an example on how to compile, execute, and free an SQL statement.

Classification:

QNX Neutrino

Safety:
Interrupt handler	No
Signal handler	No
Thread	Yes