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.5000000000
   
   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' );
   -----
   SqlProc exception - a fatal SQL error was encountered.
   
   SELECT SqlProc_GetLastError();
   -----
   gaia_sql_proc_execute: no such table: phone_book
   

   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, as it happens in this example, and in this case you can then use SqlProc_GetLastError so to retrieve the corresponding SQL errorr message.
   
   

   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.

   
   

   ---

6. Handling SQL Stored Procedures

   SELECT StoredProc_CreateTables();
   -----
   1
   

   you can use StoredProc_CreateTables in order to create on your currently connected database all metatables required to permanently register Stored Procedures and Stored Variables.
   
   

   Note: any new database created by SpatiaLite 4.5.0 (and subsequent versions) will automatically support the Stored Procedures metatables. Explicitly calling this function is mainly useful in order to retrofit Stored Procedures support on already existing databases create by previous versions. Calling StoredProc_CreateTables on behalf of a database already supporting the Stored Procedures metatable is an harmless operation simply returning 1 (full success).

   SELECT StoredProc_Register ( 'sp_1', 'a sample Stored Procedure for testing', SqlProc_FromText('SELECT @col@ FROM @tbl@;' ));
   -----
   1
   

   you can use StoredProc_Register in order to permanently register a Stored Procedure.

   SELECT StoredProc_Delete ( 'sp_1' );
   -----
   1
   

   you can use StoredProc_Delete in order to permanently remove a previously registered Stored Procedure.

   SELECT SqlProc_RawSQL ( StoredProc_Get ( 'sp_1' ) );
   -----
   SELECT @col@ FROM @tbl@;
   

   you can use StoredProc_Get in order to retrieve the SQL Procedure BLOB Object from a registered Stored Procedure.

   SELECT StoredProc_UpdateTitle ( 'sp_1', 'this Title was changed' );
   -----
   1
   

   you can use StoredProc_UpdateTitle in order to modify the Title of an already registered Stored Procedure.

   SELECT StoredProc_UpdateSqlBody ( 'sp_1', SqlProc_FromFile('/home/joe/sql_scripts/stored_proc_test_v2.txt' );
   -----
   1
   

   you can use StoredProc_UpdateSqlBody in order to modify the Sql Body of an already registered Stored Procedure (e.g. because you've discovered and fixed some bug affecting the previous SQL implementation).

   SELECT StoredProc_Execute ( 'sp_1',
       '@col@=srid',
       '@tbl@=spatial_ref_sys' );
   -----
   1
   

   and finally you can use StoredProc_Execute in order to directly execute a Stored Procedure as a single monolithic unit.
   
   
   

   ---

7. Handling SQL Stored Variables

   SELECT StoredVar_Register ( 'var_1', 'an universally useful constant', '@PI@=3.14' );
   -----
   1
   

   you can use StoredVar_Register in order to permanently register a Stored Variable.

   SELECT StoredVar_Delete ( 'var_1' );
   -----
   1
   

   you can use StoredVar_Delete in order to permanently remove a previously registered Stored Variable.

   SELECT StoredVar_Get ( 'var_1' );
   -----
   @PI@=3.14
   

   you can use StoredVar_Get in order to retrieve the Variable with Value string from a registered Stored Variable.

   SELECT StoredVar_UpdateTitle ( 'var_1', 'PI - the magic number' );
   -----
   1
   

   you can use StoredVar_UpdateTitle in order to modify the Title of an already registered Stored Procedure.

   SELECT StoredVar_UpdateValue ( 'var_1', '@PI@=3.1415926535' );
   -----
   1
   

   and finally you can use StoredVar_UpdateValue in order to modify the Variable with Value of an already registered Stored Variable.

Tutorial #1

• there are three different tables, each one corresponding to a specific National Census (years 1991, 2001 and 2011 are available).
• all three tables have exactly the same layout.
• Administrative Boundaries are always represented as MultiPolygons (Srid=32632).

--
-- removing the work table (just in case it already exists)
--
SELECT DropGeoTable('transient_table');

--
-- inserting selected data into the work table
--
CREATE TABLE transient_table AS
SELECT id, istat, name, province, region, ST_Transform(geom, @srid@) AS geom
FROM municipalities_@year@
WHERE (Upper(name) = Upper('@municipality@') OR Upper(province) = Upper('@province@') OR Upper(region) = Upper('@region@'));

--
-- properly recovering the work table
--
SELECT RecoverGeometryColumn('transient_table', 'geom', @srid@, 'MULTIPOLYGON', 'XY');

--
-- exporting from the work table into the Shapefile
--
SELECT ExportShp('transient_table', 'geom', '@shp_path@', 'CP1252');

--
-- removing the work table
--
SELECT DropGeoTable('transient_table');

• Variable @year@ is intended to select which specific Spatial Table will be queried.
• Variable @srid@ is intended to specify the output SRID.
• Variable @shp_path@ is intended to specify the pathname of the output Shapefile.
• Variables @municipality@, @province@ and @region@ are intended to specify the intended filtering criteria.
  This is a little bit tricky, so we'll go in further depth:
  • These Variables are expected to be actually replaced by text string values; so we've consistently single-quoted the variable symbol, so to allow for an easier value replacement.
  • We are systematically using the Upper() function simply because Province and Region name are all-uppercase in years 1991 and 2001 but not on year 2011.
    Systematically using Upper() will homogenize all names in a consistent way.
  • We are using three separate comparisons (one for Municipality, one for Province and one for Region) connected by OR logical operators so to make possible specifying just a single filter clause.
    Recall: unassigned Variables will default to NULL; so by just declaring @province@=Arezzo we'll then get a cooked WHERE clause like this:

    WHERE (Upper(name) = Upper('NULL') OR Upper(province) = Upper('Arezzo') OR Upper(region) = Upper('NULL'))
    

SELECT SqlProc_Execute( SqlProc_FromFile ('C:/users/joe/stored_proc_test/test_sp.txt'),
  '@srid@=3004', '@year@=2001', '@region@=Lazio', '@shp_path@=C:/users/joe/stored_proc_test/lazio_2001_3004');
-----
1

SELECT SqlProc_Execute( SqlProc_FromFile ('C:/users/joe/stored_proc_test/test_sp.txt'),
  '@srid@=4326', '@year@=1991', '@region@=puglia', '@shp_path@=C:/users/joe/stored_proc_test/puglia_1991_4326');
-----
1

SELECT SqlProc_Execute( SqlProc_FromFile ('C:/users/joe/stored_proc_test/test_sp.txt'),
  '@srid@=3003', '@year@=2011', '@province@=AREZZO', '@shp_path@=C:/users/joe/ucs5gb/stored_proc_test/arezzo_2011_3003');
-----
1

SELECT SqlProc_Execute( SqlProc_FromFile ('C:/users/joe/stored_proc_test/test_sp.txt'),
  '@srid@=3003', '@year@=2001', '@municipality@=AREZZO', '@shp_path@=C:/users/joe/stored_proc_test/com_arezzo_2001_3003');
-----
1

Tutorial #2

SELECT StoredProc_Register( 'my_proc', 'a Stored Procedure for exporting selected Shapefiles', SqlProc_FromFile ('C:/users/joe/stored_proc_test/test_sp.txt') );

SELECT StoredProc_Execute( 'my_proc', '@srid@=3004', '@year@=2001', '@region@=Lazio', '@shp_path@=C:/users/joe/stored_proc_test/lazio_2001_3004');
-----
1

SELECT StoredProc_Execute( 'my_proc', '@srid@=4326', '@year@=1991', '@region@=puglia', '@shp_path@=C:/users/joe/stored_proc_test/puglia_1991_4326');
-----
1

SELECT StoredProc_Execute( 'my_proc', '@srid@=3003', '@year@=2011', '@province@=AREZZO', '@shp_path@=C:/users/joe/stored_proc_test/arezzo_2011_3003');
-----
1

SELECT StoredProc_Execute( 'my_proc', '@srid@=3003', '@year@=2001', '@municipality@=AREZZO', '@shp_path@=C:/users/joe/stored_proc_test/com_arezzo_2001_3003');
-----
1

Tutorial #3

SELECT StoredProc_Execute( 'my_proc'), '@srid@=3003', '@year@=2011', '@municipality@=rio nell''elba', '@shp_path@=C:/users/joe/stored_proc_test/rio_elba_2011_3003');
-----
StoredProc exception - a fatal SQL error was encountered.

SELECT SqlProc_GetLastError();
-----
gaia_sql_proc_execute: near "elba": syntax error

WHERE (Upper(name) = Upper('rio nell'elba') OR Upper(province) = Upper('NULL') OR Upper(region) = Upper('NULL'))

SELECT StoredProc_Execute( 'my_proc'), '@srid@=3003', '@year@=2011', '@municipality@=rio nell''''elba', '@shp_path@=C:/users/joe/stored_proc_test/rio_elba_2011_3003');
-----
1