Re: INSERT cursors

Carlson@WHSmith wrote: > > Art S. Kagel wrote: > . . . snip . . . > > Jonathan Leffler wrote: > > > With some difficulty. The information is available in > > > SQLCA.SQLERRD[3] after each PUT which actually flushes the > > > buffer. The value in the field is zero after each PUT which > > > does not flush the buffer, so you can simply sum the values > > > as you go around the loop. The real fun occurs after the > > > cursor is closed at the end. Of course, if one of the PUT > > > statements fails, you can (if you're careful) determine which > > > row it was that failed. I'm not clear about the status of the > > > subsequent rows, but I assume that they are lost. Consequently, > > > I prefer not to use insert [...] > > > > Actually, Jonathan, if there is an error in one of the PUT rows > > the sqlca structure will report it when the block containing that > > row was flushed. Yes; I should have been clearer. > > If there is an error ONLY the row(s) successfully inserted > > before the row in error are successfully flushed. So the one that failed isn't there (it's not in the database, anyway, and presumably isn't still in the cursor). What about the other rows; can you use FLUSH again to insert them? I think not, but stand to be corrected. So you have to rework the erroneous row to fix the error, and you have to recreate any other rows afterwards. If you happen to be loading with data coming over a pipe or network connection, that's a trifle awkward -- the data is no longer available unless you cache the data until you know that it has been inserted OK. All of this can be rather painful - hence my original comment, which included words to the effect "hence I prefer not to use insert cursors if there is a significant chance of an error occurring". > > To recover you need > > to be able to backtrack to the row following the one in error and > > insert it again and perhaps log the data in error for manual > > recovery later. > > But within a transaction . . . shouldn't the whole transaction roll > back? No; only the statement. You need to be able to choose to recover from the error. Or you can decide to do the rollback yourself. But the only time the engine should do a rollback for you is if a COMMIT fails, or you disconnect with a transaction active. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>