Android Question DBUtils SQLite

Declan

Well-Known Member
Licensed User
Longtime User
I am trying to read data from a sqlite table and place each record into a variable:
My SQL statement is:
B4X:
Cursor1 = SQL0.ExecQuery("SELECT DISTINCT StoreGroup FROM tableSP2")

My Code is:
B4X:
Sub GetStoreGroups
    Dim StoreGroup1 As String
    Dim StoreGroup2 As String
    Dim StoreGroup3 As String
    Dim StoreGroup4 As String
    Dim StoreGroup5 As String
    Dim StoreGroupCount As Int
   
    Dim Cursor1 As Cursor
    Cursor1 = SQL0.ExecQuery("SELECT DISTINCT StoreGroup FROM tableSP2")
    StoreGroupCount = Cursor1.RowCount
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i
        StoreGroup1 = (Cursor1.GetString2(0))
        StoreGroup2 = (Cursor1.GetString2(1))
        StoreGroup3 = (Cursor1.GetString2(2))
        StoreGroup4 = (Cursor1.GetString2(3))
        StoreGroup5 = (Cursor1.GetString2(4))
    Next
    Cursor1.Close
End Sub

I get the following error:
B4X:
java.lang.IllegalStateException: Couldn't read row 0, col 1 from CursorWindow.  Make sure the Cursor is initialized correctly before accessing data from it.
 

Declan

Well-Known Member
Licensed User
Longtime User
OK, this works - but seems cumbersome
B4X:
Sub GetStoreGroups
    Dim StoreGroup1 As String
    Dim StoreGroup2 As String
    Dim StoreGroup3 As String
    Dim StoreGroup4 As String
    Dim StoreGroup5 As String
    Dim StoreGroupCount As Int
   
    Dim Cursor1 As Cursor
    Cursor1 = SQL0.ExecQuery("SELECT DISTINCT StoreGroup FROM tableSP2")
    StoreGroupCount = Cursor1.RowCount
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i
        If Cursor1.Position = 0 Then
            StoreGroup1 = (Cursor1.GetString2(0))
        End If
        If Cursor1.Position = 1 Then
            StoreGroup2 = (Cursor1.GetString2(0))
        End If
        If Cursor1.Position = 2 Then
            StoreGroup3 = (Cursor1.GetString2(0))
        End If
        If Cursor1.Position = 3 Then
            StoreGroup4 = (Cursor1.GetString2(0))
        End If
        If Cursor1.Position = 4 Then
            StoreGroup5 = (Cursor1.GetString2(0))
        End If

    Next
    Cursor1.Close
   
End Sub
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
Where are you supposed to use the returning values? One way would be from inside the loop. Another to store them in a list:
B4X:
Sub GetStoreGroups
Dim StoreGroupList As List
StoreGroupList.initialize
Dim StoreGroupCount As Int
Dim Cursor1 As Cursor
Cursor1 = SQL0.ExecQuery("SELECT DISTINCT StoreGroup FROM tableSP2")
StoreGroupCount = Cursor1.RowCount
if storegroupcount>0 then 
For i = 0 To Cursor1.RowCount - 1
 Cursor1.Position = i
storeGroupList.add(cursor1.getstring2(0))
Next
end if 
Cursor1.Close
' now storegrouplist contains all distinct storeGroup items. If none, its size is zero.
for j=0 to storeGroupList.size-1
log("storeGroup " & j & "=" & storeGroupList.get(j))
next
 End Sub
 
Upvote 0

Declan

Well-Known Member
Licensed User
Longtime User
@mc73, Thanks
Once I have the StoreGroup(x), I call another sub that manipulates the data and displays in a list.
The number of StoreGroup can be 1 to 50.
I have written the sub that handles the data and displays in the list - but it is very long and well, somewhat badly written.
But it works for now - so will clean-up once I have all running
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…