Hello

How would you approach this, using EDB 2.14 Unicode and D XE3?

I am designing an invoicing system that includes the following tables & fields:

Invoice   
   InvoiceID   Identity field as PK
   Date, ClientID, Amount, etc.
InvoiceDetail
   InvDetailID   Identity field as PK
   InvoiceID   FK
   Item, ItemPrice, DiscountType, Tax, yadda yadda.
Allocation (customer may over-pay the account)
   AllocationID   Identity field as PK
   InvoiceID   FK
   AllocatedAmount, PaymentID (to Payment table with payer details), etc.

There will be many items to one invoice, so I need to obtain the InvoiceID when I insert the record in the Invoice table, (using an OUT Param value) and use that InvoiceID in both the InvoiceDetail and Allocation tables. Ideally, the whole process would be run inside a Transaction, and I would expect that if there was a failure then the Rollback would abandon all the changes. I have the bones of all the separate insert queries working Ok, but...

I can't put the whole thing inside the one Stored Procedure, as I don't know how many items will be added to the invoice. I also would like to sum the InvoiceDetail.ItemPrice column and put that in the Invoice.Amount, though that idea might not be a good one.

Should I try to do it all in a script, or open a transaction in Delphi, and use an EDBStoredProcedure component to do the work of calling each SP in turn?

Regards & TIA

Peter

Peter

Personally I'd use tables but if you want to use SQL my approach would be to use a program generated script. That way you can build it to accommodate however many lines there are, run it within a transaction  in the script.

Another approach would be to write the individual lines into a temporary holding table and then write a script with one of the parameters being the table to retrieve the lines from and loop round getting them all.

Roy Lambert [Team Elevate]

Peter,

<<  Should I try to do it all in a script, or open a transaction in Delphi,
and use an EDBStoredProcedure component to do the work of calling each SP in
turn? >>

Because EDB doesn't do nested transactions yet (soon, very soon), you should
use a manual transaction around the whole process (in code, or in a script),
and call the stored procedures individually from there.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com