Android Question Load Mysql table into B4XTable

Status
Not open for further replies.

AkYusufzai

New Member
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

Expert
Licensed User
how to load result rows into B4XTable?
The B4XTable Tutorial shows an Example on how to fill the Data
B4X:
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

New Member
Thanks @DonManfred

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

B4XTable:
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.
 

DonManfred

Expert
Licensed User

AkYusufzai

New Member
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

B4XTable 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

B4XTable Mysql:
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
 
Last edited:

AkYusufzai

New Member
What is the output of:
B4X:
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).

B4X:
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?
 
Last edited:
Status
Not open for further replies.
Top