Android Question SQLite Full Text Search Table Creation Works in B4A but not B4J

[Mahares]

I have attached a small but full cross-platform example project to reproduce the issue. The B4A portion works as expected , but the B4J code crashes and fails to create the FTS4 table.

Error occurred on line: 48 (B4XMainPage)
java.lang.reflect.InvocationTargetException
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.keywords.Common.CallSubDebug2(Common.java:477)
at b4j.example.b4xpagesmanager._createpageifneeded(b4xpagesmanager.java:864)

strQuery="CREATE VIRTUAL TABLE IF NOT EXISTS " & DBTableName & " USING fts4(content TEXT)"
SQL1.ExecNonQuery(strQuery)  'error here

 

[Erel]

This seems to work:

strQuery="CREATE VIRTUAL TABLE fts_table USING fts4(content TEXT);"

Probably related to the SQLite version.

 

[Mahares]

Probably related to the SQLite version.

It works in B4A but not B4J. So what is the solution for B4J. How did you get it to work in B4J

 

[Erel]

It didn't like the NOT EXISTS. See my previous answer.

Note that you can change the SQLite version very easily: https://github.com/xerial/sqlite-jdbc/releases

 

[Mahares]

you can change the SQLite version very easily

You are absolutely correct. I downloaded sqlite-jdbc-3.36.0.1.jar, the latest jar from here: https://github.com/xerial/sqlite-jdbc/releases
to the B4J LibraryAdditional subfolder, then made a reference in Main: #AdditionalJar: sqlite-jdbc-3.36.0.1.jar
to replace the old reference. Then the syntax: CREATE VIRTUAL TABLE IF NOT EXISTS started working in B4J.
Therefore I concluded: driver #AdditionalJar: sqlite-jdbc-3.7.2 did not support IF NOT EXIST in syntax, but
sqlite-jdbc-3.36.0.1.jar did. However, I went back to the old reference for compatibility with B4J installation, unless you have a way to tell it to use the older jar if the latest is not installed or available.