Android Question sqlite "no such function" runtime error

Wong Ka Chun · Dec 20, 2021

Dear all, I have successfully done following queries in DB Browser for SQLite:

Code:

INSERT INTO tblUser (name, phone) values("oz", json('{"cell":"+491765", "home":"+498973"}'));

Code:

SELECT json_set(tblUser.phone, '$.cell', '123') FROM tblUser;

Code:

SELECT tblUser.name From tblUser WHERE json_extract(tblUser.phone, '$.cell') = '+491765';

Code:

UPDATE tblUser SET phone = (SELECT json_set(tblUser.phone, '$.cell', '123456789') from tblUser) WHERE name = 'oz';

However, SQLiteExceptions have occurred when I moved above queries into codes:

Code:

android.database.sqlite.SQLiteException: no such function: json (code 1): , while compiling: INSERT INTO tblUser(name, phone) VALUES (?, json(?))

Code:

android.database.sqlite.SQLiteException: no such function: json_set (code 1): , while compiling: SELECT json_set(tblUser.phone, '$.cell', ?) FROM tblUser

Code:

android.database.sqlite.SQLiteException: no such function: json_extract (code 1): , while compiling: SELECT tblUser.name, tblUser.phone FROM tblUser WHERE json_extract(tblUser.phone, '$.cell') = ?

Code:

android.database.sqlite.SQLiteException: no such function: json_set (code 1): , while compiling: UPDATE tblUser SET phone = (SELECT json_set(tblUser.phone, '$.cell', ?) from tblUser)WHERE name= ?

The code with errors are listed:

Code:

Public Sub insertrecord(n As String, m As Map)
    Dim sql As SQL
    Dim sqlstr As String = "INSERT INTO tblUser(name, phone) VALUES (?, json(?))"
    sql.Initialize(File.DirDefaultExternal, "JsonColumn.db", False)
    Try
        sql.ExecNonQuery2(sqlstr, Array As String(n, MapToJStr(m))) '<-- error occurs
        sql.Close
        CallSubDelayed2(callback, event, CreateMap("issuccess": True, "task": "insertrecord", "msg": "record is inserted"))
    Catch
        LogColor("insertrecord: " & LastException.Message, Colors.Red)
        CallSubDelayed2(callback, event, CreateMap("issuccess": False, "task": "insertrecord", "errmsg": LastException.Message))       
    End Try
End Sub

Code:

Public Sub updaterecord(n As String, ph As String)
    Dim sql As SQL
    Dim sqlstr As String = "UPDATE tblUser SET phone = " & _
        "(SELECT json_set(tblUser.phone, '$.cell', ?) from tblUser)" & _
        "WHERE name= ?"
    sql.Initialize(File.DirDefaultExternal, "JsonColumn.db", False)
    Try
        sql.ExecNonQuery2(sqlstr, Array As String(ph, n)) '<-- error occurs
        sql.Close
        CallSubDelayed2(callback, event, CreateMap("issuccess": True, "task": "updaterecord", "msg": "record is updated"))
    Catch
        LogColor("updaterecord: " & LastException.Message, Colors.Red)
        CallSubDelayed2(callback, event, CreateMap("issuccess": False, "task": "updaterecord", "errmsg": LastException.Message))
    End Try
End Sub

Code:

Public Sub selectrecord(pn As String)
    Dim sql As SQL
    Dim sqlstr As String = "SELECT tblUser.name, tblUser.phone FROM tblUser WHERE json_extract(tblUser.phone, '$.cell') = ?"
    sql.Initialize(File.DirDefaultExternal, "JsonColumn.db", False)
    Dim rs As ResultSet
    Try
        rs = sql.ExecQuery2(sqlstr, Array As String(pn)) '<-- error occurs
        Dim lst As List
        lst.Initialize
        Do While rs.NextRow
            lst.Add(CreateMap("name": rs.GetString("name")))
        Loop
        sql.Close
        CallSubDelayed2(callback, event, _
            CreateMap("issuccess": True, "task": "insertrecord", "msg": "record is selected", "list": lst))
    Catch
        LogColor("selectrecord: " & LastException.Message, Colors.Red)
        CallSubDelayed2(callback, event, CreateMap("issuccess": False, "task": "selectrecord", "errmsg": LastException.Message))
    End Try
End Sub

The current version of SQL library is 1.50.
Is the library not up-to-date enough to use json functions such as "json", "json_set", "json_extract"?

LucaMs · Dec 20, 2021

I don't know about B4A SQL but I found right now:
https://www.sqlite.org/json1.html

3.4. Compatibility

The json1 extension uses the sqlite3_value_subtype() and sqlite3_result_subtype() interfaces that were introduced with SQLite version 3.9.0 (2015-10-14) The json1 extension will not work in earlier versions of SQLite.

However, I will never use that function; I'd rather create other fields.

DonManfred · Dec 20, 2021

Wong Ka Chun said:
Dim sqlstr As String = "INSERT INTO tblUser(name, phone) VALUES (?, json(?))"

Replace with

B4X:

Dim sqlstr As String = "INSERT INTO tblUser(name, phone) VALUES (?, ?)"

Wong Ka Chun · Dec 20, 2021

Dear DonManfred,

I intentionally to use "json(?)" to test whether SQL object can handle json function.

Erel · Dec 20, 2021

The SQLite engine is part of the OS. It doesn't support this extension.
The B4A SQL library simply passes the commands to the native engine.

Wong Ka Chun · Dec 20, 2021

Dear Erel,

Would SQLite engine be updated to include this extension on next B4A version?

Erel · Dec 20, 2021

No. It has nothing to do with B4A. It is part of the operating system.

You will need to wait for Google to update it in a new version of Android.

LucaMs · Dec 20, 2021

LucaMs said:
However, I will never use that function; I'd rather create other fields.

From Wikipedia:

Atomicity
...
The same term is also used in the definition of First normal form in database systems, where it instead refers to the concept that the values for fields may not consist of multiple smaller value to be decomposed, such as a string into which multiple names, numbers, dates, or other types may be packed.

OliverA · Dec 20, 2021

Wong Ka Chun said:
Would SQLite engine be updated to include this extension on next B4A version?

Erel said:
No. It has nothing to do with B4A. It is part of the operating system.

You will need to wait for Google to update it in a new version of Android.

There may be a workaround: use SQLCipher. SQLCipher is based on newer versions of SQLite and may therefore support your extension(s). This solution worked in another case where a user needed FTS5 support (see https://www.b4x.com/android/forum/threads/sqlite3-fts5.110369/#post-688824).

Mahares · Dec 20, 2021

OliverA said:
There may be a workaround: use SQLCipher

Yes, @OliverA. You can use SQLCipher. I have prepared a json project to post when I get a chance in the day to come, unless someone beats me to the deadline. I just want to tweak it some, before posting. But, it does work.

Mahares · Dec 21, 2021

Wong Ka Chun said:
SQLiteExceptions have occurred when I moved above queries into codes:

Here is a full functional B4XPages project in B4A with the json extension in SQLCipher (need SQLcipher library 1.60 in Additional B4A) to insert and display records. The json extension works when you use SQLCipher.

Code:

Sub Class_Globals
    Private Root As B4XView   'ignore
    Private xui As XUI    
    Private SQL1cipher As SQLCipher   'need SQLCipher library
    Private MyFolder As String    = "test"
    Private DBFileName As String = "testcipher.db"
    Private DBFilePath As String
    Private DBTableName As String="tblUser"
    Private strQuery As String
End Sub

Public Sub Initialize
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
'    Root.LoadLayout("MainPage")
    xui.SetDataFolder(MyFolder)
    #if B4A 
        DBFilePath = xui.DefaultFolder & "/" & MyFolder
        File.MakeDir(DBFilePath,"")        
    '    File.Delete(DBFilePath,DBFileName)        
        SQL1cipher.Initialize(DBFilePath, DBFileName, True, "", "")
    #else if B4J
        SQL1cipher.InitializeSQLite(xui.DefaultFolder, DBFileName, True)
    #end if

    CreateTable  
    InsertRecords
    DisplayRecords         
End Sub

Sub CreateTable
    #if B4J 
    #else
'    strQuery=$"DROP TABLE If EXISTS ${DBTableName}"$
'    SQL1cipher.ExecNonQuery(strQuery)
    strQuery=$"CREATE TABLE  IF NOT EXISTS ${DBTableName}(name TEXT, phone TEXT, PRIMARY KEY(name))"$
    SQL1cipher.ExecNonQuery(strQuery)    
    #end if    
End Sub

Sub InsertRecords
    strQuery =$"INSERT OR IGNORE INTO ${DBTableName} (name, phone) values("oz", json('{"cell":"+491765", "home":"+498973"}'))"$
    SQL1cipher.ExecNonQuery(strQuery)
    
    strQuery =$"INSERT  OR IGNORE INTO ${DBTableName} (name, phone) values("john", json('{"cell":"123456", "home":"989654"}'))"$
    SQL1cipher.ExecNonQuery(strQuery)
    
    strQuery =$"INSERT  OR IGNORE INTO ${DBTableName} (name, phone) values("oliver", json('{"cell":"54687", "home":"6009643"}'))"$
    SQL1cipher.ExecNonQuery(strQuery)
End Sub

Sub DisplayRecords
    strQuery = $"Select name, phone From ${DBTableName} WHERE json_extract(phone, '$.home') LIKE ? "$
    Dim rs As ResultSet
    rs = SQL1cipher.ExecQuery2(strQuery, Array As String("%89%"))
    Do While rs.NextRow
        Log($"The name is: ${rs.GetString("name")}"$)
        Log($"The phone is: ${rs.GetString("phone")}"$)
    Loop
    rs.Close
End Sub

Wong Ka Chun · Dec 21, 2021

Dear OliverA and Mahares,

I have revised to use SQLCipher. The json function in sql command can be used. Thank you.

Wong Ka Chun · Dec 21, 2021

Dear LucaMs,

The reason for json or object column because I don't want to split into two tables.
I want to store 2 layers data into one table just like mongodb style.

DonManfred · Dec 21, 2021

Wong Ka Chun said:
The reason for json or object column because I don't want to split into two tables.

The column itself is a varchar or blob.
No need for a json() method in SQLite. You can build the json from within B4X without a problem. And for sure you can store any layerinfo into any varchar or blob Column.

Wong Ka Chun · Dec 21, 2021

DonManfred said:
The column itself is a varchar or blob.
No need for a json() method in SQLite. You can build the json from within B4X without a problem. And for sure you can store any layerinfo into any varchar or blob Column.

Dear DonManfred,

Functions of json (i.e. used in INSERT command) and json_set (i.e. used in UPDATE command) can be achieved by JSON library (i.e. JSONGenerator, JSONParser) in B4X.
However, function json_extract in WHERE command cannot be replaced.
You can not looping the whole records in table, parsing this column as object and compare its fields one by one!

DonManfred · Dec 21, 2021

Based on @LucaMs answer here: https://www.b4x.com/android/forum/threads/sqlite-no-such-function-runtime-error.136982/post-866800
you need to use an updated SqLite.

Did you tried to download an updated jdbc-driver?

SQLite JDBC - Library version updates

Hi everybody! This thread is supposed to get all SQLite JDBC library version links together so as to find and download them easier. As a heavy B4J+SQLite user, I'm always looking forward to have new JDBC versions. All information contained herein has been taken from Mr. Taro L. Saito (xerial)...

www.b4x.com

Wong Ka Chun · Dec 21, 2021

@DonManfred
Is sqlite-jdbc-3.36.0.3.jar downloaded from https://github.com/xerial/sqlite-jdbc/releases/download/3.36.0.3/sqlite-jdbc-3.36.0.3.jar
placed at C:\Program Files (x86)\Anywhere Software\Basic4android\Libraries?

DonManfred · Dec 21, 2021

You should put it in the additional library path.
Also note to use #additionaljar in your project to include it.

LucaMs · Dec 21, 2021

Why should you do this?

Wong Ka Chun said:
I don't want to split into two tables.

Wong Ka Chun said:
insert:
INSERT INTO tblUser (name, phone) values("oz", json('{"cell":"+491765", "home":"+498973"}'));

Just add 2 columns to tblUser: Cell & Home (and remove Phone)

Mahares · Dec 22, 2021

Wong Ka Chun said:
Is sqlite-jdbc-3.36.0.3.jar downloaded

I tried the suggestion by Manfred by downloading the jar and putting it in additional lib folder and making a reference: #additionaljar, but it would not work with : no such function json error. Did you try it on your project. And if it worked could you show how.
Please note that I have no use for json in SQLite. I am doing this only out of curiosity to learn something new to add to one's repertoire.

Android Question sqlite "no such function" runtime error

Member

Attachments

Expert

3.4. Compatibility​

Expert

Member

B4X founder

Member

B4X founder

Expert

Expert

Expert

Expert

Member

Attachments

Member

Expert

Member

Expert

Member

Expert

Expert

Expert

Similar Threads

3.4. Compatibility