D 2017-11-02T18:42:52.559
L Stored\sProcedures
P fa11034f8b0f609388b0658fcc0dd185ce06a68d
U sandro
W 13130
<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).
</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>
</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.
</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>
</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.
</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>
</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>
</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>
</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.
</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.
</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>
</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>
</li>
</ol>
<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 0231cd1f018d3a0d1181acd2567099be