New topic: Serious Bug in RB 2009 R5 - Transactions?
<http://forums.realsoftware.com/viewtopic.php?t=31372> Page 1 of 1 [ 12 posts ] Previous topic | Next topic Author Message simonjohn Post subject: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 9:08 am Joined: Sat Apr 19, 2008 12:44 pm Posts: 286 Location: Dorset, UK I cannot Start a Transaction - an error is thrown - "Cannot start a Transaction from within a Transaction". Shedrik posted in the RB Forum that the vacuum command would not work - the error shown as cannot VACUUM from within a transaction" I have investigated further and found that to be true AND that Transactions can not be started. This is EXTREMELY serious for me - anyone else encountered this or got any comments. I have raised a bug report no 10591 and I have also posted this in the NUG. This code highlights the problem: Code://Create A Temporary Test Database dim f as FolderItem f=SpecialFolder.documents.child("test.rsd") dim db as new REALSQLDatabase db.DatabaseFile=f if f<> nil then if not db.CreateDatabaseFile then msgbox "DB NOT Created" return end end if not db.connect then msgbox "Connection Failed" return end db.SQLExecute ("drop table if exists test" ) db.SQLExecute ("create table test ( entered text , thedate date )") db.SQLExecute ("insert into test ( entered ,thedate) values (" + "'" + "Today" +"'" + "," + "'" + "2009-11-05 00:00:01" + "'" + ")" ) if db.error then msgbox "Insert Error " + db.ErrorMessage / / No Error for Insert db.SQLExecute("commit") //This Should end any Transaction ( End Transaction is an alias for Commit ) if db.error then msgbox "Commit Error " + db.ErrorMessage // No Error shown for Commit db.SQLExecute("begin transaction") if db.error then msgbox "Begin Transaction Error " + db.ErrorMessage //This throws error - Cannot start a Transaction from within a Transaction - The Commit should have closed any Transactions db.SQLExecute("end transaction") if db.error then msgbox "End Transaction Error " + db.ErrorMessage // No Error Shown here db.SQLExecute( "vacuum" ) if db.error then msgbox db.ErrorMessage // Error shown - Cannot VACUUM From within a transaction = either the commit OR the End Transaction should have closed! _________________ Simon Larkin QiSQL Database Solutions Website : http://www.qisql.com Mac 10.5.8, Imac 2.4ghz, 4gb, RB 2009 R4 Top Jym Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 9:33 am Joined: Sat Oct 01, 2005 5:19 pm Posts: 2106 What happens if you Begin and End the transaction you are doing? Your insert statement is the first transaction that might be open still ? I have no issues with 2009 v5 doing the same thing, but I keep all my inserts within a transaction statement Top simonjohn Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 10:05 am Joined: Sat Apr 19, 2008 12:44 pm Posts: 286 Location: Dorset, UK If i Just issue db.SQLExecute("begin transaction") as the very first line of code after the connection, when I check for db.error I get "cannot start a transaction within a transaction". So I cannot even start a transaction!!!! _________________ Simon Larkin QiSQL Database Solutions Website : http://www.qisql.com Mac 10.5.8, Imac 2.4ghz, 4gb, RB 2009 R4 Top simonjohn Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 10:13 am Joined: Sat Apr 19, 2008 12:44 pm Posts: 286 Location: Dorset, UK Jym, sorry if this is a stupid question - but do you check for errors after "begin transaction"? Yeah , I know why would you ? but this is definitely a problem raised by RB 2009 R5 - it does not happen with R4! _________________ Simon Larkin QiSQL Database Solutions Website : http://www.qisql.com Mac 10.5.8, Imac 2.4ghz, 4gb, RB 2009 R4 Top Jym Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 10:17 am Joined: Sat Oct 01, 2005 5:19 pm Posts: 2106 Well if you are creating the database that connects you so maybe it's the double connect? Code:If not f.exists then if Not db.CreateDatabaseFile then msgbox "DB NOT Created" return End If Else if not db.Connect then msgbox "db not connected" return End If End If instead of 'doing' both Top Jym Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 10:19 am Joined: Sat Oct 01, 2005 5:19 pm Posts: 2106 it's probably the new version of SQLite and not RB but who knows. Yes I look at the database variable for errors in the dubugger when I step through my code. Top simonjohn Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 11:41 am Joined: Sat Apr 19, 2008 12:44 pm Posts: 286 Location: Dorset, UK OK this bit of example code only creates a database if it does not exist. The code does not work if the database is newly created or already exists. Double connects? A lot of the posters on this forum do loads of connects one for every time they access the database. I don't think that would make any difference to this code which is only for demonstrating the problem. Just for fun I tried it without 're-connecting' and it still does not work. If you and other readers could try this code in RB 2009 R5 and let me know what happens - there should be no errors shown (works correctly in R4) and the DB version should be 3.6.20, I would be very grateful. I have tried this on Mac and Windows. Code:dim f as FolderItem f=SpecialFolder.documents.child("TMPtransactionstest.rsd") dim db as new REALSQLDatabase db.DatabaseFile=f if f<> nil then if not db.CreateDatabaseFile then msgbox "DB NOT Created" return end end //Both of these should execute without errors db.SQLExecute( "vacuum" ) if db.error then msgbox db.errormessage db.SQLExecute("begin transaction") if db.error then msgbox db.errormessage //Only this message should be shown ( SQLite Version 3.6.20 ) dim rs as recordset=db.sqlselect ( "SELECT sqlite_version() as version") msgbox "DB Version is " + rs.field("version").getstring _________________ Simon Larkin QiSQL Database Solutions Website : http://www.qisql.com Mac 10.5.8, Imac 2.4ghz, 4gb, RB 2009 R4 Top Jym Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 12:18 pm Joined: Sat Oct 01, 2005 5:19 pm Posts: 2106 I got it to work by adding db.autocommit = true I'm guessing that's why I didn't have the issues with my code and I can't explain why but that's solves the issues with your code too. Top simonjohn Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 1:47 pm Joined: Sat Apr 19, 2008 12:44 pm Posts: 286 Location: Dorset, UK Thanks Jym! That's interesting. SQLite state that "By default, SQLite version 3 operates in autocommit mode" and "Begin Transaction" takes it out of AutoCommit mode, so "Begin transaction" would fail as it's already out of Autocommit - I think ....... The error message seems wrong but that's SQLite for you! ). In the RB Language Ref (R4 & R5) it says that Autocommit default is false, so I don't understand how this stuff worked before ( does everybody except me set AutoCommit on for every connection? ) I have quite happily used Vacuum, Begin Transaction etc. without needing to set AutoCommit. In fact I want my apps to 'Commit' and 'Rollback' when I flippin' well tell them to! I guess that RB have twiddled something and kept it a flippin' secret! I'll wait to see the result of my Bug report because I don't want to have to revisit all my applications and set this for every DB action that uses Vacuum and presumably all other DML calls if they're are going to 'fix' it! Ha Ha I don't think I can wait THAT long! _________________ Simon Larkin QiSQL Database Solutions Website : http://www.qisql.com Mac 10.5.8, Imac 2.4ghz, 4gb, RB 2009 R4 Top Jym Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 2:54 pm Joined: Sat Oct 01, 2005 5:19 pm Posts: 2106 Autocommit gets disabled when you use "BEGIN" so it's not big deal as long as you use "BEGIN" and "END" with all your transactions. Top timhare Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 2:59 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 6867 Location: Portland, OR USA It is a big deal if it's broken. But it looks like REAL are addressing the problem. Top simonjohn Post subject: Re: Serious Bug in RB 2009 R5 - Transactions?Posted: Wed Dec 02, 2009 3:24 pm Joined: Sat Apr 19, 2008 12:44 pm Posts: 286 Location: Dorset, UK Quote:so it's not big deal as long as you use "BEGIN" and "END"Er, actually it is a big deal when you have a massive amount of existing SQL code to 'update' and check. Quote:But it looks like REAL are addressing the problemRight Tim, The RB Blog entry looks promising a 5.1 release to fix important issues with the SQLite plugin - fingers (and legs) crossed! _________________ Simon Larkin QiSQL Database Solutions Website : http://www.qisql.com Mac 10.5.8, Imac 2.4ghz, 4gb, RB 2009 R4 Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 12 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
