<h2>SQL Statements Log as implemented in version 4.0.0</h2>
<a
<h3>The "<i>sql_statements_log</i>" table</h3>
This new table will be automatically created in any new DB primed using <i>libspatialite</i> version 4.0.0, and the intended scope is supporting the new <b>SQL Log</b> facility.<br><br>
<i>libspatialite</i> version 4.0.0 now includes two APIs directly supporting the SQL Log: and
Both <b>spatialite_gui</b> and <b>spatialite CLI</b> tools now fully support the SQL Log facility; and rather obviously any third party tool is welcome in exploiting this cool new feature.<br><br>
The following example explains how all this practically works.<br><br>
<table width="100%" bgcolor="#f4f4f4" border="1" cellspacing="4" cellpadding="4"><tr>
<td bgcolor="#e8e8e8"
<td bgcolor="#e8e8e8"
<td bgcolor="#e8e8e8"
<td bgcolor="#e8e8e8"
<td bgcolor="#e8e8e8"
<td bgcolor="#e8e8e8"
<td bgcolor="#e8e8e8"
<tr><td <td>2012-09-04T22:26:03.692Z</td><td>spatialite CLI</td><td>
SELECT r.nome_reg, p.nome_pro, c.nome_com<br>
FROM com2011 AS c<br>
JOIN prov2011 AS p ON (c.cod_pro = p.cod_pro)<br>
JOIN reg2011 AS r ON (c.cod_reg = r.cod_reg)<br>
WHERE c.cod_reg = 10<br>
ORDER BY c.nome_pro, c.nome_com;</td><td align="right">0</td><td>no such column:
<tr><td <td>2012-09-04T22:26:13.737Z</td><td>spatialite CLI</td><td>
SELECT r.nome_reg, p.nome_pro, c.nome_com<br>
FROM com2011 AS c<br>
JOIN prov2011 AS p ON (c.cod_pro = p.cod_pro)<br>
JOIN reg2011 AS r ON (c.cod_reg = r.cod_reg)<br>
WHERE c.cod_reg = 10<br>
ORDER BY p.nome_pro, c.nome_com;</td><td
<tr><td
SELECT r.nome_reg, p.nome_pro, c.nome_com<br>
FROM com2011 AS c<br>
JOIN prov2011 AS p ON (c.cod_pro = p.cod_pro)<br>
JOIN reg2011 AS r ON (c.cod_reg = r.cod_reg)<br>
WHERE c.cod_reg = 9<br>
ORDER BY c.nome_com DESC</td><td
</table><br>
<ul>
<li>the <b>id</b> column is the <u>event ID</u> (<i>Primary Key</i>, uniquely identifying each single SQL Statement into the Log).</li>
<li>the <b>time_start</b> and <b>time_end</b> columns contaits the <i>timestamps</i> measured immediately before and after executing the SQL statement itself.</li>
<li>the <b>user_agent</b> column identify the tool being used to executed the SQL statement.</li>
<li>the <b>sql_statement</b> column contains the SQL statement body.</li>
<li>the <b>success</b> column (<i>boolean</i>) tells if the statement execution was successful or not.</li>
<li>the <b>error_cause</b> will contain the <u>error message</u> eventually returned by SQLite.</li>
</ul><br>
You can learn more about the SQL Log as actually implemented by the <b>spatialite_gui</b> and <b>spatialite CLI</b> tools by reading this <a page</a>
<h4>Strictly related (and really useful) SQL Query</h4>
In order to directly get the execution timings (expressed in <i>milliseconds</i>) you simply have to use and SQL query like the following one:
<table bgcolor="#e0e0e0" cellspacing="8" cellpadding="4"><tr>
<td>SELECT id, JulianDay(time_end) - JulianDay(time_start) AS millis, sql_statement<br>
FROM sql_statements_log<br>
WHERE success = 1;

<hr><br>
<a