Android Question SQL Service

[BarryW]

Hi masters, how to monitor sqlite database in service?

Tnx...

 

[Erel]

It doesn't matter whether you access the database from an activity or a service. You can use the same code.

 

[peacemaker]

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 ?

 

[Erel]

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).

 

[Reviewnow]

This works for me throughout the entire application
Create a code Module Call it DbConnect

Add The following code

'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

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")

 

[BarryW]

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?

 

[keirS]

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.

 

[Taha]

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.

 

[Taha]

Did the following change in the service:

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.

 

[Erel]

Hard to say without seeing the whole project. However I agree with @Reviewnow code. This is a better approach.

 

[Taha]

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.