D 2017-12-20T08:36:33.724
L Stored\sProcedures
P 9bbb5d516e45bf624ece82664c70ef86acaa9d0f
U sandro
W 29922
<table cellspacing="12" width="100%">
<tr><td colspan="2">
<table width="100%" bgcolor="#f0f0f8">
<tr><td align="center">
</td></tr></table>
<table width="100%"><tr>
<td width="33%" align="left"></td>
<td align="center"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0+doc">back to index</a></td>
<td width="33%" align="right"></td>
</tr></table>
<h1>SQL Procedures, Stored Procedures and Stored Variables: a quick intro</h1>
Starting with version <b>4.5.0</b>, SpatiaLite supports an advanced mechanism intended to significantly extend the scripting techniques allowing for a powerful and flexible automation of complex workflows.<br>
Think of the nice flexibility possible, by mixing standard <b><i>SQL scripts</i></b> and <b><i>Variables</i></b> which are replaced before being executed.
Not only that, but think of the many wonderful opportunities offered by permanently storing, within a database, <b><i>SQL Scripts with Variables</i></b>, simplifying common automated workflows that are repeatedly executed. This is exactly the intended scope of this new module.<br><br>
We'll start first by defining a few basic concepts and objectives, so as to avoid any possible confusion.<br><br>
<hr>
<h2>Basic Definitions and Concepts</h2>
<ol>
<li>
<h4>SQL Statement</h4>
The minimal execution unit allowed by SQL syntax; a stand-alone SQL directive to be atomically executed in a single step. <br>
Samples:
<verbatim>
SELECT id, name, measure FROM some_table;
------------
DELETE FROM some_table WHERE measure > 1000;
------------
UPDATE some_table SET measure = measure / 10.0
</verbatim>
<u>Note</u>: SQL Statements are usually expected to be explicitly terminated by a <b><i>semicolon</i></b> (<b><i>;</i></b>).<br>
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 <b><i>blanks</i></b> will be always ignored).
<br><br><hr>
</li>
<li>
<h4>SQL Script</h4>
A more or less complex collection of <b><i>SQL Statements</i></b>, possibly including interleaved <b><i>Comments</i></b> and/or <b><i>Dot Macros</i></b>.<br>
SQL Scripts are intended to be executed in a single monolithic pass (atomically or not, depending on internal <b><i>Transaction</i></b> handling). Example:
<verbatim>
.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
</verbatim>
<hr>
</li>
<li>
<h4>SQL Variable</h4>
A <b><i>SQL Variable</i></b> is just a placeholder symbol, that later must be resolved, enclosed between two <i><u>special markers</u></i> (<b><i>@</i></b> or <b><i>$</i></b>). Examples:
<verbatim>
@table_name@
@col_name@
$value$
$radius$
</verbatim>
<u>Note</u>: using the <b><i>@</i></b> or the <b><i>$</i></b> markers is just a <i><u>matter of choice</u></i>; both have identical meaning and practical effect.
<br><br><hr>
</li>
<li>
<h4>SQL Body</h4>
A <b><i>SQL Script</i></b> that internally uses <b><i>Variables</i></b>. Example:
<verbatim>
.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
</verbatim>
<u>Note</u>: this second version is obviously more flexible than the initial SQL script, because the table-names and column-names are no longer <u><i>hardcoded</i></u>, but <u><i>dynamic</i></u>.<br>
So we are now free to reuse this SQL Body, in a different context after setting the appropriate Variable Values before execution.<br><br>
<b><i>SQL Bodies</i></b> exist in two different forms:
<ul>
<li><b>raw</b>: containing the symbolic Variables.</li>
<li><b>cooked</b>: with the symbolic Variables properly replaced by the actual values.<br>
Obviously, only the <b><i>cooked</i></b> form can be successfully executed.</li>
</ul>
<br><hr>
</li>
<li>
<h4>SQL Procedure BLOB object</h4>
This simply is a <b><i>BLOB encoded</i></b> with the pre-compiled '<u><i>raw</i></u>' <b><i>SQL Body</i></b>, which will be used by practically all SQL functions supporting Stored Procedures.
<br><br><hr>
</li>
<li>
<h4>Stored Procedure</h4>
A <b><i>SQL Procedure BLOB object</i></b> that is permanently stored into a database.<br>
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).<br>
Stored Procedures always contain the following elements:
<ul>
<li>a <b>name</b>, intended to be an unique identifier (<b><i>Primary Key</i></b>).</li>
<li>a <b>title</b>, i.e. a short description of the intended scope of the Stored Procedure.</li>
<li>a <b>SQL Procedure BLOB Object</b>.</li>
</ul>
<br><hr>
</li>
<li>
<h4>Variable with Value strings</h4>
Before executing a '<i>raw</i>' <b><i>SQL Body</i></b> (with placeholder <b><i>Variables</i></b>), these Variables need to be replaced ('<i>cooked</i>') with the appropriate <b><i>Values</i></b>, this properly transforming a '<b>raw</b>' SQL Body into a '<b>cooked</b>' SQL Body.<br>
A <b><i>Variable with Value</i></b> string simply is an appropriately well-formatted Text string declaring both the Symbol and the corresponding replacement Value (expressed in its textual representation). Examples:
<verbatim>
@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
</verbatim>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Note</u>: replacing Variables with the actual Values is a blind process completely insensible to the specific syntactic context.<br>
You are responsible for the correct application of single- or double-quoting, required by the SQL syntax rules.
</td></tr></table>
<br><hr>
</li>
<li>
<h4>Stored Variable</h4>
A <b><i>Variable with Value</i></b> permanently stored into a database.<br>
Stored Variables always contain the following elements:
<ul>
<li>a <b>name</b>, intended to be an unique identifier (<b><i>Primary Key</i></b>).</li>
<li>a <b>title</b>, i.e. a short description of the intended scope of the Stored Variable.</li>
<li>a <b>Value string</b>.</li><br>
</ul><table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Note</u>: the intended scope of <b>Stored Variables</b> is to define appropriate default values.<br>
If a given <b>Variable</b> is being used within a <i>raw</i> SQL Body to be executed, and a corresponding <b>Variable with Value</b> has not been resolved, then an attempt will be always made to resolve it by searching for a corresponding <b>Stored Variable</b> by name (if it exists).<br>
An explicitly defined <b>Variable with Value</b> argument will always take precedence over the corresponding <b>Stored Variable</b>; in other words a <b>Stored Variable</b> (if defined) will just be considered the default value.
</td></tr></table>
<br><hr>
</li>
<li>
<h4>SQL Logfile</h4>
SQL queries will usually return some <b>resultset</b> potentially containing useful information.<br>
But executing a <b>SQL Procedure</b> or <b>Stored Procedure</b> will be seen by SQLite just as a simple SQL function call simply returning a final success or failure return code.<br>
Any information about all the intermediate resultsets, internally produced during the execution of the Procedure, will be lost.<br><br>
To avoid this, you can configure an external <b>SQL Logfile</b> to permanently store all intermediate resultsets into an ordinary text file. The SQL Logfile supports two different modes:
<ul>
<li><b>Truncate</b>: 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.</li>
<li><b>Append</b>: 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.<br>
<u>Note</u>: you'll be obviously required to flush the Logfile from time to time so to safely avoid to consume too much storage space.</li>
</ul>
</li>
</li>
</ol><br>
<hr>
<h2>Familiarizing with SQL Functions</h2>
<ol>
<li>
<h4>Handling SQL Procedure BLOB objects</h4>
<verbatim>
SELECT SqlProc_FromText('SELECT @col@ FROM @tbl@');
SELECT SqlProc_FromFile('/home/joe/template.txt', CP1252');
</verbatim>
<ul>
<li>you can create a SQL Procedure BLOB object by directly passing a Text SQL Body.</li>
<li>or alternatively you can load the SQL Body from an external file.<br>
<u>Note</u>: this is a security sensible operation, so you always need to set an explicit authorization by defining <b>SPATIALITE_SECURITY=relaxed</b>.</li>
<li>Both functions accept an optional <b>charset encoding</b> argument; in this case the SQL Body will be appropriately converted from the given charset into <b>UTF-8</b>.</li>
</ul>
<verbatim>
SELECT SqlProc_IsValid ( SqlProc_FromText('SELECT @col@ FROM @tbl@') );
-----
1
SELECT SqlProc_IsValid ( MakePoint(11.5, 42.5, 4326) );
-----
0
</verbatim>
<ul>
<li>you can use <b>SqlProc_IsValid</b> in order to check if a generic BLOB really is a valid SQL Procedure BLOB Object.
</ul>
<verbatim>
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@
</verbatim>
<ul>
<li>you can use <b>SqlProc_NumVariables</b> in order to check how many Variables are defined by a SQL Procedure BLOB Object.</li>
<li>and you can use <b>SqlProc_VariableN</b> in order to retrieve a Variable Name by specifying its relative <b>Index</b> (First Variable always corresponds to <b>Index=0</b>).</li>
<li>and finally you can use <b>SqlProc_AllVariables</b> in order to directly get all Variable Names formatted as a space-separated list.</li>
</ul>
<br><hr>
</li>
<li>
<h4>Error handling</h4>
<verbatim>
SELECT SqlProc_GetLastError();
</verbatim>
All these functions will raise an Exception on invalid arguments or errors.<br>
By calling <b>SqlProc_GetLastError</b> you'll usually get a more detailed explanation about the specific cause accounting for the failure.
<br><br><hr>
</li>
<li>
<h4>Creating Variables with Values</h4>
<verbatim>
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'
</verbatim>
<u>Note</u>: 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.
<br><br><hr>
</li>
<li>
<h4>Raw and Cooked SQL - replacing Symbols with their actual Values</h4>
<verbatim>
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;
</verbatim>
<ul>
<li>you can use <b>SqlProc_RawSQL</b> in order to directly check the SQL Body encapsulated into a SQL Procedure BLOB Object; all initial Variable definitions will be carefully preserved.</li>
<li>and you can use <b>SqlProc_CookedSQL</b> in order to directly check the SQL Body as it actually appears after replacing all required Variable Values.</li>
<li>Both functions are mainly intended for supporting debugging activities, so to make as easy as possible identifying eventual errors.</li>
</ul><br>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Note</u>: <b>SqlProc_CookedSQL</b> will accept an arbitrary list of <b>Variable with Value</b> arguments.
<ul>
<li>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).</li>
<li>all arguments from the second till the last are expected to be well-formed Variables with Values.</li>
<li>you can define up to <b>16</b> different Variables with Values.</li>
<li>any attempt to re-define the same Variable with another Value will be always considered as a fatal error.</li>
<li>any of the Variable arguments not contained in the <i>raw</i> SQL Body will be ignored.</li>
<li>if a Variable defined in the raw SQL Body does not matches a corresponding Variable argument then it will be set with a '<b>NULL</b>' string.</li>
<li>the same identical rules for passing Variable-Value arguments apply to <b>SqlProc_CookedSQL</b>, <b>SqlProc_Execute</b> and <b>StoredProc_Execute</b>.</li>
</ul>
</td></tr></table><br>
<table bgcolor="#f8c8c8" cellpadding="6" cellspacing="8"><tr><td>
<u>Warning</u>: referencing a Variable from within another Variable is not permitted.
<verbatim>
SELECT SqlProc_CookedSQL ( SqlProc_FromText('SELECT @col@ FROM @tbl@;'),
'@col@=first_name',
'@country@=italy',
'@tbl@=@country@_phone_book' );
</verbatim>
In the above example the <b>@country@</b> term into the Value string <i>'@country@_phone_book'</i> will not be considered as a Variable and will not be replaced.
</td></tr></table>
<br><hr>
</li>
<li>
<h4>Executing a SQL Procedure</h4>
<verbatim>
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
</verbatim>
you can use <b>SqlProc_Execute</b> in order to directly execute the whole SQL Body as a single monolithic unit.<br>
At the end of the process <b>1</b> (aka <b>True</b>) will be returned on full success; an Exception will be raised on failure, as will happens in this sample, you can then use <b>SqlProc_GetLastError</b> to retrieve the corresponding SQL error message.
<br><br>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Note</u>: all <b>Dot Macros</b> found within the SQL Body being executed will always be ignored.<br>
The same rule applies to <b>StoredProc_Execute</b> as well.</b>
</td></tr></table>
<br><hr>
</li>
<li>
<h4>Handling SQL Stored Procedures</h4>
<verbatim>
SELECT StoredProc_CreateTables();
-----
1
</verbatim>
you can use <b>StoredProc_CreateTables</b> in order to create, within your currently connected database, all <b>metatables</b> required to permanently register Stored Procedures and Stored Variables.<br><br>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Note</u>: 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 used in order to add Stored Procedures support on already existing databases created by previous versions. Calling <b>StoredProc_CreateTables</b> on behalf of a database already supporting the Stored Procedures metatable is an harmless operation simply returning <b>1</b> (full success).
</td></tr></table>
<verbatim>
SELECT StoredProc_Register ( 'sp_1', 'a sample Stored Procedure for testing', SqlProc_FromText('SELECT @col@ FROM @tbl@;' ));
-----
1
</verbatim>
you can use <b>StoredProc_Register</b> in order to permanently register a Stored Procedure.
<verbatim>
SELECT StoredProc_Delete ( 'sp_1' );
-----
1
</verbatim>
you can use <b>StoredProc_Delete</b> in order to permanently remove a previously registered Stored Procedure.
<verbatim>
SELECT SqlProc_RawSQL ( StoredProc_Get ( 'sp_1' ) );
-----
SELECT @col@ FROM @tbl@;
</verbatim>
you can use <b>StoredProc_Get</b> in order to retrieve the SQL Procedure BLOB Object from a registered Stored Procedure.
<verbatim>
SELECT StoredProc_UpdateTitle ( 'sp_1', 'this Title was changed' );
-----
1
</verbatim>
you can use <b>StoredProc_UpdateTitle</b> in order to modify the Title of an already registered Stored Procedure.
<verbatim>
SELECT StoredProc_UpdateSqlBody ( 'sp_1', SqlProc_FromFile('/home/joe/sql_scripts/stored_proc_test_v2.txt' );
-----
1
</verbatim>
you can use <b>StoredProc_UpdateSqlBody</b> 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).<verbatim>
SELECT StoredProc_Execute ( 'sp_1',
'@col@=srid',
'@tbl@=spatial_ref_sys' );
-----
1
</verbatim>
and finally you can use <b>StoredProc_Execute</b> in order to directly execute a Stored Procedure as a single monolithic unit.<br>
<br><br><hr>
</li>
<li>
<h4>Handling SQL Stored Variables</h4>
<verbatim>
SELECT StoredVar_Register ( 'pi', 'an universally useful constant', '3.14' );
-----
1
</verbatim>
you can use <b>StoredVar_Register</b> in order to permanently register a Stored Variable.
<verbatim>
SELECT StoredVar_Delete ( 'pi' );
-----
1
</verbatim>
you can use <b>StoredVar_Delete</b> in order to permanently remove a previously registered Stored Variable.
<verbatim>
SELECT StoredVar_Get ( 'pi' );
-----
@PI@=3.14
</verbatim>
you can use <b>StoredVar_Get</b> in order to retrieve the Variable with Value string from a registered Stored Variable.
<verbatim>
SELECT StoredVar_UpdateTitle ( 'pi', 'PI - the magic number' );
-----
1
</verbatim>
you can use <b>StoredVar_UpdateTitle</b> in order to modify the Title of an already registered Stored Procedure.
<verbatim>
SELECT StoredVar_UpdateValue ( 'pi', '3.1415926535' );
-----
1
</verbatim>
and finally you can use <b>StoredVar_UpdateValue</b> in order to modify the Variable with Value of an already registered Stored Variable.
</li><br><hr>
<li>
<h4>Handling the SQL Logfile</h4>
<verbatim>
SELECT SqlProc_SetLogfile ( '/home/sandro/sql_log.txt' );
-----
1
SELECT SqlProc_SetLogfile ( '/home/sandro/sql_log.txt', 1);
-----
1
SELECT SqlProc_SetLogfile ( NULL );
-----
1
</verbatim>
You can use <b>SqlProc_SetLogfile()</b> in order to enable or disable the SQL Logfile:
<ul>
<li>the first form (single argument) just requires to define the <b>absolute</b> or <b>relative pathname</b> of the intended Logfile.<br>
A Logfile defined in such a way will always operate in <b>Truncate</b> mode.</li>
<li>the second form (two arguments) supports a further <b>mode</b> argument:
<ul>
<li>if set to <b>FALSE</b> (<b>0</b>, default value) the Logfile will operate in <b>Truncate</b> mode.</li>
<li>if set to <b>TRUE</b> (any other value different from <b>0</b>) the Logfile will operate in <b>Append</b> mode.</li>
</ul>
<li>finally, you can pass a <b>NULL</b> path argument in order to immediately disable the Logfile.</li>
</li>
</ul>
<verbatim>
SELECT SqlProc_GetLogfile();
-----
/home/sandro/sql_log.txt
SELECT SqlProc_GetLogfile();
-----
NULL
</verbatim>
You can use <b>SqlProc_GetLogfile()</b> in order to check if the current DB-connection is using a Logfile or not.<br><br>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Note</u>: the Logfile setting is permanent on a per-connection basis.
<ul>
<li>any new DB-connection will always start by keeping the Logfile disabled.<br>
You are always required to perform an explicit call to <b>SqlProc_SetLogfile()</b> in order to effectively enable a Logfile.</li>
<li>once a Logfile has been enabled it will continue to support the current DB-connection during its lifespan, if not been explicitly disabled.</li>
<li>Logfile settings are strictly confined withing the current DB-connection, and will never automatically be continued in any following connections.</li>
</ul>
</td></tr></table>
</li>
</ol>
<br><hr>
<h2>Tutorial #1</h2>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
All the following tutorials are based on the same sample database, which should be <a href="http://www.gaia-gis.it/gaia-sins/sp_sample_db.7z">downloaded</a> before continuing.
</td></tr></table><br>
As you can see, this database contains three Spatial Tables representing the <b><i>Administrative Boundaries</i></b> of all Italian Municipalities as officially determined by <a href="https://en.wikipedia.org/wiki/National_Institute_of_Statistics_(Italy)">ISTAT</a>:
<ul>
<li>there are three different tables, each one corresponding to a specific National Census (years 1991, 2001 and 2011 are available).</li>
<li>all three tables have exactly the same layout.</li>
<li>Administrative Boundaries are always represented as MultiPolygons (Srid=32632).
</ul><br>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<h4>The Problem</h4>
We'll implement a <b>Procedure</b> intended to export selected Shapefiles.
<ul>
<li>The Census Year must be freely selectable.</li>
<li>A required feature is allowing either filtering a single Municipality or listing all Municipalities belonging to the a Province or Region.</li>
<li>Reprojecting to some alternative SRID will be a supported feature.</li>
<li>While doing all this we'll obviously try to take full advantage of using <b>Variables</b> to make the Procedure as generic and flexible as possible.</li>
</ul>
</td></tr></table><br>
<verbatim>
--
-- 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');
</verbatim>
As you can see the proposed implementation is basically simple. Now you simply have to <b>cut&paste</b> the above SQL body saving it into a file named <b>test_sp.txt</b> for further usage.<br><br>
Few interesting details worth being explained:
<ul>
<li>Variable <b>@year@</b> is intended to select which specific Spatial Table will be queried.</li>
<li>Variable <b>@srid@</b> is intended to specify the output SRID.</li>
<li>Variable <b>@shp_path@</b> is intended to specify the pathname of the output Shapefile.</li>
<li>Variables <b>@municipality@</b>, <b>@province@</b> and <b>@region@</b> are intended to specify the intended filtering criteria.<br>
This is a little bit tricky, so we'll go in further depth:
<ul>
<li>These Variables are expected to be actually replaced by <b>text string values</b>; so we've consistently <b>single-quoted</b> the variable symbol, so to allow for an easier value replacement.</li>
<li>We are systematically using the <b>Upper()</b> function simply because Province and Region names are all-uppercase in years 1991 and 2001 but not for the year 2011.<br>
Systematically using Upper() will insure consistentency of all names.</li>
<li>We are using three separate comparisons (one for Municipality, one for Province and one for Region) connected by <b>OR</b> logical operators so to make it possible by specifying just a single or more filter clauses.<br>
<u>Recall</u>: unassigned Variables will default to NULL; so by just declaring
<b>@province@=Arezzo</b> we'll then get a <b><i>cooked</i></b> WHERE clause like this:
<verbatim>
WHERE (Upper(name) = Upper('NULL') OR Upper(province) = Upper('Arezzo') OR Upper(region) = Upper('NULL'))
</verbatim>
</li>
</ul>
</li>
</ul>
<br>
<verbatim>
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
</verbatim>
In this first tutorial we have always <b>executed</b> a Procedure loaded from an external file.<br>
As you can see executing a Procedure is a straightforward task; you simply have to specify an appropriate list of <b>Variable Values</b> and that's all.
<hr>
<h2>Tutorial #2</h2>
<verbatim>
SELECT StoredProc_Register( 'my_proc', 'a Stored Procedure for exporting selected Shapefiles', SqlProc_FromFile ('C:/users/joe/stored_proc_test/test_sp.txt') );
</verbatim>
This second tutorial is basically the same of the first one.<br>
The only difference is that we'll now use a permanently registered <b>Stored Procedure</b>.
<verbatim>
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
</verbatim>
Executing a Stored Procedure isn't very different from executing a Procedure stored into an external file.<br>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
Executing your Procedures from external files or permanently registering Stored Procedures directly within a database is a matter of free choice.<br>
If you are planning to deploy a Procedure intended to support many different databases, then using an external file could probably simplify your work.<br>
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.
</td></tr></table>
<br><hr>
<h2>Tutorial #3</h2>
This last tutorial is just a small variation on the theme intended to highlight some potentially dangerous pitfalls related to <b>single-quoting</b>.
<verbatim>
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
</verbatim>
This first attempt will fail, and using <b>SqlProc_GetLastError</b> will explain why it failed<br>
We've passed a <b>'@municipality@=rio nell''elba'</b> Variable with Value, that will lead to a <b>cooked</b> WHERE clause like this:
<verbatim>
WHERE (Upper(name) = Upper('rio nell'elba') OR Upper(province) = Upper('NULL') OR Upper(region) = Upper('NULL'))
</verbatim>
The, not always obvious cause, is that <b>'rio nell'elba'</b> is an illegal SQL text string; the correct syntax should be instead <b>'rio nell''elba'</b>.
<br><br>
<verbatim>
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
</verbatim>
Resolving such an issue is rather trivial; we just have to use <b>four</b> consecutive single-quotes so to correctly escape the string value or<br> use double quotes for fields where single quotes often occur in names (<b>"@municipality@=rio nell'elba"</b>).
<br><br>
<table width="100%"><tr>
<td width="33%" align="left"></td>
<td align="center"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0+doc">back to index</a></td>
<td width="33%" align="right"></td>
</tr></table>
Z 63a2f1b1a9ad1ba7c8e121a5c48be778