Android Question SQLite Cursor window allocation of 2048 kb failed

[Espinosa4]

This code cause that problem (Cursor windows allocation...)

Sub ActualizarTJornada
    Dim SO,SR,SP As SQL
    Dim CO,CR,CP As Cursor
    If SO.IsInitialized = False Then
        SO.Initialize(File.DirDefaultExternal, "l4pro.sql", False)
    End If
    If SR.IsInitialized = False Then
        SR.Initialize(File.DirDefaultExternal, "l4pro.sql", False)
    End If
    If SP.IsInitialized = False Then
        SP.Initialize(File.DirDefaultExternal, "l4pro.sql", False)
    End If
  
    CP = SP.ExecQuery("SELECT * FROM PrediccionServicios")
    If CP.RowCount > 0 Then
        For i = 0 To CP.RowCount-1
            CP.Position = i
            CO = SO.ExecQuery("SELECT * FROM ServOrdinarios WHERE NumServicio = '"&CP.GetString("Servicio")&"'")
            If CO.RowCount > 0 Then
                '/////////////////
            Else
                CR = SR.ExecQuery("SELECT * FROM ServReducidos WHERE NumServicio = '"&CP.GetString("Servicio")&"'")
                If CR.RowCount > 0 Then
                    '//////
                End If
            End If
        Next
    End If
    CO.Close
    CR.Close
    CP.Close
End Sub

Can anyone help me, please?
The table PrediccionServios has about 1000 records.

Best regards
Espinosa

 

[eps]

Has any of this code worked at all?

Which statement is failing? The first SQL Statement? Are the 3 tables in the same DataBase (DB)? You seem to have defined a SQL variable for all 3, but I would expect to see only 1, for the DB. The Cursors will open for each table within the DB.

So something like this...

Sub ActualizarTJornada
Dim SO As SQL
Dim CO,CR,CP As Cursor
If SO.IsInitialized = False Then
 SO.Initialize(File.DirDefaultExternal, "l4pro.sql", False)
EndIf
 
 CP = SO.ExecQuery("SELECT * FROM PrediccionServicios")
If CP.RowCount > 0 Then
For i = 0To CP.RowCount-1
 CP.Position = i
 CO = SO.ExecQuery("SELECT * FROM ServOrdinarios WHERE NumServicio = '"&CP.GetString("Servicio")&"'")
If CO.RowCount > 0Then
    '/////////////////
Else
 CR = SO.ExecQuery("SELECT * FROM ServReducidos WHERE NumServicio = '"&CP.GetString("Servicio")&"'")
If CR.RowCount > 0Then
    '//////
EndIf
EndIf
Next
EndIf

 CO.Close
 CR.Close
 CP.CloseEnd Sub

Sorry, the web browser has messed up a lot of the formatting, which I've tried to reinstate.

 

[Espinosa4]

Many thanks eps!
You know, I am a baby with nappies as programmer.

I thought I need to define or declare three sqls and three Cursors but now I know the correct procedure.

Thank youuuu!
Best regards
Espinosa

 

[eps]

Many thanks eps!
You know, I am a baby with nappies as programmer.

I thought I need to define or declare three sqls and three Cursors but now I know the correct procedure.

Thank youuuu!
Best regards
Espinosa

No problem We all have to start somewhere. Do the changes I suggested make a difference?

I think there are a few other improvements that could be made to the code, but I'm slightly unsure of what you are attempting to achieve.

 

[Espinosa4]

Yesss it works fine! Thanks!

I am trying to read a table from the first record til the last which has referential records in a couple of tables. Or the record stored is from the table B or is from the table C.

For example
Table A has
Name, country

Table B
European countries

Table C
Non European countries

Is only an example.


By the way... My application has a lot of code like I wrote. I need to changes the code in many activities because sure in a near future, it will crash.

Cheers

 

[eps]

It was just that I was wondering why you were retrieving all of the columns (*) from all 3 tables, you might not need to and then you can apply an Index and speed things up as well. You might also be to just retrieve a count of the records, which doesn't involve opening a Cursor, hence my question, but I think you are linking up all of the tables anyway, so it's not just a case of if records exist.

 

[Espinosa4]

With your last reply I learned something new.

If I need to read only a column or two I have to learn to use better the sql sentence.

Thanks!

 

[DonManfred]

If I need to read only a column or two

You should, always, only select the fields you NEED (fielda, fieldb, fieldc) instead of using * (all fields)

* can be dangerous if you dont know whats inside all the fields... There could be blobs with bigimages for ex.

 

[Espinosa4]

Thanks DonManfred for your advice!
Best regards

 

[Espinosa4]

No problem We all have to start somewhere. Do the changes I suggested make a difference?

I think there are a few other improvements that could be made to the code, but I'm slightly unsure of what you are attempting to achieve.

Hi, eps!
I think the problem is still present. Now I am using a table with 759 records. Here the error:
android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed. # Open Cursors=738 (# cursors opened by this proc=738)

    Dim sSQL As SQL
    Dim CO,CR,CP As Cursor
    Dim P As Phone
    Dim P2 As PhoneWakeState
    If sSQL.IsInitialized = False Then
        sSQL.Initialize(File.DirDefaultExternal, "l4pro.sql", False)
    End If

    CP = sSQL.ExecQuery("SELECT * FROM PrediccionServicios")
    If Activity.Width > Activity.Height Then
        P.SetScreenOrientation(0)
    Else
        P.SetScreenOrientation(1)
    End If
    P2.KeepAlive(True)
    ProgressDialogShow("Un momento por fa")
    If CP.RowCount > 0 Then
        For i = 0 To CP.RowCount-1
            DoEvents
            CP.Position = i
            CO = sSQL.ExecQuery("SELECT * FROM ServOrdinarios WHERE NumServicio = '"&CP.GetString("Servicio")&"'")
            If CO.RowCount > 0 Then
                '/////////////////
                'sSQL.ExecNonQuery("UPDATE PrediccionServicios set TJornada =  Null, otracolumna = '3'")    
            Else
                CR = sSQL.ExecQuery("SELECT * FROM ServReducidos WHERE NumServicio = '"&CP.GetString("Servicio")&"'")
                If CR.RowCount > 0 Then
                   '//////
                End If
            End If
        Next
    End If
    CO.Close
    CR.Close
    CP.Close
    sSQL.Close
    P.SetScreenOrientation(-1)
    P2.ReleaseKeepAlive
    ProgressDialogHide

I have to insert CO.close and CR.Close in my code. Using this options all works fine.

        For i = 0 To CP.RowCount-1
            DoEvents
            CP.Position = i
            CO = sSQL.ExecQuery("SELECT * FROM ServOrdinarios WHERE NumServicio = '"&CP.GetString("Servicio")&"'")
            If CO.RowCount > 0 Then
                '/////////////////
                'sSQL.ExecNonQuery("UPDATE PrediccionServicios set TJornada =  Null, otracolumna = '3'")       
            Else
                CR = sSQL.ExecQuery("SELECT * FROM ServReducidos WHERE NumServicio = '"&CP.GetString("Servicio")&"'")
                If CR.RowCount > 0 Then
                    '//////
                End If
                CR.Close
            End If
            CO.Close
        Next

 

[eps]

From the code you've posted though, you don't need a cursor at all. You do a select all, then check the Rowcount, you could just do something like this :

    Dim NumberOfRecords As Int

     NumberOfRecords = SQL1.ExecQuerySingleResult("SELECT count(_id) FROM <table>")

?

 

[Espinosa4]

From the code you've posted though, you don't need a cursor at all. You do a select all, then check the Rowcount, you could just do something like this :

    Dim NumberOfRecords As Int

     NumberOfRecords = SQL1.ExecQuerySingleResult("SELECT count(_id) FROM <table>")

?

Okay it is perfect but if the NumberOfRecords = 1000, I need to do 1000 ExecQueries in other table, one for each record and here the problem. I need a cursor to get info from the second table if the NjmberOfRecords is > 0 too. Am I right? Cursor.postion = i, Cursor.getstring("Name"), etc... and if I don't close the cursor for each query the program return the message.

Cheers

 

[eps]

I don't know, it's a bit hard to see what you are trying to achieve as not all the code is there, I suspect.

But yes, you do need to Close the Cursors - just open SQL close every time.

 

[Espinosa4]

Ok,
But I don't understand why that code return the error with the cursors.

Cheers

 

[mc73]

I strongly suspect that you're wasting resources (in cpu/ram terms) executing series of multiple queries and insertions/updates, where it's not needed probably. My suggestion is to provide the exact thing you want to achieve, and many members here, I'm sure of this, can provide sufficient help, in order to optimize your code.

 

[Espinosa4]

OK, I'll put the code here.
Thanks mc73!!