Hi Helen

Helen Borrie wrote:

> 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.
>
>

I expected the Where clause to find between one and four records to
insert. The table starts with 1-4 records with the controlref of this
entry. The SvrRecCnt field is NULL for these entries. The records
inserted above all have a SvrRecCnt field with a numeric value. I fail
to understand why you say that the where clause could select records it
has inserted.

I know it is not elegant logic but it should work. SvrRecCnt if a
foreign key into two other tables. Essentially the goal is to take the
template and apply the settings to a number of other entries.

>> 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,
>

What is the Fatal flaw? If you explain the flay I could work around it.

> 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?
>

No. There is no relation ship between the records in this table. It is
a duplication process. In this case the business logic is sound, it
makes sense to do it this way. If Firebird has a 'Fatal flaw' then
thats probably the cause of my frustration. I can work around it but
there is no need to attack the logic (I like FB there is no need to
defend it).

> 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).
>

Don't understand this statement. As I said the table starts off with
1-4 records with NULL values. Why won't the SvrRecCnt logic do it? Is
this the 'fatal flaw' or am I misunderstanding something.

> 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.
>

As you see from my subsequent email (sent before receiving this) that
there was a trigger on the table but I am having trouble with it running
(The trigger is a if WorkRef is Null then Gen value type trigger).

> 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.
>
>

What is the difference between inserting a timestamp and using that in
the where to my system of populating a field (svrReccnt).

> 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.
>

SvrRecCnt is a FK into other tables. It is not primarily to avoid
self-duplication.


Thanks Helen


Rob