SQL Procedures, Stored Procedures and Stored Variables: a quick intro

Basic Definitions and Concepts

1. SQL Statement

   The minimal execution unit allowed by SQL syntax; a stand-alone SQL directive to be atomically executed in a single step. Examples:

   SELECT id, name, measure FROM some_table;
   ------------
   DELETE FROM some_table WHERE measure > 1000;
   ------------
   UPDATE some_table SET measure = measure / 10.0
   

   Note: SQL Statements are usually expected to be explicitly terminated by a semicolon (;).
   The semicolon terminator can be safely suppressed only when the SQL Statement is immediately followed by a string terminator (leading and trailing sequences of all blanks will be always ignored).

2. SQL Script

   A more or less complex sequence of SQL Statements, possibly including interleaved Comments and/or Dot Macros.
   SQL Scripts are intended to be executed in a single monolithic pass (atomic or not, depending on internal Transaction handling). Example:

   .echo on
   
   --
   -- starting a Transaction
   --
   BEGIN;
   
   --
   -- creating a table
   --
   CREATE TABLE example (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     name TEXT NOT NULL,
     value DOUBLE);
   
   --
   -- populating the table
   --
   INSERT INTO example (id, name, value) VALUES (NULL, 'first', 12.34);
   INSERT INTO example (id, name, value) VALUES (NULL, 'second', 56.78);
   INSERT INTO example (id, name, value) VALUES (NULL, 'third", NULL);
   
   --
   -- committing the pending Transaction
   --
   COMMIT;
   
   .quit
   

3. SQL Variable

   A SQL Variable is just a mnemonic symbol enclosed between two special markers (@ or $). Examples:

   @table_name@
   @col_name@
   $value$
   $radius$
   

   Note: using the @ or the $ markers is just an aesthetic choice; both have identical meaning and practical effect.

4. SQL Body

   A SQL Script internally using Variables. Example:

   .echo on
   
   --
   -- starting a Transaction
   --
   BEGIN;
   
   --
   -- creating a table
   --
   CREATE TABLE @table@ (
     @pk@ INTEGER PRIMARY KEY AUTOINCREMENT,
     @col1@ TEXT NOT NULL,
     @col2@ DOUBLE);
   
   --
   -- populating the table
   --
   INSERT INTO @table@ (@pk@, @col1@, @col2@) VALUES (NULL, 'first', 12.34);
   INSERT INTO @table@ (@pk@, @col1@, @col2@) VALUES (NULL, 'second', 56.78);
   INSERT INTO @table@ (@pk@, @col2@, @col2@) VALUES (NULL, 'third", NULL);
   
   --
   -- committing the pending Transaction
   --
   COMMIT;
   
   .quit
   

   Note: this second version is obviously more flexible than the initial SQL script, just because there aren't hardwired table-names and column-names, all them are now become symbolic.
   So we are now free to reuse more times this SQL Body in different contexts; we just have to appropriately set the Variable Values just immediately before actual execution.
   
   SQL Bodies exist in two different forms:
   • raw: still including symbolic Variables.
   • cooked: presenting all symbols properly replaced by their actual values.
     Rather obviously, you can successfully execute only the cooked form.

5. SQL Procedure BLOB object

   This simply is a BLOB encoded pre-compiled SQL Body, and is the usual target expected to be handled by practically all SQL functions supporting Stored Procedures and alike.

6. Stored Procedure

   A SQL Procedure BLOB object permanently stored into a database. This will make easier reusing more times the same SQL workflow, perhaps so to implement routinely periodic tasks (to be executed daily / weekly / monthly and so on).
   Stored Procedures always have the following elements:
   • a name, intended to be an unique identifier (Primary Key).
   • a title, i.e. a short description of the intended scope of the Stored Procedure.
   • a SQL Procedure BLOB Object.

7. Variable with Value strings

   Before executing a SQL Body including Variables you are obviously expected to set some appropriate Value for each Variable, so to allow for replacing Symbols with actual Values (i.e. for properly transforming raw SQL into cooked SQL).
   A Variable with Value string simply is an appropriately well-formatted Text string declaring both the Symbol and the corresponding replacement Value (expressed in its textual representation). Examples:

   '@value_1@=1234'          -- integer value
   '@value_2@=12.34'         -- double value
   '@value_3@=New York City' -- text value
   '@value_4@=x''0123abcd''' -- blob value
   '@value_5@=NULL'          -- null value
   

   Note: replacing Variables with actual Values is a blind process completely insensible to the specific syntactic context. You are fully responsible for correctly apply single- or double-quoting wherever required by SQL syntax.

8. Stored Variable

   A Variable with Value string permanently stored into a database.
   Stored Variables always have the following elements:
   • a name, intended to be an unique identifier (Primary Key).
   • a title, i.e. a short description of the intended scope of the Stored Variable.
   • a Variable with Value string.

Familiarizing with SQL Functions

1. Handling SQL Procedure BLOB objects

   SELECT SqlProc_FromText('SELECT @col@ FROM @tbl@');
   
   SELECT SqlProc_FromFile('/home/joe/template.txt', CP1252');
   

   • you can create a SQL Procedure BLOB object by directly passing a Text SQL Body.
   • or alternatively you can load the SQL Body from an external file.
     Note: this is a security sensible operation, so you always need to set an explicit authorization by defining SPATIALITE_SECURITY=relaxed.
   • Both functions accept an optional charset encoding argument; in this case the SQL Body will be appropriately converted from the given charset into UTF-8.

   SELECT SqlProc_IsValid ( SqlProc_FromText('SELECT @col@ FROM @tbl@') );
   -----
   1
   
   SELECT SqlProc_IsValid ( MakePoint(11.5, 42.5, 4326) );
   -----
   0
   

   • you can use SqlProc_IsValid in order to check if a generic BLOB really is a valid SQL Procedure BLOB Object.

   SELECT SqlProc_NumVariables ( SqlProc_FromText('SELECT @col@ FROM @tbl@') );
   -----
   2
   
   SELECT SqlProc_VariableN (SqlProc_FromText('SELECT @col@ FROM @tbl@'), 0);
   -----
   @col@
   
   SELECT SqlProc_VariableN (SqlProc_FromText('SELECT @col@ FROM @tbl@'), 1);
   -----
   @tbl@
   
   SELECT SqlProc_AllVariables (SqlProc_FromText('SELECT @col@ FROM @tbl@') );
   -----
   @col@ @tbl@
   

   • you can use SqlProc_NumVariables in order to check how many Variables are defined by a SQL Procedure BLOB Object.
   • and you can use SqlProc_VariableN in order to retrieve a Variable Name by specifying its relative Index (First Variable always corresponds to Index=0).
   • and finally you can use SqlProc_AllVariables in order to directly get all Variable Names formatted as a space-separated list.

2. Error handling

   SELECT SqlProc_GetLastError();
   

   All these functions will raise an Exception on invalid arguments or errors.
   By calling SqlProc_GetLastError you'll usually get a more detailed explanation about the specific cause accounting for the failure.

3. Creating Variables with Values

   SELECT SqlProc_VarValue ('var', NULL);
   -----
   @var@=NULL
   
   SELECT SqlProc_VarValue ('var', 1);
   -----
   @var@=1
   
   SELECT SqlProc_VarValue ('var', 10.5);
   -----
   @var@=10.500000000
   
   SELECT SqlProc_VarValue ('var', 'alpha beta');
   -----
   @var@=alpha beta
   
   SELECT SqlProc_VarValue ('var', MakePoint(11.5, 42.5, 4326) );
   -----
   @var@=x'0001E610000000000000000027400000000000404540000000000000274000000000004045407C0100000000000000000027400000000000404540FE'
   

   Note: hand writing Variable with Value strings is a rather trivial operation, so calling this SQL function is often kind of an overkill. Except, may be, in the case of BLOB values.

4. Raw and Cooked SQL - replacing Symbols with their actual Values

   SELECT SqlProc_RawSQL ( SqlProc_FromText('SELECT @col@ FROM @tbl@;') );
   -----
   SELECT @col@ FROM @tbl@;
   
   SELECT SqlProc_CookedSQL ( SqlProc_FromText('SELECT @col@ FROM @tbl@;'),
       '@col@=first_name',
       '@tbl@=phone_book' );
   -----
   SELECT first_name FROM phone_book;
   

   • you can use SqlProc_RawSQL in order to directly check the SQL Body encapsulated into s SQL Procedure BLOB Object; all initial Variable definitions will be carefully preserved.
   • and you can use SqlProc_CookedSQL in order to directly check the SQL Body as it actually appears after replacing all required Variable Values.
   • Both functions are mainly intended for supporting debugging activities, so to make as easy as possible identifying eventual errors.
   
   

   Note: SqlProc_CookedSQL will accept an arbitrary list of Variable with Value arguments. an empty list (no Variables with Values at all) is a perfectly legal condition (could eventually lead to an invalid Cooked SQL Body, see below). all arguments from the second until the end are expected to be well-formed Variables with Values. you can define as much as 16 different Variables with Values. any attempt to define twice a Value for the same Variable will be always considered as a fatal error. if one of the Variable arguments does not match one the Variables internally defined by the Raw SQL Body it will be simply ignored. if a Variable defined into the Raw SQL Body does not matches a corresponding Variable argument then it will replaced by a default 'NULL' string. the same identical rules for passing Variable-Value arguments apply to SqlProc_CookedSQL, SqlProc_Execute and StoredProc_Execute.

5. Executing a SQL Procedure

   SELECT SqlProc_Execute (SqlProc_FromText('SELECT @col@ FROM @tbl@;'),
       '@col@=first_name',
       '@tbl@=phone_book' );
   -----
   1
   

   you can use SqlProc_Execute in order to directly execute the whole SQL Body as a single monolithic unit.
   At the end of the process 1 (aka True) will be returned on full success; an Exception will be raised on failure.
   
   

   Note: all Dot Macros eventually found within the SQL Body being executed will be always silently ignored. The same rule applies to StoredProc_Execute as well.