SQLBOX_EXEC(3) Library Functions Manual SQLBOX_EXEC(3)

sqlbox_exec, sqlbox_exec_asyncexecute a statement with bound parameters

library “sqlbox”

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

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

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

Executes an SQL statement. It is short-hand for sqlbox_prepare_bind(3), sqlbox_step(3), and sqlbox_free(3), This should not be used to process any rows: if the SQL statement returns rows, they are discarded and the operation is considered a success. See sqlbox_prepare_bind(3) for a description of the arguments. The SQLBOX_STMT_MULTI argument value for flags is always ignored with these functions.

The synchronous () returns whether the operation succeeded while () only returns whether box was accessed, leaving an explicit check to sqlbox_ping(3) or implicit with the next box operation.

If passed a psz of zero, this invokes sqlite3_exec(3). Otherwise, prepares with sqlite3_prepare_v2(3), binds parameters with the sqlite3_bind_blob(3) family, executes with sqlite3_step(3), then frees with sqlite3_finalize(3).

sqlbox_exec() returns SQLBOX_CODE_ERROR 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, the current role cannot access the given statement, the statement violated the constraint, the statement could not be executed, or the database raises errors. Otherwise it returns SQLBOX_CODE_OK, including if the statement returns rows (which are discarded). In addition to these, it will also return will also return SQLBOX_CODE_CONSTRAINT on constraint violation when SQLBOX_STMT_CONSTRAINT has been specified.

sqlbox_exec_async() returns zero if strings are not NUL-terminated at their size (if non-zero), memory allocation fails, or communication with bo fails. Otherwise it returns the non-zero.

Execution of sqlbox_exec_async() is asynchronous: to check whether the operation succeeded, explicitly use sqlbox_ping(3). If it fails, subsequent access to box will fail.

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);

Creating and populating a database in the fastest way possible may be accomplished with asynchronous operations:

struct sqlbox *p;
struct sqlbox_cfg cfg;
struct sqlbox_src srcs[] = {
  { .fname = (char *)":memory:",
    .mode = SQLBOX_SRC_RW }
};
struct sqlbox_pstmt pstmts[] = {
  { .stmt = (char *)"CREATE TABLE foo (col INT)" },
  { .stmt = (char *)"INSERT INTO foo (col) VALUES (?)" },
};
struct sqlbox_parm parms[] = {
  { .type = SQLBOX_PARM_INT,
    .iparm = 20 },
};

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

if ((p = sqlbox_alloc(&cfg)) == NULL)
  errx(EXIT_FAILURE, "sqlbox_alloc");
if (!sqlbox_open_async(p, 0))
  errx(EXIT_FAILURE, "sqlbox_open_async");
if (!sqlbox_exec_async(p, 0, 0, 0, NULL))
  errx(EXIT_FAILURE, "sqlbox_exec_async");
if (!sqlbox_exec_async(p, 0, 1, 1, parms))
  errx(EXIT_FAILURE, "sqlbox_exec_async");

sqlbox_free(p);

sqlbox_finalise(3), sqlbox_open(3)

December 2, 2023 OpenBSD 7.4