Chapter 6: Calling Procedures Defined Elsewhere

CQL generally doesn't see the whole world in one compilation. In this way it's a lot more like, say, the C compiler than it is like, say, Java or C# or something like that. This means several things:

You don't have to tell CQL about all your schema in all your files, so particular stored procs can be more encapsulated
You can have different databases mounted in different places and CQL won't care; you provide the database connection to the stored procedures when you call them, and that database is assumed to have the tables declared in this translation unit
Several different schema can be maintained by CQL, even in the same database, and they won't know about each other

To make this possible there are a few interesting features

Declaring Procedures Defined Elsewhere

Stored procedures defined in another file can be declared to CQL in various ways for each major type of stored procedure. These are covered in the sections below.

Simple Procedures (database free):

DECLARE PROCEDURE foo(id integer, out name text not null);

This introduces the symbol name without providing the body. This has important variations.

Procedures that use the database

DECLARE PROCEDURE foo(id integer, out name text not null) USING TRANSACTION;

Most procedures you write will use SQLite in some fashion, maybe a select or something. The USING TRANSACTION annotation indicates that the proc in question uses the database and therefore the generated code will need a database connection in-argument and it will return a SQLite error code.

Procedures that create a result set

If the procedure in question is going to use select or call to create a result set, the type of that result set has to be declared. An example might look like this:

DECLARE PROC with_result_set () (id INTEGER NOT NULL,
                                 name TEXT,
                                 rate LONG INTEGER,
                                 type INTEGER,
                                 size REAL);

This says that the procedure takes no arguments (other than the implicit database connection) and it has an implicit out-argument that can be read to get a result set with the indicated columns: id, name, rate, type, and size. This form implies USING TRANSACTION.

Procedures that return a single row with a value cursor

If the procedure emits a cursor with the OUT statement to produce a single row then it can be declared as follows:

DECLARE PROC with_result_set () OUT (id INTEGER NOT NULL,
                                     name TEXT,
                                     rate LONG INTEGER,
                                     type INTEGER,
                                     size REAL);

This form can have USING TRANSACTION or not, since it is possible to emit a row with a value cursor and never use the database. See the previous chapter for details on the OUT statement.

Procedures that return a full result set

If the procedure emits many rows with the OUT UNION statement to produce a full result set then it can be declared as follows:

DECLARE PROC with_result_set () OUT UNION (id INTEGER NOT NULL,
                                     name TEXT,
                                     rate LONG INTEGER,
                                     type INTEGER,
                                     size REAL);

This form can have USING TRANSACTION or not, since it is possible to emit a rows with a value cursor and never use the database. See the previous chapter for details on the OUT UNION statement.

Exporting Declared Symbols Automatically

To avoid errors, the declarations for any given file can be automatically created by adding something like --generate_exports to the command line. This will require an additonal file name to be passed in the --cg portion to capture the exports.

That file can then be used with #include when you combine the C pre-processor with CQL as is normally done.

Nomenclature is perhaps a bit weird here. You use --generate_exports to export the stored procedure declarations from the translation units. Of course those exported symbols are what you then import in some other module. Sometimes this output file is called foo_imports.sql because those exports are of course exactly what you need to import foo. You can use whatever convention you like of course, CQL doesn't care. The full command line might look something like this:

cql --in foo.sql --cg foo.h foo.c foo_imports.sql --generate_exports

Using the pre-processor you can get declarations from elsewhere with a directive like this:

#include "foo_imports.sql"

Declaration Examples

Here are some more examples directly from the CQL test cases; these are all auto-generated with --generate_exports.

DECLARE PROC test (i INTEGER NOT NULL);

DECLARE PROC out_test (OUT i INTEGER NOT NULL, OUT ii INTEGER);

DECLARE PROC outparm_test (OUT foo INTEGER NOT NULL) USING TRANSACTION;

DECLARE PROC select_from_view () (id INTEGER NOT NULL, type INTEGER);

DECLARE PROC make_view () USING TRANSACTION;

DECLARE PROC copy_int (a INTEGER, OUT b INTEGER);

DECLARE PROC complex_return ()
  (_bool BOOL NOT NULL,
   _integer INTEGER NOT NULL,
   _longint LONG INTEGER NOT NULL,
   _real REAL NOT NULL,
   _text TEXT NOT NULL,
   _nullable_bool BOOL);

DECLARE PROC outint_nullable (
  OUT output INTEGER,
  OUT result BOOL NOT NULL)
USING TRANSACTION;

DECLARE PROC outint_notnull (
  OUT output INTEGER NOT NULL,
  OUT result BOOL NOT NULL)
USING TRANSACTION;

DECLARE PROC obj_proc (OUT an_object OBJECT);

DECLARE PROC insert_values (
  id_ INTEGER NOT NULL,
  type_ INTEGER)
  USING TRANSACTION;

So far we've avoided discussing the generated C code in any details but here it seems helpful to show exactly what these declarations correspond to in the generated C to demystify all this. There is a very straightforward conversion.

void test(cql_int32 i);

void out_test(
  cql_int32 *_Nonnull i,
  cql_nullable_int32 *_Nonnull ii);

cql_code outparm_test(
  sqlite3 *_Nonnull _db_,
  cql_int32 *_Nonnull foo);

cql_code select_from_view_fetch_results(
  sqlite3 *_Nonnull _db_,
  select_from_view_result_set_ref _Nullable *_Nonnull result_set);

cql_code make_view(sqlite3 *_Nonnull _db_);

void copy_int(cql_nullable_int32 a, cql_nullable_int32 *_Nonnull b);

cql_code complex_return_fetch_results(
  sqlite3 *_Nonnull _db_,
  complex_return_result_set_ref _Nullable *_Nonnull result_set);

cql_code outint_nullable(
  sqlite3 *_Nonnull _db_,
  cql_nullable_int32 *_Nonnull output,
  cql_bool *_Nonnull result);

cql_code outint_notnull(
  sqlite3 *_Nonnull _db_,
  cql_int32 *_Nonnull output,
  cql_bool *_Nonnull result);

void obj_proc(
  cql_object_ref _Nullable *_Nonnull an_object);

cql_code insert_values(
  sqlite3 *_Nonnull _db_,
  cql_int32 id_,
  cql_nullable_int32 type_);

As you can see, these declarations use exactly the normal SQLite types and so it is very easy to declare a procedure in CQL and then implement it yourself in straight C, simply by conforming to the contract.

Importantly, SQLite does not know anything about CQL stored procedures, or anything at all about CQL really so CQL stored procedure names cannot be used in any way in SQL statements. CQL control flow like the call statement can be used to invoke other procedures and results can be captured by combing the OUT statement and a DECLARE CURSOR construct but SQLite is not involved in those things. This is another place where the inherent two-headed nature of CQL leaks out.

Finally, this is a good place to reinforce that procedures with any of the structured result types (select, out, out union) can be used with a suitable cursor.

create procedure get_stuff()
begin
  select * from stuff;
end;

Can be used in two interesting ways:

create procedure meta_stuff(meta bool)
begin
  if meta then
    call get_stuff();
  else
    call get_other_stuff();
  end if;
end;

Assuming that get_stuff and get_other_stuff have the same shape, then this procedure simply passes on one or the other's result set unmodified as its own return value.

But you could do more than simply pass on the result.

create procedure meta_stuff(meta bool)
begin
  declare C cursor for call get_stuff();  -- or get_meta_stuff(...)
  loop fetch C
  begin
     -- do stuff with C
     -- may be out union some of the rows after adjustment even
  end;
end;

Here we can see that we used the procedure to get the results and then process them directly somehow.

And of course the result of an OUT can similarly be processed using a value cursor, as previously seen.

These combinations allow for pretty general composition of stored procedures so long as they are not recombined with SQLite statements.

Declaring Procedures Defined Elsewhere​

Simple Procedures (database free):​

Procedures that use the database​

Procedures that create a result set​

Procedures that return a single row with a value cursor​

Procedures that return a full result set​

Exporting Declared Symbols Automatically​

Declaration Examples​