Service steals database connection from main app?

[Matt]

Hello,

I am a bit new to Droidscript but I am enjoying it so far as it makes development quite easy.

I am currently trying to create an app that has a service behind the scenes.

Both the app and the service need to talk to the same SQL database to perform actions on either user interaction (through the app) or on a time basis (through the service).

However, it seems that the app connects to the database fine but once the service runs its first query, it locks up all queries run by the app.

If you stop the service, all the queries that the app tried to run then burst into life firing all the callbacks.

Attached is a super simplified version of what I am talking about so you can see for yourselves.

Once you run the app, a message will show saying the service is running.

Close the message and then you can add records to the database and ask for a record count as well.

After a few seconds, you will not be able to perform any of these actions and they will appear to do nothing.

If you then select the option to stop the service, you will get multiple messages relevant to the buttons you pressed while the database was locked down.

So, a bit long winded I know but my question is whether this is by design and is there a way to create a solution where an app and a service can use the same database?

Thanks,

Matt

[BareK]

Correct me if I'm wrong, but it seems to be a SQLite restriction where a write operation lock the database (which is a file).

https://stackoverflow.com/questions/10325683/can-i-read-and-write-to-a-sqlite-database-concurrently-from-multiple-connections

You should be able to perform what you want if you open/close the database before/after each write operation.

[Steve Garman]

It is normally straightforward to use a database in both main app and service because ExecuteSql should perform a commit or rollback when it completes, thus unlocking the table.

I haven't had a chance to look at the spk yet.

[Chris]

That is correct, you can not alter the same database at the same time.

I would suggest opening the database in your service, and sending all queries to it. An example.

svc.SendMessage("dbquery|select * from whatever")

Then in your service handle incoming messages to split the message and handle.

var a = msg.split('|');
switch (a[0]) {

case 'dbquery':
... query here using a[1]
break;
}

Or you can open/close it on every query, which seems like a crazy task :-)

[Chris]

I guess I left out important steps up there, and they get complicated - you have to send results back with the callback.

it is very possible, just might not be worth all the work.

[Matt]

@BareK
I trie this in the SPK attached version of the app and the same thing occurs.

@Steve
If you use the SPK I attached to the original message, and try to insert while the DB is "locked down", stop the service, you can get an error that says "Error: error while trying to roll back: Cannot perform this operation because there is no current transaction."

@Chris
While I am sure making the service run ALL queries would work, potentially passing big data through JSON responses seems a bit heavy.
Obviously if this is the only option I have left then I will take it, but I am curious to see why I cannot seem to get it to work.

Any more help would be great.

Thanks

[Steve Garman]

Ok this looks to be a more general problem with the callbacks in the service not firing.

I'm not convinced you will get the SQL working correctly in the Service in this version of DS at all.

Will let you know more when I have the information but it may be a while, sorry.

[Matt]

No problem, appreciate you looking at it.

Thanks

[Steve Garman]

Just as an update, Warren Downs produced some version research which made it clear what the problem is likely to be. It is connected with the update to api 26.

Dave is looking to produce a fix but it will not be available before the next alpha version is produced at least. 

Unfortunately I cannot give you an estimated date for that though it is unlikely to be this week.

[Matt]

Hello again,

Since the latest update to DroidScript, I noticed in the release notes that the above issue had been fixed.

When debugging my app through Droidscript I am pleased to say that everything was working as expected.

Unfortunately, as soon as I built my app as an APK and then installed it, I get the same issues as before.

Namely the callbacks are not being triggered if the service steals the database connection.

Any ideas on this?

[Dave]

Hi Mat,

This is probably because you are not using the latest APK builder as we have been a bit slow in updating it.  The latest one should be going onto Google Play today but see here for getting it right now:-

https://groups.google.com/d/msg/androidscript/5lvmKTqxgtQ/1UQNArsjBwAJ