Android Question lists, (SQL)resultmaps and types - what´s the best style

[Michael99k]

I have a complex DB (15+ tables) and my app often needs to read, alter or write to the tables.
A few of the tables contain also fields like the path to an imagefile and many other information, so I was thinking about creating types for holding the values and storing these variables in lists.
Maybe maps...

But I often need listviews or something similar which just have to show e.g. one of the fields, e.g. the name of a person. For this I tried comboboxes. For feeding the combobox a list which is filled with strings would be fine, but the list contains the variable according to the type-declaration. I don´t want to always have to create a second list with only the one field just for beeing able to fill the combobox.

What is recommended for this kind of problem?

I dream of possibilities like (pure imagination):

type person(name as string, age as int)
private listPersons as person: listPersons.initialize
listPersons.add(.name="Mike", .age=40)
listPersons.add(.name="James", .age=30)
combobox1.addall(listPersons(.name))
log(listPersons.get(1).name)

Are there possibilities like the ones drafted above in the imaginary code???

 

[Erel]

I think that a list with custom types is your best option. It is very simple to create another list with a specific field if you want to show a specific field.

Later you will want to show multiple fields and use xCLV to create the items.

 

[Michael99k]

Then I would create a sub which takes a list (with custom types) and returns a list with one simple type, e.g. just the names (".name"). But I would have to tell the sub which part of the custom type (e.g. ".name") should be extracted. But how can I use e.g. the string ".name" given to the sub as a parameter to address this special part of the custom type?
It could look like the code below, but the "lstTyped.Get(i).subtype" near the end doesn´t work. In resultsets or maps I can pick the right part when I use a string with the appropriate key or column-name. Is there any similar solution for custom types too?

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Type person(name As String, age As Int)
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    Private man(2) As person
    man(1).name="Tim"
    man(1).age=44
    man(2).name="Tom"
    man(2).age=33
    Private lstMen As List
    lstMen.Initialize
    lstMen.Add(man(1))
    lstMen.Add(man(2))
    Private lstNames As List
    lstNames.Initialize
    lstNames=ExtractList(lstMen, ".name")
End Sub

Sub ExtractList(lstTyped As List, subtype As String) As List
    Private lstNonTyped As List
    lstNonTyped.Initialize
    If lstTyped.Size=0 Then Return lstNonTyped 'empty list
    For i=0 To lstTyped.Size-1
        lstNonTyped.Add(lstTyped.Get(i).subtype) 'filling the new list
    Next
    Return lstNonTyped
End Sub

 

[LucaMs]

I fear that you need to use a Select inside the loop. Changing your example:

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Type person(name As String, age As Int)
    Private Const TF_NAME As Int = 1
    Private Const TF_AGE As Int = 2
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    Private man(2) As person
    man(1).name="Tim"
    man(1).age=44
    man(2).name="Tom"
    man(2).age=33
    Private lstMen As List
    lstMen.Initialize
    lstMen.Add(man(1))
    lstMen.Add(man(2))
    Private lstNames As List
    lstNames.Initialize
    lstNames=ExtractList(lstMen, TF_NAME)
End Sub

Sub ExtractList(lstTyped As List, Field As Int) As List
    Private lstNonTyped As List
    lstNonTyped.Initialize
    If lstTyped.Size=0 Then Return lstNonTyped 'empty list

    Dim Item As person

    For i=0 To lstTyped.Size-1
        Item = lstTyped.Get(i)
        Select Field
            Case TF_NAME
                lstNonTyped.Add(Item.Name) 'filling the new list
            Case TF_AGE
                lstNonTyped.Add(Item.Age) 'filling the new list
        End Select
    Next
    Return lstNonTyped
End Sub

Note that this line:

lstTyped.Get(i).subtype

should not work even if it were written so:
lstTyped.Get(i).Name
given that Lists are not "typed" (to get something like .Name you should use arrays, which are less flexible than Lists.

 

[MarkusR]

me think about using a sql query, you can bring any field into a alias name, then put this resultset into a select box.

SELECT PersonId as Id, Name as Text FROM Table WHERE [condition];

this PersonId & Name as parameter

Dim rs As ResultSet = sql1.ExecQuery2("SELECT ? as Id, ? as Text FROM ? WHERE ? = ?",Array As String("PersonId","Name","Person","GroupId",5))

 

[OliverA]

I'm going to propose that you take a look at the source of jRDC2 (https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/). In that project, in the Main module, the following type is defined

Type DBResult (Tag As Object, Columns As Map, Rows As List)

In the RDCHandler class module, check out ExecuteQuery2. This method fills the DBResult type. (Please note, this method would need to be modified just slightly, passing it a parameterized query and parameters instead of retrieving them from an HTTP request. The JavaObject access would need to be changed to the cursor, which can provide column name and type information (see https://developer.android.com/reference/android/database/Cursor))
Now, if I want to know what column a certain entity is, I could use the following method (this is an actual method that is part of jRDC2Utils - https://www.b4x.com/android/forum/threads/jrdc2-client-example-using-modded-jrdc2.85581/)

Public Sub GetColumnIndex(res As DBResult, colName As String) As Int
   Dim colIdx As Int = -1
'   If res = Null Or res.IsInitialized = False Then Return colIdx
   Dim columns As Map = res.Columns
   For Each key As String In columns.Keys
       If key.EqualsIgnoreCase(colName) Then
           colIdx = columns.Get(key)
           Exit
       End If
   Next
   Return colIdx
End Sub

In your above code sample

combobox1.addall(listPersons(.name))

could become something like

combobox1.addall(GetColumnAsList(someDBResult, "name"))

where GetColumnAsList could be

public Sub GetColumnAsList(res As DBResult, colName As String) As List
   Dim resultList As List
   resultList.Initialize
   Dim colIdx As Int = GetColumnIndex(res, colName)
   If colIdx <> -1 Then
       For Each row() As Object In res.Rows
           resultList.Add(row(colIdx))
       Next
   End If
   Return resultList
End Sub

This is definitely more than just a Type, but it would provide re-usable code blocks without having to adapt them to each query's result.

Another solution? Look at DBUtils (https://www.b4x.com/android/forum/threads/dbutils-android-databases-are-now-simple.8475/#content). It has methods that return result sets as list of arrays, that will fill a spinner out for you or a list view and other db related helper methods.

Another solution? Just run a query specific to the item you want to fill out. What's wrong with that?

 

[Michael99k]

Ok, maybe it´s the best solution to use the query two times, one time for the whole resultset to retrieve multiple fields and and a second time for the field which is used for the combobox...
The select would work, but There are so many cases, that it´s not an elegant solution here.
Regarding jRDC2 - I´m new to Databases (just read SQL for beginners) and the programm should be crossplattform...
But thanks a lot!!!

 

[OliverA]

Regarding jRDC2 - I´m new to Databases (just read SQL for beginners) and the programm should be crossplattform

I did not say use jRDC2. I said look at it to see how it create DBResult. As to cross-platform, that could be done by hiding platform differences within the class (B4A just does not do DB access quite like JDBC does under B4J nor the DB access under iOS).