Android Question Strang sql error(limit rows)

[mohsen10024]

Hello.
I want to find my information from several rows, but when I limit the rows, I get an error
This code has error

Try
        ListView1.Clear
        cur=SQL1.ExecQuery("SELECT * FROM tbl1 WHERE Word LIKE'%"&EditText1.Text&"%'")
        For i = 0 To 60
            cur.Position=i
            ListView1.AddSingleLine2(cur.GetString("Word"),cur.GetInt("ID"))
        Next
        cur.Close
    Catch
        Log(LastException)
    End Try

Or for = 20 to 60

(CursorIndexOutOfBoundsException) android.database.CursorIndexOutOfBoundsException: Index 1 requested, with a size of 1

But for = 0 to cur.rowcount - 1 works and my rows are 1000

 

[Erel]

The best thing to do with this code is to delete it and rewrite it in a better way.

Two relevant tutorials:
[B4X] "Code Smells" - common mistakes and other tips
[B4X] Features that Erel recommends to avoid

 

[emexes]

when I limit the rows, I get an error

The second-best thing to do, if you're as curious as I am about why the heck you're getting that error reading 61 records from 1000, is to add one diagnostic line and see if the error still occurs:

Try
    ListView1.Clear
    cur=SQL1.ExecQuery("SELECT * FROM tbl1 WHERE Word LIKE'%"&EditText1.Text&"%'")
    For i = 0 To 60
        Log("n = " & cur.RowCount & ", i = " & i)
        cur.Position=i
        ListView1.AddSingleLine2(cur.GetString("Word"),cur.GetInt("ID"))
    Next
    cur.Close
Catch
    Log(LastException)
End Try

 

[klaus]

I agree with emexes, the problem is for sure the number of rows returned by your query.
With this query:
cur=SQL1.ExecQuery("SELECT * FROM tbl1 WHERE Word LIKE'%"&EditText1.Text&"%'")
How many rows do you expect getting in return.

 

[mohsen10024]

Thank you all.
Each row that has my answer.
From * to * row(s)

 

[Mahares]

CursorIndexOutOfBoundsException) android.database.CursorIndexOutOfBoundsException: Index 1 requested, with a size of 1

@emexes and @klaus exactly pinpointed the problem to you. I would like to expand on it:
When you issue this query:

cur=SQL1.ExecQuery("SELECT * FROM tbl1 WHERE Word LIKE'%"&EditText1.Text&"%'")

The cur.rowcount is immediately calculated based on that LIKE criteria. If for instance the rowcount came up with 10 records that satisfy that criteria and you are telling it to do: For i = 0 To 60, you already exceed the number of records returned by the cursor which is , say 10. So you are telling it to go over 61 records, but only 10 records exist in the cursor. Therefore, you are getting outofbounds error and the code crashes.
Now let me tell you what Erel wants you to do:
1. Do not use cursor; use Resultset instead. Actually, if you used Resultset, you would not have had that outofbounds error.
2. Use parameterized query, by using the SQL1.ExecQuery2 to prevent SQL injection. If you do not know, we can help.
3. Do not use Listview any more. Use xCustomlistview instead. If you do not know, we can help.
You will see more details when you inspect the docs that Erel suggested.

 

[mohsen10024]

Thank you all. I made it with this code and it works fine.

Try
        ListView1.Clear
        For i = 20 To 60
            cur = SQL1.ExecQuery2("SELECT * FROM tbl1 WHERE ID = ?", Array As String(i))
            cur.Position=0
            If cur.GetString("Word").Contains(EditText1.Text) Then
                ListView1.AddSingleLine2(cur.GetString("Word"),cur.GetInt("ID"))
            End If
        Next
        cur.Close
    Catch
        Log(LastException)
    End Try

 

[Mahares]

I made it with this code and it works fine

Mohsen:
I am glad you got it solved, but you took a very bad approach to get there. You should never have the SQL statement query repeated inside the loop like the way you did it. What if you had a million records to go through, you would have to execute the query a million times. That is a no no.
If you insist on using a cursor and listview, here is the way your code should look like:

Try
        ListView1.Clear
        cur = SQL.ExecQuery2("SELECT * FROM tbl1 WHERE ID BETWEEN  ? AND ?", Array As String(20, 60))
        For i=0 To cur.RowCount-1
            cur.Position=i
            If cur.GetString("Word").tolowercase.Contains(EditText1.Text.tolowercase) Then
                ListView1.AddSingleLine2(cur.GetString("Word"),cur.GetInt("ID"))
            End If
        Next
        cur.Close
    Catch
        Log(LastException)
    End Try

But even a better way to do it is to use resultset like this:

Dim rs As ResultSet
    Try
        ListView1.Clear
        rs = SQL.ExecQuery2("SELECT * FROM tbl1 WHERE ID BETWEEN  ? AND ?", Array As String(20, 60))
        Do While rs.NextRow
            If rs.GetString("Word").tolowercase.Contains(EditText1.Text.tolowercase) Then
                ListView1.AddSingleLine2(rs.GetString("Word"),rs.GetInt("ID"))
            End If
        Loop
        rs.Close
    Catch
        Log(LastException)
    End Try