Command line sqlite3 program bug

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Command line sqlite3 program bug

Domingo Alvarez Duarte-2
Hello !  

After seem several emails from a user asking about how to use sqlite3 through
shell scripts, I remember my experiences with sqlite3 but didn't mind to
report it, but now I think that it's worth mention it because it'll hurt
several users.  

The bug/problem is that the sqlite3 command line when feed with a sql script
with commands wrapped by a transaction if there is any error in the middle of
it sqlite3 reports the error but do not stop/abort the transaction and the
database end up in a dirty state.  

______example to show the bug/problem  

BEGIN;  

DROP TABLE IF EXISTS a; --to allow run more than once  

CREATE TABLE a(b); --after the next line error this should be rolled back  

INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback  

INSERT INTO a(b) values(4);  

COMMIT;  

______  

______blank database after been feed by the above sql script  

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a(b);
INSERT INTO "a" VALUES(4);
COMMIT;  

______  

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

Re: Command line sqlite3 program bug

Gerry Snyder-4
The Command Line Interface has the command:

.bail on

which will do what you want.

HTH,

Gerry Snyder
-------------------------------------------------------
On 9/8/2015 9:54 AM, Domingo Alvarez Duarte wrote:

> Hello !
>
> After seem several emails from a user asking about how to use sqlite3 through
> shell scripts, I remember my experiences with sqlite3 but didn't mind to
> report it, but now I think that it's worth mention it because it'll hurt
> several users.
>
> The bug/problem is that the sqlite3 command line when feed with a sql script
> with commands wrapped by a transaction if there is any error in the middle of
> it sqlite3 reports the error but do not stop/abort the transaction and the
> database end up in a dirty state.
>
> ______example to show the bug/problem
>
> BEGIN;
>
> DROP TABLE IF EXISTS a; --to allow run more than once
>
> CREATE TABLE a(b); --after the next line error this should be rolled back
>
> INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback
>
> INSERT INTO a(b) values(4);
>
> COMMIT;
>
> ______
>
> ______blank database after been feed by the above sql script
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE a(b);
> INSERT INTO "a" VALUES(4);
> COMMIT;
>
> ______
>
> Cheers !
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

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

Re: Command line sqlite3 program bug

Domingo Alvarez Duarte-2
Hello !  

Every day is a day to learn something new !  

It also have the command line -bail for my example "sqlite3 -bail a.db <
the.sql".  

Interesting that how easy I can miss the basics "when everything else fail,
read the manual !".  

Thank you !  

Cheers !  

>  Tue Sep 08 2015 9:40:37 pm CEST CEST from "Gerry Snyder"
><[hidden email]>  Subject: Re: [sqlite] Command line sqlite3 program
>bug
>
>  The Command Line Interface has the command:
>
> ..bail on
>
> which will do what you want.
>
> HTH,
>
> Gerry Snyder
> -------------------------------------------------------
> On 9/8/2015 9:54 AM, Domingo Alvarez Duarte wrote:
>  
>>Hello !
>>
>> After seem several emails from a user asking about how to use sqlite3
>>through
>> shell scripts, I remember my experiences with sqlite3 but didn't mind to
>> report it, but now I think that it's worth mention it because it'll hurt
>> several users.
>>
>> The bug/problem is that the sqlite3 command line when feed with a sql
>>script
>> with commands wrapped by a transaction if there is any error in the middle
>>of
>> it sqlite3 reports the error but do not stop/abort the transaction and the
>> database end up in a dirty state.
>>
>> ______example to show the bug/problem
>>
>> BEGIN;
>>
>> DROP TABLE IF EXISTS a; --to allow run more than once
>>
>> CREATE TABLE a(b); --after the next line error this should be rolled back
>>
>> INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback
>>
>> INSERT INTO a(b) values(4);
>>
>> COMMIT;
>>
>> ______
>>
>> ______blank database after been feed by the above sql script
>>
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>> CREATE TABLE a(b);
>> INSERT INTO "a" VALUES(4);
>> COMMIT;
>>
>> ______
>>
>> Cheers !
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>

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

 

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