Re: [sqlite] Commit and rollback behaviour during power loss

[Rowan Worth]

There is one degenerate case, which has been discussed a few times on this
list. With PRAGMA journal_mode=DELETE (the default), the atomic signal that
marks a transaction being committed is the deletion of the rollback
journal. Deleting a file is a directory level operation, which means there
are three files being changed:

1. The main DB file
2. The rollback journal
3. The directory

By default sqlite fsyncs (1) and (2) as part of COMMIT, but not (3). This
makes it possible for your program's control flow to get an SQLITE_OK
result from "conn.commit()" but then lose power before the OS syncs the
directory change to disk. As a result the transaction will be rolled back
when the DB is next opened.

It's a pretty extreme scenario, but you can avoid the risk via PRAGMA
synchronous=EXTRA (to also sync the directory), or PRAGMA
journal_mode=truncate (avoiding the need to modify the directory).


I don't think there is any scenario in which power loss could result in
multiple transactions being rolled back (unless fsync lies of course, in
which case all bets are off).

-Rowan

On 24 November 2017 at 16:47, Blagovest Buyukliev <
blagovest.buyukl...@gmail.com> wrote:

> Let's say we have the following Python code:
>
> import sqlite3
> conn = sqlite3.connect('mydb.db')
> c = conn.cursor()
> c.execute("INSERT INTO ...")
> conn.commit()
> c.execute("INSERT INTO ...")
> conn.commit()
>
> Can it be assumed that after conn.commit() has returned, fsync() has
> been called on the file and (assuming that fsync doesn't lie and
> doesn't just act as a barrier), the last inserted row has made its way
> to disk?
>
> Also, say that power is lost during the *second* conn.commit(). Is
> there a possibility that the database will be rolled back to the state
> before the first INSERT, or is it guaranteed that it will be rolled
> back to the state before the second INSERT?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Commit and rollback behaviour during power loss

[Blagovest Buyukliev]

> If Commit returns with SQLITE_OK, then YES, it is handed off to the disk
> with some caveats, namely:
>
> A - We are assuming the Python sqlite3 wrapper you use doesn't do
> obfuscation of any sort and directly calls the sqlite3 API and returns the
> direct result from those calls. If you haven't yet, consider using (or at
> least trying) the APSW wrapper.

Some inspection of the Python wrapper would reveal that. The important
thing you note is that this condition can be distinguished from the C
API.

> B - When SQLite reports back SQLITE_OK after a commit, it means that SQLite
> has handed off the fsync() to the OS. Some OSes may lie about this having
> been effected yet (in the interest of speed) and also, perhaps even
> unbeknown to the OS, the hardware itself may lie about having actually
> committed those bits to platters/NVRam/etc. Implementing a good database
> system is as much about making the hardware comply as making the software
> work.  That said, data failures/corruption due to this B caveat is extremely
> improbable and only ever happens in the weirdest of circumstances.

The hardware and the OS are pretty much a fixed combination, since
this will be used in an in-house hosted solution with Linux and some
chosen enterprise-level storage.

> [1] What I am not seeing in your code is conn.startTransaction() (or however
> that will be named for you). How does the connection know you have meant for
> a transaction to start so as to commit it later? I'm also not seeing you
> testing any return value from those conn.commit() calls, but that might just
> be for brevity of the post. They can however return a failure code, in which
> case the commit didn't happen.

Yes, the code is purely illustrative and lacks any error handling for brevity.

> Again, if the first Commit returned with SQLITE_OK (with noting the above
> caveats again), then no, that state is forever captured and part of the DB
> now, and any new transaction will have its own atomicity.

That's a very good thing which renders our potential solution viable.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Commit and rollback behaviour during power loss

[R Smith]

On 2017/11/24 10:47 AM, Blagovest Buyukliev wrote:

Let's say we have the following Python code:

import sqlite3
conn = sqlite3.connect('mydb.db')
c = conn.cursor()
c.execute("INSERT INTO ...")
conn.commit()
c.execute("INSERT INTO ...")
conn.commit()

Can it be assumed that after conn.commit() has returned, fsync() has
been called on the file and (assuming that fsync doesn't lie and
doesn't just act as a barrier), the last inserted row has made its way
to disk?


Probably not[1], but lets assume for a moment a transaction was started 
successfully, then:


If Commit returns with SQLITE_OK, then YES, it is handed off to the disk 
with some caveats, namely:


A - We are assuming the Python sqlite3 wrapper you use doesn't do 
obfuscation of any sort and directly calls the sqlite3 API and returns 
the direct result from those calls. If you haven't yet, consider using 
(or at least trying) the APSW wrapper.


B - When SQLite reports back SQLITE_OK after a commit, it means that 
SQLite has handed off the fsync() to the OS. Some OSes may lie about 
this having been effected yet (in the interest of speed) and also, 
perhaps even unbeknown to the OS, the hardware itself may lie about 
having actually committed those bits to platters/NVRam/etc. Implementing 
a good database system is as much about making the hardware comply as 
making the software work.  That said, data failures/corruption due to 
this B caveat is extremely improbable and only ever happens in the 
weirdest of circumstances.




Also, say that power is lost during the *second* conn.commit(). Is
there a possibility that the database will be rolled back to the state
before the first INSERT, or is it guaranteed that it will be rolled
back to the state before the second INSERT?


Again, if the first Commit returned with SQLITE_OK (with noting the 
above caveats again), then no, that state is forever captured and part 
of the DB now, and any new transaction will have its own atomicity.



[1] What I am not seeing in your code is conn.startTransaction() (or 
however that will be named for you). How does the connection know you 
have meant for a transaction to start so as to commit it later? I'm also 
not seeing you testing any return value from those conn.commit() calls, 
but that might just be for brevity of the post. They can however return 
a failure code, in which case the commit didn't happen.



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

[sqlite] Commit and rollback behaviour during power loss

[Blagovest Buyukliev]

Let's say we have the following Python code:

import sqlite3
conn = sqlite3.connect('mydb.db')
c = conn.cursor()
c.execute("INSERT INTO ...")
conn.commit()
c.execute("INSERT INTO ...")
conn.commit()

Can it be assumed that after conn.commit() has returned, fsync() has
been called on the file and (assuming that fsync doesn't lie and
doesn't just act as a barrier), the last inserted row has made its way
to disk?

Also, say that power is lost during the *second* conn.commit(). Is
there a possibility that the database will be rolled back to the state
before the first INSERT, or is it guaranteed that it will be rolled
back to the state before the second INSERT?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT or ROLLBACK failure

[Clemens Ladisch]

Igor Korot wrote:
> I need to check if the COMMIT is successful.
> But what should I do if it fails?

If the database is currently locked, you should try again later.  (But
this would be better handled with a busy handler.)

If there is some I/O error that prevents you from writing, the COMMIT
fails, but the ROLLBACK might still succeed (if nothing was written
yet).

If there is some permanent I/O error that prevents you from accessing
the files at all, both the COMMIT and the ROLLBACK will fail.

If there are pending write operations (which would be a programming
error), the COMMIT fails.  (The ROLLBACK will fail if there are any
pending queries.)  The correct way to handle this error is to fix the
bug in your program.


Read , especially the
section at the bottom.


To summarize: if the COMMIT fails, try a ROLLBACK.  This might fail too,
but there's nothing you can do then.


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

Re: [sqlite] COMMIT or ROLLBACK failure

[Keith Medcalf]

> On Thu, Sep 19, 2013 at 4:35 PM, Richard Hipp  wrote:
 
> > On Thu, Sep 19, 2013 at 5:50 PM, Igor Korot 
> wrote:

> > > Now, AFAIU, I need to check if the COMMIT is successful.
> > > But what should I do if it fails? Do I just report the failure to
> > > the user?


> > > Do I need to call ROLLBACK? And what if it will also fail?
> > > And in "else" branch - do I check for "ROLLBACK" result? And if it
> > > fails what do I do?

> > You probably should report the COMMIT failure to the user, yes.

> > Beyond that, what you do depends on what you want to do next.  You can
> > just call exit(1) if that is appropriate.  Or you can ROLLBACK and keep
> > using the connection.  Or you can sqlite3_close() the database connection 
> > if you want.

The answers to your questions are self-evident ...

> OK, so if I don't call ROLLBACK what will happen?

Then the transaction will continue.  Any further operations will take place 
within the context of the existing transaction that you have not yet committed. 
 Attempts to start a new transaction will fail.

> Will I end up with the screw-up database file?

That depends on your definition of "screw-up".  If you mean screw-up as in 
render corrupt and unuseable that will depend on the nature of the error you 
are ignoring.

If you mean "screw up" as in the database is perfectly fine but you have by 
your own actions rendered your own data inconsistent, that may be very likely.  
Especially since you want a "rule" for whether to continue or not.  Whether you 
continue or not is your decision based upon (a) the exact error (ie, the reason 
why the commit did not succeed) and (b) the consequence of continuing the same 
transaction.  It is determined by your application.
 
> Problem is that if the COMMIT fails, ROLLBACK might fail as well.

That is entirely possible.  You can determine what to do by the error code 
returned from the commit attempt.  And/or the rollback attempt.  These codes 
will inform your decision as to how to carry on.  Or to die.

> That's why I ask if I have to call ROLLBACK or just continue working.

That depends if given the circumstance and the failure to commit, you want to 
attempt to commit again, rollback, or just ignore the failure to commit and 
carry on depending, on if you need the transaction to commit, to rollback, or 
if you can just carry on.  This is an application issue, not a database issue.

Consider the following scenario:

BEGIN;
UPDATE Accounts set balance=balance-100.00 where acctno=394583;
UPDATE Accounts set balance=balance+100.00 where acctno=837294;
COMMIT;

And the commit does not return SQLITE_OK.  Is it OK to just "carry on" with the 
transfer not having been confirmed?  Do you want to try to commit again?  Do 
you want to rollback the transaction?  I don't know.  Nobody knows except the 
person writing the application.

> Thank you.




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

Re: [sqlite] COMMIT or ROLLBACK failure

[Igor Korot]

On Thu, Sep 19, 2013 at 4:35 PM, Richard Hipp  wrote:

> On Thu, Sep 19, 2013 at 5:50 PM, Igor Korot  wrote:
>
> >
> > Now, AFAIU, I need to check if the COMMIT is successful.
> > But what should I do if it fails? Do I just report the failure to the
> user?
> > Do I need to call ROLLBACK? And what if it will also fail?
> > And in "else" branch - do I check for "ROLLBACK" result? And if it fails
> > what do I do?
> >
>
> You probably should report the COMMIT failure to the user, yes.
>
> Beyond that, what you do depends on what you want to do next.  You can just
> call exit(1) if that is appropriate.  Or you can ROLLBACK and keep using
> the connection.  Or you can sqlite3_close() the database connection if you
> want.
>

OK, so if I don't call ROLLBACK what will happen?
Will I end up with the screw-up database file?

Problem is that if the COMMIT fails, ROLLBACK might fail as well. That's
why I ask if I have to call ROLLBACK or just continue working.

Thank you.


>
> Depending on the reason for the COMMIT failure, you might be able to try it
> again, after a delay.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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 or ROLLBACK failure

[Richard Hipp]

On Thu, Sep 19, 2013 at 5:50 PM, Igor Korot  wrote:

>
> Now, AFAIU, I need to check if the COMMIT is successful.
> But what should I do if it fails? Do I just report the failure to the user?
> Do I need to call ROLLBACK? And what if it will also fail?
> And in "else" branch - do I check for "ROLLBACK" result? And if it fails
> what do I do?
>

You probably should report the COMMIT failure to the user, yes.

Beyond that, what you do depends on what you want to do next.  You can just
call exit(1) if that is appropriate.  Or you can ROLLBACK and keep using
the connection.  Or you can sqlite3_close() the database connection if you
want.

Depending on the reason for the COMMIT failure, you might be able to try it
again, after a delay.

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

[sqlite] COMMIT or ROLLBACK failure

[Igor Korot]

Hi, ALL,

Consider following piece of code:

int res = sqlite3_exec(..., "BEGIN"... );
if( res != SQLITE_OK )
{
 printf( "Error occured on begin transaction. Please try again." );
 return;
}

// some operations on the database

// if operations are successful
 sqlite3_exec( ..., "COMMIT",  );
// else
 sqlite3_exec( ..., "ROLLBACK", ... );

Now, AFAIU, I need to check if the COMMIT is successful.
But what should I do if it fails? Do I just report the failure to the user?
Do I need to call ROLLBACK? And what if it will also fail?
And in "else" branch - do I check for "ROLLBACK" result? And if it fails
what do I do?

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

Re: [sqlite] commit and rollback

[John Stanton]

You can use the SQLITE_BUSY returned by sqlite3_step to synchronize. 
Just pause and resubmit the call when you get it.


arbalest06 wrote:

about this synchronization of multiple writers, can you please explain on how
to make this possible? or your just saying that i need to make a daemon that
will eventually synchronize the writers?



John Stanton-3 wrote:

Multiple writers merely have to be synchronized.

arbalest06 wrote:

so there is really no way that multiple processes can write into the
database?..but multiple processes can read at the same time right?..


Igor Tandetnik wrote:

arbalest06 <[EMAIL PROTECTED]> wrote:

q#1: is it possible that multiple users can write into the database
at the same time?

No.


q#2: if users A, B, C are writing to the database at the same time,

They can't.


q#3: if users A, B, C are writing to the database at the same time,

They can't.

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] commit and rollback

[arbalest06]

about this synchronization of multiple writers, can you please explain on how
to make this possible? or your just saying that i need to make a daemon that
will eventually synchronize the writers?



John Stanton-3 wrote:
> 
> Multiple writers merely have to be synchronized.
> 
> arbalest06 wrote:
>> so there is really no way that multiple processes can write into the
>> database?..but multiple processes can read at the same time right?..
>> 
>> 
>> Igor Tandetnik wrote:
>>> arbalest06 <[EMAIL PROTECTED]> wrote:
 q#1: is it possible that multiple users can write into the database
 at the same time?
>>> No.
>>>
 q#2: if users A, B, C are writing to the database at the same time,
>>> They can't.
>>>
 q#3: if users A, B, C are writing to the database at the same time,
>>> They can't.
>>>
>>> Igor Tandetnik
>>>
>>> -
>>> To unsubscribe, send email to [EMAIL PROTECTED]
>>> -
>>>
>>>
>>>
>> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/commit-and-rollback-tf4804976.html#a14018116
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] commit and rollback

[John Stanton]

Multiple writers merely have to be synchronized.

arbalest06 wrote:

so there is really no way that multiple processes can write into the
database?..but multiple processes can read at the same time right?..


Igor Tandetnik wrote:

arbalest06 <[EMAIL PROTECTED]> wrote:

q#1: is it possible that multiple users can write into the database
at the same time?

No.


q#2: if users A, B, C are writing to the database at the same time,

They can't.


q#3: if users A, B, C are writing to the database at the same time,

They can't.

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] commit and rollback

[Trevor Talbot]

On 11/28/07, arbalest06 <[EMAIL PROTECTED]> wrote:

> ok..thanx for that..now if process A is writing into the database, and
> process B attempts to write, does sqlite take note of B's attempt and gives
> the permission to B when A is done? like would it be a queue that the first
> process that attempted to write should be given priority to write? or is it
> that when process A is done, then process C attempts to write, C is given
> the permission to write even though process B attempted to write first than
> C?

There's no queue.  When process B attempts to write, it will be
refused, and must try again later itself.  It controls if/when it does
so; SQLite doesn't force any particular behavior.  If process C tries
before B tries again, then C will get to write and B will have to wait
again.

In API terms, an sqlite function will return SQLITE_BUSY if it can't
do what it needs to do right now.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] commit and rollback

[arbalest06]

ok..thanx for that..now if process A is writing into the database, and
process B attempts to write, does sqlite take note of B's attempt and gives
the permission to B when A is done? like would it be a queue that the first
process that attempted to write should be given priority to write? or is it
that when process A is done, then process C attempts to write, C is given
the permission to write even though process B attempted to write first than
C?


Sreedhar.a wrote:
> 
>>so there is really no way that multiple processes can write into the
> database?..but multiple processes can read at the >>same time right?..
> 
> --Yes
> 
> -Sreedhar
> 
> 
> Igor Tandetnik wrote:
>> 
>> arbalest06 <[EMAIL PROTECTED]> wrote:
>>> q#1: is it possible that multiple users can write into the database 
>>> at the same time?
>> 
>> No.
>> 
>>> q#2: if users A, B, C are writing to the database at the same time,
>> 
>> They can't.
>> 
>>> q#3: if users A, B, C are writing to the database at the same time,
>> 
>> They can't.
>> 
>> Igor Tandetnik
>> 
>> --
>> --- To unsubscribe, send email to 
>> [EMAIL PROTECTED]
>> --
>> ---
>> 
>> 
>> 
> 
> --
> View this message in context:
> http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/commit-and-rollback-tf4804976.html#a13992544
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] commit and rollback

[Sreedhar.a]

>so there is really no way that multiple processes can write into the
database?..but multiple processes can read at the >>same time right?..

--Yes

-Sreedhar


Igor Tandetnik wrote:
> 
> arbalest06 <[EMAIL PROTECTED]> wrote:
>> q#1: is it possible that multiple users can write into the database 
>> at the same time?
> 
> No.
> 
>> q#2: if users A, B, C are writing to the database at the same time,
> 
> They can't.
> 
>> q#3: if users A, B, C are writing to the database at the same time,
> 
> They can't.
> 
> Igor Tandetnik
> 
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
> 
> 
> 

--
View this message in context:
http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789
Sent from the SQLite mailing list archive at Nabble.com.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] commit and rollback

[arbalest06]

so there is really no way that multiple processes can write into the
database?..but multiple processes can read at the same time right?..


Igor Tandetnik wrote:
> 
> arbalest06 <[EMAIL PROTECTED]> wrote:
>> q#1: is it possible that multiple users can write into the database
>> at the same time?
> 
> No.
> 
>> q#2: if users A, B, C are writing to the database at the same time,
> 
> They can't.
> 
>> q#3: if users A, B, C are writing to the database at the same time,
> 
> They can't.
> 
> Igor Tandetnik
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] commit and rollback

[arbalest06]

good day!

thanx guys for helping me out..i got it working already..i just
misunderstood your solutions, that's why it took me a while to get it
right.. =^D

now i have another question, still related to this topic, but more on
theoretical..

q#1: is it possible that multiple users can write into the database at the
same time?

q#1.1: if yes, what can i do to make this possible (e.g. compiling the
sqlite source code with a macro 
  for this scenario )?

q#2: if users A, B, C are writing to the database at the same time, then
user A commits first, followed by 
   B, does the changes imposed by user A is ignored and is overwritten
by the changes imposed by user 
   B?

q#3: if users A, B, C are writing to the database at the same time, then if
user A commits first, followed by 
   B, and if user C rolls back, from what point does the database rolls
back?

explanations and C sample codes are very much appreciated..

thanx and God bless! :working:
-- 
View this message in context: 
http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991028
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] commit and rollback

[John Stanton]

Why not just test the status returned by sqlite3_step?  Use 
sqlite3_prepare_v2 to make the returned status give more information.


When you get an error just finalize your COMMIT statement and move your 
pointer from the prepared COMMIT statement to a ROLLBACK and shortcut 
the transaction.  Instead of committing you will rollback.


arbalest06 wrote:

good day!

i think its really a good solution to this problem. However, im required to
implement the c apis of sqlite..so i need to use the sqlite3_commit_hook and
sqlite3_rollback_hook..im doing some prototyping to see their
functionalities but i really cant make it work..and i also cant find some C
sample source code using these apis..if it is possible, can someone post
some c code using these apis?..

thank you and more power!

God bless!..


John Stanton-3 wrote:
All you need to do is to test the returned status of your sqlite3_step 
calls and if you get an error launch an SQL statement "ROLLBACK" and 
bail out of the transaction.  If there are no errors you complete your 
transaction with an SQL "COMMIT".


sqlite_prepare_v2 SQL statements

exec BEGIN

count = 0;
while ((count < MAX) && !error) {
  rc = sqlite3_step SQL statement[count++];
  if (rc != SQLITE_OK) {
error = TRUE;
  }
}
if (error) exec ROLLBACK
else exec COMMIT

d_maniger06 wrote:

good day!..

i have a list of records that i want to insert in my database..if ever an
error occurred ( e.g. insert was not successful ), i want to undo all the
previous inserts that i have done..to do this, i have read that i would
need
to use sqlite3_commit_hook and sqlite3_rollback_hook..i have read their
descriptions in the sqlite site, but i really can't understand it..im
just a
newbie in sqlite by the way..i also have tried searching sample c codes
that
does these things but i really cant find any..

i would just like to request a simple sample code demonstrating on how to
use these two apis..if it is ok also, please leave a very brief
explanation
on what happened in the code..

thank you and God bless!.. :working:


-
To unsubscribe, send email to [EMAIL PROTECTED]
-








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] commit and rollback

[Trevor Talbot]

On 11/26/07, arbalest06 <[EMAIL PROTECTED]> wrote:

> John Stanton-3 wrote:

> > All you need to do is to test the returned status of your sqlite3_step
> > calls and if you get an error launch an SQL statement "ROLLBACK" and
> > bail out of the transaction.  If there are no errors you complete your
> > transaction with an SQL "COMMIT".

> i think its really a good solution to this problem. However, im required to
> implement the c apis of sqlite..so i need to use the sqlite3_commit_hook and
> sqlite3_rollback_hook..im doing some prototyping to see their
> functionalities but i really cant make it work..and i also cant find some C
> sample source code using these apis..if it is possible, can someone post
> some c code using these apis?..

SQL *is* part of the API of SQLite.  There is no separate C API to
implement transactions; they are part of SQL.  The commit and rollback
hooks are called when someone uses the relevant SQL statements.  They
cannot be used to do what you have described, a transactional batch
insert.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] commit and rollback

[arbalest06]

good day!

i think its really a good solution to this problem. However, im required to
implement the c apis of sqlite..so i need to use the sqlite3_commit_hook and
sqlite3_rollback_hook..im doing some prototyping to see their
functionalities but i really cant make it work..and i also cant find some C
sample source code using these apis..if it is possible, can someone post
some c code using these apis?..

thank you and more power!

God bless!..


John Stanton-3 wrote:
> 
> All you need to do is to test the returned status of your sqlite3_step 
> calls and if you get an error launch an SQL statement "ROLLBACK" and 
> bail out of the transaction.  If there are no errors you complete your 
> transaction with an SQL "COMMIT".
> 
> sqlite_prepare_v2 SQL statements
> 
> exec BEGIN
> 
> count = 0;
> while ((count < MAX) && !error) {
>   rc = sqlite3_step SQL statement[count++];
>   if (rc != SQLITE_OK) {
> error = TRUE;
>   }
> }
> if (error) exec ROLLBACK
> else exec COMMIT
> 
> d_maniger06 wrote:
>> good day!..
>> 
>> i have a list of records that i want to insert in my database..if ever an
>> error occurred ( e.g. insert was not successful ), i want to undo all the
>> previous inserts that i have done..to do this, i have read that i would
>> need
>> to use sqlite3_commit_hook and sqlite3_rollback_hook..i have read their
>> descriptions in the sqlite site, but i really can't understand it..im
>> just a
>> newbie in sqlite by the way..i also have tried searching sample c codes
>> that
>> does these things but i really cant find any..
>> 
>> i would just like to request a simple sample code demonstrating on how to
>> use these two apis..if it is ok also, please leave a very brief
>> explanation
>> on what happened in the code..
>> 
>> thank you and God bless!.. :working:
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/commit-and-rollback-tf4804976.html#a13963043
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] commit and rollback

[John Stanton]

All you need to do is to test the returned status of your sqlite3_step 
calls and if you get an error launch an SQL statement "ROLLBACK" and 
bail out of the transaction.  If there are no errors you complete your 
transaction with an SQL "COMMIT".


   sqlite_prepare_v2 SQL statements

   exec BEGIN

   count = 0;
   while ((count < MAX) && !error) {
 rc = sqlite3_step SQL statement[count++];
 if (rc != SQLITE_OK) {
   error = TRUE;
 }
   }
   if (error) exec ROLLBACK
   else exec COMMIT

d_maniger06 wrote:

good day!..

i have a list of records that i want to insert in my database..if ever an
error occurred ( e.g. insert was not successful ), i want to undo all the
previous inserts that i have done..to do this, i have read that i would need
to use sqlite3_commit_hook and sqlite3_rollback_hook..i have read their
descriptions in the sqlite site, but i really can't understand it..im just a
newbie in sqlite by the way..i also have tried searching sample c codes that
does these things but i really cant find any..

i would just like to request a simple sample code demonstrating on how to
use these two apis..if it is ok also, please leave a very brief explanation
on what happened in the code..

thank you and God bless!.. :working:



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] commit and rollback

[d_maniger06]

good day!..

i have a list of records that i want to insert in my database..if ever an
error occurred ( e.g. insert was not successful ), i want to undo all the
previous inserts that i have done..to do this, i have read that i would need
to use sqlite3_commit_hook and sqlite3_rollback_hook..i have read their
descriptions in the sqlite site, but i really can't understand it..im just a
newbie in sqlite by the way..i also have tried searching sample c codes that
does these things but i really cant find any..

i would just like to request a simple sample code demonstrating on how to
use these two apis..if it is ok also, please leave a very brief explanation
on what happened in the code..

thank you and God bless!.. :working:
-- 
View this message in context: 
http://www.nabble.com/commit-and-rollback-tf4804976.html#a13746534
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Commit and Rollback

[Will Leshner]

On Jul 28, 2005, at 4:29 AM, Edwin Knoppert wrote:

And i wish for a test if the transaction is already set or not,  
without using a callback.




You already can:

"
  int sqlite3_get_autocommit(sqlite3*);
Test to see whether or not the database connection is in autocommit  
mode. Return TRUE if it is and FALSE if not. Autocommit mode is on by  
default. Autocommit is disabled by a BEGIN statement and reenabled by  
the next COMMIT or ROLLBACK.


"

Re: [sqlite] Commit and Rollback

[Edwin Knoppert]

And i wish for a test if the transaction is already set or not, without 
using a callback.

Even better, nested tranactions would be the best.
:)



- Original Message - 
From: "Marco Bambini" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Thursday, July 28, 2005 9:24 AM
Subject: [sqlite] Commit and Rollback


I need to know when a commit or a rollback is executed, I know that I  can 
use the sqlite_commit_hook routine to be notified of a commit  command, but 
what about rollback?

Is there a way to know (or to be notified) when a rollback happens?

Thanks a lot for your help,
Marco Bambini

[sqlite] Commit and Rollback

[Marco Bambini]

I need to know when a commit or a rollback is executed, I know that I  
can use the sqlite_commit_hook routine to be notified of a commit  
command, but what about rollback?

Is there a way to know (or to be notified) when a rollback happens?

Thanks a lot for your help,
Marco Bambini