B4J Question B4J errors with B4Xtable

[Kevin Hartin]

I am experiencing errors with a B4Xtable as follows;

Error occurred on line: 705 (B4XTable)
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near ")": syntax error)
    at org.sqlite.DB.newSQLException(DB.java:383)
    at org.sqlite.DB.newSQLException(DB.java:387)
    at org.sqlite.DB.throwex(DB.java:374)
    at org.sqlite.NativeDB.prepare(Native Method)
    at org.sqlite.DB.prepare(DB.java:123)
    at org.sqlite.Stmt.execute(Stmt.java:113)
    at anywheresoftware.b4j.objects.SQL.ExecNonQuery(SQL.java:161)
    at b4j.example.b4xtable._createtable(b4xtable.java:2318)
    at b4j.example.b4xtable$ResumableSub_SetData.resume(b4xtable.java:288)
    at b4j.example.b4xtable._setdata(b4xtable.java:221)
    at b4j.example.pagereceipt._showtable(pagereceipt.java:203)
    at b4j.example.pagereceipt._b4xpage_appear(pagereceipt.java:126)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at jdk.internal.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:108)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:504)
    at anywheresoftware.b4a.keywords.Common.access$0(Common.java:484)
    at anywheresoftware.b4a.keywords.Common$CallSubDelayedHelper.run(Common.java:558)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(Native Method)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
    at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
    at java.base/java.lang.Thread.run(Thread.java:834)

The table is being populated by a SQLite query that runs correctly, with 3 records being populated into the list The code is as follows;

Sub ShowTable
    Log("ShowTable Start")
    Dim TableData As List
    TableData.Initialize
    Log("ROWS: "&SQL.ExecQuerySingleResult("select count(*) from checkout;"))
    If SQL.ExecQuerySingleResult("select count(*) from checkout;") > 0 Then
        Dim Query As String = "SELECT ID,ItemText,ItemQty,ItemPrice,(ItemQty * ItemPrice) AS ItemTotal FROM checkout ORDER BY ID ASC;"
        Log(Query)
        RS = SQL.ExecQuery(Query)
        Do While RS.NextRow
            Dim row(5) As Object
            row(0) = RS.GetDouble("ID")
            row(1) = RS.GetString("ItemText")
            row(2) = RS.GetInt("ItemQty")
            row(3) = RS.GetString("ItemPrice")
            row(4) = RS.GetString("ItemTotal")
            TableData.Add(row)
            Log("TableData size: "&TableData.Size)
        Loop
        tblReceipt.SetData(TableData)
        RS.Close
    End If
    Log("ShowTable Finish")
End Sub

the log output leading up to the error is as follows;

lblReceipt_MouseClicked
*** receipt: B4XPage_Created [pos]
Creating PageReceipt
*** receipt: B4XPage_Appear [pos]
*** receipt: B4XPage_Resize [pos, receipt]
Receipt B4XPage_Appear
ShowTable Start
ROWS: 3
SELECT ID,ItemText,ItemQty,ItemPrice,(ItemQty * ItemPrice) AS ItemTotal FROM checkout ORDER BY ID ASC;
TableData size: 1
TableData size: 2
TableData size: 3

it seems to be the line of code "tblReceipt.SetData(TableData)" that is causing the error, which makes no sense to me as I am done with the SQLite query and resultset by that stage. The one that I am suspicous of is the

shows as a Resumable Sub or Method.

Any ideas will be appreciated,

Kev

 

[Erel]

Have you added 5 columns with the correct types?

 

[Kevin Hartin]

Have you added 5 columns with the correct types?

yes I believe so. It was working previously;

    idCol = tblReceipt.AddColumn("ID", tblReceipt.COLUMN_TYPE_NUMBERS)
    textCol = tblReceipt.AddColumn("Item", tblReceipt.COLUMN_TYPE_TEXT)
    qtyCol = tblReceipt.AddColumn("Qty", tblReceipt.COLUMN_TYPE_TEXT)
    priceCol = tblReceipt.AddColumn("Price", tblReceipt.COLUMN_TYPE_TEXT)
    totalCol = tblReceipt.AddColumn("Total", tblReceipt.COLUMN_TYPE_TEXT)

following is the 3 records in the DB;

 

[Erel]

It was working previously

Only you can say what has changed.

Better to set Qty to TYPE_NUMBERS.

 

[Kevin Hartin]

Only you can say what has changed.

Better to set Qty to TYPE_NUMBERS.

Nothing has changed between working and not. in fact it was working intermittantly. I checked the column types, sql data types, live sql data etc. and couldn't come up with any valid reason for the inconsistency.

So, I took the exact same code that builds and populates the B4Xtable in a seperate B4Xpage and put it into a Pane on my POS page. It works perfectly. Go figure...

Anyway, it better suits my app to have a live receipt displayed on the page with all the POS buttons, so I am now better off.

One thing though, how do you format the font used and its size in a B4Xtable? Settings in the Designer seem to be ignored and I cannot find a reference in the forums to do this programatically. To overcome the formatting issue I have reverted to a CLV, which works great.

Also, are there any differences in capabilities between B4A, B4I and B4J with B4Xtables?

Thanks,
Kev

 

[roerGarcia]

A question.
It would not be possible to change the code

Log ("ROWS:" & SQL.ExecQuerySingleResult ("select count (*) from checkout;"))
If SQL.ExecQuerySingleResult ("select count (*) from checkout;")> 0 Then "

by

Dim cntAll as int = SQL.ExecQuerySingleResult ("select count (*) from checkout;")
Log ("ROWS:" & cntAll)
If cntAll > 0 then

???

 

[Erel]

One thing though, how do you format the font used and its size in a B4Xtable?

You should start a new thread for a new question.

To overcome the formatting issue I have reverted to a CLV, which works great.

Ok.

Also, are there any differences in capabilities between B4A, B4I and B4J with B4Xtables?

No. It is a cross platform library.