- This topic has 4 replies, 5 voices, and was last updated 15 years, 6 months ago by
.
-
Topic
-
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,
-
Replies
-
-
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.
-
-
-
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
-
- The forum ‘Cloverleaf’ is closed to new topics and replies.
