Android Question Any example of how to display a DBResult query with B4XTable or any other view?

Sergio Castellari · May 7, 2020

Hello people,

Moving forward with my APP, now I need to display a query via jRDC2 returned in DBResult.
From what I've seen, I could do it with B4XTable or another view.
I have never done it. It's my first time

Cheers

DarkoT · May 7, 2020

Hallo,
here is small example how to show data returned from JRDC in DBresult...

Example - Returned Data as DbResult:

        Dim req As DBRequestManager = CreateRequest
        Dim cmd As DBCommand = CreateCommand("select_AllCustomers", Null)   

        ' query to insert data into SqlLite Db
        Query = "INSERT INTO OfKupci(Kupec, KupecNaziv, Dokument, DatumDok, DatumValute, DniZamude, ZaPlacilo, Placano, Saldo) "  & _
                " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?) "
        
        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)
            'work with result
            req.PrintTable(res)
            
            For Each records() As Object In res.Rows
                ' Log("Input -->" & records(1))
                i = i + 1
                SqlLite.ExecNonQuery2(Query, Array As Object(records(0), records(1), records(2), records(3), records(4), records(5), records(6), records(7), records(8) ))
            Next
          
        Else
            Log("ERROR: " & j.ErrorMessage)
        End If
        j.Release

I hope this will help you. If you need example how to fill Table, let me know...

Darko

Sergio Castellari · May 7, 2020

Hello @DarkoT

Thanks for the example. Up to that point I have come.
Now I need to know how to fill a B4XTable or another view with the obtained data.
I want to show the user the result of the query on the screen.

Cheers

josejad · May 7, 2020

Hi:

I use jRDC2 in a class, this is the sub in my class that return the data:

B4X:

Public Sub GetRecord (Command As String, parameters() As String) As ResumableSub
    Dim Respuesta As Map
    Respuesta.Initialize
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand(Command, parameters)
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    Respuesta.Put("Correcto", j.Success)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(Res As DBResult)
        'work with result
        req.PrintTable(Res)
        Respuesta.Put("Mensaje","Se han leído correctamente los datos")
    Else
        Log("ERROR: " & j.ErrorMessage)
        Respuesta.Put("Mensaje","No han leído correctamente los datos, inténtelo más tarde. Error: " & j.ErrorMessage)
    End If
    j.Release
    Respuesta.Put("Datos",Res)
    'req.PrintTable(Res)
    Return Respuesta
End Sub

You must insert your records to a list, and then pass the list to the B4XTAble.

I load the data into the B4XTable (tblPartes) with this sub, maybe you can adapt it:

B4X:

Sub LoadData
    jRDC.Initialize
    Dim MonthYear As String = DateTime.GetYear(DateTime.Now)&"/"&DateTime.GetMonth(DateTime.Now)
    Dim Parametros() As String = Array As String(MonthYear & "/1", MonthYear & "/31")
    Wait For(jRDC.GetRecord("selectParteTrabajo", Parametros)) Complete (Respuesta As Map)
    If Respuesta.Get("Correcto") Then
        Dim rs As DBResult
        rs = Respuesta.Get("Datos")
        Dim ListaPartesDia, partedia As List
        ListaPartesDia.Initialize
        For Each row() As Object In rs.Rows
            partedia.Initialize
            For Each record As Object In row
                partedia.Add(record)
            Next
            ListaPartesDia.Add(partedia)
        Next
        tblPartes.SetData(ListaPartesDia)  'Here I load the data to the table
    Else
        ToastMessageShow(Respuesta.Get("Mensaje"), True)
    End If
End Sub

DarkoT · May 7, 2020

Sergio Castellari said:
Hello @DarkoT

Thanks for the example. Up to that point I have come.
Now I need to know how to fill a B4XTable or another view with the obtained data.
I want to show the user the result of the query on the screen.

Cheers

This will help you to understand the logic... Just keep in Mind, that my example has logic - download from webservice, input into sqllite (local database) and then show data in table...

Show data in Table:

Dim Resultat As ResultSet, Query As String

    Activity.Title = "Open balance for customer: " & NazivKupca

    ' adding the table to activity
    tblFaktureK.Initialize(Me, "tblFaktureK", 5)
    tblFaktureK.AddToActivity(pnlOFKupca, 0, 0dip, 100%x, 90%y)
    tblFaktureK.FontSize = 8
    tblFaktureK.FontColor = Colors.RGB(210,101,14)
    tblFaktureK.HeaderFontColor = Colors.RGB(193,49,145)
    tblFaktureK.HeaderFontSize = 17
    ' tblFaktureK.RowHeight = 80
    ' creating header for table (carefull - need to has same number as number of array which fullfill data)
    tblFaktureK.SetHeader(Array As String("Invoice", "DateInvoice", "PaymentDate", "Open value", "Days"))

    ' Main query for read data from sqllite
    Query = "SELECT Dokument, DatumDok, DatumValute, ZaPlacilo, Placano, Saldo, DniZamude FROM OfKupci WHERE trim(Kupec) = '" & Kupec.Trim & "' ORDER BY DatumValute "

    ' read sqllite
    Resultat = Main.SqlLite.ExecQuery(Query)
    MaxRows = Resultat.RowCount
    Log(" Number of records stored in SqlLite : " & Resultat.RowCount)

    ' do while data existing in recordset
    Do While Resultat.NextRow
        
        ' Object need to have exact same number as is in recordset
        Dim row(7) As Object
        Dim l As Long
        
        row(0) =  Resultat.GetString("Dokument")
        
        DateTime.DateFormat = "yyyy-MM-dd"
        l = DateTime.DateParse(Resultat.GetString("DatumDok"))
        DateTime.DateFormat = "dd-MM-yy"
        row(1) = DateTime.Date(l)
        
        
        
        DateTime.DateFormat = "yyyy-MM-dd"
        l = DateTime.DateParse(Resultat.GetString("DatumValute"))
        DateTime.DateFormat = "dd-MM-yy"
        row(2) = DateTime.Date(l)
        
        row(3) = Format(Resultat.GetDouble("ZaPlacilo"), 2)
        row(4) = Format(Resultat.GetDouble("Placano"), 2)
        row(5) = Format(Resultat.GetDouble("Saldo"), 2)
        Dim Zamuda =  Round2(Resultat.GetInt("DniZamude"),0)
        row(6) = Zamuda
        
        ' fill table!!!
        tblFaktureK.AddRow(Array As String(row(0), row(1), row(2), row(3), row(6)))
    Loop
    ' defining widts of columns
    tblFaktureK.SetColumnsWidths(Array As Int(140dip, 80dip, 80dip, 80dip, 60dip))
    ' closing the recordset
    Resultat.Close

Sergio Castellari · May 7, 2020

DarkoT said:

This will help you to understand the logic... Just keep in Mind, that my example has logic - download from webservice, input into sqllite (local database) and then show data in table...

Show data in Table:

Dim Resultat As ResultSet, Query As String

    Activity.Title = "Open balance for customer: " & NazivKupca

    ' adding the table to activity
    tblFaktureK.Initialize(Me, "tblFaktureK", 5)
    tblFaktureK.AddToActivity(pnlOFKupca, 0, 0dip, 100%x, 90%y)
    tblFaktureK.FontSize = 8
    tblFaktureK.FontColor = Colors.RGB(210,101,14)
    tblFaktureK.HeaderFontColor = Colors.RGB(193,49,145)
    tblFaktureK.HeaderFontSize = 17
    ' tblFaktureK.RowHeight = 80
    ' creating header for table (carefull - need to has same number as number of array which fullfill data)
    tblFaktureK.SetHeader(Array As String("Invoice", "DateInvoice", "PaymentDate", "Open value", "Days"))

    ' Main query for read data from sqllite
    Query = "SELECT Dokument, DatumDok, DatumValute, ZaPlacilo, Placano, Saldo, DniZamude FROM OfKupci WHERE trim(Kupec) = '" & Kupec.Trim & "' ORDER BY DatumValute "

    ' read sqllite
    Resultat = Main.SqlLite.ExecQuery(Query)
    MaxRows = Resultat.RowCount
    Log(" Number of records stored in SqlLite : " & Resultat.RowCount)

    ' do while data existing in recordset
    Do While Resultat.NextRow
       
        ' Object need to have exact same number as is in recordset
        Dim row(7) As Object
        Dim l As Long
       
        row(0) =  Resultat.GetString("Dokument")
       
        DateTime.DateFormat = "yyyy-MM-dd"
        l = DateTime.DateParse(Resultat.GetString("DatumDok"))
        DateTime.DateFormat = "dd-MM-yy"
        row(1) = DateTime.Date(l)
       
       
       
        DateTime.DateFormat = "yyyy-MM-dd"
        l = DateTime.DateParse(Resultat.GetString("DatumValute"))
        DateTime.DateFormat = "dd-MM-yy"
        row(2) = DateTime.Date(l)
       
        row(3) = Format(Resultat.GetDouble("ZaPlacilo"), 2)
        row(4) = Format(Resultat.GetDouble("Placano"), 2)
        row(5) = Format(Resultat.GetDouble("Saldo"), 2)
        Dim Zamuda =  Round2(Resultat.GetInt("DniZamude"),0)
        row(6) = Zamuda
       
        ' fill table!!!
        tblFaktureK.AddRow(Array As String(row(0), row(1), row(2), row(3), row(6)))
    Loop
    ' defining widts of columns
    tblFaktureK.SetColumnsWidths(Array As Int(140dip, 80dip, 80dip, 80dip, 60dip))
    ' closing the recordset
    Resultat.Close

Hello,

Thank you!
Which view is "tblFaktureK", can't I account?
Could you send a screenshot to see what it looks like?

Cheers

DarkoT · May 8, 2020

Sergio Castellari said:
Hello,

Thank you!
Which view is "tblFaktureK", can't I account?
Could you send a screenshot to see what it looks like?

Cheers

Sory for late answer...

I created "form" with two components - one is panel, other is button:

After this, in source is defined:

Globals:

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

    
    ' Private SqlLite As SQL

    ' defining layout
    Private pnlOFKupca As Panel
    Private tblFaktureK As Table
    Private btnNazaj As Button
    Private myMenu As MutableMenu
    Private MaxRows As Int
    
    ' view for total 
    Private pnlSkupajPostavke As Panel
    Private txtSkupajOdprto As EditText
    Private txtSkupajZapadlo As EditText
    Private txtSkupajNezapadlo As EditText
    Private btnZapSum As Button

End Sub

In sub for showing the data - I connect the Table to the Panel:

Sub ShowDataInTable:

    tblFaktureK.Initialize(Me, "tblFaktureK", 5)
    tblFaktureK.AddToActivity(pnlOFKupca, 0, 0dip, 100%x, 90%y)

And - that it's all...

Hope, this will help you...

Darko

Sergio Castellari · May 8, 2020

Thanks @DarkoT !!!

Now I understand!
Cheers

Sergio Castellari · May 8, 2020

Excuse me @DarkoT,

B4X:

    Private tblFaktureK As Table

What library are you in?
Cheers

klaus · May 8, 2020

From the code, I suppose that he is using the TableView Class.
But, this is a very old version.

The improved version is the Flexible Table Class and CustomView.

Sergio Castellari · May 8, 2020

Thanks for your clarification @klaus,

I am starting with B4A and I need to understand tables very well. My APP is going to need to show / edit many types of tables.
Where can I find various examples of Flexible Table?

Cheers

klaus · May 8, 2020

The TableV3_17.zip file in the Flexible Table thread is a demo program.
It contains one Table added in the Designer and a second one added in the code.
The demo programme uses SQLite databases or .csv files.
A routine to download data with RDC is included in the thread, it is not included in the class.

Sergio Castellari · May 9, 2020

@klaus, thanks so much for guiding me.

I'm testing the Table V3.17 example and it's great. I think this is what I need to view / edit information from different tables.

Questions:
Is the Table.bas Class your own? Can it be used freely? Do you accept donation?

Testing the example:
[[[Excuse the audacity]]]
a) When clicking on the headers (Table 1), the order indicator (yellow triangle) is erroneously shown for columns greater than 1. The indicator is always shown in the following column.
b) Clicking on the header (Number) gives an error and exits the application.
(When deactivating "cbxFirstColumnsFixed" points a) and b) work correctly)

c) In some starts of the application the following appears (I am not sure if it is an error, the application works without problems)

** Activity (main) Pause, UserClosed = true **
Copying updated assets files (1)
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Unexpected event (missing RaiseSynchronousEvents): sv2_scrollchanged
Check the unfiltered logs for the full stack trace.

Cheers

klaus · May 9, 2020

Is the Table.bas Class your own? Can it be used freely? Do you accept donation?

The class was developed at the beginning by Erel, and melamoud and myself added other features.
You can use it freely, there is no donation button.
Thank you for reporting the problems.
a) and b) I had already seen these two problems and have amended them in version 3.18, not yet published.
c) Don't worry about this, it's a warning not an error.

Android Question Any example of how to display a DBResult query with B4XTable or any other view?

Sergio Castellari

Active Member

DarkoT

Active Member

Sergio Castellari

Active Member

josejad

Expert

DarkoT

Active Member

Sergio Castellari

Active Member

DarkoT

Active Member

Sergio Castellari

Active Member

Sergio Castellari

Active Member

klaus

Expert

Sergio Castellari

Active Member

klaus

Expert

Sergio Castellari

Active Member

klaus

Expert

Similar Threads