Search code examples
jsonsqlitenetbeanscodenameone

CodeName One, SQLite and JSON1 Extension

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.