Re: INSERT cursors

On Thu, 20 Aug 1998, Carlson@WHSmith wrote: > I've looked it up in DejaNews and I double-checked the manual [...] > I'm in the process of speeding up a very long batch job [...] > > I noticed that a certain function inserts rows into a given table. What > I determined was that an insert cursor might be a bit more effective. > So far (no surprise) I've found out that it's faster. > > The only problem is that . . . how can I verify that the number of rows > I PUT equals the number of rows I actually committed to the database? > For example, I know that I PUT 168 rows, and I can see that they were > committed, but how can my program verify this information as it runs? 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 cursors where there's a reasonable chance of failure, mainly because the error recovery is diabolical, especially in the absence of transactions. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn Example code: DATABASE stores7 MAIN DEFINE n INTEGER DEFINE i INTEGER DEFINE s CHAR(128) CREATE TEMP TABLE t(i INTEGER NOT NULL, s CHAR(128) NOT NULL) DECLARE c CURSOR FOR INSERT INTO t VALUES(i, s) OPEN c LET n = 0 FOR i = 1 TO 1024 LET s = "Row ", i USING "<<<<" PUT c LET n = n + SQLCA.SQLERRD[3] DISPLAY "i: ", i USING "###&", ", n = ", n USING "###&" END FOR FLUSH c DISPLAY "sqlca.sqlerrd[3] post flush = ", SQLCA.SQLERRD[3] CLOSE c DISPLAY "sqlca.sqlerrd[3] post close = ", SQLCA.SQLERRD[3] END MAIN