[SOLVED] Two instances of same application can not open same SQLite3 database?

[Elmug]

The resultant .exe application uses SQLite3 and works fine, but after instance-1 opens the database, a second instance of the same application cannot open the same SQlite3 database.

I am using SQLite3Connection, SQLQuery, SQLTransaction, DataSource, DBGrid, all as provided in Lazarus 32 bit, for Windows 7.

What do I have to do to be able to have concurrent capability for the application?

Thanks.

[ludob]

http://forum.lazarus.freepascal.org/index.php/topic,17759.msg98683.html#msg98683

[Elmug]

http://forum.lazarus.freepascal.org/index.php/topic,17759.msg98683.html#msg98683

The thread you point to, unfortunately, does not answer my question.

Is it possible to do it as described, or not?

[Elmug]

This is from the SQLite frequently asked questions:

(5) Can multiple applications or multiple instances of the same application access a single database file at the same time?

    Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.


The link to that is here:
http://www.sqlite.org/faq.html#q5

I have an application by others that allows multiple access to the same SQLite3 database. But there is no information on how they do it, nor on which platform they have been developed.

I believe it is the components that I may have to use. Devart components exactly describe that theirs do it, but first I am asking if or not this is possible with the components in Lazarus, perhaps in someway that I am not aware of.

That's my question.

[ludob]

The thread you point to, unfortunately, does not answer my question.

It does. It even has an example of a lazarus program that can be executed simultanuously as many times as you want and that interacts with the same table in a dbgrid and using the components you listed. What more do you need?

The link to that is here:
http://www.sqlite.org/faq.html#q5

Sure. As repeatedly explained in the mentioned thread the problem is not the SELECT but the explicit transaction that is started by sqldb which puts a reserved lock on the table. See http://www.sqlite.org/lockingv3.html and http://www.sqlite.org/lang_transaction.html. The bottom of your faq q5 explains what happens with the SELECT when the database is locked.

[teos]

You CAN open a database multiple times and if lucky post data too.

If you need concurrent acces to the same database, you have to use something different than SQLLite (which documentation states that clearly).

I am working on a client-server with Indy, so that I can provide the best of all: multi-user, record locks and automatic updates of changed records. Might be what you look for.

[Dick, from the internet]

I have no problems opening multiple instances of my application, reading from the same SQLite3 database.  I have to admit, it took a little while to figure out updating the database with one of the instances, however, after re-reading ludo's post here http://forum.lazarus.freepascal.org/index.php/topic,17759.msg98683.html#msg98683, it was easy; the key being

Code: [Select]

SQLTransaction1.CommitRetaining;Inserting this in the Form.Create and in the update procedure made everything work as advertised!!
I had initially coded my app without any thoughts or regards to multiple users, as it is just for personal use, but this thread piqued my interest.  I learned something else today; thanks ludo!!

  regards,
          geno

[Elmug]

Hi Geno,

I am on Windows-7 and I tried these all I could but I will try again.

What operating system are you using, by the way?

If you are using Windows-7, that is more reason for me to insist on getting this to work as you say.

Thanks!

[Dick, from the internet]

 

What operating system are you using, by the way?

Have a look at my signature

[Dick, from the internet]

Once again, we are at an impasse here, until you post your offending code so other eyes may see.  You don't have to publish your whole project, just create a trimmed down version that exhibits the effects you describe.

  v/r
       geno

[teos]

I'm doubt if sharing a SQLite file is OS dependant.. But tell me what to test and I'll be glad to help.

I run Windows 7, Vista and Linux on a virtual machine.

[Leledumbo]

I'm doubt if sharing a SQLite file is OS dependant

Now you have no doubt: http://www.sqlite.org/onefile.html

[teos]

yep. clear to me. en what I expected.

[Elmug]

Hi everyone,

My problem is that when I open Instance1 and, in it, connect DatabaseX, then Instance2 CAN NOT open the database.

So now I put a button on the application, with which I can toggle to True/False the SQLite3Connection1,  and view (in the buttons caption) the actual state of SQLite3Connection1, then Instance2 of the application CAN INDEED connect to the same database when Instance1 has connection=False.

So all this time, my real problem has not been refreshes, nor commitretainings, but simply and more fundamentally the fact that in Windows7, when SQLite1 is ON in Instance1, Instance2 can not at all even connect to DatabaseX.

My belief right now is that the issue is related to the Windows7 NEW locking system is very much involved.

I found the inability to open Instance2 a while back and asked about it, but since I had no idea of the cause, the issue got very confused.

So with this new finding, maybe someone might suggest or know of a workaround.

Also, a few weeks ago I tried to use this project in my previous XP machine, but there were quite a few messages of not being able to do it, and that's because in that machine I have a version of Lazarus and FPC of about a year ago.

I have also found web info where DESKTOP databases running in XP were giving some problems in Windows7, not necessarily Lazarus, but I believe the desktop M.S. database, I believe Access.

I also have found that the file locks systems in Windows-7 is quite different now, so that XP filelock utilities do not work.

Thanks everyone, I've been eagerly reading all the posts on this, and feel this has nothing to do with my code. The app is now finished and works just fine.

In fact, as soon as I can connect using the new button, I can do selects from Instance1 and Instance2, but only writes from one or the other.

But still my problem will be that Instance2 CAN NOT open the database, unless the connection is disabled at Instance1.

If at connection time I can send a request to the op system to change the lock value, and it is granted, maybe that would be a way out.

Otherwise, perhaps the SQLite3 connection may need some updating to cope with the Windows-7 newer locking modes (or newer features of SQLite3?).

Thanks.


 

[Elmug]

I was doing some more investigating into my situation, and I found this:
----------------------------------------------------------------------------
SQLite    SQLite    Yes    No    -    i386: Linux, Win32
----------------------------------------------------------------------------
describing what FPC supports.

My Lazarus installation is the latest, for 32 bits. But my windows is Win-7 64 bit.

So, does any one know whether this might be a problem on this?

The page refered to is:
http://wiki.freepascal.org/Databases#Dataset_State

Thanks.