odbc – multiple tables to update

Homepage Clovertech Forums Read Only Archives Cloverleaf Cloverleaf odbc – multiple tables to update

  • Creator
  • #50741
    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.


Viewing 3 reply threads
  • Author
    • #67323
      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.

    • #67324
      Jim Kosloskey


      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

    • #67325
      Steve Robertson

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

    • #67326
      Steve Pringle


      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…


Viewing 3 reply threads
  • The forum ‘Cloverleaf’ is closed to new topics and replies.

Forum Statistics

Registered Users
Topic Tags