SQLBOX_PREPARE

NAME

sqlbox_prepare_bind, sqlbox_prepare_bind_async — prepare a statement and bind parameters

SYNOPSIS

#include <stdint.h>
#include <sqlbox.h>

size_t
sqlbox_prepare_bind(struct sqlbox *box, size_t src, size_t idx, size_t psz, const struct sqlbox_parm *ps, unsigned long flags);

int
sqlbox_prepare_bind_async(struct sqlbox *box, size_t src, size_t idx, size_t psz, const struct sqlbox_parm *ps, unsigned long flags);

DESCRIPTION

Prepares an SQL statement idx with bound parameters ps for further execution by sqlbox_step(3).

The synchronous sqlbox_prepare_bind() returns the identifier while sqlbox_prepare_bind_async() only returns whether box was accessed. It is used for implicit identifiers (i.e., an identifier of zero) in subsequent operations on the statement.

The idx is the statement index in the struct sqlbox_pstmt array given to sqlbox_alloc(3). The src is the database source as returned by sqlbox_open(3). If src is zero, the last-opened database is used. The number of parameters psz must match the prepared statement parameters given in the SQL or be zero.

Floating point numbers bound as NAN (not a number) are interpreted as NULL values by the underlying data store. Infinite values are passed through.

The ps array, if psz is non-zero, consists of typed values bound to those parameters. Types must correspond to typed values, for example, floats of type SQLBOX_PARM_FLOAT must be set in the fparm of struct sqlbox_parm.

The sz of struct sqlbox_parm is ignored for floats and integers, but must be provided for blobs and optionally for strings. For strings, a zero indicates it should be computed using strlen(3). Otherwise, it should be the full string length including the NUL terminator. If the string is shorter than the given length (i.e., contains an embedded NUL terminator), the database will still return the full given length of the original size (terminating NUL inclusive).

The flags may be zero or one of SQLBOX_STMT_CONSTRAINT for subsequent sqlbox_step(3) to allow constraint violations or SQLBOX_STMT_MULTI to allow the server to asynchronously fetch rows in advance of sqlbox_step(3). If zero, each sqlbox_step(3) is a round-trip synchronous call to access the next row of data where constraint violations are considered database errors.

sqlbox_prepare_bind() returns a non-zero identifier for later use by sqlbox_step(3) and sqlbox_finalise(3).

Statements are automatically finalised when the database is closed or the system exits. The former case of sqlbox_close(3) without finalised statements will result in an error.

SQLite3 Implementation

Prepares the statement with sqlite3_prepare_v2(3), randomly backing off if returning SQLITE_BUSY, SQLITE_LOCKED, or SQLITE_PROTOCOL, otherwise failing if not SQLITE_OK. Parameters are bound with the sqlite3_bind_blob(3) family.

RETURN VALUES

Returns zero if strings are not NUL-terminated at their size (if non-zero), memory allocation fails, communication with box fails, the statement could not be prepared due to an illegal statement or database value, the current role cannot prepare the given statement, or the database itself raises an error. Otherwise it returns the >0 statement identifier.

If sqlbox_prepare_bind() fails, box is no longer accessible beyond sqlbox_ping(3) and sqlbox_free(3).

EXAMPLES

The following opens a database db.db and inserts an integer value into it. It depends upon sqlbox_free(3) to close out the database.

size_t dbid, stmtid;
struct sqlbox *p;
struct sqlbox_cfg cfg;
struct sqlbox_src srcs[] = {
  { .fname = (char *)"db.db",
    .mode = SQLBOX_SRC_RW }
};
struct sqlbox_pstmt pstmts[] = {
  { .stmt = (char *)"INSERT INTO foo (bar) VALUES (?)" },
};
struct sqlbox_parm parms[] = {
  { .iparm = 10,
    .type = SQLBOX_PARM_INT },
};
const struct sqlbox_parmset *res;

memset(&cfg, 0, sizeof(struct sqlbox_cfg));
cfg.msg.func_short = warnx;
cfg.srcs.srcsz = 1;
cfg.srcs.srcs = srcs;
cfg.stmts.stmtsz = 1;
cfg.stmts.stmts = pstmts;

if ((p = sqlbox_alloc(&cfg)) == NULL)
  errx(EXIT_FAILURE, "sqlbox_alloc");
if (!(dbid = sqlbox_open(p, 0)))
  errx(EXIT_FAILURE, "sqlbox_open");
if (!(stmtid = sqlbox_prepare_bind(p, dbid, 1, 1, parms, 0)))
  errx(EXIT_FAILURE, "sqlbox_prepare_bind");
if ((res = sqlbox_step(p, stmtid)) == NULL)
  errx(EXIT_FAILURE, "sqlbox_step");
if (!sqlbox_finalise(p, stmtid))
  errx(EXIT_FAILURE, "sqlbox_finalise");

sqlbox_free(p);