sqllite issue with some handsets - memory issue?

[yonson]

Hi,

firstly many thanks for taking the time to read through this, hope you can help me out.

I've got an app deployed on the market which I developed on my HTC sensation, where it works fine. Now that its live we're getting some people saying its not working on their handsets, I managed to borrow one of the problem phones last week (a sory ericcson xperia mini pro) and have managed to see where the app is hanging.

I'll give a quick outline of the process:-

1. the app downloads an xml file from a webserver
2. it stores this file locally on the phone
3. this xml file is used to populate an sqllite database on the phone

The app works perfectly for steps 1 and 2. The problem arises on step 3, when its attempting to populate the biggest table within the database.

Here are the problem sql statements:-

1. DROP TABLE IF EXISTS [menuitem]
2. CREATE TABLE IF NOT EXISTS [menuitem] ([id] TEXT, [restaurant_id] TEXT, [title] TEXT, [description] TEXT, [menugrouping_id] TEXT, [price] TEXT, [flag_veg] TEXT, [flag_vegan] TEXT, [flag_gluten] TEXT, [flag_heat] TEXT, [flag_fish] TEXT, [menu1] TEXT, [menu2] TEXT, [menu3] TEXT, [menu4] TEXT, [menu5] TEXT, [menu6] TEXT)
3. InsertMaps (first query out of 1458): INSERT INTO [menuitem] ([id], [restaurant_id], [title], [description], [menugrouping_id], [price], [flag_veg], [flag_vegan], [flag_gluten], [flag_heat], [flag_fish], [menu1], [menu2], [menu3], [menu4], [menu5], [menu6]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

As I have mentioned these statements work perfectly on my phone (HTC sensation) and on the other HTC models I've tested, but not on some other models.

Now my assumption is that its the number of row inserts I'm doing (1458 in total) that is somehow causing the app to crash, probably because of some memory issue (although I checked on the test phone and it had plenty of free space). Perhaps its a RAM issue?

Anyway would greatly appreciate any advice or workaround solution.

Thanks!
Yonson

 

[Erel]

You should always check the logs when the app crashes.

If this step takes too long (more than 5 seconds) it may crash the app with "application not responding" message.
Are you using SQL.BeginTransaction in the third step? Your code will run much faster.

 

[yonson]

Hi Erel,

thanks for the info, yes I'm using Begin transation, and I'm using the InsertMaps function which I think is taken direct from the DBUtils Library. I've pasted the code below just to confirm.

The logs don't show any problems or errors - it just puts up the create sql statement but then the app just hangs.

Any ideas ?
Thanks
Yonson





'Inserts the data to the table.
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
Sub InsertMaps(SQL As SQL, TableName As String, ListOfMaps As List)
'If (TableName="menuitem") Then Return Null
Dim sb, columns, values As StringBuilder
'Small check for a common error where the same map is used in a loop
If ListOfMaps.Size > 1 AND ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
Log("Same Map found twice in list. Each item in the list should include a different map object.")
ToastMessageShow("Same Map found twice in list. Each item in the list should include a different map object.", True)
Return
End If
SQL.BeginTransaction
Try
For i1 = 0 To ListOfMaps.Size - 1
sb.Initialize
columns.Initialize
values.Initialize
Dim listOfValues As List
listOfValues.Initialize
sb.Append("INSERT INTO [" & TableName & "] (")
Dim m As Map
m = ListOfMaps.Get(i1)
For i2 = 0 To m.Size - 1
Dim col As String
Dim value As Object
col = m.GetKeyAt(i2)
value = m.GetValueAt(i2)
If i2 > 0 Then
columns.Append(", ")
values.Append(", ")
End If
columns.Append("[").Append(col).Append("]")
values.Append("?")
listOfValues.Add(value)
Next
sb.Append(columns.ToString).Append(") VALUES (").Append(values.ToString).Append(")")
'Log(sb.ToString)
DoEvents
If i1 = 0 Then Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
DoEvents
SQL.ExecNonQuery2(sb.ToString, listOfValues)
DoEvents
Next
SQL.TransactionSuccessful
DoEvents
Catch
ToastMessageShow(LastException.Message, True)
Log(LastException)
End Try
SQL.EndTransaction


End Sub

 

[Erel]

Why did you add the DoEvents calls? It is worth trying to remove them (or call them only every 100 iterations).

 

[yonson]

many thanks Erel - we couldn't get it to work in the end but actually decided it would be much simpler just to export the database from the webseerver in sqllite format - it appears to be only a third of the size of the xml file we were using and is of course much easier to load into the app so the phone is doing much less work.

If anyone wants the PHP library we've done or the code to grab the file into the app, feel free to contact me for a copy of the working code, the sqllite reloading is actually really simple of course:-

FTP.Initialize("FTP", FTP_address, FTP_port, FTP_username, FTP_password)FTP.DownloadFile(dbFile, False, File.DirInternal, dbFile)

' called once download complete
Sub FTP_DownloadCompleted (ServerPath As String, Success As Boolean)
FTP.CloseNow
' reload this new sqllite3 file to replace the old one
SQL.Initialize(File.DirInternal,dbFile,True)
End Sub