Re: A question about database commits...

[Royce Lithgo <RLithgo@MACQUARIE.COM.AU>]

Alex,

SQR only implicitly commits when it reaches the end of the program (and even that doesn't occur on some platforms, eg Sybase).  You should have no problems doing what you want to do.

Royce.

-----Original Message-----
From: Alex WOLLANGK
[mailto:alex_wollangk_at_doit-mail4@CCMAIL.ADP.WISC.EDU]
Sent: Thursday, 12 August 1999 4:06 AM
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)