Android Question sqlite "no such function" runtime error

Dear all, I have successfully done following queries in DB Browser for SQLite:
  1. insert:
    INSERT INTO tblUser (name, phone) values("oz", json('{"cell":"+491765", "home":"+498973"}'));
  2. select1:
    SELECT json_set(tblUser.phone, '$.cell', '123') FROM tblUser;
  3. select2:
    SELECT tblUser.name From tblUser WHERE json_extract(tblUser.phone, '$.cell') = '+491765';
  4. update:
    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. error1:
    android.database.sqlite.SQLiteException: no such function: json (code 1): , while compiling: INSERT INTO tblUser(name, phone) VALUES (?, json(?))
  2. error2:
    android.database.sqlite.SQLiteException: no such function: json_set (code 1): , while compiling: SELECT json_set(tblUser.phone, '$.cell', ?) FROM tblUser
  3. error3:
    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. error4:
    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:

insert:
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

update:
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

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

Attachments

  • usingSqliteJson.zip
    13.1 KB · Views: 49

LucaMs

Expert
Licensed User
Longtime User
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.
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Dim sqlstr As String = "INSERT INTO tblUser(name, phone) VALUES (?, json(?))"
Replace with
B4X:
Dim sqlstr As String = "INSERT INTO tblUser(name, phone) VALUES (?, ?)"
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
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.
 
Upvote 0

OliverA

Expert
Licensed User
Would SQLite engine be updated to include this extension on next B4A version?
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).
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
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.
B4X:
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
 
Upvote 0
Dear OliverA and Mahares,

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

Attachments

  • usingSqliteJson.zip
    15 KB · Views: 40
Upvote 0
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.
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
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.
 
Upvote 0
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!
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
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?
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
Upvote 0

Mahares

Expert
Licensed User
Longtime User
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.
 
Upvote 0
Top