Issue
I've written a CodeName One application in NetBeans and I'm testing via the Simulator.
I have a local SQLite database and can execute a simple query in my application e.g.
SELECT *
FROM tempJSON;
When I try to introduce a function (e.g. json_tree) from the JSON1 Extension (https://www.sqlite.org/json1.html) e.g.
SELECT j.value
FROM tempJSON AS d
JOIN json_tree(d.textJSON) AS j
WHERE j.key = 'RunnerName';
I receive the following error.:
java.io.IOException: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)
Note: both queries execute successfully in SQLiteStudio
What am I missing? (e.g. a configuration issue)
Or is this not possible (yet)?
Solution
You can't use extensions in the standard SQLite. On the device we use the builtin sqlite versions and they differ a bit between iOS/Android so relying on an extension that might not be there is problematic.
As a solution we did this: https://www.codenameone.com/blog/spatial-pluggable-sqlite.html
This was done for spatial extensions but the concept is identical if you want to support JSON extensions: bundle your own copy of sqlite.
Answered By - Shai Almog
Answer Checked By - Willingham (JavaFixing Volunteer)
