Android Question [Solved] simple sqlite table to json and vice versa codes

[AnandGupta]

I am trying to create simple sqlite table to json and vice versa

Below code gives the json from sqlite table (here I use the strings.db of localizator as example). Ok here.

    Dim JSONGen As JSONGenerator
    JSONGen.Initialize(DBUtils.ExecuteJSON(sql, "SELECT key, lang, value FROM data", Null, 0, Array As String(DBUtils.DB_TEXT, DBUtils.DB_TEXT, DBUtils.DB_TEXT)))
    Dim JSONString As String
    JSONString = JSONGen.ToPrettyString(4)
    Log(JSONString)

Below code I try to insert the above json to similar db "strings_empty.db

    Dim lstRecords2 As List
    lstRecords2.Initialize
    Dim parser2 As JSONParser
    parser2.Initialize(JSONString)
    Dim mapRoot2 As Map = parser2.NextObject
    Dim lstRoot2 As List = mapRoot2.Get("root")
    Log(lstRoot2)

    Dim result2 As Boolean = DBUtils.InsertMaps(sql2, "data", lstRoot2)
    Log(result2)
    Log(LastException)

But not working. I think the "lstRoot2" is not correct format for InsertMaps.
Any idea how to solve it ?

BTW I got the above codes from below, thanks to @LucaMs

Exporting Sqlite data

Hi all, which could be the preferable way to export some data from an internal sqlite table? A bit of context. An app stores some data in one or more tables while the DB is initialized in DirInternal. A SELECT statement is needed to extract only some data. Columns are of different data types...

www.b4x.com

 

[LucaMs]

"SELECT key, lang, value FROM data"

I didn't look at everything, my attention was immediately directed to the query. It is very likely that "data" is a keyword, so it shouldn't be used as a table (or field) name.

 

[teddybear]

I am trying to create simple sqlite table to json and vice versa

Below code gives the json from sqlite table (here I use the strings.db of localizator as example). Ok here.

    Dim JSONGen As JSONGenerator
    JSONGen.Initialize(DBUtils.ExecuteJSON(sql, "SELECT key, lang, value FROM data", Null, 0, Array As String(DBUtils.DB_TEXT, DBUtils.DB_TEXT, DBUtils.DB_TEXT)))
    Dim JSONString As String
    JSONString = JSONGen.ToPrettyString(4)
    Log(JSONString)

Below code I try to insert the above json to similar db "strings_empty.db

    Dim lstRecords2 As List
    lstRecords2.Initialize
    Dim parser2 As JSONParser
    parser2.Initialize(JSONString)
    Dim mapRoot2 As Map = parser2.NextObject
    Dim lstRoot2 As List = mapRoot2.Get("root")
    Log(lstRoot2)

    Dim result2 As Boolean = DBUtils.InsertMaps(sql2, "data", lstRoot2)
    Log(result2)
    Log(LastException)

But not working. I think the "lstRoot2" is not correct format for InsertMaps.
Any idea how to solve it ?

BTW I got the above codes from below, thanks to @LucaMs

Exporting Sqlite data

Hi all, which could be the preferable way to export some data from an internal sqlite table? A bit of context. An app stores some data in one or more tables while the DB is initialized in DirInternal. A SELECT statement is needed to extract only some data. Columns are of different data types...

www.b4x.com

I don't know what issue you encountered, but it works for me

 

[AnandGupta]

It is very likely that "data" is a keyword

No, data is table name.

 

[AnandGupta]

I don't know what issue you encountered, but it works for me

Thanks @teddybear

This got me thinking and I recreated strings_empty.db (which may have been corrupted earlier) and it works perfectly,

 

[LucaMs]

No, data is table name.

II know but "data" is an SQLite keyword, so it's very likely that it's not accepted as a table name.

 

[Mahares]

II know but "data" is an SQLite keyword, so it's very likely that it's not accepted as a table name.

No. Data is not a reserved word in SQLite. You are probably thinking such because Erel uses it as the table name in the internal database used by B4XTable. Erel could have used any other name and it would have worked.

 

[LucaMs]

No. Data is not a reserved word in SQLite. You are probably thinking such because Erel uses it as the table name in the internal database used by B4XTable.

No, because to hurry I asked Perplexity, who is either uninformed or spiteful

 

[Mahares]

No, because to hurry I asked Perplexity, who is either uninformed or spiteful

Can you explain in plain English what you just said. I have no idea what you just wrote.

 

[LucaMs]

You are probably thinking such because Erel uses

No, I didn't assume that "data" was a reserved word, I asked Perplexity rather than looking for, and... it lied, answering "yes, it is"