Android Question Import csv to SQLite problems with Android 5.0

[Dman]

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.

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}

 

[Erel]

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]

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

 

[keirS]

Try putting dbcursor.close inside your while loop; just before the loop statement.

 

[Dman]

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

 

[NJDude]

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

 

[Dman]

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.

 

[NJDude]

That's the way to do it.

 

[Dman]

Thank you sir!

 

[Dman]

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.

 

[NJDude]

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.

 

[Dman]

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!

 

[NJDude]

Bravo!