[SOLVED] Two instances of same application can not open same SQLite3 database? lazarus / 16-Databases ---------------------------------------------------------------------- Elmug · 2012-08-19 17:44 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 · 2012-08-19 18:26 http://forum.lazarus.freepascal.org/index.php/topic,17759.msg98683.htm l#msg98683 ---------------------------------------------------------------------- Elmug · 2012-08-19 22:27 Quote from: ludob on August 19, 2012, 08:26:55 pmhttp://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 · 2012-08-19 22:50 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 · 2012-08-20 06:14 QuoteThe 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? QuoteThe link to that is here: http://www.sqlite.org/faq.html#q5Sure. 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 · 2012-08-20 14:35 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 · 2012-08-20 15:06 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.laz arus.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 · 2012-08-20 17:36 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 · 2012-08-20 18:34 QuoteWhat operating system are you using, by the way? Have a look at my signature ---------------------------------------------------------------------- Dick, from the internet · 2012-08-20 18:38 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 · 2012-08-20 19:30 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 · 2012-08-20 22:33 QuoteI'm doubt if sharing a SQLite file is OS dependantNow you have no doubt: http://www.sqlite.org/onefile.html ---------------------------------------------------------------------- teos · 2012-08-20 23:12 yep. clear to me. en what I expected. ---------------------------------------------------------------------- Elmug · 2012-08-21 00:09 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 · 2012-08-21 06:41 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. ---------------------------------------------------------------------- ludob · 2012-08-21 07:32 QuoteSo 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.Completely and fundamentally wrong. QuoteI 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.There is only one person confused here because he is not listening. Did you try the code in  http://forum.lazarus.freepascal.org/index.php /topic,17759.msg98683.html#msg98683 ? You couldn't get it compiled? I have the full program attached. In case that is still too difficult I have the executable and my sqlite3.dll uploaded to http://dl.dropbox.com/u/61365485/concurrentexe.zip. No initial database needed, the program will create it for you. It is called testconcurrent.sq3 and will be created in the dir of the program. QuoteMy 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?I have also such a configuration. Attached win 32 program ran fine a few minutes ago on that config with 3 simultaneous connections inserting lines in all 3 at the same time.  The Connect and Disconnect buttons are to show how to use a database without being connected. That is not needed for this demo!!! It was added because you said in the previous thread that this was non possible. I repeat, the buttons are not related to your question in this thread.    I rephrase, the demo is not based on disconnecting from the database. You can work with the table in all instances while remaining connected. Remove them from the form and delete the code if you don't believe me. ---------------------------------------------------------------------- Elmug · 2012-08-21 09:14 Ludob, I've had this for a long time: SQLQuery1.open; // := True; SQLTransaction1.CommitRetaining; The Grid fills up. Instance 2 does not open the database. I have a button that disconnects SQLite3Connection1 and THEN Instance2 can open the database. This was explained in my previous post. ALSO: My application I have made to stop exactly at the moment the database opens, with no query yet made, and no grid yet. At that point: SQLiteConnection1 = Connected, Transaction = Off, Query = Off, and Instance2 CAN NOT open the database. If I disconnect with the button the SQLite3 Connection1, THEN the database connects. So, as said, the problem here is CONNECTING the database by Instance2, you see? I will download your example and let you know what I find. I don't mind being confused, wrong, and WHATEVER you want to call me. I'd be all that and be happy if I get it to work as supposed. ---------------------------------------------------------------------- Elmug · 2012-08-21 09:22 Ludob, I downloaded your zip, unzipped it, and clicked on .lpi I get this error: Stream=Tform1.Root=:Tform1 Component Claass: TDBNavigator Error reading DBNavigator1.Options: Unknown property: "Options". Stream position: 1644 I told it to continue loading: Debugger: Identifier not found: RequireDerivedFormResource I discarded changes. ---------------------------------------------------------------------- ludob · 2012-08-21 09:33 I'm getting so tired of this  QuoteMy Lazarus installation is the latest, for 32 bits. But my windows is Win-7 64 bit.QuoteIdentifier not found: RequireDerivedFormResourceRequireDerivedFormResource was introduced in Lazarus in Feb 2011.    http://lists.lazarus.freepascal.org/pipermail/ lazarus/2011-February/060229.html ---------------------------------------------------------------------- Elmug · 2012-08-21 09:57 Quote from: ludob on August 21, 2012, 11:33:03 amI'm getting so tired of this  QuoteMy Lazarus installation is the latest, for 32 bits. But my windows is Win-7 64 bit.QuoteIdentifier not found: RequireDerivedFormResourceRequireDerivedFormResource was introduced in Lazarus in Feb 2011.    http://lists.lazarus.freepascal.org/pipermail/ lazarus/2011-February/060229.html I will try a simple project tomorrow on my old XP all 32 bit laptop and see what happens. I thank you for your kindness in helping out. ---------------------------------------------------------------------- Dick, from the internet · 2012-08-21 14:24 @elmug Open the concurrent.lpr file and either comment out or remove the line: QuoteRequireDerivedFormResource := True; and then load the project.  Even after doing so, and then trying to compile/run will throw error messages.  (*)Ignore this and run it again - maybe even a third time, but the project will eventually compile and execute as it should.   Now, you will see what we have been trying to tell you - running multiple instances is possible, no matter your OS, 32bit, 64bit, 2bit, moon is in wrong location in relation to Venus, or even Global Warming.    I am using version .9.30.4 dated 27June2012, and I also had a problem loading the project because of this one line, but it is not a show stopper - improvise, adapt and overcome! According to the message referenced by ludob, using this parameter should be normal behaviour, however, even when I create a new project from scratch, I do not see RequreDerivedFormResource inserted into my .lpr.  Oh well, its all good 'cause it still works for me!   regards       geno edit:  (*) I forgot to mention - the error you see from the DBNavigator1 control can be fixed by removing the control - it is not necessary for the demo. ---------------------------------------------------------------------- Leledumbo · 2012-08-21 14:51 QuoteRequireDerivedFormResource was introduced in Lazarus in Feb 2011And is documented here. ---------------------------------------------------------------------- ludob · 2012-08-21 16:08 Quote I am using version .9.30.4 dated 27June2012, and I also had a problem loading the project because of this one line, but it is not a show stopper - improvise, adapt and overcome!The message I referred to was indeed for trunk. I just double checked and the change didn't make it in version .9.30.4 which was released 5 months ago.  RC1 released end last month, the latest, does have RequireDerivedFormResource. ---------------------------------------------------------------------- Elmug · 2012-08-21 17:23 "Now, you will see what we have been trying to tell you - running multiple instances is possible, no matter your OS, 32bit, 64bit, 2bit, moon is in wrong location in relation to Venus, or even Global Warming. " Thanks Geno. If I wouldn't believe in all these things, I wouldn't be here. But do you all believe what I say about my own particular case? ---------------------------------------------------------------------- Dick, from the internet · 2012-08-21 20:39 ludob said: QuoteThe message I referred to was indeed for trunk. I just double checked and the change didn't make it in version .9.30.4 which was released 5 months ago.  RC1 released end last month, the latest, does have RequireDerivedFormResource.That's good news; at least for me.  It means I didn't bone something on my end!! elmug said: QuoteBut do you all believe what I say about my own particular case?Not really, not until someone else running  Win7 can confirm there may be an issue here.  Now, I am not trying to insult you by implying that you don't know what you are talking about; its just that over the years I have learned not to rely on just one source of information. Maybe I will get the chance to sneak in and install Lazarus on my wife's laptop while she not looking, and I can give it a try myself, but I am not going to count on that!   v/r      geno ---------------------------------------------------------------------- Leledumbo · 2012-08-21 22:26 QuoteBut do you all believe what I say about my own particular case?Buy me a Win 7 and let me test ---------------------------------------------------------------------- Elmug · 2012-08-22 01:26 Hi everyone. I found the problem: I had an initiation procedure that used the file reset function to check for the file to open, to avoid creating empty SQLite3 databases, on the old understanding that reset "opens to read". However, if the file is already open, reset does not return zero. Thanks every one! ---------------------------------------------------------------------- ludob · 2012-08-22 06:06 QuoteI found the problem: I had an initiation procedure that used the file reset function ...That is exactly the reason why we keep insisting on a simple test program that shows the problem. If you think something is not working, test your suspicion with a small test program that does nothing else. If the test program is working the problem is something else. Writing a test program seems a lot of work but compared to how much time this issue has cost you (and others ) it is almost always the best solution in the long term. The multiple "show us some code" requests is for exactly the same reason. People asking for that suspect there is probably something else wrong. ---------------------------------------------------------------------- Elmug · 2012-08-22 09:00 The thing is the application is 500 lines long. ---------------------------------------------------------------------- teos · 2012-08-22 11:13 Glad is is solved. 500 lines as attached file(s) is not that long :-)