Hi Everybody,

I have an application that has been deployed using FB 1.52, written in
D6 and dbExpress for data access. We're preparing some updates that
require some database changes, and are having some issues running some
scripts...

When the application starts, it checks to see what version of the
database is present on disk, and upgrades it using our scripts as
necessary. We though we should wrap this in a transaction, so that if
something goes wrong, we've not done a 'half way' update.

So, we start a transaction, and then start sending our sql commands from
our script, if there's an exception, we'll do a rollback.

===

transDesc.TransactionID := 1;
transDesc.IsolationLevel := xilREADCOMMITTED;

dbConnection.StartTransaction(transDesc);
try
< loop here to go through various lines etc>
dbConnection.ExecuteDirect(sql);

dbConection.Commit(transDesc);
except
dbConnection.Rollback(transDesc);
end;

===

Before we had the explicit StartTransaction, Commit, Rollback etc, the
script ran fine. Now, it fails on this second the following lines with
this error; 'violation of PRIMARY or UNIQUE KEY constraint "INTEG_113"
on table "C_CHORD" '. I've included the first line to show the only
other changes to the C_CHORD table that the script would have already done.

ALTER TABLE C_CHORD ADD EDITABLE SMALLINT DEFAULT 1;

UPDATE C_CHORD C SET C.EDITABLE = C.EDIT;

It also runs fine in IBExpert... I'm sure this has something to do with
my lack of understanding of transactions - or dbExpress doing some
sneaky transaction stuff - can anybody help?

Thanks,

Peter Lee

--
Peter Lee ptle@...
-----------------------------------------------------------------------
Rising Software Australia Pty. Ltd. http://www.risingsoftware.com/
Publishers of 'Auralia' - Ear Training and 'Musition' - Theory Training
Ph: +61 3 9481 3320 FAX: +61 3 9481 3380 USA Freecall: 1 888 667 7839