Wish Sql/ResultSet

DarkoT

Active Member
Licensed User
I think that will be very useful when I can get number of records in ResultSet. When I want to show user how the process running and how long will take for all updates in database, I need first to calculate number of all records (select count(*) from MyTable where ...) and after this I can show the progress...

Will be easy when I can get in resultset .NumRecs...
It's possible?

Thank you, Darko
 

emexes

Expert
Licensed User

1673953496827.png


I am pretty sure that "or" is a typo, and should be "of". ☮️

(and if anybody's fixing that, may as well fix the other occurrence too, at: https://www.b4x.com/android/help/sql.html#cursor_rowcount )
 

aeric

Expert
Licensed User
Longtime User
Another option is to try using my MiniORM :)

DBTable.Count return number of rows in the query result.

B4X:
Private Sub ReadRefreshTokenLifeTime As Long
    Dim con As SQL = Main.DB.GetConnection
    Dim TokenLifetime As Long
    Dim DB2 As MiniORM
    DB2.Initialize(con)
    DB2.Table = "ClientMaster"
    DB2.Where = Array("ClientId = ?", "ClientSecret = ?")
    DB2.Parameters = Array(Main.AUTH.CLIENT_ID, Main.AUTH.CLIENT_SECRET)
    DB2.Query
    If DB2.DBTable.Count > 0 Then
        Dim client As Map = DB2.DBTable.First
        TokenLifetime = client.Get("RefreshTokenLifeTime")
    End If
    Main.DB.CloseDB(con)
    Return TokenLifetime
End Sub
 

emexes

Expert
Licensed User
after this I can show the progress...

If you're processing a bunch of records but the order doesn't matter, then this might get you halfway to a progress status with one query (not two) :

SQL:
SELECT rowid, * FROM MyTable ORDER BY rowid DESC;

where you note the rowid of the first result row, and then the % progress is (1 - rowid / firstrowid) * 100%
 

DarkoT

Active Member
Licensed User
If you're processing a bunch of records but the order doesn't matter, then this might get you halfway to a progress status with one query (not two) :

SQL:
SELECT rowid, * FROM MyTable ORDER BY rowid DESC;

where you note the rowid of the first result row, and then the % progress is (1 - rowid / firstrowid) * 100%
This is only true when you want to update all rows in table... Without where (and in case that you have incremental primary key which is equal as RowID). In most cases we using select with Where which will return rows where last rowId is not highest rowId...
 

giannimaione

Well-Known Member
Licensed User
Longtime User
maybe I didn't understand:

after select pass parameter rs (ResultSet)

B4X:
Sub rowCount(rs As JavaObject) As Int
    rs.RunMethodJO("last", Null)
    Dim count As Int = rs.RunMethod("getRow", Null)
    rs.RunMethod("beforeFirst", Null)
    Return count
End Sub
 

emexes

Expert
Licensed User
This is only true when you want to update all rows in table...

I said half-way, not perfect. It will be approximately true if the SELECTed records are randomly distributed across rowid. I agree that if this is not the case, then the progress estimate could have odd jumps eg if the WHERE is for last year of data out of five years of data then the progress estimate would count from 0% to 20% and then jump straight to 100%.

But if your query was for eg all students who studied chemistry, or all overseas students, then the progress estimate would probably be good enough.

You're free not to use the idea. ☮️
 
Last edited:
Top