Android Question [CLOSE]B4XTable ERROR when SetData

[trejocode]

Hi, I have a strange error when I try to feed the table, I simply get data from an API and insert it in a table but it returns an error that I have no idea what it means, since I do not use SQLite.

Error occurred on line: 601 (B4XTable)
android.database.sqlite.SQLiteException: near ")": syntax error (code 1): , while compiling: CREATE TABLE data )

My code just feed the table with the JSON results:

{"orders":[{"id_pedido":5,"fecha":"2019-06-24T08:03:26.000Z","producto":"Horchata de canela 3.785 L","cantidad":5,"total":"825.00"},{"id_pedido":5,"fecha":"2019-06-24T08:03:26.000Z","producto":"Jamaica 700ml","cantidad":2,"total":"58.00"}],"successful":true}

To the top:

Table.AddColumn("col1", Table.COLUMN_TYPE_TEXT)
    Table.AddColumn("col2", Table.COLUMN_TYPE_TEXT)

ProgressDialogShow("OBTENIENDO DETALLE DEL PEDIDO")
    Dim Data As Map
    Data.Initialize
    Data.Put("id", someID)
    Dim JSONGenerator As JSONGenerator
    JSONGenerator.Initialize(Data)
    Dim JSONString As String
    JSONString = JSONGenerator.ToString
    HTTP.PostString("http://"&IP&":8081/something", JSONString)
    HTTP.GetRequest.SetContentType("application/json")
    Wait For (HTTP) JobDone(response As HttpJob)
    If response.Success Then
        Dim parser As JSONParser
        parser.Initialize(response.GetString)
        Dim Root As Map = parser.NextObject
        Dim Datas As List = Root.Get("datas")
        For Each Data As Map In Datas
            Dim row(2) As Object
            row(0) = Datas.Get("col1")
            row(1) = Datas.Get("col2")
            DataList.Add(row)
        Next       
        ToastMessageShow("ERROR", True)       
    End If
    HTTP.Release
    Table.SetData(DataList)
    ProgressDialogHide

 

[nibbo]

Can you post the CREATE TABLE statement?

 

[trejocode]

Can you post the CREATE TABLE statement?

At no time do I use a CREATE Table

 

[nibbo]

At no time do I use a CREATE Table

Ah, looks like the B4XTable library must do this.
I don't use this library so cant help sorry.

 

[Erel]

1. You should post the error message as text.
2. Make HTTP a local variable and dim it before you use it.
3. Based on the error message you haven't added columns. Maybe the code you posted is never called.

 

[nibbo]

More likely that the For Each in Datas loop in does not find anything, I don't see Datas node in the JSON.
This would mean nothing is ever added to DataList?

 

[trejocode]

1. You should post the error message as text.
2. Make HTTP a local variable and dim it before you use it.
3. Based on the error message you haven't added columns. Maybe the code you posted is never called.

All are initialized and declared.
There are 2 tables, first one to list "Clients", it works perfect.
This second, is to list products of customers, it does not work, look: The elements to add exist and the error is still the same related to SQLite, I DO NOT USE SQLITE.

Errors:

Class not found: b4a.example.b4xfloattextfield, trying: maya.caribbean.app.b4xfloattextfield
{"details":[{"id_pedido":5,"fecha":"2019-06-24T08:03:26.000Z","producto":"Horchata de canela 3.785 L","cantidad":5,"total":"825.00"},{"id_pedido":5,"fecha":"2019-06-24T08:03:26.000Z","producto":"Jamaica 700ml","cantidad":2,"total":"58.00"}],"successful":true}
Error occurred on line: 601 (B4XTable)
android.database.sqlite.SQLiteException: near ")": syntax error (code 1): , while compiling: CREATE TABLE data )
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1677)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1608)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
    at maya.caribbean.app.b4xtable._createtable(b4xtable.java:1943)
    at maya.caribbean.app.b4xtable$ResumableSub_SetData.resume(b4xtable.java:282)
    at maya.caribbean.app.b4xtable._setdata(b4xtable.java:221)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:176)
    at anywheresoftware.b4a.shell.DebugResumableSub$RemoteResumableSub.resume(DebugResumableSub.java:22)
    at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:250)

 

[nibbo]

the error is still the same related to SQLite, I DO NOT USE SQLITE.

Your code may not use SQLite but my guess is that B4XTable library does, it probably creates a temporary table in the background.
The object being passed to SetData must be in error.
Your For Next loop looks OK now but I don't see where you add rows to DataList.

 

[trejocode]

Your code may not use SQLite but my guess is that B4XTable library does, it probably creates a temporary table in the background.
The object being passed to SetData must be in error.
Your For Next loop looks OK now but I don't see where you add rows to DataList.

It's a minimal recreation example, but i use:
DataList.Add(row)

DataList is a "List" i have other table with the same "data" and its works fine, but this table doesn't work.

 

[nibbo]

Without seeing the exact code it is very hard to determine what is going wrong.
It will definitely be something to do with the content of DataList.

It may be worth writing a small loop to log each row and each column in datalist just before SetData is called to see exactly what is being passed in.
Perhaps one of the column names is not valid for SQLite resukting in an invalid SQLite create statement?

 

[Erel]

Try to reproduce it in a small project and upload it.

 

[trejocode]

Okay, here is the version:
On the Login screen jus select the first: "Desarrollo" and press on Login, now, press on the first button "Ver pedidos" in this screen are the problem, please see the table, works fine, press on a element of the table and now the error appears. Inside of the project the module is: "ModuleOrders".
Thank you, i can't find the solution.

Try to reproduce it in a small project and upload it.

Okay, here is the version:
On the Login screen jus select the first: "Desarrollo" and press on Login, now, press on the first button "Ver pedidos" in this screen are the problem, please see the table, works fine, press on a element of the table and now the error appears. Inside of the project the module is: "ModuleOrders".
Thank you, i can't find the solution.

 

[Erel]

Sorry but you need to reproduce it in a small project. It should be simple to do. Just create a new project with the table, add the columns and set the data from the json string.

 

[trejocode]

Sorry but you need to reproduce it in a small project. It should be simple to do. Just create a new project with the table, add the columns and set the data from the json string.

I'm sorry Erel, I reproduced a small project only obtain the JSON and show in the table, press one element to the table to show the error.
If the data is not displayed please try in 10 seconds. Probably the API is "sleeping", it only takes 10 seconds to wake up.

Thank you for your help

 

[Erel]

I ran your code. This is what I see:

No errors.

There are several mistakes:

1. SQL should only be initialized when FirstTime is true.
2. Http shouldn't be a global variable. It should be a local variable.
3. You should set the theme to light theme (change Themes.DarkTheme to Themes.LightTheme in the manifest editor).

 

[trejocode]

I ran your code. This is what I see:

No errors.

There are several mistakes:

1. SQL should only be initialized when FirstTime is true.
2. Http shouldn't be a global variable. It should be a local variable.
3. You should set the theme to light theme (change Themes.DarkTheme to Themes.LightTheme in the manifest editor).

Erel, press an element of the table to see the error.

 

[trejocode]

I ran your code. This is what I see:

No errors.

There are several mistakes:

1. SQL should only be initialized when FirstTime is true.
2. Http shouldn't be a global variable. It should be a local variable.
3. You should set the theme to light theme (change Themes.DarkTheme to Themes.LightTheme in the manifest editor).

I'll explain, it's the first table gets information from an API.
When the data is listed in the table I can click on an item to see another table with more information, which is obtained from another call to an API.
The error is when you click on an element of the table and try to display the data in the other table.
Thank you for the help and the attention.

 

[trejocode]

More likely that the For Each in Datas loop in does not find anything, I don't see Datas node in the JSON.
This would mean nothing is ever added to DataList?

Can you see my Project?

 

[mangojack]

In Sub TableOrders_CellClick you are calling TableDetails.Clear ...

b4xTable.Clear (Clears the Columns and Data)

So your code should be ...

Sub TableOrders_CellClicked (ColumnId As String, RowId As Long)
   
    TableDetails.Clear
   
    TableDetails.AddColumn("Fecha", TableDetails.COLUMN_TYPE_TEXT)
    TableDetails.AddColumn("Producto", TableDetails.COLUMN_TYPE_TEXT)
    TableDetails.AddColumn("Cantidad", TableDetails.COLUMN_TYPE_TEXT)
    TableDetails.AddColumn("Total", TableDetails.COLUMN_TYPE_TEXT)
   
    Dim RowData As Map = TableOrders.GetRow(RowId)
    'more code ..............

 

[emexes]

If the multiple B4XTables are still not working as expected, this reply to your earlier query might help too:

As part of looking into another B4XTable query, I had two B4XTables side-by-side in the same activity, no problem.

But... apparently the data is not actually transferred to the table's internal database within the .SetData call, but a short time later.

So this could mean that if you have reused the one list to load up both B4XTables, then there might be a mixup happening.

The bulletproof solution is for each B4XTable to be loaded from its own separate list, ie, one list per table, not reused.