Inserting to Multiple Tables

[marge0512]

Hello, I am using Delphi2010 and TOracleDataSet to access tables in Oracle. My question is how do I insert rows to multiple tables? I was told to use the AppendRecord method. Here is an example:

Example of value statements (this has been shortened obviously):

Suffix := trim(edSuffx.text);
Salutn := trim(edDear.text);

Example of AppendRecord:

DM.protinfoDS.Open;

DM.protinfoDS.AppendRecord([casenum,TPID,SSN,LName,FName,
LName2,FName2,Title,Co,Addr1,Addr2,City,State,ZIP,Suffix,Salutn]);

DM.protinfoDS.Close;

Example of the next table:

RepState := Trim(edRepState.text);
RepZip := Trim(edRepZip.text);

DM.repinfoDS.Open;

DM.repinfoDS.AppendRecord([casenum,RepName,RepCo,RepAddr1,
RepAddr2,RepCity,RepState,RepZip]);

DM.repinfoDS.Close;

Now........there are two other tables also. These all work and all four tables are inserted like they should be but I am leary of what could happen. I think there should be some kind of code in between appending information to the four tables. For example, the unique key for all four tables is a casenumber. What if 2 tables insert just fine and then an error occurs inserting values in the third table and then I have 2 tables with rows and 2 tables without? Matching casenumbers will exist in the 2 that inserted rows but not the other two.

Then, I was told to set the dataset's CachedUpdates property to true. When I do that, none of the tables update.

Is there a master/detail that I should set up in the datasets?

Please help and thanks in advance!

 

[Marco Kalter]

If you want to insert multiple records into different tables and commit or rollback all changes as one transaction, I would use TOracleQuery instances with insert statements. You can simply set the variable values, execute the inserts, commit after the 4th (successful) insert, and use a single exception handler to perform a rollback and other error processing.

 

[marge0512]

Thanks for responding!

Can I do the same thing with the TOracleDataSet instead of the TOracleQuery and if not, would you mind explaining so I would understand?

 

[Marco Kalter]

You can do the same with a TOracleDataSet, but appending records is not as efficient as directly executing SQL insert statements. Also note that you have to set TOracleDataSet.CommitOnPost to False to get control over transaction management. Otherwise each appended/posted record is committed.

 

[marge0512]

Thank you!!! That's what I was doing wrong.....I had .CommitOnPost set to true. As soon as I unchecked that...it worked fine.

I do have another question though, before I was directed to use this forum, another forum that was helping me said that I should have .CachedUpdates set to true but when I do that NO rows are saved to any table. Would I need to set that?? I thought that was only for the BDE tools. Also, should I be doing something with the Master/Detail properties?

I had tried to use the TOracleQuery but I could not get it to work. Within the SQLEditor I had put:

insert into prot_info values CaseNum, :ssn, :lname, :fname, :state, :zip, :suffix, :salutatn)

but it kept giving me an error saying CaseNum could not be null (which it wasn't when I was debugging) so I made some changes, got more errors....I can't remember what they were now.....so I just gave up and used the TOracleDataSet instead. I had also put the variables in the Variables Editor so I don't know what I was doing wrong.

Since you said that using the TOracleQuery is more efficient, I will try it again.

 

[Marco Kalter]

No, you should not use CachedUpdates. You don't need Master/Detail properties either if you are going to explicitly add all records.

When using a TOracleQuery and an insert statement, you need to set the variable values through TOracleQuery.SetVariable. If you do not set a value, it will be null and you will get exceptions for mandatory columns. For example:

with ProtInfoInsertQuery do
begin
  SetVariable('CaseNum', 10);
  SetVariable('ssn', '123-45-6789');
  ...
  Execute;
end;

 

[marge0512]

I'm finally getting back to work on this project.....

Thank you for this info! This really helped and it works. I do have another question though (of course)......does it matter what type the variables are set at in the Variables Editor? To better explain...if a field is set in the Oracle table as an integer, can that variable be of type string in the Editor or would it have to be set as an integer? I have all variables set as string, except for :casenum. I just want to make sure this isn't going to cause a problem. Thanks!

 

[Marco Kalter]

The data types will implicitly be converted on the server, but you should always use the correct variable data types. For dates and non-integer numbers you can get conversion errors depending on the NLS_LANGUAGE and NLS_DATE_FORMAT settings on the Oracle Server.

 

[marge0512]

Thank you!

I noticed something though....when I had my integer fields set as integer in the Variables Editor, the numbers would save as, for example, 45.00 instead of 45.12 in the table. When I changed them to string, it would save as it should. Their type is Number(10, 2) in Oracle. Why would that happen?

 

[Marco Kalter]

Integers do not have a fraction. You should declare the variable as Float.

 

[marge0512]

Got it! Thank you so much!!

 

[marge0512]

with ProtInfoInsertQuery do
begin
  SetVariable('CaseNum', 10);
  SetVariable('ssn', '123-45-6789');
  ...
  Execute;
end;

Would this example work for Update as well? That is my next attempt. Thanks!

 

[Marco Kalter]

If the Query.SQL.Text contains an appropriate update statement then this will indeed work.