Syntax to set the conflict resolution of a transaction

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Syntax to set the conflict resolution of a transaction

Luiz Américo
I'm trying to set the conflict resolution of an entire transaction by
using the ON CONFLICT clause without success.

I'm using the following syntax:

BEGIN ON CONFLICT ROLLBACK;
INSERT INTO TableX (Id) Values (1);
INSERT INTO TableX (Id) Values (2);
INSERT INTO TableX (Id) Values (3);
COMMIT;

But get the error (using 3.6.14.2): SQLITE_ERROR - near "on": syntax error

I found that syntax at the mail archives:

http://thread.gmane.org/gmane.comp.db.sqlite.general/1563
http://thread.gmane.org/gmane.comp.db.sqlite.general/5200
http://thread.gmane.org/gmane.comp.db.sqlite.general/2276
http://thread.gmane.org/gmane.comp.db.sqlite.general/1562

I also tried the syntax found in the SQL wikipedia page:

BEGIN;
[..]
IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;

Also no luck.

Is there a way to set the conflict resolution for an entire transaction?

Luiz
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax to set the conflict resolution of a transaction

Kees Nuyt
On Tue, 2 Jun 2009 08:40:01 -0300, Karl Brandt
<[hidden email]> wrote:

>I'm trying to set the conflict resolution of an entire transaction by
>using the ON CONFLICT clause without success.
>
>I'm using the following syntax:
>
>BEGIN ON CONFLICT ROLLBACK;
>INSERT INTO TableX (Id) Values (1);
>INSERT INTO TableX (Id) Values (2);
>INSERT INTO TableX (Id) Values (3);
>COMMIT;
>
>But get the error (using 3.6.14.2): SQLITE_ERROR - near "on": syntax error
>
>I found that syntax at the mail archives:
>
>http://thread.gmane.org/gmane.comp.db.sqlite.general/1563
>http://thread.gmane.org/gmane.comp.db.sqlite.general/5200
>http://thread.gmane.org/gmane.comp.db.sqlite.general/2276
>http://thread.gmane.org/gmane.comp.db.sqlite.general/1562
>
>I also tried the syntax found in the SQL wikipedia page:
>
>BEGIN;
>[..]
>IF ERRORS=0 COMMIT;
>IF ERRORS<>0 ROLLBACK;
>
>Also no luck.
>
>Is there a way to set the conflict resolution for an entire transaction?

It's not part of the syntax of BEGIN.
http://www.sqlite.org/lang_transaction.html

As far as I can tell you'll have to use it in every INSERT
statement, which has implications for your program flow.
http://www.sqlite.org/lang_insert.html
http://www.sqlite.org/lang_conflict.html

(You probably already read those pages, I included the links
for the convenience of other readers)

>Luiz
--
  (  Kees Nuyt
  )
c[_]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax to set the conflict resolution of a transaction

J. King-3
In reply to this post by Luiz Américo
On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt <[hidden email]>  
wrote:

> I'm trying to set the conflict resolution of an entire transaction by
> using the ON CONFLICT clause without success.
>
> [...]
>
> Is there a way to set the conflict resolution for an entire transaction?

Such a thing is not possible.  You may specify a conflict resolution on a  
given statement (eg. 'INSERT OR ROLLBACK') or on a table (on PRIMARY KEY,  
NOT NULL and UNIQUE constraints), but not on a transaction.

--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax to set the conflict resolution of a transaction

Luiz Américo
2009/6/2 J. King <[hidden email]>

>
> On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt <[hidden email]>
> wrote:
>
> > I'm trying to set the conflict resolution of an entire transaction by
> > using the ON CONFLICT clause without success.
> >
> > [...]
> >
> > Is there a way to set the conflict resolution for an entire transaction?
>
> Such a thing is not possible.  You may specify a conflict resolution on a
> given statement (eg. 'INSERT OR ROLLBACK') or on a table (on PRIMARY KEY,
> NOT NULL and UNIQUE constraints), but not on a transaction.

Thanks for the info.

Let me explain the complete picture so someone can help me.

I develop a wrapper around sqlite that tracks the changed records and
than save the changes to the database by building and executing a SQL
query (a transaction).

Currently it executes the SQL and check the return value.
If the return value is different from SQLITE_OK it executes a
separated ROLLBACK command so another transaction can be started.

The problem is that after the ROLLBACK command, sqlite3_errmsg will
return "no error", giving the user no clues of what happened.

I tried INSERT OR ROLLBACK syntax but it will work only for
SQLITE_CONSTRAINT. I would need to handle also SQLITE_ERROR.

So there's a way to check if a transaction failed (for constraint or
another error) and than rollback without clearing the error message
returned by sqlite3_errmsg?

Thanks in advance.

Luiz
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax to set the conflict resolution of a transaction

Kees Nuyt
On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt
<[hidden email]> wrote:

>2009/6/2 J. King <[hidden email]>
>>
>> On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt <[hidden email]>
>> wrote:
>>
>> > I'm trying to set the conflict resolution of an entire transaction by
>> > using the ON CONFLICT clause without success.
>> >
>> > [...]
>> >
>> > Is there a way to set the conflict resolution for an entire transaction?
>>
>> Such a thing is not possible.  You may specify a conflict resolution on a
>> given statement (eg. 'INSERT OR ROLLBACK') or on a table (on PRIMARY KEY,
>> NOT NULL and UNIQUE constraints), but not on a transaction.
>
>Thanks for the info.
>
>Let me explain the complete picture so someone can help me.
>
>I develop a wrapper around sqlite that tracks the changed records and
>than save the changes to the database by building and executing a SQL
>query (a transaction).
>
>Currently it executes the SQL and check the return value.
>If the return value is different from SQLITE_OK it executes a
>separated ROLLBACK command so another transaction can be started.
>
>The problem is that after the ROLLBACK command, sqlite3_errmsg will
>return "no error", giving the user no clues of what happened.
>
>I tried INSERT OR ROLLBACK syntax but it will work only for
>SQLITE_CONSTRAINT. I would need to handle also SQLITE_ERROR.
>
>So there's a way to check if a transaction failed (for constraint or
>another error) and than rollback without clearing the error message
>returned by sqlite3_errmsg?

After a ROLLBACK; there is no error (ROLLBACK is succesful),
so the error message will be cleared.

You can use INSERT .... ON CONFLICT ABORT ... ;
Catch the constraint error, fetch the sqlite3_errmsg() and
ROLLBACK yourself.

http://www.sqlite.org/lang_conflict.html

(untested)

>Thanks in advance.
>
>Luiz
--
  (  Kees Nuyt
  )
c[_]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax to set the conflict resolution of a transaction

Luiz Américo
2009/6/2 Kees Nuyt <[hidden email]>:

> On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt
> <[hidden email]> wrote:
>>
>>Let me explain the complete picture so someone can help me.
>>
>>I develop a wrapper around sqlite that tracks the changed records and
>>than save the changes to the database by building and executing a SQL
>>query (a transaction).
>>
>>Currently it executes the SQL and check the return value.
>>If the return value is different from SQLITE_OK it executes a
>>separated ROLLBACK command so another transaction can be started.
>>
>>The problem is that after the ROLLBACK command, sqlite3_errmsg will
>>return "no error", giving the user no clues of what happened.
>>
>>I tried INSERT OR ROLLBACK syntax but it will work only for
>>SQLITE_CONSTRAINT. I would need to handle also SQLITE_ERROR.
>>
>>So there's a way to check if a transaction failed (for constraint or
>>another error) and than rollback without clearing the error message
>>returned by sqlite3_errmsg?
>
> After a ROLLBACK; there is no error (ROLLBACK is succesful),
> so the error message will be cleared.
>
> You can use INSERT .... ON CONFLICT ABORT ... ;
> Catch the constraint error, fetch the sqlite3_errmsg() and
> ROLLBACK yourself.
>

Thanks.
I'm aware of this solution. The problem is that storing the
sqlite3_errmsg result is not doable because i expose the return (or
error) message in a separated function, so i'd need to store the msg
in the other functions where a sql is executed leading to poor
performance (not always the user check for the return string).

It works like that:

ApplyUpdates executes the query
ReturnString returns the return/error string by calling sqlite3_errmsg

call ApplyUpdates
if something got wrong check ReturnString value

Luiz
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users