Why error on commit transaction

[veneff]

I'm having trouble on the "COMMIT TRANSACTION" Query.

Here is the test program:



CLS
PRINT "Start"
SQLITECONNECT #SQLiteDataBase, "Shopper.dbs"
#SQLiteDataBase EXECUTE( "SELECT MAX( ShoppingListIndex ) AS MaxShoppingListIndex FROM ShoppingLists" )
PRINT "Executed select max"
#CurrentQueryRow = #SQLiteDataBase #NEXTROW()
ShoppingListIndex$ = STR$( (#CurrentQueryRow MaxShoppingListIndex()) + 1 )
PRINT "Figured next index value"
#SQLiteDataBase EXECUTE( "BEGIN TRANSACTION" )
PRINT "Began transaction"
#SQLiteDataBase EXECUTE( "INSERT Into ShoppingLists (CustomerIndex, ShoppingListDate, ShoppingListTime, ShoppingListIndex, CombinedShoppingList ) Values (""1"", ""01/25/2009"", ""12:50:30"", ""1"", ""0"" )" )
PRINT "Inserted new data into ShoppingLists"
#SQLiteDataBase EXECUTE( "COMMIT TRANSACTION" )
PRINT "committed transaction"
#SQLiteDataBase DISCONNECT()
PRINT "End"
END


And here is the displayed output:

Start
Executed select max
Figured next index value
Began transaction
Inserted new data into ShoppingLists

Runtime Error in program 'TestProblem': #SQLiteDataBase EXECUTE( "COMMIT TRANSACTION" )
SQL logic error or missing database

If I remove the 3 lines (not counting the PRINT statements):



#SQLiteDataBase EXECUTE( "SELECT MAX( ShoppingListIndex ) AS MaxShoppingListIndex FROM ShoppingLists" )
PRINT "Executed select max"
#CurrentQueryRow = #SQLiteDataBase #NEXTROW()
ShoppingListIndex$ = STR$( (#CurrentQueryRow MaxShoppingListIndex()) + 1 )
PRINT "Figured next index value"


The program runs successfully.

Or, if I insert a disconnect and connect sequence just after those lines, the program runs OK.

Why?

Thanks for any help!

Vance

[Carl Gundel - admin]

Well, I'm not sure. What if you wrap those three lines in their own transaction like so?

#SQLiteDataBase EXECUTE( "BEGIN TRANSACTION" )
#SQLiteDataBase EXECUTE( "SELECT MAX( ShoppingListIndex ) AS MaxShoppingListIndex FROM ShoppingLists" )
PRINT "Executed select max"
#CurrentQueryRow = #SQLiteDataBase #NEXTROW()
ShoppingListIndex$ = STR$( (#CurrentQueryRow MaxShoppingListIndex()) + 1 )
PRINT "Figured next index value"
#SQLiteDataBase EXECUTE( "COMMIT TRANSACTION" )
I would recommend you post this question on the SQLite mailing list because someone there may be able to give you a precise answer.

sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-Carl

[veneff]

Carl,

Yes that will work. But, doesn't disconnecting and reconnecting to a database take a relatively long time to do? Particularly if it needs to be done often. If this is true, then we need a way to release the dataset created by a select query such as a reset or finalize.

Vance

[Carl Gundel - admin]

Jan 26, 2009 14:28:14 GMT -5 veneff said:

Carl,

Yes that will work. But, doesn't disconnecting and reconnecting to a database take a relatively long time to do? Particularly if it needs to be done often. If this is true, then we need a way to release the dataset created by a select query such as a reset or finalize.
Vance

Connecting and disconnecting with SQLite is extremely fast (you're just binding and unbinding a library), but I wasn't recommending that you do that. I mentioned putting the three lines of code you were having trouble with in their own transaction to see if that helped. Does it?

As I said, you may want to post about your problem on the SQLite mailing list. This doesn't seem like an actual Run BASIC issue.

-Carl

[veneff]

Carl

Yes, I did check with the SQLite group. They were who indicated that the dataset from the previous SELECT needed to be released before beginning the transaction, either via a SQLite3_reset or SQLite3_finalize.
I did insert the disconnect/connect sequence before any transactions that are prefaced by a SELECT query. That seems to work just fine.

Vance

[Carl Gundel - admin]

Jan 27, 2009 8:35:29 GMT -5 veneff said:

Carl

Yes, I did check with the SQLite group. They were who indicated that the dataset from the previous SELECT needed to be released before beginning the transaction, either via a SQLite3_reset or SQLite3_finalize.
I did insert the disconnect/connect sequence before any transactions that are prefaced by a SELECT query. That seems to work just fine.

Vance

Interesting. Thanks for the info.

-Carl