D 2017-11-02T22:57:24.513
L Stored\sProcedures
P a0db55375115ac8c6ff4a1f45c93aa43f7a45b35
U sandro
W 16794
<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 since 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 allowed by mixing standard <b><i>SQL scripts</i></b> and <b><i>Variables</i></b> to be replaced just before actual execution.
Not only: think of many wonderful opportunities deriving from permanently storing within a database such <b><i>SQL Scripts with Variables</i></b> so to make as simple as possible automating common workflows to be repeatedly executed.
This exactly is the intended scope of this new module.<br><br>
We'll start first by formally defining few basic concepts and objects so 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. Examples:
<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 sequence 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 (atomic 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 <i><u>mnemonic symbol</u></i> 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 an <i><u>aesthetic 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> internally using <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, just because there aren't <u><i>hardwired</i></u> table-names and column-names, all them are now become <u><i>symbolic</i></u>.<br>
So we are now free to reuse more times this SQL Body in different contexts; we just have to appropriately set the Variable Values just immediately before actual execution.<br><br>
<b><i>SQL Bodies</i></b> exist in two different forms:
<ul>
<li><b>raw</b>: still including symbolic Variables.</li>
<li><b>cooked</b>: presenting all symbols properly replaced by their actual values.<br>
Rather obviously, you can successfully execute only the <b><i>cooked</i></b> form.</li>
</ul>
<br><hr>
</li>
<li>
<h4>SQL Procedure BLOB object</h4>
This simply is a <b><i>BLOB encoded</i></b> pre-compiled <b><i>SQL Body</i></b>, and is the usual target expected to be handled by practically all SQL functions supporting Stored Procedures and alike.
<br><br><hr>
</li>
<li>
<h4>Stored Procedure</h4>
A <b><i>SQL Procedure BLOB object</i></b> permanently stored into a database. This will make easier reusing more times the same SQL workflow, perhaps so to implement routinely periodic tasks (to be executed daily / weekly / monthly and so on).<br>
Stored Procedures always have 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 <b><i>SQL Body</i></b> including <b><i>Variables</i></b> you are obviously expected to set some appropriate <b><i>Value</i></b> for each Variable, so to allow for replacing <u><i>Symbols</i></u> with actual <u><i>Values</i></u> (i.e. for properly transforming <b>raw</b> SQL into <b>cooked</b> SQL).<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 actual Values is a blind process completely insensible to the specific syntactic context.<br>
You are fully responsible for correctly apply single- or double-quoting wherever required by SQL syntax.
</td></tr></table>
<br><hr>
</li>
<li>
<h4>Stored Variable</h4>
A <b><i>Variable with Value string</i></b> permanently stored into a database.<br>
Stored Variables always have 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>Variable with Value string</b>.</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.500000000
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 s 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 until the end are expected to be well-formed Variables with Values.</li>
<li>you can define as much as <b>16</b> different Variables with Values.</li>
<li>any attempt to define twice a Value for the same Variable will be always considered as a fatal error.</li>
<li>if one of the Variable arguments does not match one the Variables internally defined by the Raw SQL Body it will be simply ignored.</li>
<li>if a Variable defined into the Raw SQL Body does not matches a corresponding Variable argument then it will replaced by a default '<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><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' );
-----
1
</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.<br><br>
<table bgcolor="#f8f8d8" cellpadding="6" cellspacing="8"><tr><td>
<u>Note</u>: all <b>Dot Macros</b> eventually found within the SQL Body being executed will be always silently 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 on 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 useful in order to retrofit Stored Procedures support on already existing databases create 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@=first_name',
'@tbl@=phone_book' );
-----
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 ( 'var_1', 'an universally useful constant', '@PI@=3.14' ));
-----
1
</verbatim>
you can use <b>StoredVar_Register</b> in order to permanently register a Stored Variable.
<verbatim>
SELECT StoredVar_Delete ( 'var_1' );
-----
1
</verbatim>
you can use <b>StoredVar_Delete</b> in order to permanently remove a previously registered Stored Variable.
<verbatim>
SELECT StoredVar_Get ( 'var_1' ) );
-----
@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 ( 'var_1', '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 ( 'var_1', '@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>
</ol>
<br><hr>
<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 4dac5103f03f61997f1f55e2909b1138