SQLBOX_STEP(3)

NAME

sqlbox_step — execute a prepared statement

SYNOPSIS

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

const struct sqlbox_parmset *
sqlbox_step(struct sqlbox *box, size_t id);

Executes a statement id as returned by sqlbox_prepare_bind(3). Typically run for returned rows in query statements. If id is zero, the last prepared statement is used. Returns a result set with the following fields:

code: Either SQLBOX_CODE_OK if the query executed or SQLBOX_CODE_CONSTRAINT on a constraint violation if sqlbox_prepare_bind(3) is passed SQLBOX_STMT_CONSTRAINT.
ps: The results (columns) themselves or NULL if there are no results.
psz: The number of columns in the results. If this is zero and code is SQLBOX_CODE_OK, this means that there are no more rows. This applies both to the last step of a SELECT statement or any non-returning statements like CREATE TABLE.

Result columns consist of the following fields:

sz: The size of the data in bytes. For strings, this includes the NUL terminator.
type: May be one of SQLBOX_PARM_FLOAT for a double-precision floating point value, SQLBOX_PARM_INT for a signed 64-bit integer, SQLBOX_PARM_STRING for a NUL-terminated string, SQLBOX_PARM_BLOB for a binary array, or SQLBOX_PARM_NULL for a value-less "null" result.
fparm, iparm, sparm, bparm: Values corresponding to the float, integer, string, and binary types.

The result set as a whole, including the memory allocated for each sparm and bparm column, is only valid until the next sqlbox_step() or sqlbox_finalise(3).

When there are no more rows available, sqlbox_rebind(3) or sqlbox_finalise(3) are usually used.

Filtering

If the configuration passed to sqlbox_alloc(3) has filters, they will be used prior to returning a value. The filter structure has the following fields:

col: Column in result set (starting from zero) to manipulate. If this does not refer to a valid column, it's silently ignored.
filt: Actual filter function. This function must set all values, including the size. It may use any type. If the function returns zero, the system will exit. This may not be NULL. If the function needs to allocate memory, such as for setting a string or binary data, it should set the void pointer to what should be passed into the free function.
free: An optional function for freeing memory given to the pointer of filt.
stmt: The applicable statement index starting at zero. This must be a valid statement.
type: Currently only SQLBOX_FILT_GEN_OUT, for a generative filter coming out of the database.

If a filter is defined for a statement's result column, it is run in lieu of database retrieval.

SQLite3 Implementation

Uses sqlite3_step(3) to step through, randomly backing off on return of SQLITE_BUSY, SQLITE_LOCKED, or SQLITE_PROTOCOL, and failing if not returning SQLITE_DONE or SQLITE_ROW. Handling of SQLITE_CONSTRAINT depends upon whether constraint violations are allowed when sqlbox_prepare_bind(3) was invoked.

RETURN VALUES

Returns the results of the query or NULL on failure. Failure covers situations like failure to communicate with the box, stepping after the last result has been returned, or database error. Constraint violations are allowed (with the appropriate code set) only when stipulated by sqlbox_prepare_bind(3). If sqlbox_step() 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. It assumes that the bar column has been initialised as an SQL type INTEGER.

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

/* The result will have zero columns. */

if (!sqlbox_finalise(p, stmtid))
  errx(EXIT_FAILURE, "sqlbox_finalise");

sqlbox_free(p);

The following extracts and prints rows using a SELECT statement. It assumes an existing 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 *)"SELECT * FROM foo" }
};
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");

for (i = 0; i < res->psz; i++)
  switch (res->ps[i].type) {
    case SQLBOX_PARM_BLOB:
      printf("Blob: %zu bytes\n", res->ps[i].sz);
      break;
    case SQLBOX_PARM_FLOAT:
      printf("Float: %f\n", res->ps[i].fparm);
      break;
    case SQLBOX_PARM_INT:
      printf("Blob: %" PRId64 "\n", res->ps[i].iparm);
      break;
    case SQLBOX_PARM_NULL:
      printf("Null\n");
      break;
    case SQLBOX_PARM_STRING:
      printf("String: %s\n", res->ps[i].sparm);
      break;
  }

if (!sqlbox_finalise(p, stmtid))
  errx(EXIT_FAILURE, "sqlbox_finalise");

sqlbox_free(p);

The following example shows how filters may be used to change the outcome of database queries. First, define a filter, in this one that generates an integer:

static int
filter_int(struct sqlbox_parm *p, void **arg)
{
  p->type = SQLBOX_PARM_INT;
  p->iparm = 20;
  return 1;
}

Next, assign the filter for use. This uses the asynchronous versions of functions when possible. It also assumes that the database db.db is already populated with at least a single integer column.

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 *)"SELECT * FROM foo" }
};
struct sqlbox_filt	 filts[] = {
  { .col = 0,
    .stmt = 0,
    .type = SQLBOX_FILT_GEN_OUT,
    .filt = filter_int,
    .free = NULL }
};
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;
cfg.filts.filtsz = 1;
cfg.filts.filts = filts;

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_prepare_bind_async(p, 0, 2, 0, NULL, 0))
  errx(EXIT_FAILURE, "sqlbox_prepare_bind_async");
if ((res = sqlbox_step(p, 0)) == NULL)
  errx(EXIT_FAILURE, "sqlbox_step");
if (res->psz < 1)
  errx(EXIT_FAILURE, "not enough columns");
if (res->ps[0].type == SQLBOX_PARM_INT)
  printf("Integer: %" PRId64 "\n", res->ps[i].iparm);

/* That should have printed 20. */

sqlbox_free(p);

NAME

LIBRARY

SYNOPSIS

DESCRIPTION

Filtering

SQLite3 Implementation

RETURN VALUES

EXAMPLES

SEE ALSO