Android Code Snippet DBUtils 2 - New Method - HasRecords

While converting my code (to make compatible with A, i, J) from using:
Private Cursor as Cursor
--- to:
Private Cursor as ResultSet
I found the need to determine if the dataset has any rows... (like: Cursor.RowCount - which does not exist in ResultSet).

I added this method to my DBUtils module. I noticed Erel used this:
If cur.NextRow = True - in other methods to determine if (rows > 0) in resultset.

'Executes the query and returns True if ResultSet has data
' Appends " LIMIT 1" to query passed to make it "light" (one record)
'
Public Sub HasRecords(SQL As SQL, Query As String, StringArgs() As String) As Boolean
    Dim res As Boolean = False
    Query = Query&" LIMIT 1"
    Dim cur As ResultSet
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    Log("Execute this query for recs: " & Query)
    If cur.NextRow = True Then
         res = True
    End If
    cur.Close
    Return res
End Sub

Typical useage:

Sub GetDivs(id As Int) As Map
 
    Dim ret As Map
    ret.Initialize

    Dim q As String = "SELECT * FROM message_mast where mast_id = "&id ' the query

    If DBUtils.HasRecords(Starter.SQL1, q, Null)  Then ' check if table has records before proceeding
        Dim Cursor As ResultSet  ' now get a full result set
        Cursor = Starter.SQL1.ExecQuery( q )
        Dim i As Int = 0  ' need a row position (index) - since ResultSet doesn't have one..
  
        Do While Cursor.NextRow
            Dim dd As Map
            dd.Initialize
            dd.Put("id",Cursor.GetInt("id"))
            dd.Put("mast_id",Cursor.GetInt("mast_id"))
            dd.Put("name",Cursor.GetString("name"))
            dd.Put("topic_id",Cursor.GetString("topic_id"))
            dd.Put("prime",Cursor.GetInt("prime"))
            dd.Put("subscribed",Cursor.GetInt("subscribed"))
            dd.Put("spare1",Cursor.GetString("spare1"))
            dd.Put("spare2",Cursor.GetString("spare2"))
            dd.Put("spare3",Cursor.GetString("spare3"))

            ret.Put(i,dd)

            i = i + 1

        Loop
        Cursor.Close ' close here - since we actually created a cursor...
    End If
 
    Return ret
 
End Sub

Thanks

 

[mc73]

As I expected (and as documentation states - which I followed), invoking .NextRow moves the cursor position 1 down row of resultset.
So, if the NEXT call to .NextRow had only one row in Cursor/resultset to begin with - It Returns False! (we consumed 1 row in first call (moving it by one).
Your suggestion failed. Try it yourself.

If resultset returned many rows, this will work.. If rs returns only 1 row - you will suck air...

Sub UpDateZone(txt As String)

    Dim q As String
    Dim tbl As String
    Dim Cursor As ResultSet
    Dim res As String = "Not Found!"
    If txt = "" Then
        lblzone.Text = res
        Return
    End If

    If rbcan.Checked Then
      tbl = "postal_codes"
      q = "Select * from "&tbl&" where postal_code LIKE ('"&txt&"%')"
      Cursor = SQL2.ExecQuery(q)
    Else
      tbl = "zip_codes"
      q = "Select * from "&tbl&" where postal_code LIKE ('"&txt&"%')"
      Cursor = SQL3.ExecQuery(q)
    End If

' your suggestion...

    Private nextRecord As Boolean = Cursor.NextRow  ' this will move cursor 1 row - as stated (wishing we had a rowcount across all platforms - but not yet)
    If Not(nextRecord) Then
        Log("no records")
    Else if nextRecord Then
        Log("We had (at least 1 record in result - no way to determine how many...")
          
        Do While Cursor.NextRow
            res = Cursor.GetString("place_name")&", "&Cursor.GetString("acode1")&CRLF&Cursor.GetString("aname2")
            Log("We have a record to set - exit loop ")

            Exit 
        Loop
'        Log("Should be records - but if only one existed ... we skipped the only record found already!!!!")
      
    End If
    Cursor.Close
    lblzone.Text = res
  
End Sub

Please explain where I going wrong. "No" is not definitive as a conclusive explanation, as I understand it the issue.

PS:
The call to HasRecords (providing it is a simple statement) has no effect (delay) on the system. Result is returned in under 1 ms. Works for me.


Thanks

The trick in my code is declaring a boolean, the "nextRecord".
When we set it to resultSet.nextRow, indeed the cursor moves on step forward. So, in the beginning, if no results are available, nextRecord is false. Thus, "if" statement will perform the "no records" log. So, here's the trick: If nextRecord is true, we immediately read data from the very first available position of the cursor. AFTER reading, we reset nextRecord to resultSet.nextRow and we loop. The problem with your code is that it contains a second IF, which is NOT what I've suggested, as it will obviously move on.
Let me rewrite my code:

Private nextRecord As Boolean=res.NextRow
If Not(nextRecord) Then
   Log("no records")
Else 'why did you put another "If" here?
   Do While nextRecord
      'process
      ' you read data here. What is the initial position of our cursor here? Answer: The very first one.
      nextRecord=res.NextRow
     'the above statement should be the very last before the "loop". 
   Loop
End If
res.Close

Test:

Private query As String="select 1 union select 2"
Private res As ResultSet=sql.ExecQuery(query)
Private nextRecord As Boolean=res.NextRow
If Not(nextRecord) Then
    Log("no records")
Else
    Do While nextRecord
       'process
       Log(res.GetString2(0))
       nextRecord=res.NextRow
    Loop
End If
res.Close

Adding a counter:

   Private counter As Int
   Private query As String="select 1 union select 2"
   Private res As ResultSet=sql.ExecQuery(query)
   Private nextRecord As Boolean=res.NextRow
   If Not(nextRecord) Then
       Log("no records")
   Else
       Do While nextRecord
           'process
           counter=counter+1
           Log(res.GetString2(0))
           nextRecord=res.NextRow
       Loop
       Log("records="&counter)
   End If
   res.Close

 

[OliverA]

Another (valid) example???

I think this is a good example. Please notice that you are not using another cursor in the SUB of the example. All other examples, you are using a cursor right after the check to redo the same query that HasRecords uses. In those cases, HasRecords just duplicates the effort. In this case, it does not.

 

[Harris]

So, here's the trick: If nextRecord is true, we immediately read data from the very first available position of the cursor

Yes, I see that now! Very well explained.
My narrow mind couldn't see past the fact the cursor position was moving.... and MY do while WAS moving it off the only record I had...
Thanks Much!


Updated sub:

Sub UpDateZone(txt As String)
    Dim q As String
    Dim tbl As String
    Dim Cursor As ResultSet
    Dim res As String = "Not Found!"
    If txt = "" Then
        lblzone.Text = res
        Return
    End If
    Dim sq As SQL
    If rbcan.Checked Then 
      tbl = "postal_codes" ' get names from Canadian postal codes
      sq = SQL2
    Else
      sq = SQL3   
      tbl = "zip_codes"    ' get names from USA zip codes
    End If
    q = "Select * from "&tbl&" where postal_code LIKE ('"&txt&"%') LIMIT 10"
    Cursor = sq.ExecQuery(q)
    
    Private cnt As Int = 0
    Private nextRecord As Boolean = Cursor.NextRow
    If Not(nextRecord) Then
        Log("no records")
    Else
        Log("We had (at least 1 record in result - up to LIMIT of 10 ")
            
        Do While nextRecord
            cnt = cnt + 1
            res = Cursor.GetString("place_name")&", "&Cursor.GetString("acode1")&CRLF&Cursor.GetString("aname2")
            Log("We have a record to set - count and place:  "&cnt &"  -  "&Cursor.GetString("place_name"))
            nextRecord = Cursor.NextRow
'            Exit   
        Loop
        
    End If
    Cursor.Close
    lblzone.Text = res
    
End Sub

Log Results when txt = "T". When txt = "T0B" - 1 result: Wainwright Region (Tofield) - Perfect!

We had (at least 1 record in result - up to LIMIT of 10
We have a record to set - count and place:  1  -  Eastern Alberta (St. Paul)
We have a record to set - count and place:  2  -  Wainwright Region (Tofield)
We have a record to set - count and place:  3  -  Central Alberta (Stettler)
We have a record to set - count and place:  4  -  Western Alberta (Jasper)
We have a record to set - count and place:  5  -  North Central Alberta (Slave Lake)
We have a record to set - count and place:  6  -  Northwestern Alberta (High Level)
We have a record to set - count and place:  7  -  Southeastern Alberta (Drumheller)
We have a record to set - count and place:  8  -  International Border Region (Cardston)
We have a record to set - count and place:  9  -  Kananaskis Country (Claresholm)
We have a record to set - count and place:  10  -  Central Foothills (Sundre)

 

[Harris]

This thread exposes one truism ( a classic example ) :
" You (and I) may not have the definitive answer/solution - (proper, simple, or correct) "

I am always amazed with what other members come up with - when I understand the meaning of their solution.

We all get trapped into our own narrow logic. When we reach out (like this post) - we find that others may have a different way at looking at the issue.
We must thank those who "take the time" to study our posts, and offer an expert (alternative) solution (within our limited time).

With the concise explanation of @mc73 , I was able to understand an alternative method that will accomplish my desire - without additional overhead.

Thanks to all