Skip to main content

Chapter 7: CQL Result Sets

Most of this tutorial is about the CQL language itself but here we must diverge a bit. The purpose of the result set feature of CQL is to create a C interface to SQLite data. Because of this there are a lot of essential details that require looking carefully at the generated C code. Appendix 2 covers this code in even more detail but here it makes sense to at least talk about the interface.

Let's say we have this simple stored procedure:

create table foo(id integer not null, b bool, t text);

create proc read_foo(id_ integer not null)
select * from foo where id = id_;

We've created a simple data reader: this CQL code will cause the compiler to generate helper functions to read the data and materialize a result set.

Let's look at the public interface of that result set now considering the most essential pieces.

/* this is almost everything in the generated header file */
#define read_foo_data_types_count 3
read_foo_result_set, \

extern cql_int32 read_foo_get_id(read_foo_result_set_ref
_Nonnull result_set, cql_int32 row);
extern cql_bool read_foo_get_b_is_null(read_foo_result_set_ref
_Nonnull result_set, cql_int32 row);
extern cql_bool read_foo_get_b_value(read_foo_result_set_ref
_Nonnull result_set, cql_int32 row);
extern cql_string_ref _Nullable read_foo_get_t(
read_foo_result_set_ref _Nonnull result_set,
cql_int32 row);
extern cql_int32 read_foo_result_count(read_foo_result_set_ref
_Nonnull result_set);
extern cql_code read_foo_fetch_results(sqlite3 *_Nonnull _db_,
read_foo_result_set_ref _Nullable *_Nonnull result_set,
cql_int32 id_);
#define read_foo_row_hash(result_set, row) \
rowHash((cql_result_set_ref)(result_set), row)
#define read_foo_row_equal(rs1, row1, rs2, row2) \
cql_result_set_get_meta((cql_result_set_ref)(rs1)) \
->rowsEqual( \
(cql_result_set_ref)(rs1), row1, \
(cql_result_set_ref)(rs2), row2)

Let's consider some of these individually now


This declares the data type for read_foo_result_set and the associated object reference read_foo_result_set_ref. As it turns out, the underlying data type for all result sets is the same, and only the shape of the data varies.

extern cql_code read_foo_fetch_results(sqlite3 *_Nonnull _db_,
read_foo_result_set_ref _Nullable *_Nonnull result_set,
cql_int32 id_);

The result set fetcher method gives you a read_foo_result_set_ref if it succeeds. It accepts the id_ argument which it will internally pass along to read_foo(...). The latter function provides a sqlite3_stmt* which can then be iterated in the fetcher. This method is the main public entry point for result sets.

Once you have a result set, you can read values out of it.

extern cql_int32 read_foo_result_count(read_foo_result_set_ref
_Nonnull result_set);

That function tells you how many rows are in the result set.

For each row you can use any of the row readers:

extern cql_int32 read_foo_get_id(read_foo_result_set_ref
_Nonnull result_set, cql_int32 row);
extern cql_bool read_foo_get_b_is_null(read_foo_result_set_ref
_Nonnull result_set, cql_int32 row);
extern cql_bool read_foo_get_b_value(read_foo_result_set_ref
_Nonnull result_set, cql_int32 row);
extern cql_string_ref _Nullable read_foo_get_t(
read_foo_result_set_ref _Nonnull result_set,
cql_int32 row);

These let you read the id of a particular row, and get a cql_int32 or you can read the nullable boolean, using the read_foo_get_b_is_null function first to see if the boolean is null and then read_foo_get_b_value to get the value. Finally the string can be accessed with read_foo_get_t. As you can see, there is a simple naming convention for each of the field readers.

Note: The compiler has runtime arrays that control naming conventions as well as using CamelCasing. Additional customizations may be created by adding new runtime arrays into the CQL compiler.

Finally, also part of the public interface, are these macros:

#define read_foo_row_hash(result_set, row)
#define read_foo_row_equal(rs1, row1, rs2, row2)

These use the CQL runtime to hash a row or compare two rows from identical result set types. Metadata included in the result set allows general purpose code to work for every result set. Based on configuration, result set copying methods can also be generated. When you're done with a result set you can use the cql_release(...) method to free the memory.

Importantly, all of the rows from the query in the stored procedure are materialized immediately and become part of the result set. Potentially large amounts of memory can be used if a lot of rows are generated.

The code that actually creates the result set starting from the prepared statement is always the same. The essential parts are:

First, a constant array that holds the data types for each column.

uint8_t read_foo_data_types[read_foo_data_types_count] = {

All references are stored together at the end of the row, so we only need the count of references and the offset of the first one to do operations like cql_retain or cql_release on the row.

#define read_foo_refs_offset cql_offsetof(read_foo_row, t) // count = 1

Lastly we need metadata to tell us count of columns and the offset of each column within the row.

static cql_uint16 read_foo_col_offsets[] = { 3,
cql_offsetof(read_foo_row, id),
cql_offsetof(read_foo_row, b),
cql_offsetof(read_foo_row, t)

Using the above we can now write this fetcher

sqlite3 *_Nonnull _db_,
read_foo_result_set_ref _Nullable *_Nonnull result_set,
cql_int32 id_)
sqlite3_stmt *stmt = NULL;
cql_profile_start(CRC_read_foo, &read_foo_perf_index);

// we call the original procedure, it gives us a prepared statement
cql_code rc = read_foo(_db_, &stmt, id_);

// this is everything you need to know to fetch the result
cql_fetch_info info = {
.rc = rc,
.db = _db_,
.stmt = stmt,
.data_types = read_foo_data_types,
.col_offsets = read_foo_col_offsets,
.refs_count = 1,
.refs_offset = read_foo_refs_offset,
.rowsize = sizeof(read_foo_row),
.crc = CRC_read_foo,
.perf_index = &read_foo_perf_index,

// this function does all the work, it cleans up if .rc is an error code.
return cql_fetch_all_results(&info, (cql_result_set_ref *)result_set);

Results Sets From OUT UNION

The out keyword was added for writing procedures that produce a single row result set. With that, it became possible to make any single row result you wanted, assembling it from whatever sources you needed. That is an important case as single row results happen frequently and they are comparatively easy to create and pass around using C structures for the backing store. However, it's not everything; there are also cases where full flexibility is needed while producing a standard many-row result set. For this we have out union which was discussed fully in Chapter 5. Here we'll discuss the code generation behind that.

Here’s an example from the CQL tests:

create proc some_integers(start integer not null, stop integer not null)
declare C cursor like select 1 v, 2 v_squared, "xx" some_text;
declare i integer not null;
set i := start;
while (i < stop)
fetch C(v, v_squared, junk) from values (i, i*i, printf("%d", i));
out union C;
set i := i + 1;

In this example the entire result set is made up out of thin air. Of course any combination of this computation or data-access is possible, so you can ultimately make any rows you want in any order using SQLite to help you as much or as little as you need.

Virtually all the code pieces to do this already exist for normal result sets. The important parts of the output code look like this in your generated C.

We need a buffer to hold the rows we are going to accumulate; We use cql_bytebuf just like the normal fetcher above.

// This bit creates a growable buffer to hold the rows
// This is how we do all the other result sets, too
cql_bytebuf _rows_;

We need to be able to copy the cursor into the buffer and retain any internal references

// This bit is what you get when you "out union" a cursor "C"
// first we +1 any references in the cursor then we copy its bits
cql_retain_row(C_); // a no-op if there is no row in the cursor
if (C_._has_row_) cql_bytebuf_append(&_rows_, (const void *)&C_, sizeof(C_));

Finally, we make the rowset when the procedure exits. If the procedure is returning with no errors the result set is created, otherwise the buffer is released. The global some_integers_info has constants that describe the shape produced by this procedure just like the other cases that produce a result set.

(cql_result_set_ref *)_result_set_);

The operations here are basically the same ones that will happen inside of the standard helper cql_fetch_all_results, the difference, of course, is that you write the loop manually and therefore have full control of the rows as they go in to the result set.

In short, the overhead is pretty low. What you’re left with is pretty much the base cost of your algorithm. The cost here is very similar to what it would be for any other thing that make rows.

Of course, if you make a million rows, well, that would burn a lot of memory.

A Working Example

Here's a fairly simple example illustrating some of these concepts including the reading of rowsets.

-- hello.sql:

create proc hello()

create table my_data(
pos integer not null primary key,
txt text not null

insert into my_data values(2, 'World');
insert into my_data values(0, 'Hello');
insert into my_data values(1, 'There');

select * from my_data order by pos;

And this main code to open the database and access the procedure:

// main.c

#include <stdlib.h>
#include <sqlite3.h>

#include "hello.h"

int main(int argc, char **argv)
sqlite3 *db;
int rc = sqlite3_open(":memory:", &db);
if (rc != SQLITE_OK) {
exit(1); /* not exactly world class error handling but that isn't the point */
hello_result_set_ref result_set;
rc = hello_fetch_results(db, &result_set);
if (rc != SQLITE_OK) {
printf("error: %d\n", rc);

cql_int32 result_count = hello_result_count(result_set);

for(cql_int32 row = 0; row < result_count; row++) {
cql_string_ref text = hello_get_txt(result_set, row);
cql_alloc_cstr(ctext, text);
printf("%d: %s\n", row, ctext);
cql_free_cstr(ctext, text);


From these pieces you can make a working example like so:

# ${cgsql} refers to the root directory of the CG-SQL sources
cql --in hello.sql --cg hello.h hello.c
cc -o hello -I ${cgsql}/sources main.c hello.c ${cgsql}/sources/cqlrt.c -lsqlite3

Additional demo code is available in Appendix 10.