Android Question Resultset

[Guenter Becker]

Dear B4X Friends,

I like to set the pointer of the resultset to got to a special record (position unknown) by a given column value and I am looking for the best and fast way to do it.
Any idea? Do'nt like to loop over the records trying to find a more direct way.

Thank you for your answer.

 

[aeric]

Do'nt like to loop over the records trying to find a more direct way.

Instead of using a List, we can also use Map. No loop is used when "finding" the record.

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private SQL1 As SQL
    Private Result As Map
End Sub

Public Sub Initialize
    Result.Initialize
    SQL1.Initialize(File.DirInternal, "data.sqlite", False)
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    Dim RS As ResultSet = SQL1.ExecQuery("SELECT id, name, age FROM data")
    Do While RS.NextRow
        Result.Put(RS.GetInt("id"), CreateMap("name": RS.GetString("name"), "age": RS.GetInt("age")))
    Loop
    RS.Close
End Sub

Private Sub BtnFind_Click
    Dim id As Int = 2
    Dim record As Map = Result.Get(id)
    Dim name As String = record.Get("name")
    Dim age As Int = record.Get("age")
    Log($"${name} is ${age} years old"$)
End Sub

 

[Guenter Becker]

Here is an example of what we can do with resultset.

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private SQL1 As SQL
    Private Result As List
End Sub

Public Sub Initialize
    Result.Initialize
    SQL1.Initialize(File.DirInternal, "data.sqlite", False)
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    Dim RS As ResultSet = SQL1.ExecQuery("SELECT id, name FROM table1")
    Do While RS.NextRow
        Result.Add(CreateMap("id": RS.GetInt("id"), "name": RS.GetString("name")))
    Loop
    RS.Close
End Sub

Private Sub BtnFind_Click
    Dim id As Int = 2
    Dim record As Map = findPosition(id)
    Log(record.Get("name"))
End Sub

Private Sub findPosition (id As Int) As Map
    For Each row As Map In Result
        If id = row.Get("id") Then
            Return row
        End If
    Next
    Return CreateMap()
End Sub

I close the resultset when I don't want to "touch" it anymore. I always do it immediately.
Therefore, we are not finding in the resultset but the List that we already passed to.

Thats the way I do it yet. Understand no one has abetter solution. Maybe Erle will attach a FInd Method ion the next Resultset update.
Thanks for your assisstance.

 

[aeric]

Thats the way I do it yet. Understand no one has abetter solution. Maybe Erle will attach a FInd Method ion the next Resultset update.
Thanks for your assisstance.

I may be wrong. I don't think Erel will add that method.
For me, a resultset is just a temporary storage object.
You can assign the values from it to another object.

 

[netsistemas]

After reading everything, I think this is not the solution:
Select top 1 from table order by tuid

(top 1 will be limit 1 in sqlite)

Searching within a group of records is not possible, beyond loading it in some type of data that allows searches, if you are going to do more than 1 search, but if it is only one, you will have to search one by one.
I suppose your goal would be something like FILTER or SEARCH, but I don't see that as possible
----------
Tras leer todo, creo que esto no es la solución:
Select top 1 from tabla order by tuid

(top 1 será limit 1 en sqlite)

Lo de buscar dentro de un grupo de registros, no se puede, mas allá de cargarlo en algún tipo de datos que permita busquedas, si vas a hacer mas de 1 busqueda, pero si solo es una, deberás buscar uno a uno.
Supongo que tu objetivo sería algo como FILTER o SEARCH, pero eso no lo veo posible

 

[emexes]

Maybe OFFSET could be used to do a poor man's binary search. Or to display a specific (say) 20 rows of a (say) 10000 row result set, without having to iterate through the 10000 rows.

https://www.sqlitetutorial.net/sqlite-limit

 

[DonManfred]

Maybe OFFSET could be used to do a poor man's binary search.

The problem is that he do not know the index of the newly generated dataset. It may be the first, the 12.345th or even the last record if he rerun the SQL-Query.

 

[emexes]

The problem is that he do not know the index of the newly generated dataset. It may be the first, the 12.345th or even the last record if he rerun the SQL-Query.

But he will know the value of the field that the result set is sorted by, and so be able to narrow down to the OFFSET of the newly added record.

I agree that perhaps it's not worth the added complexity, and might even be quicker to scan through 10,000 rows of a result set.

 

[aeric]

Understand no one has abetter solution. Maybe Erle will attach a FInd Method ion the next Resultset update.
Thanks for your assisstance.

Can you explain why the solutions I proposed not even one answer to your issue?
I hope you have seriously look at my replies and tell us why you think there is no better solution.

I think it is easily ignore what I have written so I am going to summarize them.

1. Use a List
2. Use a Map
3. Use B4XTable or a Paging view
4. Use Autoincrement id

I also can suggest you to check my MiniORMUtils.

I have another solution related to #3, but until you explain further, I am not posting it now.

 

[MrKim]

Have a look at this:

Sub DBResultToMapEthernet(Res As JdbcResultSet) As List
    Dim Rows As List, Y As Int', X As Int
    Rows.Initialize
    Do While Res.NextRow
        Dim Cols As Map
        Cols.Initialize
        For Y = 0 To Res.ColumnCount - 1
            If Res.GetColumnName(Y).EqualsIgnoreCase("Picture") Then
                Cols.Put(Res.GetColumnName(Y), Res.GetBlob2(Y))
            Else
                Cols.Put(Res.GetColumnName(Y), Res.GetString2(Y))
            End If
        Next
        Rows.Add(Cols)
    Loop
    Return Rows
End Sub

It converts a resultset in to a list of maps that you can use very much like a recordset that has find next, previous, etc. by creating your own routines for find the list. It is EXTREMELY fast at converting a result set into something that is more flexible.

 

[MrKim]

Maybe Erle will attach a FInd Method ion the next Resultset update.

Highly unlikely. As I understand it Resultset is just a B4a Wrap of Java code. The best solution available on your "Terms". Take a look at my other post. It will turn any reultset in to a list of maps. It is extremely fast. You can then write your own Find routine. Assuming the resultset is ordered by the field you want to find you just keep splitting the number. If you have 1000 records look at 500. If the one you want is less than, look at 250. > 375. < 312. < 381. > 396. etc. When you get to 5 or 10 or 20 then just go through the list. You have searched 1000 records and only had to look at 10 or 20.

Look, I understand where you're coming from Am an old school VBA programmer and used to the tools you are but the reality is I have found all that overhead in a DAO or ADO recordset was SLOW. It is nice to get back my records FAST without all the overhead and If I need to look for a particular record I can do it but I am not supporting that overhead every single time I run a query. After a couple of years of annoyance, I wrote the routine I refer to and I now put my records in a list of maps if I need anything other than a straight, forward only recordset.

 

[aeric]

This is how I do in MiniORMUtils to get the row at position 2 (index of 1).

Dim DB As MiniORM
DB.Initialize(DBType, DBOpen)
DB.Table = "tbl_products"
DB.Query
Dim Item As Map = DB.Results.Get(1)
Log(Item)

Note MiniORMUtils is completely open source that support B4A, B4i and B4J for SQLite database. It also support MySQL in B4J.
I also have another closed source library that support more databases like MS SQL, PostgreSQL and Firebird.