Android Question B4XTable am I using it for the wrong purpose?

[jimseng]

It seems that the B4XTable is more of a visual (view?) than an object to hold database data. I have had some success with it having only just tried it today but I wonder if I am over complicating things.
Here is a generic sql subroutine to populate a table so I can access the data in my app:

private Sub do_sql(sq As String) As B4XTable
    Dim table As B4XTable
    Dim lst As List
    lst.Initialize
    Dim RS As SD_ResultSet  = MYSQL.ExecQuery(sq)
    Dim colcount As Int = RS.ColumnCount
    Dim colnames(colcount) As String
    table.Initialize("","")
    For i = 0 To colcount -1
        colnames(i) = RS.GetColumnName(i)
        table.AddColumn(colnames(i), table.COLUMN_TYPE_TEXT)
    Next
    Do While RS.NextRow
        Dim row(colcount) As Object
        For i = 0 To colcount -1
            row(i) = RS.getstring(colnames(i))
        Next
        lst.Add(row)
        Loop
        
    RS.Close
    table.SetData(lst)
    table.Tag = lst.size
    Return table
    
End Sub

And I am using it to populate a combobox like this:

humantable = do_sql("Select * from Humans")
cmbHuman.cmbBox.Clear
For i = 1 To humantable.tag
      cmbHuman.cmbBox.Add(humantable.Getrow(i).Get("Name"))
Next

I have spent ages trying to work out how to get the number of rows in the table to no avail, hence me cheating by using putting the size of the list that is the table source in the table tag.
Is this a good use of B4Xtables if I am not going to actually show the data table?
Of the many things that confuse me, one is why is B4xTable.Getrow(0) always null, row(1) is the first result form my query. And another is how can I get the number of populated rows? (B4XTable.size returns 0)
Am I misusing B4xTables?

 

[aeric]

First row of B4XTable is header.

 

[emexes]

more of a visual (view?) than an object to hold database data

Correct. Yes, you can store data in a B4XTable, but it's a suboptimal way to do it.

Similar to storing numbers as strings: yes, you can do it, but there are usually better ways to do it (simpler, faster, smaller).

Depending on the size and use of your data, you'd either store it in an Array, a List, a Map, some combination of those (possibly as a Custom Type), or an SQL database file, or an in-memory SQL database (which is what B4XTable uses internally).

If you give an example of the data in humans, then we can analyse the advantages and disadvantages of each storage structure.

 

[aeric]

Welcome to check this example to learn more about using B4XTable.

[B4X] B4XTable sample working with SQLite

[B4X] B4XTableSample A sample project to demonstrate loading SQLite data into B4XTable GitHub: https://github.com/pyhoon/B4XTableSample-B4X Features Use OkHttpUtils to download json file from a URL Parse the JSON as List and extract each item as Map Store the data into SQLite database Perform...

www.b4x.com

 

[aeric]

how can I get the number of populated rows? (B4XTable.size returns 0)

 

[LucaMs]

O.T.

Sorry, but I can't help myself, I have to write this joke!

@jimseng ... is good for your health.

 

[jimseng]

If you give an example of the data in humans, then we can analyse the advantages and disadvantages of each storage structure.

This is a very small database and this example of the humans table shows that there are only a few fields in the tables, mostly varchars and only a few tables. (Much of this is for my own education.)

SELECT * FROM `Humans` WHERE 1;​

​

ID	Name	Description	Gender
2	Matthew	A handsome fellow	Boy
3	Bella	A very naughty Greyhound	Girl
4	Paul	Never stops sneezing	Boy

​

So I started off thinking that I would use global lists but on a different thread I posed the question as to whether one could bind data to a combo box like one can in .net and I have ended up down this rabbit hole and now I think maybe I need to climb back out and retreat.
Given that my "do_sql" populates a list, I guess sticking with lists might be more efficient, but I haven't found how to do access the list data .
Much of this is accessing the data via a combobox SelectedIndex, The idea being that the "ID" field in the tables matches the index of the combo box.

 

[LucaMs]

This is a very small database and this example of the humans table shows that there are only a few fields in the tables, mostly varchars and only a few tables. (Much of this is for my own education.)

SELECT * FROM `Humans` WHERE 1;​

​

ID	Name	Description	Gender
2	Matthew	A handsome fellow	Boy
3	Bella	A very naughty Greyhound	Girl
4	Paul	Never stops sneezing	Boy

​

So I started off thinking that I would use global lists but on a different thread I posed the question as to whether one could bind data to a combo box like one can in .net and I have ended up down this rabbit hole and now I think maybe I need to climb back out and retreat.
Given that my "do_sql" populates a list, I guess sticking with lists might be more efficient, but I haven't found how to do access the list data .
Much of this is accessing the data via a combobox SelectedIndex, The idea being that the "ID" field in the tables matches the index of the combo box.

https://www.b4x.com/android/forum/threads/b4x-lmb4xcombobox.116767/#content

???

 

[jimseng]

@lucasm Thanks. That is sort of it but I think for my purposes I need to store the data in some form of table or list.
One thing I clearly don't understand is lists, and I can't get the data out of it without using

table.SetData(lst)

If I could understand that I would probably stick to storing the data in a list.
So for instance. If I put a break over the list, I can see the data results, but no reference to the column names:

And yet when a table is populated with the list data (table.setdata(list)) the column names are available for me to pull out. I have a fundamental gap in my understanding of how to use lists and so far I have only be able to retrieve the data by using B4Xtables, which is unlikely to be the most efficient way.

 

[LucaMs]

for my purposes

Obviously, I don't know your purpose.
I'll try to give you some general information, hoping it'll be useful.

B4XTable manages an SQLite database internally. You can run queries against this database - search the site.
The list you see contains an array of objects in each of its items; there are no table field names.

If you want to use List instead of B4XTable, the best way is to create a custom type that represents the record in your table, create a variable of this type, initialize it, fill it with the record's data, and add it to the List. This way, you can also sort the List items by field (the custom type's field name, which should ideally be the same as the table field).

For example:

Type tHuman(ID As Int, Name As String, Description As String, Gender As String)
Public lstHumans as List

lstHumans.Initialize

'Here write code to read records. A "Do While RS.NextRow ..."

ID = RS.GetInt("ID")
Name = RS.GetString("Name")
Description = RS.GetString("Description")
Gender = RS.GetString("Gender")

Dim CurrHuman As tHuman
CurrHuman = CreatetHuman(ID, Name, Description, Gender)

lstHumans.Add(CurrHuman)

'RS.Close

Public Sub CreatetHuman (ID As Int, Name As String, Description As String, Gender As String) As tHuman
    Dim t1 As tHuman
    t1.Initialize
    t1.ID = ID
    t1.Name = Name
    t1.Description = Description
    t1.Gender = Gender
    Return t1
End Sub

[
Sorting examples:
lstHumans.SortType("Name", True / False)
lstHumans.SortTypeCaseInsensitive("Gender", True / False)
]

 

[LucaMs]

If you want to use List instead of B4XTable

An alternative to the List is to use a Map (B4XOrderedMaps are better).
In the Map, you could write the ID as the key and a tHuman variable as the value.



I forgot a note: List with custom types is similar to:
Dim MyList As List Of(<some type>)
VB.Net.

 

[jimseng]

The list you see contains an array of objects in each of its items; there are no table field names.

Thanks LucasMs. I have used types before so I will go back over that.
So I think the bit I am not understanding is if the B4XTable contains field names after it is populated from the list, is it not possible to extract the data directly from the list, including the field names. If I understand you correctly, elements of the list are objects. Do they not contain the columns as well as the actual data?
So if the B4XTable is populated with

B4XTable.SetData(list)

And then one can extract the data by column name:

Dim hID As String  = b4XTable.GetRow(1).Get("ID")

Then is it not possible to get the ID directly from the list that was the source of the B4Xtable? The data must be in there somewhere, or have I missed the point (very likely).
In the past I used the old method of json arrays and

Dim m as Map = list.get(index)
dim res as string = m.get("ID")

 

[LucaMs]

the bit I am not understanding

Sorry, I don't have time to read your post right now, but I'll give you a tip that I think is the best I can give for understanding B4XTable.

Get the B4XTable source code (it's a zip file), use it in your project instead of the library, add a few breakpoints, run the project step by step (press F8) and everything will be crystal clear.

 

[jimseng]

That is indeed a good tip. I didn't realise that was a thing.

 

[emexes]

if the B4XTable contains field names after it is populated from the list, is it not possible to extract the data directly from the list, including the field names

Do you mean "is it not possible to extract the data directly from the B4XTable," ?

Because yes, it is possible to extract the data directly from the List, because that is what was done when the B4XTable was populated from the list.

 

[emexes]

is it not possible to extract the data directly from the list

The data that was in the list, remains in the list, unaltered after passing to B4XTable.SetData()

So the answer to that question is yes.

Perhaps try this (requires DateUtils library) (and B4XTable library) :

Root = Root1
Root.LoadLayout("MainPage")

B4XTable1.AddColumn("Left", B4XTable1.COLUMN_TYPE_TEXT)
B4XTable1.AddColumn("Middle", B4XTable1.COLUMN_TYPE_NUMBERS)
B4XTable1.AddColumn("Right", B4XTable1.COLUMN_TYPE_DATE)

Dim data As List
data.Initialize
data.Add(Array("First",  2,   DateUtils.SetDate(1969, 7, 20)))
data.Add(Array("Second", 3.4, DateUtils.SetDate(1978, 6,  5)))
data.Add(Array("Third",  5,   DateUtils.SetDate(2001, 9, 11)))
data.Add(Array("Fourth", 6.7, DateUtils.SetDate(2025, 8 , 1)))
data.Add(Array("Fifth",  8,   DateUtils.SetDate(1971, 1, 11)))
data.Add(Array("Sixth",  9.0, DateUtils.SetDate(1972, 2, 22)))
B4XTable1.SetData(data)

'display list data using row and column indexes

For r = 0 To data.Size - 1
    Dim rowarray() As Object = data.Get(r)
    For c = 0 To rowarray.Length - 1
        Log(r & TAB & c & TAB & rowarray(c))
    Next
Next

'display list data using indexless iteration

For Each rowobject As Object In data
    Dim rowarray() As Object = rowobject

    For Each cellobject As Object In rowarray
        Log(cellobject)
    Next
Next

row 0    col 0    First
row 0    col 1    2
row 0    col 2    -14292000000
row 1    col 0    Second
row 1    col 1    3.4
row 1    col 2    265816800000
row 2    col 0    Third
row 2    col 1    5
row 2    col 2    1000130400000
row 3    col 0    Fourth
row 3    col 1    6.7
row 3    col 2    1753970400000
row 4    col 0    Fifth
row 4    col 1    8
row 4    col 2    32364000000
row 5    col 0    Sixth
row 5    col 1    9.0
row 5    col 2    67525200000

First
2
-14292000000
Second
3.4
265816800000
Third
5
1000130400000
Fourth
6.7
1753970400000
Fifth
8
32364000000
Sixth
9.0
67525200000

 

[emexes]

is it not possible to extract the data directly from the list, including the field names

the data: yes (example in previous post)

the field names: no (they were never in the list; the list is only data; the columns are defined separately, using B4XTable.AddColumn() call for each column)

 

[emexes]

It seems that we don't need the (unnecessary but otherwise brilliant) "1=1", we can get away with:

Log("source list size = " & data.Size)
Log("before = " & B4XTable1.Size)
B4XTable1.ClearDataView
Log("after = " & B4XTable1.Size)

source list size = 6
before = 0
after = 6

 

[LucaMs]

The main point is that B4XTable is not a B4XDBTable; it isn't associated with database tables. You can insert any type of data into it and when you use a database, you only pass the values contained in the records to the B4XTable, not the table field names.

You could create EXTERNAL data structures and functions to maintain a sort of DB-B4XTable alignment (I'll think about it) or you would need to create a B4XDBTable, using the B4XTable source code as a basis.

 

[jimseng]

the field names: no (they were never in the list; the list is only data; the columns are defined separately, using B4XTable.AddColumn() call for each column)

Ok. That now makes sense. I had missed that bit and that is where my confusion lies. So thanks for the replies. I basically need to decide whether to stick with B4XTables or create data types. I like the slightly generic:

table.AddColumn(colnames(i), table.COLUMN_TYPE_TEXT)

In my fantasy world I have convinced myself this might save time in the future if I expand the database tables, although I have been lazy and haven't established the data type, just assumed strings. I have just got away with it so far.
B4XTables are probably a bit over the top for what I am doing but they are working.
@emexes.

B4XTable1.ClearDataView

Thank you. That was it.