Android Question SQL Service

BarryW

Active Member
Licensed User
Longtime User
Hi masters, how to monitor sqlite database in service?

Tnx...
 

peacemaker

Expert
Licensed User
Longtime User
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 ?
 
Upvote 0

Reviewnow

Active Member
Licensed User
Longtime User
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")
 
Upvote 0

BarryW

Active Member
Licensed User
Longtime User
Tnx for your reply but. I want to monitor the changes in my database and display a log for that in service. Is it possible?
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
Tnx for your reply but. I want to monitor the changes in my database and display a log for that in service. Is it possible?

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.
 
Upvote 0

Taha

Member
Licensed User
Longtime User
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.
 
Last edited:
Upvote 0

Taha

Member
Licensed User
Longtime User
Did the following change in the service:
B4X:
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.
 
Upvote 0

Taha

Member
Licensed User
Longtime User
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. ;)
 
Upvote 0
Top