Android Question Error using SQLite on Android 7 OS

[Mike Parris]

I am getting an error when accessing data from an SQLite database.

The error is intermittent and occurs at different point in my code. The code runs correctly most of the time but occasionally fails with a disk IO error --- An error has occurred in sub:sqldata_setracepromotionnumber(Java line: 2359) android.database.sqlite.SQLiteDiskIOException:disk I/O error(code 1802) Continue no/yes

The problem appears to be OS dependant. On a Motorola G5 running Android 7.1 get the problem. On a Nexus5 phone running Android 6 it runs perfectly. It also runs correctly on an Amazon Fire. It happens running both version 6.8 and 7.3 of B4A

The problem occurs when a cursor is used, after being created by an ExecQuery command. Other database commands do not cause the issue. It occurs at different points in the code.
Typical code where the error occurs is -
<CODE>
Dim q As String
Dim EntrantId As Int
EntrantId = 44
Dim C As Cursor
q = "SELECT * FROM RaceResults INNER JOIN Entrants ON RaceResults.EntrantId = Entrants.Id WHERE Entrants.Id = " & EntrantId
C = sqlDatabase.ExecQuery(q)
If C.RowCount <> 0 Then
C.Close
Return True
Else
C.Close
Return False
End If
</CODE>

Is this an issue caused by using SQLite on Android 7? Any solutions?

Mike Parris

 

[Alexander Stolte]

why do you first store in a string? Save it directly in the cursor.

 

[Mike Parris]

It is easier to debug. You can break after the query is formed before the query is run. Is this going to cause my problem?

 

[Emme Developer]

Can you post the errore message? I use SQLite on android 7 without any problem

 

[Mike Parris]

Error message is a dialog box with the following -
An error has occurred in sub:sqldata_setracepromotionnumber(Java line: 2359) android.database.sqlite.SQLiteDiskIOException:disk I/O error(code 1802) Continue no/yes

 

[Alexander Stolte]

It could be due to the fact that you close the cursor but not the database.

Add under c.Close this
sqlDatabase.Close

 

[Mike Parris]

This means I would close the database at the end of each database access. Database access would then require the database to be initialised many times. I have not seen this in any of the B4A tutorials and examples. Can you point me to an example that works in this way?

 

[fixit30]

What is your target SDK version in your manifest file?

It could be related to runtime pemissions.

 

[Mike Parris]

Here's my manifest file -

AddManifestText(
<uses-sdk android:minSdkVersion="5" android:targetSdkVersion="14"/>
<supports-screens android:largeScreens="true"
android:normalScreens="true"
android:smallScreens="true"
android:anyDensity="true"/>)
SetApplicationAttribute(android:icon, "@drawable/icon")
SetApplicationAttribute(android:label, "$LABEL$")
SetApplicationAttribute(android:theme, "@android:style/Theme.Holo")

 

[eps]

Apologies if you've abbreviated the code at all, but shouldn't

SELECT * FROM RaceResults INNER JOIN Entrants ON RaceResults.EntrantId = Entrants.Id WHERE Entrants.Id = " & EntrantId

just be

"SELECT * FROM RaceResults  WHERE EntrantId = " & EntrantId

There doesn't seem to be a need for an INNER JOIN there - although this might not solve anything for you or there might be more information being retrieved from the other table as well, but it doesn't look like there is.

If using a Cursor seems to generate a problem have you attempted to not use a Cursor and see what the results are?

It may well be worth trying to work out the different versions of SQLite in play - there could have been a change which is now affecting your code. I seem to remember having some SQL which happily ran until newer devices came out and needed a slight re-jig which worked on the older and newer versions.

Typically I Open a Cursor, retrieve the data, populate a list and then close the Cursor. But all you're doing is executing a count - you could achieve this using something like this :

Count = sqlDatabase.ExecQuerySingleResult("SELECT count(_id) FROM RaceResults WHERE EntrantId =" & EntrantId)

Then check Count and set TRUE or FALSE accordingly

 

[Mike Parris]

Hi eps, yes you have found an inconsistency in my code!
However, my issue occurs at a number of different points in my code. The code above is just a sample to show my code structure.
I agree it would be useful to remove the use of cursors, but how do I get multiple rows from the database without using a cursor?
How do I determine or change the different versions of SQLite in play as you suggest?

 

[eps]

It's a bit of a nightmare to be honest..!

In this case though you are not getting multiple records as you just seem to be looking for the existence of 1 or more rows - hence my suggestion of a count instead - then no cursor needed. Then setting a TRUE or FALSE flag.

If you are retrieving multiple records then yes - but we might need to see the code for that part.

 

[eps]

Dunno - there's this, which is up to date as Oct 2016... https://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android

Which has a link to this : https://developer.android.com/reference/android/database/sqlite/package-summary.html

 

[eps]

I don't think they tend to break SQLite, they just tend to fix it to adhere more to standards and in doing so some code which used to work suddenly doesn't

 

[Erel]

You don't need to close the database.

Can you post the full error message from the logs (right click to copy)?

 

[Mike Parris]

Here's the log-

Logger connected to: motorola Moto G (5)
--------- beginning of main
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (events) Create, isFirst = true **
** Activity (events) Resume **
Error occurred on line: 78 (SQLData)
android.database.sqlite.SQLiteDiskIOException: disk I/O error (code 1802)
at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:845)
at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)
at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:143)
at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:132)
at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:219)
at anywheresoftware.b4a.sql.SQL$CursorWrapper.setPosition(SQL.java:322)
at b4a2.example.sqldata._getresultstatusid(sqldata.java:1425)
at b4a2.example.importexport._createneweventbyimport(importexport.java:514)
at b4a2.example.events._mnuimport_click(events.java:1247)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:710)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:342)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:139)
at anywheresoftware.b4a.BA$1.run(BA.java:325)
at android.os.Handler.handleCallback(Handler.java:751)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:154)
at android.app.ActivityThread.main(ActivityThread.java:6123)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:867)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:757)

The relevant code is -
<CODE>
Sub GetResultStatusID(ResultStatusType As String) As Int
Dim C As Cursor
Dim q As String
Dim i As Int = 0
q = "Select * from RaceresultsStatusTypes where Type = '" & ResultStatusType & "'"
C = sqlDatabase.ExecQuery(q)
If C.IsInitialized = True Then
C.Position = 0 ' this is the line it failed on
i = C.GetInt("Id")
End If
C.Close
Return i
End Sub
</CODE>

I know the C.IsInitialized code is not needed, this was one of the unsuccessful things I tried.

 

[OliverA]

The problem occurs when a cursor is used, after being created by an ExecQuery command. Other database commands do not cause the issue.

ExecQuery is a blocking call. Where do you initialize your database? In Main?

 

[Mike Parris]

Database is initialised in module Main. My app has a number of Actvity type modules and it is also called in the Activity_create sub of each module.

Should I be calliing close when I change modules?

 

[OliverA]

Database is initialised in module Main

You may have a locking issue. For more information please see this thread on stackoverflow: https://stackoverflow.com/questions...qlitediskioexception-disk-i-o-error-code-1802

 

[eps]

What is that code trying to do?

Surely you need to be checking if your Query actually returns anything before using it..?

As said, if you're performing a count, just use execquerysingle and count...

or loop and use something like this

For i = 0 To Cursor2.RowCount - 1
  do/assign stuff etc..
Next