Re: [sqlite] COMMIT in SQLite

[Paul Harris]

Thanks guys.  Perhaps docs could be updated in the _prepare_v2 section, to
mention the 'best practices' lifecycle of a statement in regards to commit



On 9 October 2013 04:08, Stephan Beal  wrote:

> On Tue, Oct 8, 2013 at 9:58 PM, Petite Abeille  >wrote:
>
> >
> > On Oct 8, 2013, at 8:10 PM, Stephan Beal  wrote:
> >
> > > (link to the original post not included because the archives are only
> > > visible to list members):
> >
> > Hmm?
> >
> > http://news.gmane.org/gmane.comp.version-control.fossil-scm.user
>
>
> Sorry for the confusion: that was on fossil-dev, which isn't publicly
> archived.
>
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/fossil-dev
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[Stephan Beal]

On Tue, Oct 8, 2013 at 9:58 PM, Petite Abeille wrote:

>
> On Oct 8, 2013, at 8:10 PM, Stephan Beal  wrote:
>
> > (link to the original post not included because the archives are only
> > visible to list members):
>
> Hmm?
>
> http://news.gmane.org/gmane.comp.version-control.fossil-scm.user


Sorry for the confusion: that was on fossil-dev, which isn't publicly
archived.

http://sqlite.org:8080/cgi-bin/mailman/listinfo/fossil-dev

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[Petite Abeille]

On Oct 8, 2013, at 8:10 PM, Stephan Beal  wrote:

> (link to the original post not included because the archives are only
> visible to list members):

Hmm?

http://news.gmane.org/gmane.comp.version-control.fossil-scm.user

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[Stephan Beal]

On Tue, Oct 8, 2013 at 8:01 PM, Dan Kennedy  wrote:

> On 10/08/2013 07:39 PM, Clemens Ladisch wrote:
>
>> Paul Harris wrote:
>>
>>> Many years ago, Igor mentioned that you should always reset/finalize any
>>> prepared statements before calling COMMIT.
>>>
>>> I am wondering, is this still true?
>>>
>> Yes.
>>
>

A related note from a recent conversation on the Fossil SCM mailing list
(link to the original post not included because the archives are only
visible to list members):


On Fri, Aug 16, 2013 at 7:29 PM, Richard Hipp  wrote:

> On Fri, Aug 16, 2013 at 1:26 PM, Stephan Beal 
>  wrote:
>
>> Hi again,
>>
>> db.pAllStmt and db_static_prepare() - i've ported/implemented that to
>> work at the db-connection level, but i have a question about this
>> particular implementation detail:
>>
>> void db_end_transaction(int rollbackFlag){
>>  ...
>>while( db.pAllStmt ){
>>   db_finalize(db.pAllStmt);
>> }
>> ...
>> }
>>
>>
>> Is there a technical reason which cached statements need to be cleaned up
>> in the commit/rollback or was that just a matter of taste/memory usage/etc?
>> i ask because i'm currently keeping mine open as long as the db connection
>> is alive and don't want to stumble into on a hard-to-track bug if there is
>> a reason the handles _must_ (for correct behaviour) be finalized as part of
>> the commit process.
>>
>
> There used to be.  When I wrote that code, SQLite required all prepared
> statements to be reset or finalized before a transaction would commit or
> rollback.  SQLite has since relaxed that requirement, however.
>
>

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[Dan Kennedy]

On 10/08/2013 07:39 PM, Clemens Ladisch wrote:

Paul Harris wrote:

Many years ago, Igor mentioned that you should always reset/finalize any
prepared statements before calling COMMIT.

I am wondering, is this still true?

Yes.


Do I have to reset before I commit?  And where is the requirement written
in the sqlite documentation?

 says:
| An implicit transaction (a transaction that is started automatically,
| not a transaction started by BEGIN) is committed automatically when
| the last active statement finishes. A statement finishes when its
| prepared statement is reset or finalized.
|
| The explicit COMMIT command runs immediately, even if there are
| pending SELECT statements. However, if there are pending write
| operations, the COMMIT command will fail with an error code
| SQLITE_BUSY.


Actually, I think the paragraphs above mean that it is
Ok to COMMIT while you have active read-only statements
(e.g. SELECT statements). The COMMIT statement will
commit the write-transaction to disk, but a read-transaction
will be held open until all the SELECTs have finished.

As for "pending write operations", I think you only get
this if you try to COMMIT in the callback for a user-defined
function or some such shenanigans. Calling sqlite3_step()
on a write statement but not calling reset()/finalize()
shouldn't leave it pending - a write statement is no longer
pending once it has returned SQLITE_DONE.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[Clemens Ladisch]

Paul Harris wrote:
> Many years ago, Igor mentioned that you should always reset/finalize any
> prepared statements before calling COMMIT.
>
> I am wondering, is this still true?

Yes.

There was a change in 3.7.11, but not with COMMIT itself:
| * Pending statements no longer block ROLLBACK. Instead, the pending
|   statement will return SQLITE_ABORT upon next access after the
|   ROLLBACK.

> Do I have to reset before I commit?  And where is the requirement written
> in the sqlite documentation?

 says:
| An implicit transaction (a transaction that is started automatically,
| not a transaction started by BEGIN) is committed automatically when
| the last active statement finishes. A statement finishes when its
| prepared statement is reset or finalized.
|
| The explicit COMMIT command runs immediately, even if there are
| pending SELECT statements. However, if there are pending write
| operations, the COMMIT command will fail with an error code
| SQLITE_BUSY.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[Paul Harris]

Hello all,

Many years ago, Igor mentioned that you should always reset/finalize any
prepared statements before calling COMMIT.

I am wondering, is this still true?

I have prepared SELECTs and INSERTs which have been step()'d, but not reset.
the SELECTs tend to be stepped until there is no more data left, but that
may not be guaranteed in the future.

Do I have to reset before I commit?  And where is the requirement written
in the sqlite documentation?

thanks,
Paul


- OLD EMAIL -

Dec 15, 2008; 9:39pm Re: COMMIT in SQLite
"hussainfarzana"
<[hidden email]> wrote in
message news:[hidden email]
> We have used BEGIN and COMMIT while executing our statements.While
> executing the BEGIN the return value is 0,but for COMMIT its
> returning 1.

COMMIT is failing. Without seeing your code, it's hard to know why.

One possible reason is that you still have open statements. Make sure to
sqlite3_reset or sqlite3_finalize all open statement handles before
executing COMMIT.

Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[Igor Tandetnik]

"hussainfarzana"
 wrote in
message news:21009480.p...@talk.nabble.com
> We have used BEGIN and COMMIT while executing our statements.While
> executing the BEGIN the return value is 0,but for COMMIT its
> returning 1.

COMMIT is failing. Without seeing your code, it's hard to know why.

One possible reason is that you still have open statements. Make sure to 
sqlite3_reset or sqlite3_finalize all open statement handles before 
executing COMMIT.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[hussainfarzana]

Thanks for your reply Igor.

We have used BEGIN and COMMIT while executing our statements.While executing
the BEGIN the return value is 0,but for COMMIT its returning 1.

Please guide us.

Regards,
Farzana.


Igor Tandetnik wrote:
> 
> "hussainfarzana"
>  wrote in
> message news:20971588.p...@talk.nabble.com
>> We have developed our application in eVC++ 3.0 using SQLite as the
>> database. While insertion or updation of records
>> We have used BEGIN and COMMIT method to insert or update the records.
>> While running the application we are able to insert and update and we
>> are able to check that value also.Once we exit the application and
>> reopen the application again we are not able to see the values and
>> these records are missing in the database also.The records are
>> inserted or updated temporarily.
> 
> You are executing a BEGIN, but you are _not_ executing a COMMIT. Check 
> your code, figure out why. Without COMMIT, your transaction gets rolled 
> back.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/COMMIT-in-SQLite-tp20971588p21009480.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[Igor Tandetnik]

"hussainfarzana"
 wrote in
message news:20971588.p...@talk.nabble.com
> We have developed our application in eVC++ 3.0 using SQLite as the
> database. While insertion or updation of records
> We have used BEGIN and COMMIT method to insert or update the records.
> While running the application we are able to insert and update and we
> are able to check that value also.Once we exit the application and
> reopen the application again we are not able to see the values and
> these records are missing in the database also.The records are
> inserted or updated temporarily.

You are executing a BEGIN, but you are _not_ executing a COMMIT. Check 
your code, figure out why. Without COMMIT, your transaction gets rolled 
back.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] COMMIT in SQLite

[hussainfarzana]

Hi All,

We have developed our application in eVC++ 3.0 using SQLite as the database.
While insertion or updation of records
We have used BEGIN and COMMIT method to insert or update the records. While
running the application we are able to insert and update and we are able to
check that value also.Once we exit the application and reopen the
application again we are not able to see the values and these records are
missing in the database also.The records are inserted or updated
temporarily.

Please help us how to save the values.

Regards
HussainFarzana.
-- 
View this message in context: 
http://www.nabble.com/COMMIT-in-SQLite-tp20971588p20971588.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users