Android Example [B4X] B4XPages B4XPages_SQLiteLight2 cross-platform project

The attached project is a porting of the B4X_SQLiteLight2 project which is included in the B4X SQLLite database booklet.
It uses as much as possible XUI objects and common code.
Tested with B4A, B4i and B4J.

 

[Mahares]

Tested with B4A, B4i and B4J.

Bonjour Monsieur klaus:
I tried to close the SQL here, error when I get back into the database at this line:
ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM persons")
Where should the sql be closed.

Private Sub B4XPage_CloseRequest As ResumableSub
    Dim sf As Object = xui.Msgbox2Async("Do you want to quit ?", "Quit", "Yes", "Cancel", "No", Null)
    Wait For (sf) Msgbox_Result (Result As Int)
    If Result = xui.DialogResponse_Positive Then
        SQL1.Close  'added by mahares
        Return True
    End If
    Return False
End Sub

Error occurred on line: 118 (B4XMainPage)
java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/data/b4a.example/files/persons.db
    at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1450)
    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1393)
    at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
    at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
    at b4a.example.b4xmainpage._readdatabaserowids(b4xmainpage.java:496)
    at b4a.example.b4xmainpage._showtable(b4xmainpage.java:83)
    at b4a.example.b4xmainpage._b4xpage_appear(b4xmainpage.java:62)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA$2.run(BA.java:387)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:145)
    at android.app.ActivityThread.main(ActivityThread.java:6939)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1404)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1199)
** Activity (main) Pause event (activity is not paused). **

 

[klaus]

Thank you for reporting this.
In what condition and what platform did you get the error?
I have never encountered this error during my tests.

 

[Mahares]

In what condition and what platform did you get the error?

I tested it with B4A, version 9.9 that is the only platform I use. Debug or Release, on a tablet with os 5.1 and os 8.1. To make it easier for you to check, here is the complete project.

 

[klaus]

The project you posted works fine on my device also without the SQL1.Close.
But, anyway I added it to the project in the first post..

 

[Mahares]

The project you posted works fine on my device

Your project works now with closing the database this way, this is your code:

 Return True
 SQL1.Close

But when I use the below code (reversing the lines), that is when the error happens when you run it the second time. Why did it make that much diference by reversing the 2 lines:

SQL1.Close
Return True

 

[klaus]

I'm afraid that I misunderstood your post#2.
If I understand correctly, you get the error with this code:

SQL1.Close
Return True

My modification is none sense:

Return True
SQL1.Close

Because SQL1.Close is not executed.

On my device with Android 9, I am not able to reproduce your error.
I removed the SQL1.Close from the project in post#1.

 

[Mahares]

I removed the SQL1.Close from the project in post#1.

Why do you do that. Shouldn't a database be closed when exiting the app?

I tested again on a Samsung phone S10e with OS 10 and if I use these 2 below lines in this order, I get the error on the 2nd run of the app.

SQL1.Close
Return True

But if I reverse the order of the 2 lines, it works on subsequent test runs without any problems, but I think the database has never closed since return true is ahead of the SQL1.close. Maybe, someone who tested your project can verify it also if they explicitly close the database.

 

[klaus]

I have never closed a SQL database.
In my mind, when you quit the application SQL is closed automatically.
And when you use:

Return True
SQL1.Close

SQL1.Close is never executed, which is the same as if you remove it.
That's why I said that my code is none sense.
I added SQL1.Close without looking carefully where I added it.

 

[klaus]

The project has been updated in post #1, amended error reported HERE.

 

[RauchG]

Hello Klaus, would your example also work with CustomListView?

 

[klaus]

Yes, but you need to modify the filling of the CustomListView.
I did not use CustomListView because it has no horizontal scrolling.
When you have more columns to display the screen width can become too small, therefore horizontal scrolling becomes necessary.
B4XTable has horizontal scrolling but no vertical scrolling, it is page based.

 

[RauchG]

I don't need horizontal scrolling because I add several fields to the items.

I'll try it

 

[RauchG]

It works, but opening 220 records takes almost 30 seconds ... a shame

Now I'm trying LazyLoading.

Nevertheless, thank you for your great example.

 

[klaus]

It works, but opening 220 records takes almost 30 seconds ... a shame

This sounds very strange to me.
220 seconds in Release Mode ?
How many columns do you have ?
How do you fill the CustomListView ?

 

[DonManfred]

220 seconds in Release Mode ?

30 seconds for 220 RECORDS ;-)

 

[klaus]

Oh sorry, even 30 seconds is very very long !

 

[Erel]

There is no way that it takes 30 seconds to show 220 records.

 

[RauchG]

Sorry for the fuss ... I didn't try very hard.

 

[Erel]

Logs:

** Activity (main) Create, isFirst = true **
xui.DefaultFolder /data/user/0/b4a.example/files
List population time = 1.68 seconds to populate 221 produkt names
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
** Activity (main) Resume **
List population time = 1.87 seconds to populate 221 produkt names

Takes 1.87 second here.

1. Note that it is called twice. In most cases it is not needed to do anything in B4XPage_Appear.
2. Make sure to test it in release mode.
3. You should switch to lazy loading.