Re: A question about database commits...

["Johnson, Dan" <Dan_Johnson@WRIGHTEXPRESS.COM>]

Hi Alex,
        Is there some reason that you cannot 'clean' the files of bad data
before you try to insert them? Perhaps you can clean the data and store it
in an array then feed it into your database.

Happy programming
Dan

        -----Original Message-----
        From:   Alex WOLLANGK
[SMTP:alex_wollangk_at_doit-mail4@CCMAIL.ADP.WISC.EDU]
        Sent:   Wednesday, August 11, 1999 2:06 PM
        To:     Multiple recipients of list SQR-USERS
        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)