Android Code Snippet DBUtils 2 - New Method - HasRecords

Harris · Jan 18, 2019

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.

B4X:

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

B4X:

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

Myr0n · Jan 18, 2019

You can simplify your code with something like this

B4X:

Public Sub HasRecords(SQL As SQL, Query As String, StringArgs() As String) As Boolean
    Dim count As Int = SQL.ExecQuerySingleResult2(Query, StringArgs)
    Return count > 0
End Sub

Harris · Jan 19, 2019

Myron said:
You can simplify your code with something like this

B4X:

Public Sub HasRecords(SQL As SQL, Query As String, StringArgs() As String) As Boolean Dim count As Int = SQL.ExecQuerySingleResult2(Query, StringArgs) Return count > 0 End Sub

Function returns a string.
if no records found, returns Null.
Is Null > 0?

Function help (copy)...
Dim NumberOfMatches As Int
NumberOfMatches = SQL1.ExecQuerySingleResult2("SELECT count(*) FROM table1 WHERE col2 > ?", Array As String(300))
This would work, I suspect, since the query is returning a number (count(*) ).

In my example, I would need 2 queries for this work. One to get a count, and one to get all records....
I was looking to keep it simple with one query - as per example.

I just wish ResultSet had the same functionality as Cursor - but that's not for me to dictate - just workaround for 3 platform compatibility.

Thanks for commenting. All points of view are welcome - so we can learn.

Myr0n · Jan 19, 2019

Yes you are right I am really sorry, but in my mind will all be to check if there are records or not, meaning "SELECT count(*) FROM YourTable where AnyField=?" this query all return 0 or more than 0.

You can use resumablesub too one sub returning different answer
like:

B4X:

    Wait For (HasRecords(Starter.SQL, "SELECT count(*) FROM Students WHERE Id=?", Array As String(spnrStudentId.SelectedItem),0)) Complete (ResultBoolean As Boolean)
    Log("ResultBoolean "&ResultBoolean)
    Wait For (HasRecords(Starter.SQL, "SELECT count(*) FROM Students WHERE Id=?", Array As String(spnrStudentId.SelectedItem),1)) Complete (ResultInteger As Int)
    Log("ResultInteger"&ResultInteger)

Public Sub HasRecords(SQL As SQL, Query As String, StringArgs() As String, Boolean0OrNumberOfRecords1 As Int) As ResumableSub
    Dim count As Int = SQL.ExecQuerySingleResult2(Query, StringArgs)
    If Boolean0OrNumberOfRecords1 = 0 Then Return count > 0 Else Return count
End Sub

B4X:

ResultBoolean true
ResultInteger 1

Harris · Jan 19, 2019

Did I happen to mention a "simple" approach? Geezzz...
Trying to use the KISS approach - (Keep It Simple Stupid)... (or keep it stupidly simple...)
Thanks

udg · Jan 19, 2019

Hi, correct me if I'm wrong.
Using HasRecords results in doubling the efforts from the SQL engine prospective. I mean, the function itself needs a ResultSet and a Query (altough limited to 1 record) in order to determine if there are records, then the calling method prepares a second ResultSet (complete) and a second query to operate on the data.
Wouldn't it be simpler to dim just one ResultSet and just the real query in the calling method and then, eventually, check the returned ResultSet like Erel did?
What am I missing?

Mahares · Jan 19, 2019

Harris said:
the need to determine if the dataset has any rows... (like: Cursor.RowCount - which does not exist in ResultSet).

Myron said:
You can use resumablesub too one sub returning different answer

udg said:
What am I missing?

Of course Resultset has RowCount syntax:
As an example:

B4X:

Dim rs As ResultSet
    rs=Starter.SQL1.ExecQuery2(MyQuery, Array As String(mykey))
    Log(rs.RowCount)  'displays number of records in query
    rs.Close

klaus · Jan 19, 2019

Be aware that RowCount is available only in B4A.
Therefore it is not a solution for cross platform projects.

Mahares · Jan 20, 2019

klaus said:
Be aware that RowCount is available only in B4A.

Thanks for clarifying. Post #1 of this thread mentioned that RowCount does not exist in ResultSet. Since it did not explicitly specify that RowCount does not exist in B4i and B4J, many of us in the forum that do not work with B4i and B4J would not know that; hence my post #7.

Harris · Jan 20, 2019

Harris said:
While converting my code (to make compatible with A, i, J) from using:

(to make compatible with A, i, J) - was the key point....

OliverA · Jan 21, 2019

I think I need to see another example, since I can just rewrite your example as:

B4X:

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

Actually, all I did is comment out your HasRecords check. The result is the same, except I have one less call to the server (a point @udg was making). I'm not saying this is not useful, I just don't see the need for HasRecords in your example.

Harris · Jan 21, 2019

OliverA said:
I need to see another example

True... That isn't the best example of why I need the function..

This one does....

The client posts a message (with possible images attached) and this method is called to send to server...
The server will send to others who are subscribed to the FCM topic....

Here, I enable a timer (set to 60 seconds). The timer_tick will call this method.
We know there is at least 1 record that needs to be sent.
WHEN the record gets sent (might fail due to no wireless service, server down, etc.), the sent field is set to 1.

60 seconds later, the timer calls this method, determines there are no records to send (or 1 or more still exist due to fail).
The timer is disabled when no records found to send. I suppose I could use a counter in the method - to disable the timer after (x many) tries (futile to continue attempts).

The method exists in a service - it can run at startup to check if anything was pending when app (device) was shut down.

B4X:

Sub SendtoServer3

sendtimer.Enabled = True

Dim mast As Map
mast.Initialize

Dim q As String = "SELECT * FROM messmast WHERE sent = 0"
If DBUtils.HasRecords(Starter.SQL1,q,Null) Then   
    Dim Cursor As ResultSet
    Cursor = Starter.SQL1.ExecQuery(q)

    Do While Cursor.NextRow

        Dim mesmap As Map
        mesmap.Initialize

        mast.Put(Cursor.GetString("mastid"), Cursor.GetString("message"))
        For j = 0 To Cursor.ColumnCount -1
            mesmap.Put(Cursor.GetColumnName(j), Cursor.GetString(Cursor.GetColumnName(j)))
        Next
'        mast.Put(Cursor.GetString("userid"), Cursor.GetString("userid"))
        
        ToastMessageShow("Sending Data: "&CRLF&Cursor.GetString("message"),True)
        Sleep(200)
        
        LogColor(" MASTER rowid: "&Cursor.GetString("id")&" mastid: "&Cursor.GetString("mastid")&"   message: "&Cursor.GetString("message")&"   userid: "&Cursor.GetString("userid"), Colors.Magenta)
    Loop
    Cursor.Close

Else
    ToastMessageShow(" All Message Records Were Sent!",True)   
    
    sendtimer.Enabled = False

End If
    
If mast.Size = 0 Then  ' not really required - but what the heck....
    Return   
End If

For Each key As String In mast.Keys

   SendData(key, mast.Get(key), mesmap)
  
Next
        
 End Sub

OliverA · Jan 21, 2019

You're not going to like me (maybe):

B4X:

Sub SendtoServer3

sendtimer.Enabled = True

Dim mast As Map
mast.Initialize

Dim q As String = "SELECT * FROM messmast WHERE sent = 0"
'If DBUtils.HasRecords(Starter.SQL1,q,Null) Then  
    Dim Cursor As ResultSet
    Cursor = Starter.SQL1.ExecQuery(q)

    Do While Cursor.NextRow

        Dim mesmap As Map
        mesmap.Initialize

        mast.Put(Cursor.GetString("mastid"), Cursor.GetString("message"))
        For j = 0 To Cursor.ColumnCount -1
            mesmap.Put(Cursor.GetColumnName(j), Cursor.GetString(Cursor.GetColumnName(j)))
        Next
'        mast.Put(Cursor.GetString("userid"), Cursor.GetString("userid"))
       
        ToastMessageShow("Sending Data: "&CRLF&Cursor.GetString("message"),True)
        Sleep(200)
       
        LogColor(" MASTER rowid: "&Cursor.GetString("id")&" mastid: "&Cursor.GetString("mastid")&"   message: "&Cursor.GetString("message")&"   userid: "&Cursor.GetString("userid"), Colors.Magenta)
    Loop
    Cursor.Close

'Else
'NOTE: I'm moving the Else portion of to the If mast.Size = 0 statement
'    ToastMessageShow(" All Message Records Were Sent!",True)  
'   
'    sendtimer.Enabled = False
'
'End If
   
If mast.Size = 0 Then  ' not really required - but what the heck....
'Now it is required to take care of the commented Else condition above
    ToastMessageShow(" All Message Records Were Sent!",True)  
    sendtimer.Enabled = False
    Return  
End If

For Each key As String In mast.Keys

   SendData(key, mast.Get(key), mesmap)
 
Next
       
 End Sub

You still do not need to make an extra SQL call to see if anything is available. The code (in this case the variable mast) takes care of that for you. Again, there is nothing wrong with your way, I'm just saying you don't have to check the SQL server first to see if anything is there (clarification: in this code example).

Harris · Jan 21, 2019

That works too! Many brains provide alternate solutions to the same issue.
If I wasn't so narrow minded, I might have seen that too!

If an empty map wasn't involved - then HasRecords would be valid - in this example.

mc73 · Jan 21, 2019

You could even try this:

B4X:

Private nextRecord As Boolean=res.NextRow
If Not(nextRecord) Then
   Log("no records")
Else
   Do While nextRecord
      'process
      nextRecord=res.NextRow
   Loop
End If
res.Close

Harris · Jan 21, 2019

mc73 said:

You could even try this:

B4X:

Private nextRecord As Boolean=res.NextRow
If Not(nextRecord) Then
   Log("no records")
Else
   Do While nextRecord
      'process
      nextRecord=res.NextRow
   Loop
End If
res.Close

Seems to me that would miss the first record of the resultset - since that moved the pointer 1 row....

mc73 · Jan 21, 2019

Harris said:
Seems to me that would miss the first record of the resultset - since that moved the pointer 1 row....

No.

Harris · Jan 21, 2019

Another (valid) example???

B4X:

Sub FindMess(s As String)
Dim q As String = "SELECT * FROM inmast where message LIKE ('%"&s&"%') Order by mastid DESC"
    
If DBUtils.HasRecords( Starter.SQL1, q, Null) Then
    Starter.isbusy = False
    cqry = "SELECT * FROM inmast where message LIKE ('%"&s&"%') Order by mastid DESC"
    TabStrip1.ScrollTo(0, False)

Else
    ToastMessageShow("Nothing Found In Messages!",True)
    cqry = "SELECT * FROM inmast where mastid >= "&maxdate&" Order by mastid DESC"
    Starter.isbusy = False
End If
    
End Sub

Harris · Jan 21, 2019

mc73 said:
No.

I will try it to see what I get... Thanks

Harris · Jan 22, 2019

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

B4X:

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

mc73 said:
No.

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

Android Code Snippet DBUtils 2 - New Method - HasRecords

Myr0n

Active Member

Harris

Expert

Myr0n

Active Member

Harris

Expert

udg

Expert

Mahares

Expert

klaus

Expert

Mahares

Expert

Harris

Expert

OliverA

Expert

Harris

Expert

OliverA

Expert

Harris

Expert

mc73

Well-Known Member

Harris

Expert

mc73

Well-Known Member

Harris

Expert

Harris

Expert

Harris

Expert

Android Code Snippet DBUtils 2 - New Method - HasRecords

Active Member

Expert

Active Member

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Expert

Privacy & Transparency

Privacy & Transparency