qdb_binding_t

Information to bind data to an SQL statement variable

Synopsis:

#include <qdb/qdb.h>

typedef struct {
    int index;
    int type;
    int len;
    const void *data;
    unsigned long long intcopy;
} qdb_binding_t; 

Data:

index
The index of the variable parameter that this data should be bound to in the prepared statement. The placeholder is in the form of ?n, where n is a number between 1 and 999.
type
The data type, one of: QDB_NULL, QDB_BLOB, QDB_TEXT, QDB_INTEGER, or QDB_REAL.
len
The length of the data argument. This number excludes the null-terminator for QDB_TEXT, and is set to sizeof(double) for QDB_REAL and sizeof(int64_t) for QDB_INTEGER.
data
The data to bind. 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.
intcopy
A 64-bit field for holding a copy of integer values. This field may or may not be used.

Library:

qdb

Description:

The qdb_binding_t structure stores the information needed to bind a data value to a variable parameter in a prepared SQL statement. This information includes fields describing the data type and length as well as a reference to the data value.

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.

The variables in the macro prototypes have these meanings:

The following macros can be used to define a single data-binding structure:

QDB_SETBIND(bind, i, t, l, d)
Bind any specified data type.
QDB_SETBIND_INT(bind, i, d)
Bind a 64-bit integer; subsequent changes to the same integer variable alter the bound data.
QDB_SETBIND_NULL(bind, i)
Bind NULL.
QDB_SETBIND_TEXT(bind, i, d)
Bind text.
QDB_SETBIND_INTCOPY(bind, i, d)
Bind 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 a blob.
QDB_SETBIND_REAL(bind, i, d)
Bind a real number.

The single-structure macros are useful only when your statement has one variable. In this case, you can just pass in the address of the structure when calling qdb_stmt_exec() (and set binding_count to 1 to indicate there's only one binding item). If you need to define multiple variables, you must declare an array of qdb_binding_t structures and then fill in the individual array entries. You can do this by using the array-based data-binding macros.

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

Suppose you're using an array of binding structures to assign data to multiple variable parameters in an SQL statement. 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.

After you've defined the data for the statement variables, you can execute the SQL statement by calling qdb_stmt_exec(), passing in the statement ID and a reference to the array of qdb_binding_t structures. Any variables that aren't defined are interpreted as NULL.

Note:

There's a limit to the amount of data that can be sent to a database with qdb_stmt_exec(). This limit is the lesser of the following values:

  • the limit set by the database
  • x = 231 - ( binding_count + 1 ) × 12, where x is the data limit, in bytes

Examples:

The following code sample shows 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.