odbc – multiple tables to update

[Ryan Spires]

I am curious how others are handling multiple table writes via odbc.

We have a need to write to several tables and wanted to know how others typically handle this.  As far as ODBC and cloverleaf, I have not done much, but was concerned with the possibility of tables getting out of sync if for some reason some tables were accessible while others were not or if we lost connectivity to the database while involved with writing the tables.

We are considering using a stored procedure to handle the actual writing of the tables, but wanted to get others’ thoughts on the matter.

Thanks,

Topic

[Lawney Lovell]

We use stored procedures to update multiple tables from the odbc connection to our data repository.  The stored procedures have some validation in them as well so the tables will remain in sync.  If a key data element is null or invalid it writes to an interface_errors table for us to view and correct errors.

[Jim Kosloskey]

Ryan,

Our preference is to utilize Stored Procedures as well.

This places the ‘business rules’ in the hands of the receiving system where we believe they belong.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Steve Robertson]

We use stored procedures, too. Actually, we use packages/package bodies in Oracle.

[Steve Pringle]

Ryan,

You can put your table updates in begin/end transaction statements.  This will insure that either all the updates are successful, or none are in the case of an error.  If there is an error during the updates the tables are “rolled back” to their initial state.  There could be a performance penalty for doing this – if others want to read/write to those tables they may have to wait unitil your transaction completes before the locks are released.  You could also write a stored procedure which has the begin/end transaction with your updates, which would simplify your odbc code.

We do this in tcl procs all the time.

If you’re using a lot of odbc you might consider getting a book on sql…

–Steve

[Author]

Replies