Android Question [Solved] Do I need set the position of ResultSet before Do While?

[asales]

In the code below, I get the result and set the positon = -1 (before the initial record) to list the records.

Do I need set the position?

Dim rs As ResultSet
rs = SQL1.ExecQuery(txt_sql)

If rs.RowCount > 0 Then
    rs.Position = -1  'DO I NEED THIS ??
    Do While rs.NextRow
        Log(rs.GetString("id"))
    Loop
End If

rs.Close

 

[DonManfred]

Do I need set the position?

No.

rs = SQL1.ExecQuery(txt_sql)

If rs.RowCount > 0 Then
    Do While rs.NextRow
        Log(rs.GetString("id"))
    Loop
End If

 

[klaus]

Be aware that the RowCount property does exist only in B4A !
If you want to make cross-platform projects you should avoid it.
This code is enough:

Do While rs.NextRow
    Log(rs.GetString("id"))
Loop

If RowCount = 0, Do While will not enter the Loop.

 

[Mahares]

If you need or have to check for records, this will work too:

Sub HasRecords As Boolean
    Return IIf(SQL.ExecQuerySingleResult($"SELECT count(*) FROM mytable LIMIT 1"$) = 0 , False , True )
End Sub

Log(HasRecords)  'returns true or false

 

[RB Smissaert]

No.

rs = SQL1.ExecQuery(txt_sql)

If rs.RowCount > 0 Then
    Do While rs.NextRow
        Log(rs.GetString("id"))
    Loop
End If

> No

This is not quite right.
I had another look at this as it seemed that doing rs.Position = -1 could take quite some time (1 second for a ResultSet of only 40000 rows).
This is to do with a Sub that determines the data type of a ResultSet column.

If you run this test code you will see that RS1.Position = -1 is needed in this case.

Sub TestRS_Position
    
    Dim i As Int
    Dim c As Int
    Dim strSQL As String
    Dim RS1 As ResultSet
    Dim strDataType As String 'ignore

    strSQL = "create table if not exists TestRS_Position(col1 text, col2 text, col3 text, col4 text, col5 text)"
    SQL1.ExecNonQuery(strSQL)
    
    strSQL = "insert into TestRS_Position values('A', 'B', 'C', 'D', 'E')"
    For i = 0 To 4
        SQL1.ExecNonQuery(strSQL)
    Next
    
    strSQL = "select * from TestRS_Position"
    RS1 = SQL1.ExecQuery(strSQL)
    
    For c = 0 To 4
        strDataType = GetResultSetColumnDataType2(RS1, c)
    Next
    
End Sub

'this one presumes that numbers can only have one dot max and no comma's
'-----------------------------------------------------------------------
Sub GetResultSetColumnDataType2(RS1 As ResultSet, _
                               iColumn As Int) As String

    Dim i As Int
    Dim strNew As String
    Dim strOld As String
    Dim arrBytes() As Byte
    Dim bHasDots As Boolean
    Dim bHasNumber As Boolean
    Dim strDataType As String
    
    RS1.Position = -1 'this is needed to avoid skipping rows!
    
    Do While RS1.NextRow
        
        strNew = RS1.GetString2(iColumn)
        
        If strNew <> Null Then
            If strNew.Length > 0 Then
                'this can make this about twice as fast if there is a sorted
                'column and if the rows to be tested are not taken randomly
                '-----------------------------------------------------------
                If strNew <> strOld Then

                    strOld = strNew
                    arrBytes = bC.StringToBytes(strNew, "ASCII")
                    bHasDots = False

                    For i = 0 To arrBytes.Length - 1
                        If arrBytes(i) > 57 Then
                            Log(strNew & "  return T as byte > 57")
                            Return "T"
                        Else    'If arrBytes(i) > 57
                            If arrBytes(i) < 48 Then
                                Select Case arrBytes(i)
                                    Case 45    '-
                                        If i > 0 Then
                                            Return "T"    'as number can't have minus at position past zero
                                        Else
                                            strDataType = "T"    'provisional value, can change to an int or double
                                        End If
                                    Case 46    '.
                                        'presume a number can't have multiple dots
                                        If bHasDots Then Return "T"
                                        bHasDots = True
'                                    Case 44  ',
'                                        'presume numbers can't have comma's
                                        'Return "T"
                                    Case Else
                                        'Log("< 48 and not - or .: " & strNew)
                                        Return "T"
                                End Select
                            Else    'If arrBytes(i) < 48
                                bHasNumber = True
                            End If    'If arrBytes(i) < 48
                        End If    'If arrBytes(i) > 57
                    Next 'For i = 0 To arrBytes.Length - 1
                End If    'If str <> strOld
            End If    'If str.Length > 0
        End If 'If strNew <> Null

    Loop
    
    If bHasNumber Then
        If bHasDots = False Then
            Return "I"
        Else    'If bHasDots = False
            Return "R"
        End If
    Else   'If bHasNumber
        If strDataType = "T" Then
            Return "T"
        Else
            Return "N"
        End If
    End If    'If bHasNumber

End Sub

RBS

 

[RB Smissaert]

You sure about that?

I think so, yes, but happy to be proven wrong.
Did you run the testing code?

RBS

 

[emexes]

It looks to me that @DonManfred's "no" was to the original - and at that point, only - question:

In the code below, I get the result and set the positon = -1 (before the initial record) to list the records.

Do I need set the position?

Dim rs As ResultSet
rs = SQL1.ExecQuery(txt_sql)

If rs.RowCount > 0 Then
    rs.Position = -1  'DO I NEED THIS ??
    Do While rs.NextRow
        Log(rs.GetString("id"))
    Loop
End If

and thus not not quite right.

 

[RB Smissaert]

It looks to me that @DonManfred's "no" was to the original - and at that point, only - question:



and thus not not quite right.

Yes, if the cursor (or whatever you call that) hasn't been set in code, either by RS1.Position = x or by calling Do While RS1.NextRow, then yes there is no need
to do RS1.Position = -1.

RBS