[sqlite] Interrupt SQL query in SHELL
Ryan, Simon, thank you both for the help. The script works fine, and I now also understand how to use this bail mechanism. It is great! once more: THANK YOU! gert 2015-04-01 23:17 GMT+02:00 Simon Slavin : > > On 1 Apr 2015, at 8:09pm, Gert Van Assche wrote: > > > But this is definitely not the right way to do it. If I understand the > doc > > correctly, it should be an expression, but I don't see how I can do > this... > > You can trigger a bail by causing any SQL error. For example, inserting a > duplicate value in a column which is declared UNIQUE, maybe a primary key. > Or inserting a NULL value in a column defined as NOT NULL. Or failing any > other constraint check. So you may be able to use CASE or a calculation to > arrange that under the condition you're watching, one of these happens. > > You may be able to check the value returned by sqlite3 as it exits to see > whether it bailed or exited normally. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Interrupt SQL query in SHELL
On 2015-04-01 09:09 PM, Gert Van Assche wrote:
> Dr. Hipp, thanks for the tip. I put
> .bail on
> in the script.
>
>
> Ryan, I think I don't know how to trigger the bail out from within a SELECT
> statement.
> I tried
> SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2)
> then 'OK' else RAISE(FAIL) END;
>
> But this is definitely not the right way to do it. If I understand the doc
> correctly, it should be an expression, but I don't see how I can do this...
On closer inspection, it seems the raise function is really only allowed
in triggers. So you can use a trigger.
AS a proof of concept I've made a table like this:
CREATE TABLE "vChk" ("CheckTS" NUMERIC DEFAULT (CURRENT_TIMESTAMP));
CREATE TRIGGER Trig_vChk_T1_T2 AFTER INSERT ON vChk FOR EACH ROW
BEGIN
SELECT (CASE (select count(*) from T1) WHEN (select count(*) from T2)
THEN 'OK' ELSE RAISE(FAIL,'The number of Inserts are mismatched...') END);
END;
So then at the end of any insert bits of a script, you would simply add
a line like this:
INSERT INTO vChk DEFAULT VALUES;
and that would cause the script to stop execution and fail if the
statement requires it.
You can add any amount of triggers to that one vChk table to check all
sorts of things.
I've tested this time, it works.
[sqlite] Interrupt SQL query in SHELL
On 1 Apr 2015, at 8:09pm, Gert Van Assche wrote: > But this is definitely not the right way to do it. If I understand the doc > correctly, it should be an expression, but I don't see how I can do this... You can trigger a bail by causing any SQL error. For example, inserting a duplicate value in a column which is declared UNIQUE, maybe a primary key. Or inserting a NULL value in a column defined as NOT NULL. Or failing any other constraint check. So you may be able to use CASE or a calculation to arrange that under the condition you're watching, one of these happens. You may be able to check the value returned by sqlite3 as it exits to see whether it bailed or exited normally. Simon.
[sqlite] Interrupt SQL query in SHELL
Dr. Hipp, thanks for the tip. I put .bail on in the script. Ryan, I think I don't know how to trigger the bail out from within a SELECT statement. I tried SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2) then 'OK' else RAISE(FAIL) END; But this is definitely not the right way to do it. If I understand the doc correctly, it should be an expression, but I don't see how I can do this... thanks gert 2015-04-01 20:44 GMT+02:00 Richard Hipp : > On 4/1/15, Gert Van Assche wrote: > > > > My question is: when the import fails (detected in the script.sql file), > I > > would like to stop executing the batch file (runscript.cmd). > > > > Is there a way to do so? > > > > Have you tried the -bail command-line option? > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Interrupt SQL query in SHELL
On 2015-04-01 08:29 PM, Gert Van Assche wrote: > Hi all, > > When running SQLite3 from command line, is there a way to interrupt the CMD > file when a condition is true? > > I'm importing a lot of plain text files that should all count the same > number of lines, so I want to compare the record count of all imported data > with the first file that was imported. > > I can detect this easily with a query like this: > > .import 'x.txt' T1 > .import 'y.txt' T2 > .mode list > .output importerror.txt > SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2) > then 'OK' else 'Not all files contain the same number of lines' END; > > This is in an "script.sql" file and I run it by executing a batch file > (runscript.cmd). > > type script.sql | sqlite3.exe test.db > > > My question is: when the import fails (detected in the script.sql file), I > would like to stop executing the batch file (runscript.cmd). > > Is there a way to do so? Hi Gert, I have not tested this but there is an SQLite expression called RAISE(conflict-clause, message) which will stop transaction execution - not too sure if it stops a file import in its tracks, but the test will be easy. https://www.sqlite.org/syntax/raise-function.html It is usually used inside triggers for this purpose, but I assume it will work wherever.
[sqlite] Interrupt SQL query in SHELL
Hi all, When running SQLite3 from command line, is there a way to interrupt the CMD file when a condition is true? I'm importing a lot of plain text files that should all count the same number of lines, so I want to compare the record count of all imported data with the first file that was imported. I can detect this easily with a query like this: .import 'x.txt' T1 .import 'y.txt' T2 .mode list .output importerror.txt SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2) then 'OK' else 'Not all files contain the same number of lines' END; This is in an "script.sql" file and I run it by executing a batch file (runscript.cmd). type script.sql | sqlite3.exe test.db My question is: when the import fails (detected in the script.sql file), I would like to stop executing the batch file (runscript.cmd). Is there a way to do so? thanks for your thoughts, Gert
[sqlite] Interrupt SQL query in SHELL
On 4/1/15, Gert Van Assche wrote: > > My question is: when the import fails (detected in the script.sql file), I > would like to stop executing the batch file (runscript.cmd). > > Is there a way to do so? > Have you tried the -bail command-line option? -- D. Richard Hipp drh at sqlite.org
