I use quite a few script-type sql lists to achieve some functions in some systems, by which I basically mean I make up a list of SQL
statements and then just send them to the very convenient sqlite3_exec() function when I'm pretty sure I cannot do it any faster by
individual steps or speed is no concern...
A typical script might look like this:
-- ------------------------------------------------------------------
-- Alter Table masterOrderData after Importing
BEGIN TRANSACTION;
DROP TABLE IF EXISTS `TempAlteringTable`;
ALTER TABLE masterOrderData RENAME TO `TempAlteringTable`;
CREATE TABLE `masterOrderData` (
`PurchaseOrder` TEXT PRIMARY KEY COLLATE NOCASE,
`MStockCode` TEXT COLLATE NOCASE,
`MDescription` TEXT,
... other irrelevant columns omitted ....
) WITHOUT ROWID;
INSERT OR ROLLBACK INTO `masterOrderData` (PurchaseOrder, MStockCode,
MDescription, etc.....)
SELECT PurchaseOrder, MStockCode, MDescription, etc. same as above...)
FROM `TempAlteringTable`;
DROP TABLE `TempAlteringTable`;
COMMIT;
-- ------------------------------------------------------------------
Basically that master table gets created sometime before this script from an import via another DB and with automatic columns as
just Text columns, so I run the script to change the columns to have better definitions and Primary Key etc. etc.. It all works
perfectly.
Sometimes though, the imported table might have duplicate PurchaseOrder No's or such, some or other reason the Insert fails and
rolls back, which it does perfectly.
However, since I just push the whole script to the sqlite3_exec() function, it replies with SQLITE_OK always... even if a rollback
occured, which is probably correct since it did what I asked it to do, and it did it correctly... My question is though, is there a
way to learn somehow whether a rollback or constraint violation happened during the whole execute process which caused a rollback?
Kind of like saying: "Yes I know the script executed fully, but was there a mess-up
at some point?"
I'm being lazy here and do not want to check the tables after each script, so
hoping there is a way...
Thank you kindly,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
