Wong Ka Chun
Member
Dear all, I have successfully done following queries in DB Browser for SQLite:
However, SQLiteExceptions have occurred when I moved above queries into codes:
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"?
-
insert:
INSERT INTO tblUser (name, phone) values("oz", json('{"cell":"+491765", "home":"+498973"}')); -
select1:
SELECT json_set(tblUser.phone, '$.cell', '123') FROM tblUser; -
select2:
SELECT tblUser.name From tblUser WHERE json_extract(tblUser.phone, '$.cell') = '+491765'; -
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:
-
error1:
android.database.sqlite.SQLiteException: no such function: json (code 1): , while compiling: INSERT INTO tblUser(name, phone) VALUES (?, json(?)) -
error2:
android.database.sqlite.SQLiteException: no such function: json_set (code 1): , while compiling: SELECT json_set(tblUser.phone, '$.cell', ?) FROM tblUser -
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') = ? -
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= ?
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 SubThe 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"?
