How to enable SQL logging from Java?

[Jukka Rahkonen]

Hi,

How could I enable SQL statement logging when I create a connection from Java? I have read that there is a dot command for that https://www.gaia-gis.it/fossil/libspatialite/wiki?name=tools-4.0 but I have no idea about how to execute that from Java.

-Jukka Rahkonen-

[a.fu...@lqt.it]

Hi Jukka,

both spatialite_gui and spatialite CLI have the ability to
permanently save any SQL query being executed into the
"sql_statements_log" table, but this feature is implemented
by the tools code and isn't directly supported by libspatialite
itself.

the "dot command" you are referencing simply switches on/off
sql logging when using the CLI tool; on the GUI there is button
on the toolbar for the same task.

just a basically simple consideration: spatialite simply is
an extension to SQLite, and SQLite calls libstatialite
only when some Spatial SQL function requires to be executed.
short conclusion: libsqlite itself never has the slightest
idea about the SQL query under execution, it only knows
about single SQL functions calls.

so implementing a SQL logging facility can never be a task
directly supported by the library itself; it's the main
application executing SQL queries that must be responsible
for all this.

implementing SQL logging it's not a too much difficult task
anyway; basically, you simply have to:
1. centralize the execution of all SQL queries in a single
function, so to strongly semplify the required code.
2. save the SQL statement text, the result state (success or
failure) and the begin/end timestamps
3. and finally you can insert a row into "sql_statements_log"
for each SQL query being executed before returning the
resultset to the caller.

bye Sandro