D 2018-09-06T09:03:11.854
L Stored\sProcedures
P 8e1114ed50e30294658473aaa4ab5ff334a47127
U sandro
W 49001
<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>5.0.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 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 <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>
<li>
<h4>Sql/Stored Procedures as real Functions</h4>
An Sql or Stored Procedure can effectively act as a genuine Function by appropriately calling <b>SqlProc_Return()</b> (or <b>StoredProc_Return()</b>, that simply is an <i>alias name</i> for the same function).
<verbatim>
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
</verbatim>
<ul>
<li>This function stops as soon as possible the execution of the current SQL Body, then returning a <b>value</b> to the caller.<br>
The <b>Return Value</b> defined by <b>SqlProc_Return()</b> can freely be of any possible data-type: NULL, INTEGER, DOUBLE, TEXT or BLOB.</li>
<li><u>Note</u>: it's not an exact equivalent of the most usual <b>RETURN</b> statement defined by many common languages:
<ul>
<li>Just calling <b>SqlProc_Return()</b> will not cause the execution to immediately stop; this could easily be a deferred action.</li>
<li>What <b>SqlProc_Return()</b> really does is <i>booking</i> 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.</li>
<li>Only at this point <b>SqlProc_Execute()</b> (or <b>StoredProc_Execute)</b> will really stop executing the current SQL Block imediately returning the execution control to the caller and passing back to it the <b>Return Value</b> spefified by <b>SqlProc_Return()</b>.</li>
</ul></li>
<li><u>Important notice</u>: an SQL Body terminating without explicitly calling <b>SqlProc_Return()</b> will behave exactly as if had implicitly called <b>SqlProc_Return(NULL).</li>
<li><u>Warning</u>: any call to <b>SqlProc_Return()</b> outside the execution context of <b>SqlProc_Execute()</b> (or <b>StoredProc_Execute</b>) will be simply considered as an effectless <b>no-op</b>.
</ul>
<br>
Just few basic examples about Sql Procedures acting as real Functions.<br><br>
<b>Example #1</b>: a pseudo-function returning the sum of two Integers.
<verbatim>
SELECT SqlProc_Execute(SqlProc_FromText(
'SELECT SqlProc_Return(@arg1@ + @arg2@)'),
'@arg1@=1000', '@arg2@=25');
-------------------------
1025
</verbatim>
<b>Example #2</b>: a pseudo-function returning the product of two Doubles.
<verbatim>
SELECT SqlProc_Execute(SqlProc_FromText(
'SELECT SqlProc_Return(@arg1@ * @arg2@)'),
'@arg1@=3.25', '@arg2@=100.0');
-------------------------
325.000000
</verbatim>
<b>Example #3</b>: a pseudo-function returning the concatenation of two Text Strings.
<verbatim>
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
</verbatim>
<b>Example #4</b>: a pseudo-function returning a BLOB Geometry.
<verbatim>
SELECT AsEWKT(SqlProc_Execute(SqlProc_FromText(
'SELECT SqlProc_Return(MakePoint(@x@, @y@, @srid@))'),
'@x@=10', '@y@=20', '@srid@=4326'));
-------------------------
SRID=4326;POINT(10 20)
</verbatim>
</li><li>
<h4>Sql/Stored Procedures iterative execution</h4>
<b>SqlProc_ExecuteLoop()</b> and <b>StoredProc_ExecuteLoop()</b> are very similar respectively to <b>SqlProc_Execute()</b> and <b>StoredProc_Execute()</b>; the signature of these SQL function pairs is exactly the same.<br><br>
But there is a very relevant difference in their respective behavior:
<ul>
<li>as we have already seen, both <b>SqlProc_Execute()</b> and <b>StoredProc_Execute()</b> run just once, then returning to the caller the <b>return value</b> implicitly or explicitly set by <b>SqlProc_Return()</b> or <b>StoredProc_Return()</b>.</li>
<li>both <b>SqlProc_ExecuteLoop()</b> and <b>StoredProc_ExecuteLoop()</b> will instead automatically iterate as many times as required the execution of their <b>SQL Body</b>.<br>
The loop will break only when the last iteration will finally return a <b>negative</b> or <b>zero</b> integer value.</li>
<li><b><u>Warning</u></b>: a badly implemented iteration can easily cause a nasty <a href="https://en.wikipedia.org/wiki/Infinite_loop">endless loop</a>; beware and be very cautious.</li>
</ul><br>
<b>Example</b>
<verbatim>
--
-- 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;
</verbatim>
This sample SQL Body (<b>loop.sql</b>) will take the following actions:
<ol>
<li>first it will try to create a table; if the table alredy exists any error will be silently ignored.</li>
<li>then it will INSERT a random row in this table.</li>
<li>and finally if will check how many rows have been already inserted in the table.<br>
When the predefined limit will be reached, the loop will terminate.</li>
</ol>
<verbatim>
SELECT SqlProc_ExecuteLoop(SqlProc_FromFile('./loop.sql'), '@LIMIT@=1000');
SELECT * FROM looping;
</verbatim>
As you can easily check, the iterative execution of <b>loop.sql</b> has just created the target table inserting thousand rows into it.
</li>
<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>
</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 third 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><hr>
<h2>Tutorial #4</h2>
This last tutorial is exactly the same of <b>Tutorial #2</b>, but this time we'll enable a <b>logfile</b>.
<verbatim>
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
</verbatim>
We'll start by calling <b>SqlProc_SetLogfile()</b> so to enable the logging facility; the logfile will be <b>C:/users/joe/stored_proc_test/logfile.txt</b> and it will be opened in <b>append mode</b>.
We'll end finally by calling once again <b>SqlProc_SetLogfile()</b> by passing a <b>NULL</b> pathname so to disable the logging facility.<br><br>
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:
<verbatim>
=========================================================================================
== 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
=========================================================================================
</verbatim>
<br><hr>
<h2>Tutorial #5 (advanced)</h2>
<h3>5.1 Adapted Stored Procedure for istat_it</h3>
Based on <b>Tutorial #2</b>, but adapted so that the Stored Procedures are contained in an external <b><i>attach</i></b>ed Database where the source Database should only be read but not changed.
<br><br>
The Stored Procedure has been adapted to support an <b><i>attach</i></b>ed Database.<br>
It is specific for the Source <b>istat_it</b> Database.<br>
<b>@attach_schema_name@</b> will contain the schema-name used in the calling script:
<verbatim>
-- -- ---------------------------------- --
-- 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');
-- -- ---------------------------------- --
</verbatim>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Notes</u>: Difference from main Sample:<br>
The calling script will run against the Database that contains this Stored Procedure and ATTACH the source Database using a <b>schema-name</b>.
<ul>
<li>Use of ATTACH/DETACH from inside a Stored Procedures will work the first time,but fail when called again with
<ul>
<li>'gaia_sql_proc_execute: <b>database db_source is already in use</b></li>
</ul></li>
<li>Temporary TABLE created in during the Stored Procedure
<ul>
<li>will be contained in the Stored Procedures Database and not in the source Database.</li>
</ul></li>
<li>SELECT messages called during the Stored Procedure
<ul>
<li>will be not be shown and have been commented out.</li>
</ul></li>
<li>The Export Shape directory and file names are seperate
<ul>
<li>was <b>shp_path</b>, now <b>shp_directory</b> and <b>shp_file</b>.</li>
<li>The needed directory "/" between shp_directory and shp_file will be added here.</li>
</ul></li>
</ul>
</td></tr></table>
<br><hr>
<h3>5.2 Creating Stored Procedure Database</h3>
The Stored Procedure and possible default values will be stored in the created Stored Procedures Database.<br>
<b>@attach_schema_name@</b> will contain the schema-name used in the calling script:
<verbatim>
-- -- ---------------------------------- --
-- export SPATIALITE_SECURITY=relaxed
-- rm stored_procedure_db.db
-- spatialite stored_procedure_db.db
</verbatim>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Notes</u>: Difference from main Sample:
<ul>
<li>The called Stored Procedure is specific to the ATTACHed source Database.
<ul>
<li>care must be taken that they both match</li>
</ul></li>
<li>A different Source Database can be added during the calling script
<ul>
<li>that must call a corresponding Export Stored Procedure.</li>
</ul></li>
<li>The Shape output directory
<ul>
<li>may be, but must not be the same for all exported Shapes.</li>
</ul></li>
</ul>
</td></tr></table>
<br><hr>
<h3>5.3 The calling script, possibly calling difference Stored Procedures and source Databases</h3>
The Stored Procedure and possible default values will be stored in the created Stored Procedures Database.<br>
Different Source Databases, using different Stored Procedure, could be used within one script:
<verbatim>-- -- ---------------------------------- --
-- 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();
-- -- ---------------------------------- --
</verbatim>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Notes</u>: Difference from main Sample:
<ul>
<li>The called Stored Procedure assumes that the source Database has been ATTACHed, using the given <b>attach_schema_name</b>.
<ul>
<li>if the used schema-name <b>is not</b> the same as the set default value, it <b>must</b> be given as a Stored Procedure parameter.</li>
</ul></li>
<li>The Shape output fine-name
<ul>
<li>must be <b>unique</b> within the Shape output directory.</li>
</ul></li>
<li>The Shape output directory
<ul>
<li>may be, but must not be the same for all exported Shapes.</li>
</ul></li>
</ul>
</td></tr></table>
<br><hr>
<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 1e20cee1885d996fccb375b17e7aa849