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.
   Samples:

   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 collection of SQL Statements, possibly including interleaved Comments and/or Dot Macros.
   SQL Scripts are intended to be executed in a single monolithic pass (atomically 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 placeholder symbol, that later must be resolved, enclosed between two special markers (@ or $). Examples:

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

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

   ---

4. SQL Body

   A SQL Script that internally uses 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, because the table-names and column-names are no longer hardcoded, but dynamic.
   So we are now free to reuse this SQL Body, in a different context after setting the appropriate Variable Values before execution.
   
   SQL Bodies exist in two different forms:
   • raw: containing the symbolic Variables.
   • cooked: with the symbolic Variables properly replaced by the actual values.
     Obviously, only the cooked form can be successfully executed.
   
   

   ---

5. SQL Procedure BLOB object

   This simply is a BLOB encoded with the pre-compiled 'raw' SQL Body, which will be used by practically all SQL functions supporting Stored Procedures.
   
   

   ---

6. Stored Procedure

   A SQL Procedure BLOB object that is permanently stored into a database.
   This makes it easier to use in a automated workflow, perhaps to be implemented as a routinely periodic task (daily / weekly / monthly and so on).
   Stored Procedures always contain 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 'raw' SQL Body (with placeholder Variables), these Variables need to be replaced ('cooked') with the appropriate Values, this properly transforming a 'raw' SQL Body into a 'cooked' SQL Body.
   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 the actual Values is a blind process completely insensible to the specific syntactic context. You are responsible for the correct application of single- or double-quoting, required by the SQL syntax rules.

   
   

   ---

8. Stored Variable

   A Variable with Value permanently stored into a database.
   Stored Variables always contain 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 Value string.
   
   

   Note: the intended scope of Stored Variables is to define appropriate default values. If a given Variable is being used within a raw SQL Body to be executed, and a corresponding Variable with Value has not been resolved, then an attempt will be always made to resolve it by searching for a corresponding Stored Variable by name (if it exists). An explicitly defined Variable with Value argument will always take precedence over the corresponding Stored Variable; in other words a Stored Variable (if defined) will just be considered the default value.

   
   

   ---

9. SQL Logfile

   SQL queries will usually return some resultset potentially containing useful information.
   But executing a SQL Procedure or Stored Procedure will be seen by SQLite just as a simple SQL function call simply returning a final success or failure return code.
   Any information about all the intermediate resultsets, internally produced during the execution of the Procedure, will be lost.
   
   To avoid this, you can configure an external SQL Logfile to permanently store all intermediate resultsets into an ordinary text file. The SQL Logfile supports two different modes:
   • Truncate: the Logfile will be automatically created when not already existing, and it will be always truncated before starting an execution unit; in other words it will always preserve just the resultsets produced by the most recent execution unit.
   • Append: the Logfile will be automatically created when not already existing, and it will be never truncated before starting an execution unit; in other words it will progressively grow so to store all the resultsets produced by following execution units.
     Note: you'll be obviously required to flush the Logfile from time to time so to safely avoid to consume too much storage space.

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 a 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 till the last are expected to be well-formed Variables with Values. you can define up to 16 different Variables with Values. any attempt to re-define the same Variable with another Value will be always considered as a fatal error. any of the Variable arguments not contained in the raw SQL Body will be ignored. if a Variable defined in the raw SQL Body does not matches a corresponding Variable argument then it will be set with a 'NULL' string. the same identical rules for passing Variable-Value arguments apply to SqlProc_CookedSQL, SqlProc_Execute and StoredProc_Execute.

   
   

   Warning: referencing a Variable from within another Variable is not permitted. SELECT SqlProc_CookedSQL ( SqlProc_FromText('SELECT @col@ FROM @tbl@;'), '@col@=first_name', '@country@=italy', '@tbl@=@country@_phone_book' ); In the above example the @country@ term into the Value string '@country@_phone_book' will not be considered as a Variable and will not be replaced.

   
   

   ---

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 will happens in this sample, you can then use SqlProc_GetLastError to retrieve the corresponding SQL error message.
   
   

   Note: all Dot Macros found within the SQL Body being executed will always be 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, within your currently connected database, all metatables required to permanently register Stored Procedures and Stored Variables.
   
   

   Note: any new database created by SpatiaLite 5.0.0 (and subsequent versions) will automatically support the Stored Procedures metatables. Explicitly calling this function is mainly used in order to add Stored Procedures support on already existing databases created 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 ( 'pi', 'an universally useful constant', '3.14' );
   -----
   1
   

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

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

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

   SELECT StoredVar_Get ( 'pi' );
   -----
   @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 ( 'pi', '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 ( '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.

8. Sql/Stored Procedures as real Functions

   An Sql or Stored Procedure can effectively act as a genuine Function by appropriately calling SqlProc_Return() (or StoredProc_Return(), that simply is an alias name for the same function).

   SELECT SqlProc_Return ( NULL );
   ------
   1
   
   SELECT SqlProc_Return ( 12345 );
   ------
   1
   
   SELECT SqlProc_Return ( 123.45 );
   ------
   1
   
   SELECT SqlProc_Return ( 'this is a value' );
   ------
   1
   
   SELECT SqlProc_Return ( zeroblob(10) );
   ------
   1
   

   • This function stops as soon as possible the execution of the current SQL Body, then returning a value to the caller.
     The Return Value defined by SqlProc_Return() can freely be of any possible data-type: NULL, INTEGER, DOUBLE, TEXT or BLOB.
   • Note: it's not an exact equivalent of the most usual RETURN statement defined by many common languages:
     • Just calling SqlProc_Return() will not cause the execution to immediately stop; this could easily be a deferred action.
     • What SqlProc_Return() really does is booking the termination of the current SQL Block, but the current SQL statement being executed (the one including the SqlProc_Return() call) will continue to be executed until its natural completion.
     • Only at this point SqlProc_Execute() (or StoredProc_Execute) will really stop executing the current SQL Block imediately returning the execution control to the caller and passing back to it the Return Value spefified by SqlProc_Return().
   • Important notice: an SQL Body terminating without explicitly calling SqlProc_Return() will behave exactly as if had implicitly called SqlProc_Return(NULL).
   • Warning: any call to SqlProc_Return() outside the execution context of SqlProc_Execute() (or StoredProc_Execute) will be simply considered as an effectless no-op.
   
   Just few basic examples about Sql Procedures acting as real Functions.
   
   Example #1: a pseudo-function returning the sum of two Integers.

   SELECT SqlProc_Execute(SqlProc_FromText(
          'SELECT SqlProc_Return(@arg1@ + @arg2@)'), 
          '@arg1@=1000', '@arg2@=25'); 
   -------------------------
   1025
   

   Example #2: a pseudo-function returning the product of two Doubles.

   SELECT SqlProc_Execute(SqlProc_FromText(
          'SELECT SqlProc_Return(@arg1@ * @arg2@)'), 
          '@arg1@=3.25', '@arg2@=100.0'); 
   -------------------------
   325.000000
   

   Example #3: a pseudo-function returning the concatenation of two Text Strings.

   SELECT SqlProc_Execute(SqlProc_FromText(
          'SELECT SqlProc_Return(@str1@ || '' '' || @str2@)'), 
          '@str1@=''Linus Torvalds''', '@str2@=''is the author of Linux'''); 
   -------------------------
   Linus Torvalds is the author of Linux
   

   Example #4: a pseudo-function returning a BLOB Geometry.

   SELECT AsEWKT(SqlProc_Execute(SqlProc_FromText(
          'SELECT SqlProc_Return(MakePoint(@x@, @y@, @srid@))'), 
          '@x@=10', '@y@=20', '@srid@=4326')); 
   -------------------------
   SRID=4326;POINT(10 20)
   

9. Sql/Stored Procedures iterative execution

   SqlProc_ExecuteLoop() and StoredProc_ExecuteLoop() are very similar respectively to SqlProc_Execute() and StoredProc_Execute(); the signature of these SQL function pairs is exactly the same.
   
   But there is a very relevant difference in their respective behavior:
   • as we have already seen, both SqlProc_Execute() and StoredProc_Execute() run just once, then returning to the caller the return value implicitly or explicitly set by SqlProc_Return() or StoredProc_Return().
   • both SqlProc_ExecuteLoop() and StoredProc_ExecuteLoop() will instead automatically iterate as many times as required the execution of their SQL Body.
     The loop will break only when the last iteration will finally return a negative or zero integer value.
   • Warning: a badly implemented iteration can easily cause a nasty endless loop; beware and be very cautious.
   
   Example

   --
   -- creating a test table
   --
   CREATE TABLE IF NOT EXISTS looping (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       colint INTEGER NOT NULL,
       colstr TEXT NOT NULL,
       tmstamp TEXT NOT NULL);
   
   --
   -- inserting a random row
   --
   INSERT INTO looping (id, colint, colstr, tmstamp)
       VALUES (NULL, random(), hex(randomblob(16)), datetime('now'));
   
   --
   -- setting up a return value
   --
   SELECT CASE count(*) >= @LIMIT@ WHEN 1 THEN SqlProc_Return(0)
          ELSE SqlProc_Return(1) END FROM looping;
   

   This sample SQL Body (loop.sql) will take the following actions:
   1. first it will try to create a table; if the table alredy exists any error will be silently ignored.
   2. then it will INSERT a random row in this table.
   3. and finally if will check how many rows have been already inserted in the table.
      When the predefined limit will be reached, the loop will terminate.

   SELECT SqlProc_ExecuteLoop(SqlProc_FromFile('./loop.sql'), '@LIMIT@=1000');
   
   SELECT * FROM looping;
   

   As you can easily check, the iterative execution of loop.sql has just created the target table inserting thousand rows into it.

10. Handling the SQL Logfile

    SELECT SqlProc_SetLogfile ( '/home/sandro/sql_log.txt' );
    -----
    1
    
    SELECT SqlProc_SetLogfile ( '/home/sandro/sql_log.txt', 1);
    -----
    1
    
    SELECT SqlProc_SetLogfile ( NULL );
    -----
    1
    

    You can use SqlProc_SetLogfile() in order to enable or disable the SQL Logfile:
    • the first form (single argument) just requires to define the absolute or relative pathname of the intended Logfile.
      A Logfile defined in such a way will always operate in Truncate mode.
    • the second form (two arguments) supports a further mode argument:
      • if set to FALSE (0, default value) the Logfile will operate in Truncate mode.
      • if set to TRUE (any other value different from 0) the Logfile will operate in Append mode.
    • finally, you can pass a NULL path argument in order to immediately disable the Logfile.

    SELECT SqlProc_GetLogfile();
    -----
    /home/sandro/sql_log.txt
    
    SELECT SqlProc_GetLogfile();
    -----
    NULL
    

    You can use SqlProc_GetLogfile() in order to check if the current DB-connection is using a Logfile or not.
    
    

    Note: the Logfile setting is permanent on a per-connection basis. any new DB-connection will always start by keeping the Logfile disabled. You are always required to perform an explicit call to SqlProc_SetLogfile() in order to effectively enable a Logfile. once a Logfile has been enabled it will continue to support the current DB-connection during its lifespan, if not been explicitly disabled. Logfile settings are strictly confined withing the current DB-connection, and will never automatically be continued in any following connections.

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 names are all-uppercase in years 1991 and 2001 but not for the year 2011.
    Systematically using Upper() will insure consistentency of all names.
  • We are using three separate comparisons (one for Municipality, one for Province and one for Region) connected by OR logical operators so to make it possible by specifying just a single or more filter clauses.
    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/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

Tutorial #4

SELECT SqlProc_SetLogfile( 'C:/users/joe/stored_proc_test/logfile.txt', 1);
-----
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@=2001', '@municipality@=AREZZO', '@shp_path@=C:/users/joe/stored_proc_test/com_arezzo_2001_3003');
-----
1

SELECT SqlProc_SetLogfile( NULL );
-----
1

=========================================================================================
==     SQL session start   =   2017-12-21 12:50:09
=========================================================================================
SELECT DropGeoTable('transient_table');
-------------------------------
DropGeoTable('transient_table')
-------------------------------
0
=== 1 row === Execution time: 0.003

CREATE TABLE transient_table AS
SELECT id, istat, name, province, region, ST_Transform(geom, 4326) AS geom
FROM municipalities_1991
WHERE (Upper(name) = Upper('NULL') OR Upper(province) = Upper('NULL') OR Upper(region) = Upper('puglia'));
=== Execution time: 0.111

SELECT RecoverGeometryColumn('transient_table', 'geom', 4326, 'MULTIPOLYGON', 'XY');
----------------------------------------------------------------------------
RecoverGeometryColumn('transient_table', 'geom', 4326, 'MULTIPOLYGON', 'XY')
----------------------------------------------------------------------------
1
=== 1 row === Execution time: 0.031

SELECT ExportShp('transient_table', 'geom', 'C:/users/joe/stored_proc_test/puglia_1991_4326', 'CP1252');
-------------------------------------------------------------------------------
ExportShp('transient_table', 'geom', 'C:/users/joe/stored_proc_test/puglia_1991_4326', 'CP1252')
-------------------------------------------------------------------------------
257
=== 1 row === Execution time: 0.077

SELECT DropGeoTable('transient_table');
-------------------------------
DropGeoTable('transient_table')
-------------------------------
1
=== 1 row === Execution time: 0.002

=========================================================================================
==     SQL session end   =   2017-12-21 12:50:09   =   5 statements were executed
=========================================================================================


=========================================================================================
==     SQL session start   =   2017-12-21 12:53:01
=========================================================================================
SELECT DropGeoTable('transient_table');
-------------------------------
DropGeoTable('transient_table')
-------------------------------
0
=== 1 row === Execution time: 0.002

CREATE TABLE transient_table AS
SELECT id, istat, name, province, region, ST_Transform(geom, 3003) AS geom
FROM municipalities_2001
WHERE (Upper(name) = Upper('AREZZO') OR Upper(province) = Upper('NULL') OR Upper(region) = Upper('NULL'));
=== Execution time: 0.032

SELECT RecoverGeometryColumn('transient_table', 'geom', 3003, 'MULTIPOLYGON', 'XY');
----------------------------------------------------------------------------
RecoverGeometryColumn('transient_table', 'geom', 3003, 'MULTIPOLYGON', 'XY')
----------------------------------------------------------------------------
1
=== 1 row === Execution time: 0.013

SELECT ExportShp('transient_table', 'geom', 'C:/users/joe/stored_proc_test/com_arezzo_2001_3003', 'CP1252');
-----------------------------------------------------------------------------------
ExportShp('transient_table', 'geom', 'C:/users/joe/stored_proc_test/com_arezzo_2001_3003', 'CP1252')
-----------------------------------------------------------------------------------
1
=== 1 row === Execution time: 0.008

SELECT DropGeoTable('transient_table');
-------------------------------
DropGeoTable('transient_table')
-------------------------------
1
=== 1 row === Execution time: 0.007

=========================================================================================
==     SQL session end   =   2017-12-21 12:53:01   =   5 statements were executed
=========================================================================================

Tutorial #5 (advanced)

5.1 Adapted Stored Procedure for istat_it

-- -- ---------------------------------- --
-- ATTACH source Database AS 'db_source'
-- -- ---------------------------------- --
-- Note: Source Database must be attached before this is called
--  If called from inside a Stored Procedures will work the first time,
--   then fail with 'gaia_sql_proc_execute: database db_source is already in use'
-- -- ---------------------------------- --
-- removing the work table (just in case it already exists)
-- -- ---------------------------------- --
SELECT DropGeoTable('transient_table');
-- -- ---------------------------------- --
-- inserting selected istat_it data into the work table
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),'calling: CREATE TABLE';
CREATE TABLE transient_table AS
SELECT id,
 istat,
 name,
 province,
 region,
 ST_Transform(geom, @srid@) AS geom
FROM '@attach_schema_name@'.municipalities_@year@
WHERE
(
 Upper(name) = Upper('@municipality@') OR Upper(province) = Upper('@province@') OR Upper(region) = Upper('@region@')
);
-- -- ---------------------------------- --
-- properly recovering the work table
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),'calling: RecoverGeometryColumn';
SELECT RecoverGeometryColumn('transient_table', 'geom', @srid@, 'MULTIPOLYGON', 'XY');
-- -- ---------------------------------- --
-- exporting from the work table into the Shapefile
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),'calling: ExportShp';
SELECT ExportShp('transient_table', 'geom', '@shp_directory@/@shp_file@', 'CP1252');
-- -- ---------------------------------- --
-- removing the work table
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),'calling: DropGeoTable';
SELECT DropGeoTable('transient_table');
-- -- ---------------------------------- --

5.2 Creating Stored Procedure Database

-- -- ---------------------------------- --
-- export SPATIALITE_SECURITY=relaxed
-- rm stored_procedure_db.db
-- spatialite stored_procedure_db.db 

5.3 The calling script, possibly calling difference Stored Procedures and source Databases

-- -- ---------------------------------- --
-- export SPATIALITE_SECURITY=relaxed
-- spatialite stored_procedure_db.db  created with 'store.export_shape.sp.sql'
-- Temporary TABLES created in during the Stored Procedure
--  will be contained in the Stored Procedures Database and not in the source Database.
-- -- ---------------------------------- --
-- Use a Log-file for debugging
-- -> will be written in the same directory as the script
-- -- ---------------------------------- --
SELECT SqlProc_SetLogfile ( 'log.select.export_shape_from_db.txt' );
-- -- ---------------------------------- --
-- Attach source Database for use from this script
-- Note: use of ATTACH/DETACH from inside a
--  Stored Procedures will work the first time,
--   then fail with 'gaia_sql_proc_execute: database db_source is already in use'
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),'calling: ATTACH DATABASE';
-- -- ---------------------------------- --
-- The used schema-name must be passed on to the called Stored Procedure
-- -> 'db_source'
-- -- ---------------------------------- --
ATTACH DATABASE 'source_db/sp_sample_db.db' AS db_source;
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),"calling: 'export_shape' for Lazio";
SELECT StoredProc_Execute( 'export_shape',
    '@attach_schema_name@=db_source',
    '@year@=2001',
    '@srid@=3004',
    '@region@=Lazio',
    '@shp_file@=lazio_2001_3004' );
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),"calling: 'export_shape' for Puglia";
SELECT StoredProc_Execute( 'export_shape',
    '@attach_schema_name@=db_source',
    '@year@=1991',
    '@srid@=4326',
    '@region@=puglia',
    '@shp_file@=puglia_1991_4326' );
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),"calling: 'export_shape' for Arezzo";
SELECT StoredProc_Execute( 'export_shape',
    '@attach_schema_name@=db_source',
    '@year@=2011',
    '@srid@=3003',
    '@province@=AREZZO',
    '@shp_file@=province_arezzo_2011_3003' );
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),"calling: 'export_shape' for Arezzo";
SELECT StoredProc_Execute( 'export_shape',
    '@attach_schema_name@=db_source',
    '@year@=2001',
    '@srid@=3003',
    '@municipality@=Arezzo',
    '@shp_file@=municipality_arezzo_2001_3003' );
-- -- ---------------------------------- --
-- DETACH source Database
-- The used schema-name must be used here
-- -> 'db_source'
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),'calling: DETACH DATABASE';
DETACH DATABASE db_source;
-- -- ---------------------------------- --
-- SELECT DateTime('now','localtime'),"calling: SqlProc_GetLastError";
SELECT SqlProc_GetLastError();
-- -- ---------------------------------- --