Re: [sqlite] Checking for open transactions attach/detach database
I think I have found a solution After the commit I have set up a loop with a 1 sec sleep that checks for the existence of the file "ATTACHedDatabase-journal" Once it no longer exists the script continues, DETACHing the database without error. I would have thought there would be a similar SQL command to check for the existence of a ...-journal file. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MoDementia Sent: Wednesday, 28 May 2008 6:10 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Checking for open transactions attach/detach database Thanks for the clarification. The error I am receiving must then be produced by the application rather than just reporting an SQLite error. Or The application is taking control of the commit wait time, allowing my script to continue. Or more correct perhaps; the script commands are passed to the application which allows the script to continue producing the DETACH error. * There was a problem querying the database: Error executing SQL statement "DETACH ExportDB " : SQL logic error or missing database (1,1) Cancel, Retry, Ignore The error is ambiguous at best but I believe the second scenario is what is happening given the consistent time before clicking "Retry" is accepted without error. I have posted the information received so far to the developers however, I'm not expecting the application to be corrected/changed in a hurry as ATTACH/DETACH are unusual events in user scripts. So the question remains: Is there a way to check for open transactions / locks from the command line? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Tuesday, 27 May 2008 11:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Checking for open transactions attach/detach database MoDementia wrote: > The database file is ready to (copy) Detach as soon as the commit completes. > Yes. > This is the problem. > Detach: "This statement will fail if SQLite is in the middle of a > transaction." This is true. This is why you must commit your transaction before you can detach. > If I commit 10k row updates I cannot detach the database until it is > finished. > The database (SQLite) will be finished all its processing by the time it returns to your program after executing the commit statement. Attach Begin Loop to insert many rows Commit Detach > I need to be able to check some sort of table entry that will be clear once > the commit is finished. The commit is finished when it returns to your program. > > I don't have access to any of the higher level functions so it needs to be > something like > > SELECT Commit_Status FROM Active_Transactions > > Then I can wait till Commit_Status = something > Before I attempt to DETACH the database > > Obviously the application (written in some C language) can tell that it is > still committing and throws the error > But I need to do this check from the command line only The same logic applies to the command line. It has completed its processing by the time it displays the prompt after you enter the commit command. > > I hope this is making sense > Sort of. It seems like you are worrying about a non issue. HTH Dennis COte ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking for open transactions attach/detach database
Thanks for the clarification. The error I am receiving must then be produced by the application rather than just reporting an SQLite error. Or The application is taking control of the commit wait time, allowing my script to continue. Or more correct perhaps; the script commands are passed to the application which allows the script to continue producing the DETACH error. * There was a problem querying the database: Error executing SQL statement "DETACH ExportDB " : SQL logic error or missing database (1,1) Cancel, Retry, Ignore The error is ambiguous at best but I believe the second scenario is what is happening given the consistent time before clicking "Retry" is accepted without error. I have posted the information received so far to the developers however, I'm not expecting the application to be corrected/changed in a hurry as ATTACH/DETACH are unusual events in user scripts. So the question remains: Is there a way to check for open transactions / locks from the command line? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Tuesday, 27 May 2008 11:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Checking for open transactions attach/detach database MoDementia wrote: > The database file is ready to (copy) Detach as soon as the commit completes. > Yes. > This is the problem. > Detach: "This statement will fail if SQLite is in the middle of a > transaction." This is true. This is why you must commit your transaction before you can detach. > If I commit 10k row updates I cannot detach the database until it is > finished. > The database (SQLite) will be finished all its processing by the time it returns to your program after executing the commit statement. Attach Begin Loop to insert many rows Commit Detach > I need to be able to check some sort of table entry that will be clear once > the commit is finished. The commit is finished when it returns to your program. > > I don't have access to any of the higher level functions so it needs to be > something like > > SELECT Commit_Status FROM Active_Transactions > > Then I can wait till Commit_Status = something > Before I attempt to DETACH the database > > Obviously the application (written in some C language) can tell that it is > still committing and throws the error > But I need to do this check from the command line only The same logic applies to the command line. It has completed its processing by the time it displays the prompt after you enter the commit command. > > I hope this is making sense > Sort of. It seems like you are worrying about a non issue. HTH Dennis COte ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking for open transactions attach/detach database and Trigger behaviour with attached databases
What is the appropriate etiquette if I don't receive a solution/answer in ?? days -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MoDementia Sent: Friday, 23 May 2008 12:32 PM To: sqlite-users@sqlite.org Subject: [sqlite] Checking for open transactions attach/detach database and Trigger behaviour with attached databases Hi, My first submission 1 Problem and 1 question J My problem I have 2 processes that are causing problems after commits/locks 1. Copy main database to copydatabase using filesystem object How to determine when copydatabase is ready for attach? Attach copydatabase <-- need to retry a few times Delete some rows Detach copydatabase 2. Attach copydatabase Begin transaction Update main database from data in copydatabase Commit How to determine when copydatabase is ready for detach? Detach copydatabase <-- Need to retry many times The main application traps the errors before my MS VBScript can use ON ERROR so I need to test for the error before it occurs. Hope There is an easy solution My question (This seems to be answered as I didn't lose all the rows in the main database) Are triggers restricted to their respective databases? Main Database Table Name = Songs Attached Database Table Name = Songs Both have triggers CREATE TRIGGER delete_songs DELETE ON Songs BEGIN ... END Will these act only their respective tables or will they both act on the main database? Regards Terry Ganly __ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Checking for open transactions attach/detach database and Trigger behaviour with attached databases
Hi, My first submission 1 Problem and 1 question J My problem I have 2 processes that are causing problems after commits/locks 1. Copy main database to copydatabase using filesystem object How to determine when copydatabase is ready for attach? Attach copydatabase <-- need to retry a few times Delete some rows Detach copydatabase 2. Attach copydatabase Begin transaction Update main database from data in copydatabase Commit How to determine when copydatabase is ready for detach? Detach copydatabase <-- Need to retry many times The main application traps the errors before my MS VBScript can use ON ERROR so I need to test for the error before it occurs. Hope There is an easy solution My question Are triggers restricted to their respective databases? Main Database Table Name = Songs Attached Database Table Name = Songs Both have triggers CREATE TRIGGER delete_songs DELETE ON Songs BEGIN ... END Will these act only their respective tables or will they both act on the main database? Regards Terry Ganly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
