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]

Is the condition will return you a unique row?
example:
SELECT * FROM MyTable WHERE ColumnName = 'Value'
I think you can use a Primary Key or row_id.

 

[Guenter Becker]

RowID seems to be good because many people think the value is fix and tied to the record. But it is'nt! For example if you compress a database all rowids will be autoincremented new.
Next if you insert a record in the database you have to reload the resultset to include the newly inserted record. The position of the record changes if the undelaying SELECT contains an ORDER condition. That means the easy way to position on the last resultset record is not the solution.
You see there are several situations where the known position of a record is unknow after reload. To update the position as well you have to find the new position of the changed/inserted record. Th best solution is to have find method for the resultset but there is none yet.
At least the way to change the SELECT to show only the new/updated record will work but in this case you are loosing record movement because all other records are not included.

 

[aeric]

I don't use row_id but I add my own autoincrement column which I name it id.
I am not sure why the exact position is important in your case.
My experience with SQL is I always use the primary key as reference. I never concern about it's position.

 

[Guenter Becker]

Ok think I did not make myself clear.
Lets say we have a resultset with an ordered selection of 100 records. We insert a new record in the database table. To include that new record we have to reload the resultset because sql INSERT only works on the database side.
After reload we want to position the record pointer on the new record. How will you go to find its position? At present I loop over the records in the resultset and if I find the saved value (own autoinc value or other) I have found the position. This works but costs time if the load of the resiltset is a big load.

 

[aeric]

As I said, I use auto increment id in SQLite.
When a new row is inserted, I can get the latest id by executing the following SQL command.

SELECT LAST_INSERT_ROWID()

I don't use loop to search for that record.
edit: It doesn't matter I order the resultset or not.

 

[Guenter Becker]

No, we are not on the same page!

You are talking about a selection to retrieve the rowid of the last inserted record in the Database. I know that possibility.

My question is how to find this rowid as a special record(position) in a resultset after a fresh select of several records.
For example in MS VB there is a function called rs.find that is what I like to do in B4A.

private sub findPosition(searchfor as long)
    dim oldpos = rs.position
    dim newpos as long
    dim found as boolean = false
    do while rs.nextrow
        if r.getlong("rowid") = searchfor then
            found = true
            newpos = rs.position
            exit
        end if
    loop
    ' reset to old position if not found
    if found = false the rs.position = oldpos
end sub

 

[aeric]

My question is how to find this rowid as a special record(position) in a resultset after a fresh select of several records.
For example in MS VB there is a function called rs.find that is what I like to do in B4A.

I don't understand what you mean by find.

What is a special record to you? (I treat all records the same, no bias)

Find the last row?
Find by what condition?

Use SELECT command to find and filter by condition(s) using WHERE.
Can you give an example so we can understand what you are looking for.

 

[Guenter Becker]

see updated reply please.

 

[aeric]

I don't understand why don't you just straight away query for that id in the first place but instead do extra steps to get it.
I am not sure you are referring to ADO.NET DataTable or DataAdapter stuffs that you treat the resultset as an offline dataset.
When working with local/embeded database like SQLite, I don't think we need to concern about the connection or performance.
Just make new queries.

 

[emexes]

I don't understand

I don't 100% understand either, but my first guess is:

maybe the resultset is being displayed as a selection list, and we're trying to maintain the scrolled position of that selection list, or trying to reposition the list to the newly-added entry, which could be anywhere (top, middle, bottom) of the sorted resultset.

Actually, after putting it in my own words, I now think this post is clear:

Let's say we have a resultset with an ordered selection of 100 records. We insert a new record in the database table. To include that new record we have to reload the resultset because sql INSERT only works on the database side.
After reload we want to position the record pointer on the new record. How will you go to find its position?

It's sort-of like: how to do a select within a resultset? My first thought was to have a temporary table instead of a resultset, but I'm pretty sure that'll take longer than "manually" searching the resultset.

 

[emexes]

I am no SQL genius but this feels like it might get you closer to the finish line:

https://x.com/i/grok?conversation=1895310171519492513

 

[emexes]

https://x.com/i/grok/share/dDdUhklV4G1xlezGCHjrpoREC

 

[aeric]

My random guess is the OP have a SELECT * query let say 100 rows out of 1,000,000 rows. Display the 100 rows in a table view such as B4XTable. With this preloaded data, the users can navigate back and forward, go to first or last and between pages just like B4XTable paging control.
Maybe something else similar.
If not using B4XTable, what I can think of is to loop through the resultset once to add the rows into a List and then work from there. Use IndexOf to search/find for the wanted row.

 

[emexes]

My random guess

I am having same random guess (but could be 10,000 records of 1,000,000) and the question is:

when a new record is added to the table, and the result set regenerated, how move the result set cursor to the new record directly, without having to do a sequential scan through the result set?

Particularly if each advancement of the cursor through the result set also caused collateral disk reads of the data.

 

[Guenter Becker]

Dear SIrs,
thank you for reply and discussion. Your response showed to me that I made myself clear. We will not find a solution talking about possiblities on side of sqlite database. Maybe there is a selectable resultset or not.

You know in B4X we have to load data into a B4X Resultset and there is no query or find method. An that is my question does anyone konw a fast workaround to solve that?

 

[aeric]

when a new record is added to the table, and the result set regenerated, how move the result set cursor to the new record directly, without having to do a sequential scan through the result set?

I don't understand again. Maybe a GUI design can help me understand better.
Are we talking about using a B4XTable or CustomListView for the UI?
B4XTable helps building a good user interface with many rows of records. It provides navigation and search function.
When talking about cursor, I can relate which index of rows or items. In current development, I don't see it is required, especially talking about B4X cross platform solution.
Remember that B4XTable utilize an in-memory database. We can treat it as an offline dataset which is not affecting the main database.
For example, we have a database table name "tblCustomer" in SQLite. In B4XTable, we are dealing with the table name "data".
In my understanding, resultset is use to return the data in a single or multiple rows from a query call.
Once you get the resultset, we use Do While loop to populate a list or display the data on UI.
After that, we don't "touch" the resultset anymore.

Also note that every new row is added at the end. Why we need to search? Unless you are not using an autoincrement id.

 

[LucaMs]

Dear SIrs,
thank you for reply and discussion. Your response showed to me that I made myself clear. We will not find a solution talking about possiblities on side of sqlite database. Maybe there is a selectable resultset or not.

You know in B4X we have to load data into a B4X Resultset and there is no query or find method. An that is my question does anyone konw a fast workaround to solve that?

I didn't understand either.
Maybe you can solve it by creating a query with two nested Selects?
(Not having understood the question well and not having time, now, to reread everything, I'll try this suggestion).

 

[aeric]

Maybe I put it this way.
You go to a buffet style place, pickup a salad with combination of vegetables and put into your bowl.
You walk to your table.
You saw a cherry tomato in your partner's bowl.
You took it and put into your bowl.
The cherry tomato is on top of the salad.
It should not appear beneath the salad.
No need to find it.

Joke aside.
To understand the problem, I already said. What is the User Interface design and how the user is going to use the app? Is there a paging control?

 

[aeric]

Once you get the resultset, we use Do While loop to populate a list or display the data on UI.

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.