At 11:23 AM 11/09/2007, you wrote:

>Hi
>
>I have an IBOQuery running the following query (Built in code)...
>
>
>INSERT INTO WorkNewItemExtraPricing (WorkRef, ControlRef, Svrreccnt,
>StockPriceTypeRef, SellPriceSys, SellPriceNoTax)
>SELECT GEN_ID(WorkControl_Gen, 1), ' + aControlref + ', ' +
>IntToStr(RecCount) + ', StockPriceTypeRef, SellPriceSys, SellPriceNoTax
>FROM WorkNewItemExtraPricing WHERE ControlRef = ' + aControlref + ' AND
>svrRecCnt IS NULL
>
>This is part of a loop where RecCount is incremented (aControlRef
>remains the same).
>
>The idea is that there are some base entries in the table (up to 4
>records) with the same ControlRef but a null svrreccnt. The code goes
>through a loop and duplicates these entries for each svrRccnt.

Hmmm, I don't think so....that logic doesn't work.

>At a random point in the loop the query fails to execute with a PK
>violation of the WorkRef field (the PK). I don't understand why this
>is? putting a dely into the code before the .execSQL command allows it
>to go further through the loop before encountering the PK violation.
>
>The SQL is in its own transaction.
>
>Any ideas?

The flaw in the logic above is that the WHERE clause can find more
than one record to copy, *including* records it has inserted itself.

>Or is this a Firebird thing (should I post to the Firebird list)?

It's a mix of ill logic on both sides of your design combined with a
fatal flaw inherent in the "INSERT INTO ME...SELECT...FROM ME"
implementation in Firebird and IB. However, that patchwork approach,
constructing a one-off string to apply to an SQL property, is not
just inefficient, it's also prone to the kind of iffy logic you've
ended up with here. And do I need to warn you off using client-side
loops that create inter-dependencies between records in the same
table and then subsequently rely on them?

You need to create a field with an absolute value that prevents the
statement from visiting the records it has already inserted. Your
SvrRecCount logic won't do it, as it is never going to find a record
containing NULL there (amongst other problems).

Why not clean up the design of this routine and approach it in the
proper way for both Firebird and IBO? Write a BEFORE INSERT trigger
to take care of the PK, leave WorkRef out of your INSERT spec and use
a parameter for ControlRef.

Do this change first:

ALTER TABLE WorkNewItemExtraPricing
ADD InsertStamp TIMESTAMP;
COMMIT;

Then, in your app, do this:

INSERT INTO WorkNewItemExtraPricing (
ControlRef,
StockPriceTypeRef,
SellPriceSys,
SellPriceNoTax,
InsertStamp)
SELECT
ControlRef,
StockPriceTypeRef,
SellPriceSys,
SellPriceNoTax,
CURRENT_TIMESTAMP
FROM WorkNewItemExtraPricing
WHERE
ControlRef = :Controlref
AND
(InsertStamp is null or InsertStamp < CURRENT_TIMESTAMP)

CURRENT_TIMESTAMP delivers exactly the same moment in time to each of
the records inserted by your statement (down to the nearest second in
sub-V.2 of Firebird), which should be strong enough to ensure that
the statement doesn't revisit the records it inserted.

If the only purpose of SvrRecCount was as a measure to avoid
self-duplication then ditch it. It's a lot of extra overhead and it
doesn't work as intended anyway.

If you need it for something else and you want it to happen at insert
time, writing the SvrRecCount value for the new records as an
increment on the highest SvrRecCount value in the existing set, the
loop needs to be on the server side, i.e., you need an executable SP
for the entire operation because DSQL can't loop within a statement
that targets multiple records.

Helen