Android Question Load Mysql table into B4XTable

[AkYusufzai]

Hello everyone.

I'm trying to load Mysql database table data into B4XTable by using JRDC2. I can print results in logs. But the data is not showing in B4XTable columns it shows table empty. Anyone can explain how to load result rows into B4XTable?
Thank you.

 

[DonManfred]

how to load result rows into B4XTable?

The B4XTable Tutorial shows an Example on how to fill the Data

[B4X] B4XTable - Cross platform, sortable, searchable, customizable table

B4XTable is a paged based table. The UI is made of a horizontal xCLV. The data is stored in an in-memory SQLite database. How to use? 1. Add a B4XTable with the designer. 2. Add the columns: B4XTable1.AddColumn("US County", B4XTable1.COLUMN_TYPE_NUMBERS) B4XTable1.AddColumn("Name"...

www.b4x.com

Dim data As List
data.Initialize
data.Add(Array(1, 2, 3))
data.Add(Array(4, 5, 6))
B4XTable1.SetData(data)

You need to fill "data" with your Results from jrdc.
You need to create a List. Each item in this List represents one Row. The Rowdata must be an Array.

 

[AkYusufzai]

Thanks @DonManfred

I have 8 Column in Mysql Table and this is how i'm trying. but no success.

Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("select_allvehicles", Null)
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        Dim Data As List
        Data.Initialize
        For Each row() As Object In res.Rows
            Data.Add(Array(0,1,2,3,4,5,6))
'            Data.Add(Array(1))
'            Data.Add(Array(2))
'            Data.Add(Array(3))
'            Data.Add(Array(4))
'            Data.Add(Array(5))
            Data.Add(row)
    Next
        VehiclesListB4XTable.SetData(Data)
        req.PrintTable(res)
    Else
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release

What i'm not getting how to use array to load table as list in B4XTable. Sorry if i'm being noob.

 

[Erel]

What happens after this code? I expect the table to show something.

 

[DonManfred]

I expect the table to show something.

but only if the res.Rows is > 0
Or am i wrong here?

 

[Erel]

You are of course correct.

@AkYusufzai put a breakpoint after the For loop and see whether there is data in the Data list.

 

[DonManfred]

Additionally

I have 8 Column in Mysql Table

Data.Add(Array(0,1,2,3,4,5,6))

But you are only setting 7 here. Shouldn´t it be 8 parameters?

But start at checking whether there is data in the Data list or not.

 

[AkYusufzai]

I created break point at Data.Add(row) it shows there is a row with id 1 and req.PrintTable(res) print all the records in log window but B4XTable shows nothing. i have used b4xtable with sqlite and it works, but now when i am try the same with mysql the data is not loading into b4xtable. this is how i used in Sqlite

Sub GetListOfVehicles
    B4XTableVehicles.AddColumn("status", B4XTableVehicles.COLUMN_TYPE_TEXT)
    B4XTableVehicles.AddColumn("year", B4XTableVehicles.COLUMN_TYPE_TEXT)
    B4XTableVehicles.AddColumn("model", B4XTableVehicles.COLUMN_TYPE_TEXT)
    B4XTableVehicles.AddColumn("make", B4XTableVehicles.COLUMN_TYPE_TEXT)
    B4XTableVehicles.AddColumn("plate", B4XTableVehicles.COLUMN_TYPE_TEXT)
    B4XTableVehicles.AddColumn("id", B4XTableVehicles.COLUMN_TYPE_TEXT)
    Dim Data As List
    Data.Initialize
    Dim rs As ResultSet = Connection.b4SQL.ExecQuery("Select VehicleId, Plate, Make, Model, Year, Status FROM Vehicles ORDER BY Plate ASC")
    Do While rs.NextRow
        Dim row(6) As Object
        row(0) = rs.GetString("Status")
        row(1) = rs.GetString("Year")
        row(2) = rs.GetString("Model")
        row(3) = rs.GetString("Make")
        row(4) = rs.GetString("Plate")
        row(5) = rs.GetString("VehicleId")
        Data.Add(row)
    Loop
    rs.Close
    B4XTableVehicles.SetData(Data)
End Sub

if you can help by modifying the code will be great help

Sub GetAllVehiclesList

    VehiclesListB4XTable.AddColumn("status", VehiclesListB4XTable.COLUMN_TYPE_TEXT)

    VehiclesListB4XTable.AddColumn("type", VehiclesListB4XTable.COLUMN_TYPE_TEXT)

    VehiclesListB4XTable.AddColumn("color", VehiclesListB4XTable.COLUMN_TYPE_TEXT)

    VehiclesListB4XTable.AddColumn("year", VehiclesListB4XTable.COLUMN_TYPE_TEXT)

    VehiclesListB4XTable.AddColumn("model", VehiclesListB4XTable.COLUMN_TYPE_TEXT)

    VehiclesListB4XTable.AddColumn("make", VehiclesListB4XTable.COLUMN_TYPE_TEXT)

    VehiclesListB4XTable.AddColumn("plate", VehiclesListB4XTable.COLUMN_TYPE_TEXT)

    VehiclesListB4XTable.AddColumn("id", VehiclesListB4XTable.COLUMN_TYPE_TEXT)

    Dim Data As List

    Data.Initialize

    Dim req As DBRequestManager = CreateRequest

    Dim cmd As DBCommand = CreateCommand("select_allvehicles", Null)

    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)

    If j.Success Then

        req.HandleJobAsync(j, "req")

        Wait For (req) req_Result(res As DBResult)

            For Each row() As Object In res.Rows

                Data.Add(Array(0,1,2,3,4,5,6,7))

                Data.Add(row)

            Next

        VehiclesListB4XTable.SetData(Data)

        req.PrintTable(res)

    Else

        Log("ERROR: " & j.ErrorMessage)

    End If

    j.Release

End Sub

 

[Erel]

What is the output of:

Log("size: " & Data.Size)
B4XTableVehicles.SetData(Data)

 

[AkYusufzai]

What is the output of:

Log("size: " & Data.Size)
B4XTableVehicles.SetData(Data)

i Get (size = 194)
Tag: null, Columns: 9, Rows: 97
9th column is blob image column which i'm not loading into table. only loading 0 to 7


Got it working now all the data is loading into table with no extra rows with (0).

Sub GetAllVehiclesList
    VehiclesListB4XTable.AddColumn("status", VehiclesListB4XTable.COLUMN_TYPE_TEXT)
    VehiclesListB4XTable.AddColumn("type", VehiclesListB4XTable.COLUMN_TYPE_TEXT)
    VehiclesListB4XTable.AddColumn("color", VehiclesListB4XTable.COLUMN_TYPE_TEXT)
    VehiclesListB4XTable.AddColumn("year", VehiclesListB4XTable.COLUMN_TYPE_TEXT)
    VehiclesListB4XTable.AddColumn("model", VehiclesListB4XTable.COLUMN_TYPE_TEXT)
    VehiclesListB4XTable.AddColumn("make", VehiclesListB4XTable.COLUMN_TYPE_TEXT)
    VehiclesListB4XTable.AddColumn("plate", VehiclesListB4XTable.COLUMN_TYPE_TEXT)
    VehiclesListB4XTable.AddColumn("id", VehiclesListB4XTable.COLUMN_TYPE_TEXT)
    Dim Data As List
    Data.Initialize
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("select_allvehicles", Null)
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
            For Each row() As Object In res.Rows
                Data.Add(Array(0,1,2,3,4,5,6,7)) 'Just Removed this and all data loaded into B4XTable'
                Data.Add(row)
            Next
        VehiclesListB4XTable.SetData(Data)
        req.PrintTable(res)
    Else
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release
End Sub

now when i search in b4xtable after typing i get this error

Error occurred on line: 461 (B4XTable)
java.lang.NullPointerException: Attempt to invoke virtual method 'java.lang.String java.lang.String.toLowerCase()' on a null object reference

Note: If i don't load all columns B4XTable shows no data except data count under searchbox. when i add reference to all 9 columns data is properly loaded into table.

i want to hide last image column in table. any help?

 

[Erel]

now when i search in b4xtable after typing i get this error

This means that you are adding Null values to the table. Best way is to remove nulls in the query.

For further discussion please start a new thread.