Android Question B4ATable Set Data

rhanger · Jan 16, 2024

I cannot get the B4atable.setdata to work. I have looked all through the postings and found references to others with the same issue but not with an answer.
I started with a sample project from this site.
I defined the table as follows

B4XTable1.Initialize(Me,"B4XTable1")

'create the columns
B4XTable1.AddColumn("sequence", B4XTable1.COLUMN_TYPE_TEXT)
B4XTable1.AddColumn("quantity", B4XTable1.COLUMN_TYPE_numbers)
B4XTable1.AddColumn("weight", B4XTable1.COLUMN_TYPE_numbers)

I then pull data from a SQL Server Database which works so rs has data in it.
I have tried a bunch of different ways to add the data from each row to the list and have been successful. You can see by the commented lines I have tried adding it to the list as the
entire row, as an array, as csv variables etch.
The list accepts the add.
But no matter what I do when I try to apply the list to the b4xtable using .setdata I get the following error which looks like it is coming from the b4xtable library.
'********************************************************************
Error occurred on line: 705 (B4XTable)
android.database.sqlite.SQLiteException: near ")": syntax error (code 1 SQLITE_ERROR): , while compiling: CREATE TABLE data )
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1045)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:652)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:33)
at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1919)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1841)
at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
at b4a.example.b4xtable._createtable(b4xtable.java:2417)
at b4a.example.b4xtable$ResumableSub_SetData.resume(b4xtable.java:500)
at b4a.example.b4xtable._setdata(b4xtable.java:433)
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:157)
at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:262)
at b4a.example.b4xmainpage._loadlist(b4xmainpage.java:393)
at b4a.example.b4xmainpage$ResumableSub_SQLC_DataArrival.resume(b4xmainpage.java:564)
at b4a.example.b4xmainpage._sqlc_dataarrival(b4xmainpage.java:459)
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:157)
at anywheresoftware.b4a.BA$2.run(BA.java:395)
at android.os.Handler.handleCallback(Handler.java:938)

'*********************************************************************
I have downloaded the most recent release of B4A and I still get the error. If anyone has any idea how to fix this I would appreciate a response.
Thanks

Sub LoadList
Dim ts As String
Dim rs As JdbcResultSet
Dim ll As List
ll.Initialize
ts = "select sequence, quantity, weight from inventory where shipment = '00074195'"

rs = jsqlm.RunQuery(ts )
Do While rs.NextRow
ll.Add(rs)

' ll.Add(Array(rs.GetString("Sequence")))
' 'll.Add (Array As String (rs.GetString ("Sequence"),rs.GetString ("Quantity"),rs.GetString("Weight")))
' Log(rs.GetString("Sequence"))
Loop

Wait for (B4XTable1.SetData(ll)) Complete (Unused As Boolean)
B4XTable1.refresh

mcqueccu · Jan 16, 2024

1. Use Code Tags when posting code

2. IN your Loop use this

B4X:

ll.Add (Array (rs.GetString ("Sequence"),rs.GetString ("Quantity"),rs.GetString("Weight")))

3. The column Type too, why is numbers in lowercase instead of B4XTable1.COLUMN_TYPE_NUMBERS

rhanger · Jan 16, 2024

Thanks for your reply. I fixed the lower case issue in the table column declarations and used your code in adding to the list.
I copied the entire sub below. But I still get the same error

Sub LoadList
Dim ts As String
Dim rs As JdbcResultSet
Dim ll As List
ll.Initialize
ts = "select sequence, quantity, weight from barcom.dbo.inventory where shipment = '00074195'"

rs = jsqlm.RunQuery(ts )
Do While rs.NextRow
ll.Add (Array (rs.GetString ("Sequence"),rs.GetString ("Quantity"),rs.GetString("Weight")))
Loop

Wait for (B4XTable1.SetData(ll)) Complete (Unused As Boolean)

End Sub

Any other ideas? Thanks

rhanger · Jan 16, 2024

I also tried converting all the data to strings by changing the column definitions to STRING and the sql statement below
"select sequence, convert(varchar(12),quantity) as quantity, convert(varchar(12),weight) AS Weight from inventory where shipment = '00074195'"
Same Error.

mcqueccu · Jan 16, 2024

Are you getting data from the query? log the result to check?

rhanger · Jan 16, 2024

Yes Getting data. Current code and Log below Now using only string definitions in the table. I unzipped the b4xtable library and copied the lines the error referenced to see if I could get a clue. Going by reported line number, I listed the function being called after the log entries.

Sub LoadList
Dim ts As String
Dim rs As JdbcResultSet
Dim ll As List
ll.Initialize
ts = "select sequence, convert(varchar(12),quantity) as quantity, convert(varchar(12),weight) AS WEIGHT from barcom.dbo.inventory where shipment = '00074195'"
rs = jsqlm.RunQuery(ts )
Do While rs.NextRow
ll.Add (Array (rs.GetString ("Sequence"),rs.Getstring ("Quantity"),rs.Getstring("Weight")))
Log(rs.GetString("Sequence"))
Log(rs.Getstring ("Quantity"))
Log(rs.Getstring("Weight"))
Loop

Wait for (B4XTable1.SetData(ll)) Complete (Unused As Boolean)
B4XTable1.refresh

End Sub
.

00561015
20
920
00561027
20
960
00561030
20
920
00561064
20
920
00561079
20
920
00561088
20
920
00561110
20
920
00561119
20
920
00561662
20
960
00561672
20
960
00561686
20
960
00561693
20
960

'Code from Library
********************************
Looks like it is having trouble creating whatever kind of table it is using to build the list. My data is coming from an actual SQL Server database so possibly it does no report the correct sql data type.

Private Sub CreateTable
Dim sb As StringBuilder
sb.Initialize
sb.Append("CREATE TABLE data (")
Dim i As Int
For Each Column As B4XTableColumn In Columns
If Column.ColumnType = COLUMN_TYPE_VOID Then Continue
Column.SQLID = "c" & i
sb.Append(Column.SQLID & " " & ColumnToSQLType(Column) & ",")
i = i + 1
Next
sb.Remove(sb.Length - 1, sb.Length)
sb.Append(")")
sql1.ExecNonQuery(sb.ToString)
End Sub

Private Sub ColumnToSQLType (c As B4XTableColumn) As String
If c.ColumnType = COLUMN_TYPE_TEXT Then Return "TEXT" Else Return "INTEGER"
End Sub
********************************

mcqueccu · Jan 16, 2024

You are not using Code Tag its making it difficult to read your code.

Kindly do a small example with a sample database and post

mcqueccu · Jan 16, 2024

I used some of your information to recreate your scenario. Everything works ok

B4X:

Sub LoadList
    
    Dim rs As ResultSet
    rs = jsqlm.ExecQuery2("SELECT sequence, quantity, weight from inventory where shipment = ?",Array As String("00074195"))
    
    Dim ll As List
    ll.Initialize
    
    Do While rs.NextRow
        Dim sequence As Int = rs.GetString("sequence")
        Dim quantity As Int = rs.Getstring ("quantity")
        Dim weight As Int = rs.Getstring("weight")
        
        ll.Add(Array(sequence,quantity,weight))
    Loop

    B4XTable1.SetData(ll)
End Sub

Erel · Jan 17, 2024

Why aren't you posting with code tags???

It was requested two times.

Thread locked.

Android Question B4ATable Set Data

rhanger

Member

mcqueccu

Well-Known Member

rhanger

Member

rhanger

Member

mcqueccu

Well-Known Member

rhanger

Member

mcqueccu

Well-Known Member

mcqueccu

Well-Known Member

Attachments

Erel

B4X founder

Similar Threads

Android Question B4ATable Set Data

Member

Well-Known Member

Member

Member

Well-Known Member

Member

Well-Known Member

Well-Known Member

Attachments

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency