Why error on commit transaction

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

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

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

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