Wish Sql/ResultSet

[DarkoT]

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]

Like with:

https://www.b4x.com/android/help/sql.html#resultset_rowcount

? ?

 

[emexes]

https://www.b4x.com/android/help/sql.html#resultset_rowcount

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 )

 

[Alexander Stolte]

? ?

yes, but this is B4A only. In B4J and B4I you need 2 SELECTS, one with count...

 

[emexes]

yes, but this is B4A only. In B4J and B4I you need 2 SELECTS, one with count...

You are correct as always. ?

Needing 2 SELECTS is rather sad. I wonder why it would be that way.

 

[aeric]

Another option is to try using my MiniORM

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

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

 

[DarkoT]

yes, but this is B4A only. In B4J and B4I you need 2 SELECTS, one with count...

Exactly... Question was related to B4J where I'm missing rowcount...

 

[emexes]

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) :

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]

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) :

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...

 

[Gianni M]

maybe I didn't understand:

after select pass parameter rs (ResultSet)

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]

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.

 

[Erel]

The row count isn't always available. In many cases, especially with large results, it isn't available.