Android Question Import csv to SQLite problems with Android 5.0

Discussion in 'Android Questions' started by Dman, Feb 2, 2015.

  1. Dman

    Dman Active Member Licensed User

    I am working on an app to import a csv file into an sqlite database and it works on Android 4.x but gives me a "android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed. # Open Cursors=749 (# cursors opened by this proc=749)" error on Android 5.0 and exits the application.

    I have attached a test app and I think I am close but I am having a hard time trying to figure out what is wrong with the code. If you click on the View button you can see that it imports some rows but not all of them. On my device it imports 1994 rows before kicking me out according to the count button. The csv file has 2339 rows.

    Like I said I think I am close but for some reason my brain isn't functioning well as what my issue is. Any help would be appreciated.

    Here is the import sub.

    Code:
    Sub importvehicles

      
    Dim j As Int
      
    Dim FileDialog1 As FileDialog
      
    ProgressDialogShow("Importing vehicles, please wait")
       
    DoEvents
      FileDialog1.FilePath = 
    File.DirDefaultExternal & "/import"
       
      SQL1.BeginTransaction
      
    'read csv File
      Dim Reader As TextReader
      Reader.Initialize(
    File.OpenInput(FileDialog1.FilePath, "vehicle.csv"))
      
    Try
      
    Dim line As String

      
    Do While line <> Null
      j = j + 
    1
      line = Reader.ReadLine
             

      
    Dim ColumnContent() As String
      ColumnContent = 
    Regex.Split(";", line)

                         
    'stuff I added
                 Dim orgrow As String
                 orgrow = ColumnContent(
    0)
                 

                 
    Try
                   dbcursor = SQL1.ExecQuery(
    "SELECT * FROM vehicle WHERE orgrow ='" & orgrow & "' AND handheldid = 'Paul'"'Grab the data from the ftp file
                 Catch
                   
    Log(LastException.Message)
                 
    End Try

                 
    If dbcursor.RowCount > 0 Then
                   SQL1.ExecNonQuery2(
    "UPDATE vehicle SET orgrow = ?, vehid = ?, invoicenumber = ?, date = ?, stocknumber = ?, year = ?, make = ?, model = ?, color = ?, vinnumber = ?, amount = ?, tax = ?, extra1 = ?, extra2 = ?, extra3 = ?, extra4 = ? WHERE orgrow = '" & orgrow & "' AND handheldid = 'Paul' AND extra1 <> '3'"Array As String(ColumnContent(0), ColumnContent(1), ColumnContent(2), ColumnContent(3), ColumnContent(4), ColumnContent(5), ColumnContent(6), ColumnContent(7), ColumnContent(8), ColumnContent(9), ColumnContent(10), ColumnContent(11), ColumnContent(12), ColumnContent(13), ColumnContent(14), ColumnContent(15)))
                 
    Else

             SQL1.ExecNonQuery2(
    "INSERT INTO vehicle VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"Array As Object (ColumnContent(0), ColumnContent(1), ColumnContent(2), ColumnContent(3), ColumnContent(4), ColumnContent(5), ColumnContent(6), ColumnContent(7), ColumnContent(8), ColumnContent(9), ColumnContent(10), ColumnContent(11), ColumnContent(12), ColumnContent(13), ColumnContent(14), ColumnContent(15), "Paul"))
                 
    End If
               
    DoEvents
      
    Loop
      
    Catch
      
    Log(LastException.Message)
      
    End Try
      Reader.Close
      SQL1.TransactionSuccessful
      SQL1.EndTransaction
         dbcursor.close
      
    ProgressDialogHide
      
    Msgbox("Vehicles imported!""Message")

       
    End Sub 
    [/code
    }
     

    Attached Files:

  2. Erel

    Erel Administrator Staff Member Licensed User

    Why don't you use StringUtils.LoadCSV to read the table?

    I recommend you to remove the DoEvents call from the loop and close dbcursor at the end of each loop.
     
    Dman likes this.
  3. Dman

    Dman Active Member Licensed User

    OK I will work on it some more when I get home. Thanks!
     
  4. keirS

    keirS Well-Known Member Licensed User

    Try putting dbcursor.close inside your while loop; just before the loop statement.
     
    Dman likes this.
  5. Dman

    Dman Active Member Licensed User

    Thanks guys, that appears to work. If I take the DoEvents out of the loop, the progress dialog will not work. Any suggestions?
     
  6. NJDude

    NJDude Expert Licensed User

    You could add a DoEvents, but to run it every X numbers of records, without DoEvents the progress won't update.
     
    Dman likes this.
  7. Dman

    Dman Active Member Licensed User

    Is there any recommended way to put a "Please wait" message besides doing it that way or the way I am doing it? I would like to do it the best way but I need to have something up there.
     
  8. NJDude

    NJDude Expert Licensed User

    That's the way to do it.
     
    Dman likes this.
  9. Dman

    Dman Active Member Licensed User

    Thank you sir!
     
  10. Dman

    Dman Active Member Licensed User

    OK I am a dummy. How would you run something every x number of records in a Do While Loop? I can think of ways that are extremely complicated but most of the time when I do that, there is usually a simple way. Knowing the row count in a For Loop would be simple to do I think but I am stumped on this one.
     
    Last edited: Feb 2, 2015
  11. NJDude

    NJDude Expert Licensed User

    One idea, use the RowCount, and every time the result is a multiple of 10 or 20 or whatever number of records you trigger the DoEvents.
     
    Last edited: Feb 2, 2015
    Dman likes this.
  12. Dman

    Dman Active Member Licensed User

    Thanks. I got it figured out. I now have it firing every 10 records and it cut the import time on this sample from over 40 seconds to 10.

    Awesome, thanks!
     
    luqmanhaidar likes this.
  13. NJDude

    NJDude Expert Licensed User

    Bravo!
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice