Android Question Cursor stops functioning...

[DrownedBat]

Been banging my head on the desk for days over this one...

I have a program that tracks my card collections for me. This pulls information from a SQL database and displays which card I'm looking for and how many of them I own.

This code actually works:

     Dim Cursor1 As Cursor
    Dim i As Int
    Dim j As Int
    Dim Multiply As Int

    Cursor1 = SQL1.ExecQuery("SELECT ID, Name, Group1, Group2, Height, Width, Inclusion, Restricted, Box, Front, Back FROM CardGame1")
    Cursor1.Position=0

    'Scan all cards for pre-set "Filter" string
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        If Cursor1.GetString2(8)=Filter Then
            Multiply=Cursor1.GetInt2(6)
            For j=0 To Multiply-1
                Log(Cursor1.GetString2(1))
            Next
        End If
    Next
    Cursor1.close

So, what this does is basically checks the database for "CardGame1", searches through Col 8 for the "filter" keyword that I'm looking for (in which "Box" the card was included), and (for now) generates a list in the log of each card in the collection that matches the keyword filter. So, if I have 3 cards that match the keyword I'm looking for and 3 of each card, the log will look like this:

Card A
Card A
Card A
Card B
Card B
Card B
Card C
Card C
Card C

Here's the problem:

I have an IDENTICAL code set that checks all other card games in my collection for the same filter. The database has the same columns as the previous card game, only the table name is different (note "CardGame2" below):

     Dim Cursor1 As Cursor
    Dim i As Int
    Dim j As Int
    Dim Multiply As Int

    Cursor1 = SQL1.ExecQuery("SELECT ID, Name, Group1, Group2, Height, Width, Inclusion, Restricted, Box, Front, Back FROM CardGame2")
    Cursor1.Position=0

    'Scan all cards for pre-set "Filter" string
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        If Cursor1.GetString2(8)=Filter Then
            Multiply=Cursor1.GetInt2(6)
            For j=0 To Multiply-1
                Log(Cursor1.GetString2(1))
            Next
        End If
    Next
    Cursor1.close

Even though this code is identical and runs through the same procedure as the previous one, I get an error message that reads:

Card D
Card D
Card D
java.lang.IllegalStateException: Couldn't read row 0, col 8 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.

Even more frustrating, this code runs perfectly until it is supposed to reach row 1. Cards D,E, and F are in the filter range, with 3 copies of each. It detects the first set of 3 just fine, but then tells me it can't read row 0 (which, by my logic, should be on row 1). Even if it wasn't advancing, it would simply read "Card D" 9 times would it not?

This has boggled me for 3 days. Any help from the experts out there would be greatly appreciated! Happy coding all!

 

[gerredtor]

Can you send me the DB file and your project ? or the code snipped + code from SQL1 and the DB file

Use the right syntax, in sql is "SELECT ID, Name" incorrect, correct is: "SELECT `ID`, `Name`" usw...

 

[DrownedBat]

Well the inclusion of ' ' in the syntax removes the error, but the log is blank once it completes. Does this mean it is not running the code or just that portion?

 

[gerredtor]

when the log is blank, then use a simple debug:

    For i=0 To Cursor1.RowCount-1

Log("1") <-----

        Cursor1.Position=i
        If Cursor1.GetString2(8)=Filter Then

Log("2") <-----

            Multiply=Cursor1.GetInt2(6)
            For j=0 To Multiply-1

Log("3") <-----

                Log(Cursor1.GetString2(1))
            Next
        End If
    Next

 

[DrownedBat]

Ah, I see. Tracking each step as it were...
*inserts code*
Well, it seems to stop at step 1. With ' ' included in the syntax, the log now reads:

1
1
1

Which tells me it is running the correct number of times (3 card TYPES in the db and therefore a good RowCount), but it cannot extract the information needed from the database. I just checked the filter to make sure that it matches the "Box" and vice-versa and everything is good there. So the cursor must be initialized, otherwise the RowCount would not return the appropriate number of times.

Just for a giggle, I changed the GetString2(8) to GetString("Box") and I got this:

1
java.lang.IllegalArgumentException: column 'Box' does not exist

You sure the syntax requires ' ' around the column names? Without them the code handles just fine for CardGame1, and even to some extent on CardGame2. This is what confuses me. Why does identical code pulling information from identical tables generate different results? Do you think it is possible the database is corrupt?

For example, without ' ' around the syntax the log reads:

1
2
3
Card D
3
Card D
3
Card D
1
java.lang.IllegalStateException: Couldn't read row 0, col 8 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.

*still learning*

 

[gerredtor]

No not ' ' you use this ` ` , this ' ' is to use on values, but ` ` this for index. can you send me a screenshot from your db ? or direct your db

for you:

SQL1.ExecQuery("SELECT `ID`, `Name`, `Group1`, `Group2`, `Height`, `Width`, `Inclusion`, `Restricted`, `Box`, `Front`, `Back` FROM `CardGame2`");

but why you dont use

SQL1.ExecQuery("SELECT * FROM `CardGame2`");

?

 

[DrownedBat]

Ah. ' vs `. Sorted that out, thanks!

With ` ` around syntax I get:

1
2
3
Card D
3
Card D
3
Card D
1
java.lang.IllegalStateException: Couldn't read row 0, col 8 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.

Seems to run the code just fine until it reaches row 1.
Having an error uploading the DB but here's the screenshot.

P.S. I do use "SELECT * FROM `CardGame2`", but only after it functions properly, so I can verify the column positions with the GetString2(X) and GetInt2(X) commands. This way I don't have to switch between my DB and the code window. Once everything works, I change it to *. Might be a tad inefficient but it helps me keep track of everything.

 

[DrownedBat]

Thanks to gerredtor for getting me back to square 1 on this. It seems my image sizes were too large in the BLOB fields. Didn't realize that android's cursor object only holds a maximum of 1MB. Problem solved!

http://stackoverflow.com/questions/12716859/retrieve-large-blob-from-android-sqlite-database