Hello,

I'm still fairly new to DOA 3.3.3 and all of its features. I have two questions regarding the OracleQuery Component and Transactions. The code below sits on an App server and processes (creates) invoices for all exams that have not already been invoiced. In short, it
* Finds all centers with open invoices (openinvcenters)
* Get total invoice amounts and number of tests (code not shown)
* Inserts a record in the Invoice table and updates the exams table (AddInvoice & UpdateExams)

The 3rd bullet is where my questions arise. I have a OracleQuery that performs a standard INSERT INTO for adding invoices. In that insert SQL, Invoice_Sequence.nextval is used to populate the primary key. How do I get the sequence number from the insert after it is executed (see code below). Secondly, this 'invoicing' process will take place on one on more Test Centers (each test center can have >1 exams & invoices). I need to know two things : 1) have I coded the RollBack\Commit correctly so that nothing is saved to either the Invoice & exams tables if an error occurs and 2) How can I make sure that this process continues for each an every test center (while not EOF on OpenInvCenters) - regardless of whether the previous record posting was successful? I realize the second is more of a Delphi - not DOA question - but any thoughts would be appreciated.


try
with openinvcenters do
open;
if openinvcenters.recordcount = 0 then
raise EMyException.Create('There aren''t any open invoices. Process Completed Successfully.');
first;
while not eof do begin
// Get Number of tests here
// multiply total tests by fee here
oraclesession.Savepoint('Started');
try
// Insert Records via OracleQuery in Invoices Table
With AddInvoice do begin
setvariable('cntr', openinvcenterstest_center_id.value);
setvariable('amt', t1fee + t2fee + t3fee + t4fee + t5fee);
setvariable('num1', t1s);
setvariable('num2', t2s);
setvariable('num3', t3s);
setvariable('num4', t4s);
setvariable('num5', t5s);
execute;
end;
// Update the Exams via Oracle Dataset Table
with UpdateExams do begin
setvariable('cntr', openinvcenterstest_center_id.value);
open;
first;
while not eof do begin
edit;
UpdateExamsinvoiced.value := 'Y';
UpdateExamsinv_date.value := date;
UpdateExamsinvoice_number.value := 999; <-- Need Seq # from AddInvoice Insert
post;
next;
end;
end;
oraclesession.commit;
except
oraclesession.rollbacktoSavepoint('Started');
end; // END TRY INVOICE INSERT & EXAMS UPDATE
next;
end; // END WHILE OPENINVCENTERS NOT EOF
end; // END WITH OPENINVCENTERS


.... other cleanup stuff and end try

[quote]Originally posted by d4jaj1:
How do I get the sequence number from the insert after it is executed (see code below).[/quote]
If you are using Oracle8, you can use the 'returning into' clause:
[quote][/quote]
The :new_id variable will hold the generated value for the id column. If you are using Oracle7, you need to use a little PL/SQL Block:
[quote][/quote]
The result is the same.

[quote]Originally posted by d4jaj1:
1) have I coded the RollBack\Commit correctly so that nothing is saved to either the Invoice & exams tables if an error occurs[/quote]
If UpdateExamsinvoiced.CommitOnPost = False, then it seems okay to me. You have hidden any exception though. Also note that it seems that the UpdateExamsinvoiced loop could be replaced by a single update statement: all updated columns are the same for all records.

[quote]Originally posted by d4jaj1:
2) How can I make sure that this process continues for each an every test center (while not EOF on OpenInvCenters) - regardless of whether the previous record posting was successful?[/quote]
What is the problem?

------------------
Marco Kalter
Allround Automations

I am also a new person with DOA, I got similar problem like yours, trying to get the sequence number for a table......
my question is: where can I put the coding line: "insert into(id,....) values(seq.nextval, ...) returning id into.."
in trigger, or in procedure ????????

Thanks

You can put this code in the SQL text of a TOracleQuery or TOracleDataSet and execute it.

If you are using a TOracleDataSet to post new records, you can simply set the SequenceField properties instead.

------------------
Marco Kalter
Allround Automations

[This message has been edited by mkalter (edited 29 September 2000).]