This may be a side issue for you ...

With this configuration, the dbExpress driver failed after an exact
number of transactions in my initial performance testing attempts
(2014 is the number I remember).

Of the connectors I tested, only IBO was even able to complete the test
suite. It was also the fastest by 30% or better.

This testing eventually led me to abandon Delphi for the applications I
am doing, because I was able to establish that the Delphi model would
not scale to meet my needs.

Java and the jaybird driver, on the other hand, scales nicely. With
SWT, you can make a GUI that is every bit as nice as a Delphi GUI, and
is cross platform in "out of the box" binary deployments.

On Tue, 2005-11-15 at 14:37 +1100, Peter Lee wrote:
> 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
>