Re: A question about database commits...

[Steven Calvert <calvert@ULETH.CA>]

Welcome to the group, Alex!

Regarding implicit commits, I believe SQR only "implicitly commits" database changes when the program finishes. If this is not true, I hope someone else on the list enlightens both of us. Note that Oracle (other databases, too?) implicitly commits all database changes whenever the user executes specific (all?) data definition language statements like "create table". Note that if your files are very large, you may run into "rollback segment too small" problems unless you explicitly commit or rollback every now and then.

You could also:

1. Write specific code to "back out" the data for any file which turns out to be corrupt (i.e implement your own rollback procedure). Depending on the complexity of your import, this may not be feasible. Or,
2. Do two passes on the text file. The first pass would check file integrity. If the file is OK, close/reopen it and process the records in the second pass. Or,
3. Use some variation of the above like making a temporary copy of the real data and applying your changes to the copy (if the file turns out to be OK, replace the real data with the updated copy). Or,
4. Use some other equally obtuse, yet functionally equivalent method. Don't you hate people who use unusual words to obfuscate things?

As always, the "correct solution" depends on your specific circumstances
(file/table sizes, processing speed, whether or not it is raining <grin>,
...) and your mileage may vary. Good luck!

Steve

"My opinions are my own, unless you like them too."

Alex WOLLANGK wrote:

     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)

--
Steven Calvert
calvert@uleth.ca
University of Lethbridge
(403)329-2071