Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 3 of 3 total |
Transaction protection |
Tue, Nov 5 2013 12:24 AM | Permanent Link |
Peter | 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 |
Tue, Nov 5 2013 4:30 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Nov 5 2013 2:43 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |