Re: [sqlite] Version 3.1.0 API suggestion: sqlite3_commit()

[Eric Scouten]

Ulrik Petersen wrote:
Eric,
Eric Scouten wrote:
D. Richard Hipp wrote:
This released is labeled "alpha" but it is still very well
tested.  By being "alpha" it means that there is still a
small window of opportunity during when users can suggest
API changes.  Once we go to beta (in about a week) no more
changes will be accepted.  So if you want to suggest changes,
please do so quickly.
 

I'm making a push to use prepared statements as much as possible and 
I've been unable to make use of a prepared statement that commits a 
transaction.

What I've tried (roughly, I'm doing this from memory since the code 
is gone now):

   sqlite3_exec( "BEGIN TRANSACTION;" );
  // do other stuff...
   sqlite3_stmt commitStmt;
   sqlite3_prepare( db, "COMMIT;", -1, , NULL );
   sqlite3_step( commitStmt );
  // error...
I forget the exact error message and result code, but the jist of it 
was that the transaction couldn't be committed because one or more 
statements were still running. From what I can tell, the only 
statement that hadn't been run to completion was the commit statement 
itself!

Bear in mind that this is a quick-n-dirty example to demonstrate the 
issue. In real life, I would have prepared and reused the "BEGIN 
TRANSACTION" statement (which works, BTW), cached commitStmt, paid 
attention to result codes, etc., etc.

It seems kind of silly to me to have to use sqlite3_exec( ... 
"COMMIT" ... ) to work around that problem, but that's what I'm doing 
at the moment. If there's a way to prepare and use a "COMMIT" 
statement, please let me know. If not, consider this a feature 
request for a sqlite3_commit() function so I don't have to spend the 
time compiling "COMMIT" over and over again.

It's not completely clear how you detect the error, so I have to ask: 
Did you use sqlite3_finalize?
I never got as far as sqlite3_finalize, because the error was reported 
on sqlite3_step.

-Eric

Re: [sqlite] Version 3.1.0 API suggestion: sqlite3_commit()

[Ulrik Petersen]

Eric,
Eric Scouten wrote:
D. Richard Hipp wrote:
This released is labeled "alpha" but it is still very well
tested.  By being "alpha" it means that there is still a
small window of opportunity during when users can suggest
API changes.  Once we go to beta (in about a week) no more
changes will be accepted.  So if you want to suggest changes,
please do so quickly.
 

I'm making a push to use prepared statements as much as possible and 
I've been unable to make use of a prepared statement that commits a 
transaction.

What I've tried (roughly, I'm doing this from memory since the code is 
gone now):

   sqlite3_exec( "BEGIN TRANSACTION;" );
  // do other stuff...
   sqlite3_stmt commitStmt;
   sqlite3_prepare( db, "COMMIT;", -1, , NULL );
   sqlite3_step( commitStmt );
  // error...
I forget the exact error message and result code, but the jist of it 
was that the transaction couldn't be committed because one or more 
statements were still running. From what I can tell, the only 
statement that hadn't been run to completion was the commit statement 
itself!

Bear in mind that this is a quick-n-dirty example to demonstrate the 
issue. In real life, I would have prepared and reused the "BEGIN 
TRANSACTION" statement (which works, BTW), cached commitStmt, paid 
attention to result codes, etc., etc.

It seems kind of silly to me to have to use sqlite3_exec( ... "COMMIT" 
... ) to work around that problem, but that's what I'm doing at the 
moment. If there's a way to prepare and use a "COMMIT" statement, 
please let me know. If not, consider this a feature request for a 
sqlite3_commit() function so I don't have to spend the time compiling 
"COMMIT" over and over again.
It's not completely clear how you detect the error, so I have to ask: 
Did you use sqlite3_finalize?

From :
"The sqlite3_finalize() routine deallocates a prepared SQL statement. 
All prepared statements must be finalized before the database can be 
closed. The sqlite3_reset() routine resets a prepared SQL statement so 
that it can be executed again."

HTH
Ulrik P.
--
Ulrik Petersen, MA, B.Sc.

Re: [sqlite] Version 3.1.0 API suggestion: sqlite3_commit()

[Eric Scouten]

D. Richard Hipp wrote:
This released is labeled "alpha" but it is still very well
tested.  By being "alpha" it means that there is still a
small window of opportunity during when users can suggest
API changes.  Once we go to beta (in about a week) no more
changes will be accepted.  So if you want to suggest changes,
please do so quickly.
 

I'm making a push to use prepared statements as much as possible and 
I've been unable to make use of a prepared statement that commits a 
transaction.

What I've tried (roughly, I'm doing this from memory since the code is 
gone now):

   sqlite3_exec( "BEGIN TRANSACTION;" );
  // do other stuff...
   sqlite3_stmt commitStmt;
   sqlite3_prepare( db, "COMMIT;", -1, , NULL );
   sqlite3_step( commitStmt );
  // error...
I forget the exact error message and result code, but the jist of it was 
that the transaction couldn't be committed because one or more 
statements were still running. From what I can tell, the only statement 
that hadn't been run to completion was the commit statement itself!

Bear in mind that this is a quick-n-dirty example to demonstrate the 
issue. In real life, I would have prepared and reused the "BEGIN 
TRANSACTION" statement (which works, BTW), cached commitStmt, paid 
attention to result codes, etc., etc.

It seems kind of silly to me to have to use sqlite3_exec( ... "COMMIT" 
... ) to work around that problem, but that's what I'm doing at the 
moment. If there's a way to prepare and use a "COMMIT" statement, please 
let me know. If not, consider this a feature request for a 
sqlite3_commit() function so I don't have to spend the time compiling 
"COMMIT" over and over again.

FWIW, I think I was using 3.0.7 at the time I encountered this issue.
-Eric