[sqlite] Please confirm this is a bug !
Hi, Using the C API if i submit the statements: BEGIN; CREATE TABLE test (id, name); COMMIT; within a single Exec command (or as a single line on the command line tool) then it works the first time and displays an error the second time as expected due to the table already existing. However, when the error occurs I am then unable to run anymore transactions because it says that a transaction is already active. So if I ran that set of statements a third time it would come up with the error: SQL error: cannot start a transaction within a transaction When reading the documentation I realised that sending a set of statements in an Exec command is treated as a single transaction anyway and therefore I can remove the BEGIN and COMMIT statements. When I do this then I can run that set of statements multiple times without SQLite displaying an error saying that a Transaction is already active. I haven't looked deep into the code but I thought I would ask the list if this is a bug or if it's just me not reading the documentation properly. Not knowing the code that well but guessing I would say that because the statements are on a single line I believe maybe the SQL interpreter is reading the BEGIN (start a transaction) but not reading the rest of the commands when an error occurs and therefore doesn't see the COMMIT (end transaction) at the end. Andrew -- Andrew Clark
[sqlite] Table locked when trying to delete a record whilst a cursor to the table is open
Hi, First of all here the simple example of what i am doing would be: "SELECT id FROM test" Get first Result DELETE FROM test WHERE id=xxx Close Cursor - When i call the DELETE command I get an error of TABLE LOCKED which for those that actually know something about the source code might understand, but not being to familiar about it I am not. Now I know that some of you will reply with... "Why not call the DELETE command with just the id's that we want to delete". Well, esentially my program is not wanting to do that.. without any further debates about it could we please discuss what we could do in this situation. I read somewhere in the documentation about if a transaction fails for whatever reason then in the future the transaction will be retried after say the table has been unlocked. Does this exist yet either in Beta development or something? If not, any help with either solving this problem or telling me why it shouldn't be fixed would be of great help. As I said I am not that knowledgable about SQLite but I see the error occurs from within a call to fileBtreeDelete which itself is called from the OP_DELETE part of vdbe Could someone please tell me if this is a simple thing to solve, and even if whether it should be solved or not. I may indeed look at changing my app design to call DELETE commands directly if this cannot be resolved quickly. Thanks in advance Andrew -- Andrew Clark
[sqlite] Table locked when trying to delete a record whilst a cursor to the table is open
Hi, Sorry I forgot to say.. I do know that the error occurs because there are read locks on the table, but is there any reason why say a single process should not be able to modify the table anyway? Or is there something in the Cursor enumeration code that would not be happy if a record was deleted after it had encountered that record. Andrew -- Andrew Clark
Re: [sqlite] Table locked when trying to delete a record whilst a cursor to the table is open
Hi, My program model in effect was a Component based model whereby I created an Object Enumerator which is currently implemented as an SQLite Select Query and whilst enumerating the objects I was then calling Delete() on some objects to tell them to destroy. The problem with SQLite was that i wasn't able to delete because the query was still active. I believe the best way to implement this is going to be to cache the results after creating the enumerator and then use the results cache to return an object from the enumerator. Although i suppose I could try and do as you said and cache the objects to be deleted into a list and then destroy them at the end of the enumerator. It's a pity it couldn't have been implemented in SQLite though to be more flexible. Thanks anyway Andrew -- Andrew Clark
Re: [sqlite] Table locked when trying to delete a record whilst a cursor to the table is open
One work around is to load the results of your SELECT into a TEMP table, then loop through the TEMP table to do your DELETEs. SQLite does allow you to delete, update, or insert tables from within the middle of a select as long as the tables being updated, deleted, or inserted are distinct from those being queried. Hi, Thanks I think I will indeed use a temporary table instead. It will probably use less memory and less hassle than me caching all the results myself. Thanks again Andrew -- Andrew Clark
