Android Question sqlite "no such function" runtime error

[Wong Ka Chun]

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

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

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

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

4. 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:

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

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

3. 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') = ?

4. 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:

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

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

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"?

 

[Wong Ka Chun]

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.

@Mahares No, I have the same "no such function" error even having the jar in additional library folder. However, I try using sqlitecipher instead in coming project.

 

[DonManfred]

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.

Please note that it just was what you mention. A suggestion. I do not know if it is the solution as the downloads are only the "maybe compatible" jdbc driver.
The SQLite engine running on the android device is most probably more relevant. Don´t know exactly which SQLite Version is running on my devices. Or even on the TO devices
I just tried to help. I do not have a need for the mentioned json methods in SQLite too. But out of curiosity i would like to hear any progress about.

 

[Mahares]

But out of curiosity i would like to hear any progress

What puzzles me is the fact that json the extension was introduced in SQLite version 3.9.0 (2015-10-14) and the version on my tablet is: 3.19.4.
You would think that it should work without resorting SQLCipher, but it does not.

 

[RB Smissaert]

Please note that it just was what you mention. A suggestion. I do not know if it is the solution as the downloads are only the "maybe compatible" jdbc driver.
The SQLite engine running on the android device is most probably more relevant. Don´t know exactly which SQLite Version is running on my devices. Or even on the TO devices
I just tried to help. I do not have a need for the mentioned json methods in SQLite too. But out of curiosity i would like to hear any progress about.

>> Don´t know exactly which SQLite Version is running on my devices.

select sqlite_version()

I use sqlitecipher and get version 3.25.2

RBS

 

[Wong Ka Chun]

Thanks to Mahares and RB Smissaert,

Public Sub versionOne() As String
    Dim ver As String = ""
    Dim sql As SQL
    sql.Initialize(File.DirDefaultExternal, "JsonCol1.db", False)
    Try
        ver = sql.ExecQuerySingleResult("SELECT sqlite_version()")
    Catch
        LogColor(LastException, Colors.Red)
    End Try
    sql.close
    Return ver
End Sub

This function returns 3.8.10.2 (SQLite) < 3.9.0

Public Sub versionTwo() As String
    Dim ver As String = ""
    Dim sqlc As SQLCipher
    sqlc.Initialize(File.DirDefaultExternal, "JsonCol2.db", False, "123321", "")       
    Try
        ver = sqlc.ExecQuerySingleResult("SELECT sqlite_version()")
    Catch
        LogColor(LastException, Colors.Red)
    End Try   
    sqlc.close
    Return ver
End Sub

This function returns 3.25.2 (SQLite Cipher) > 3.9.0

That is the answer why SQLite Cipher works while SQLite doesn't.

 

[Wong Ka Chun]

Dear all,

I have just run this app on Mi 10 Lite 5G android phone which OS is Android 11.
It shows SQLite version 3.28.0 > 3.9.0 while SQLite Cipher version 3.25.2

Previously, I run this app on Android 6.0 and shows SQLite version 3.8.10.2 < 3.25.2 while SQLite Cipher version 3.25.2

Per Mr. Erel had said that SQLite version is dependent on Android OS but not on B4X IDE !!!

Thus, SQLite Cipher version is independent on Android OS and more reliable.

 

[Mahares]

OS is Android 11.
It shows SQLite version 3.28.0

I also ran it on OS 11 which has a SQLLite version 3.28.0 and SQLCipher 3.25.2. . Why don't the Json extension commands work on SQLite 3.28.0, but work on SQLCipher.3.25.2. Does anyone have an explanation.

 

[Wong Ka Chun]

I also ran it on OS 11 which has a SQLLite version 3.28.0 and SQLCipher 3.25.2. . Why don't the Json extension commands work on SQLite 3.28.0, but work on SQLCipher.3.25.2. Does anyone have an explanation.

@Mahares My coming app is for different version devices including 4.4.2, 5.0, 6.0 and 11.0. In order to support all above versions, local database must be same version (i.e. at least greater than 3.9.0) too.