Posted by Anonymous on 10/13/06 09:03

Alan Little wrote:

> Thanks for your detailed response. However, it doesn't work, because I
> don't want to wait until I've already attempted the insert, to know if it
> fails. The issue of two different affiliates having the same order ID
> isn't a problem, as there is an affiliate ID as well, so what needs to be
> unique is AID + OID, as you've described.
>
> The affiliates are providing their own order IDs, since they're going to
> be using whatever systems they use to collect the orders. When they
> submit the orders for fufillment, here's the sequence:
>
> 1) Affiliate submits a batch of (say) 100 orders
> 2) Check all the order data
> 3) Submit the batch as a single order to ZC
> 4) Store the sub-orders in custom tables
>
> The problem with doing the checking at insert time is that the first 90
> orders may be OK, with a problem on order #91. The whole batch has to be
> rejected and re-submitted, so that means I have to delete the 90 sub-
> orders already entered, *and* the order submitted to ZC. Thus I want to
> do the ID check at step 2.
>
> Hmmm..... I just had a thought, though. If I do a bulk insert, the whole
> thing would fail if there was a dup, and then I wouldn't have to delete
> the already-processed sub-orders, but I'd still have to delete the ZC
> order. I'll have to think about it some more.

If all inserts have to fail if one fails use a transaction. That's what
they are here for. :-)

If you have never worked with transactions before read them up in the
MySQL manual.

Bye!

[Back to original message]