But extra question: if SQL object is declared inside a service, and the service sometimes must be stopped for changing initial important settings and also database re-initing - but an activity should going on working with SQL - how to organize such app ?
If the SQL object is a process global object then it will stay alive as long as the process is running. It doesn't matter whether the service is destroyed and recreated (assuming that you are not using the async features which do require a context).
This works for me throughout the entire application
Create a code Module Call it DbConnect
Add The following code
B4X:
'Code module
'Subs in this code module will be accessible from all modules.
Sub Process_Globals
Dim dbSQL As SQL
Dim dbCursor As Cursor
End Sub
Sub InitdbSQL
'Change to Correct path of your database
dbSQL.Initialize(File.DirDefaultExternal, "yourDB.db", True)
End Sub
in your Service Module Add the following Code in Service_Create
B4X:
Sub Service_Create
'You can add this also in activities
If DbConnect.dbSQL.IsInitialized = False Then
DbConnect.InitdbSQL
End If
End Sub
'Call your sql from anywhere
DbConnect.dbSQL.ExecQuery("SELECT * From YourDb")
That's easy to do. Use triggers in your SQLite tables to generate a log table. Then just query the log table to see what has changed. A simple tutorial on triggers can be found here.
I noticed that SQL database can't be updated by INSERT statement in Service. Read operations work fine. I tried both ExecNonQuery using Transaction, AddNonQueryToBatch/ExecNonQueryBatch. Both return positive result but database is not updated. Tried to use both SQL (initializing the same database as in main activity) and Main.SQL. Nothing helped. Various magic stuff like 'android metadata' didn't help either.
The same code is working flawless in main activity.
Sub Service_Create
If Main.SQL1.IsInitialized Then Main.SQL1.Close
If SQL2.IsInitialized=False Then
SQL2.Initialize(DBFileDir, DBFileName, False)
End If
End Sub
...
Cursor=SQL2.ExecQuery("SELECT * FROM Table;")
The last line has Result: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase:
This is indeed strange: I closed a database of Main activity and reinitialized the same database in service module.
My bad is that I don't carefully read your theory, Erel:
Rules - If the target module is already running then the sub will be called.
- If the target module is a service and it is not already running then it will first be started (Service_Create and Service_Start will first be executed). The sub will be called after Service_Start.
I understand the important thing (maybe I am wrong) - Service module shall be strict and as effective as possible, although you say it is more simple than activity.