Stored Procedures
Not logged in

back to index

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

Starting with version 5.0.0, SpatiaLite supports an advanced mechanism intended to significantly extend the scripting techniques allowing for a powerful and flexible automation of complex workflows.
Think of the nice flexibility possible, by mixing standard SQL scripts and Variables which are replaced before being executed. Not only that, but think of the many wonderful opportunities offered by permanently storing, within a database, SQL Scripts with Variables, simplifying common automated workflows that are repeatedly executed. This is exactly the intended scope of this new module.

We'll start first by defining a few basic concepts and objectives, so as to avoid any possible confusion.


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

All the following tutorials are based on the same sample database, which should be downloaded before continuing.

As you can see, this database contains three Spatial Tables representing the Administrative Boundaries of all Italian Municipalities as officially determined by ISTAT:
  • 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).

The Problem

We'll implement a Procedure intended to export selected Shapefiles.
  • The Census Year must be freely selectable.
  • A required feature is allowing either filtering a single Municipality or listing all Municipalities belonging to the a Province or Region.
  • Reprojecting to some alternative SRID will be a supported feature.
  • While doing all this we'll obviously try to take full advantage of using Variables to make the Procedure as generic and flexible as possible.

--
-- 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');
As you can see the proposed implementation is basically simple. Now you simply have to cut&paste the above SQL body saving it into a file named test_sp.txt for further usage.

Few interesting details worth being explained:
  • 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
In this first tutorial we have always executed a Procedure loaded from an external file.
As you can see executing a Procedure is a straightforward task; you simply have to specify an appropriate list of Variable Values and that's all.

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') );
This second tutorial is basically the same of the first one.
The only difference is that we'll now use a permanently registered Stored Procedure.
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
Executing a Stored Procedure isn't very different from executing a Procedure stored into an external file.
Executing your Procedures from external files or permanently registering Stored Procedures directly within a database is a matter of free choice.
If you are planning to deploy a Procedure intended to support many different databases, then using an external file could probably simplify your work.
But if you are planning to deploy a Procedure intended to automate many routinely task to be executed often on the same database, then permanently registering a Stored Procedure would probably be better and faster.


Tutorial #3

This third tutorial is just a small variation on the theme intended to highlight some potentially dangerous pitfalls related to single-quoting.
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
This first attempt will fail, and using SqlProc_GetLastError will explain why it failed
We've passed a '@municipality@=rio nell''elba' Variable with Value, that will lead to a cooked WHERE clause like this:
WHERE (Upper(name) = Upper('rio nell'elba') OR Upper(province) = Upper('NULL') OR Upper(region) = Upper('NULL'))
The, not always obvious cause, is that 'rio nell'elba' is an illegal SQL text string; the correct syntax should be instead 'rio nell''elba'.

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
Resolving such an issue is rather trivial; we just have to use four consecutive single-quotes so to correctly escape the string value or
use double quotes for fields where single quotes often occur in names ("@municipality@=rio nell'elba").

Tutorial #4

This last tutorial is exactly the same of Tutorial #2, but this time we'll enable a logfile.
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
We'll start by calling SqlProc_SetLogfile() so to enable the logging facility; the logfile will be C:/users/joe/stored_proc_test/logfile.txt and it will be opened in append mode. We'll end finally by calling once again SqlProc_SetLogfile() by passing a NULL pathname so to disable the logging facility.

The logfile is just a plain text file; you can open it by using any text editor so to check that it will look like this:
=========================================================================================
==     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

Based on Tutorial #2, but adapted so that the Stored Procedures are contained in an external attached Database where the source Database should only be read but not changed.

The Stored Procedure has been adapted to support an attached Database.
It is specific for the Source istat_it Database.
@attach_schema_name@ will contain the schema-name used in the calling script:
-- -- ---------------------------------- --
-- 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');
-- -- ---------------------------------- --
Notes: Difference from main Sample:
The calling script will run against the Database that contains this Stored Procedure and ATTACH the source Database using a schema-name.
  • Use of ATTACH/DETACH from inside a Stored Procedures will work the first time,but fail when called again with
    • 'gaia_sql_proc_execute: database db_source is already in use
  • Temporary TABLE created in during the Stored Procedure
    • will be contained in the Stored Procedures Database and not in the source Database.
  • SELECT messages called during the Stored Procedure
    • will be not be shown and have been commented out.
  • The Export Shape directory and file names are seperate
    • was shp_path, now shp_directory and shp_file.
    • The needed directory "/" between shp_directory and shp_file will be added here.


5.2 Creating Stored Procedure Database

The Stored Procedure and possible default values will be stored in the created Stored Procedures Database.
@attach_schema_name@ will contain the schema-name used in the calling script:
-- -- ---------------------------------- --
-- export SPATIALITE_SECURITY=relaxed
-- rm stored_procedure_db.db
-- spatialite stored_procedure_db.db 
Notes: Difference from main Sample:
  • The called Stored Procedure is specific to the ATTACHed source Database.
    • care must be taken that they both match
  • A different Source Database can be added during the calling script
    • that must call a corresponding Export Stored Procedure.
  • The Shape output directory
    • may be, but must not be the same for all exported Shapes.


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

The Stored Procedure and possible default values will be stored in the created Stored Procedures Database.
Different Source Databases, using different Stored Procedure, could be used within one script:
-- -- ---------------------------------- --
-- 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();
-- -- ---------------------------------- --
Notes: Difference from main Sample:
  • The called Stored Procedure assumes that the source Database has been ATTACHed, using the given attach_schema_name.
    • if the used schema-name is not the same as the set default value, it must be given as a Stored Procedure parameter.
  • The Shape output fine-name
    • must be unique within the Shape output directory.
  • The Shape output directory
    • may be, but must not be the same for all exported Shapes.


back to index