Android Question SQLite - Simultaneous transactions

[gawie007]

Hi Erel,
I have the following situation:
I receive SMS's which I intercept with a service and log the values (tank levels) in MyDB. The values are currently logged to the MyDB in a sub in a code module.
I open MyDB when my App starts and close it when "UserClosed".
...this works fine.

I also intercept the SMS's using intent while "Userclosed = True" and the service is started.
When this happens, I try to access the sub in the code module and since I can't use callsub on the module I have an error that the program is not running.

I intend to leave the code in the code module for when the program is running (to allow deliveries to be added/edited) and move the data logging code (log the tank level) to the service (every time a SMS is received - whether the program is running or not).

If what I have read in other posts, am I correct, in that, you are saying that if I:
open MyDB1 from xyz.db in the main program, and,
open MyDB2 from xyz.db in the service,
that they should be fine even if the same tables are written to at the same time?​

 

[Informatix]

SQLite does not support concurrent writings so two processes cannot update the same data at the same time. One should have to wait for the other to end its writing. This is handled automatically by the engine ONLY if these processes use the same SQLiteDatabase object. Otherwise you will get a message saying the database is locked (or something like that).

 

[gawie007]

SQLite does not support concurrent writings so two processes cannot update the same data at the same time. One should have to wait for the other to end its writing. This is handled automatically by the engine ONLY if these processes use the same SQLiteDatabase object. Otherwise you will get a message saying the database is locked (or something like that).

Thank you Informatix,

So what I should not do is:
Dim MyDB1 As SQL
Dim MyDB2 As SQL
(Initialise to the same db file)

But do:
In Main: Dim MyDB As SQL
In Service: refer to Main.MyDB

This is how I had it originally, however, when I exit the main program and need the service to receive data, the database will be closed.
A messy alternative would be to save all the SMS's to a file then when the program runs, load the data into the database. There must be an easier way???

 

[Erel]

If I understand the configuration correctly then there is only one process running.

I try to access the sub in the code module and since I can't use callsub on the module I have an error that the program is not running.

You do not need to use callsub. You can call the sub directly.

Create a single SQL object in the code module and create an init sub:

If MyDB.IsInitialized = False Then
  MyDB.Initialize(...)
End If

You should call this init method from Activity_Create and Service_Create.

 

[gawie007]

Hi Erel,
Sorry for the delay but I had loads of changes to make to accommodate the few lines you have given me:
5 Activities, 2 code modules and 1 service...
It seems to be working as required!
I was busy writing code to store unwritten data when you replied.

The key was the Code Module containing the global SQL object.

"Thank you" to both you and Informatix.

I have included some of what I have done should anyone else have the same issues.

Code Module: (DBConnect.bas). This is the complete code module.

Sub Process_Globals

    Dim SQL1401 As SQL

End Sub

Sub InitSQL1401

    SQL1401.Initialize(File.DirRootExternal, "SQL1401.db", True)

End Sub

Main:

If File.Exists(File.DirRootExternal, "SQL1401.db") = False Then
    dbExists = False     'Flag
End If

...

If DBConnect.SQL1401.IsInitialized = False Then
    DBConnect.InitSQL1401
End If

...

Service Module:

Sub Service_Create

    If DBConnect.SQL1401.IsInitialized = False Then
        DBConnect.InitSQL1401
    End If

End Sub

 

[Erel]

Small tip: it is more elegant and safer to make this check in the init sub.

 

[gawie007]

Small tip: it is more elegant and safer to make this check in the init sub.

Hi Erel,

I have a few Activities & services that deal with the database.
I close the database after each Activity uses it and when the Main activity closes.
The service that must intercept the data from an SMS still needs to access the database even if the Main Activity has been closed.

I come from the old school of Openfile then Closefile otherwise all funny things happen.
Would it be safe NOT to close the db?

Essentially I need it when:
a) the App is running - user changes (accessed in multiple Activities).
b) data is received from an SMS
P.S. I have just been covering all bases - most probably not very elegant.
As always anywhere where I can be advised on how to improve will be appreciated!

 

[Erel]

Would it be safe NOT to close the db?

Yes. The file will be closed when the process is killed.

 

[gawie007]

Thanks for your valued support Erel,
I will open the DB from a couple of more suitable places if it is not open and remove all close statements.