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 casnumber. 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.

Please help and thanks in advance!

[AthenaOfDelphi]

Hi marge0512 and welcome to PGD.

To make sure it all works as expected, you should use transactions.

Start a transaction -> Do the adds -> If everything goes ok, commit the transaction, if not, then rollback

I will say however, that it reads like you are going to rely on all four records getting the same identity in the four tables. This is fraught with potential disaster. What you should think about doing is having one of the tables be the master if you like such that you add a record to it and then use the ID it generates to link the records in the other tables back to it.

You absolutely should not rely on all four records getting the same ID as it seems you are planning on doing.

Another thing you should potentially not do (I've always been advised not to by people far more knowledgeable than me and text books etc.) is access server based tables (i.e. those provided by an RDBMS server such as Oracle, SQL Server or MySQL) using one of the dataset based TTable variants like TADOTable. TTable was only really ever meant to be used with database systems like Paradox or Access (i.e. file based systems), especially for things like adding and deleting records.

Hope this helps.

[marge0512]

Hello and thanks for responding!

You are correct, that was what I had planned on doing yet I knew it was a disaster waiting to happen which is why I'm asking for help. I've only created one application in Delphi and it was using only one table. This application uses four tables and I wasn't sure how to code it correctly.

I'm not sure how to code using the master/detail table way but I am going to research the internet now to get some ideas.

The good news is that I am not using TTable or variants like TADOTable. I'm using TOracleSession, TDataSource and TOracleDataSets.