Re: A question about database commits...

[Wes Williams <wwilliams@MAIL.RKD.SNDS.COM>]

Hi Alex, If found that with SQLBase SQR does an implicit commit. I aslo discovered that SQL Server requires an explicit commit. e.g. begin transaction and commit transaction. I know from experience that when using SQL Server without the explicit commit nothing gets committed(except me). So it appears to be platform dependent. I read in from flatfiles and vallidate my data prior to an insert or update statement. If it fails validation I append to an error file, skip that record and move on.

HTH

Wes Williams

Wes Williams
Hamilton Sundstrand
815.226.6196

>>> Alex WOLLANGK <alex_wollangk_at_doit-mail4@CCMAIL.ADP.WISC.EDU> 08/11 1:06 PM >>>
     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)