Clovertech

odbc – multiple tables to update

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

  • Creator
    Topic
  • March 23, 2009 at 5:14 pm #50741
    Ryan Spires
    Participant

      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,

    • Creator
      Topic
    Viewing 3 reply threads
    • Author
      Replies
      • March 23, 2009 at 5:41 pm #67323
        Lawney Lovell
        Participant

          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.

        • March 23, 2009 at 5:55 pm #67324
          Jim Kosloskey
          Participant

            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.

          • March 25, 2009 at 2:55 pm #67325
            Steve Robertson
            Participant

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

            • March 26, 2009 at 11:23 pm #67326
              Steve Pringle
              Participant

                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
            Viewing 3 reply threads
            • The forum ‘Cloverleaf’ is closed to new topics and replies.