Re: A question about database commits...

[Franz Diemel <franz.a.diemel@US.PWCGLOBAL.COM>]

Hi Alex:
     Not a bad question. Since I am usually a lurker reading the posting
when they are
interesting to me, I guess I should repay all the postings that I have
read.
     In my experience, SQR does an implicit commit at the end of the
program to any
database updates and inserts unless there is an error. On a database error,
the program
will stop and all database changes will be rollback.
     In the case of your multiple file inserts, you could do a commit based
on a successful
processing of that file for every file.  If the processing of the file is
unsuccessful, you could do
a rollback for that file. The pseudo code would look like this:
      start program
     check for files
     open all detected files
     start reading first file
      while not done processing current file
          while not end of file
                process file
                                 end-while
                if process file successful
                     commit changes to database
                else
                                 rollback
                     commit
                                      end-if
          get next file to process
     end-while processing of current file
                do house cleaning
                end program

If  you code it this way, you can control the rollbacks and commits and not
be concerned with
the implicit commit at the end of the program. I hope this helps.
                                    Franz







Alex WOLLANGK <alex_wollangk_at_doit-mail4@CCMAIL.ADP.WISC.EDU> on 08/11/99
02:06:28 PM

Please respond to SQR-USERS@list.iex.net
To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:
Subject:  A question about database commits...




     Hello Everybody!

     I am new to the list and to SQR and have just taken a position where I
     need to do some SQR coding and I have a question.

     I am writing an application which will import data from one or more
     text files (generated by a web interface) into a suspense database.
     (This will replace the current method where they print these files out
     and have someone manually re-key the information into the database...
     ewwwww....)

     My problem is that if there is a problem importing one of the files,
     we would like to stop processing that file and move on to the next one
     and not import any of the data from the bad file.  I thought that the
     best way to handle this would be to explicitly start a transaction
     when I start processing a file and explicitly either commit or
     rollback the work when that file processing finishes depending on an
     error flag.  I heard, however, that SQR does some implicit commits
     which could throw a monkey wrench in the works.  During processing I
     am only reading from the flat file and processing one segment of data
     into variables in memory, then I will run an insert statement within a
     'begin-sql' 'end-sql' block.  I do not need to read anything from the
     database, the only problems I am currently trapping are
     inconsistencies in the file itself which may come from file transfer
     errors.  (The files are moved via FTP which is pretty good, but I
     wouldn't bet my life on a file surviving with not corruption at all
     and corruption in the wrong key field can really throw things off.)

     Alex Wollangk
     University of Wisconsin - Division of Information Technology (DoIT)


----------------------------------------------------------------
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited.   If you received
this in error, please contact the sender and delete the material from any
computer.