Re: INSERT cursors

Jonathan Leffler wrote: > > 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. > Here's the gist of my code . . . declare c_ins_cursor cursor with hold for "insert into table_name (val1, val2, val3) values (?, ?, ?) ................................................................... open c_ins_cursor declare cursor with hold to get all store numbers foreach store_num compute_info_to_insert() begin work for i = 1 to some_number put c_ins_cursor using pa_var1, pa_var2, pa_arrval1[i] if sqlca.sqlcode < 0 then display error message set return code exit for end if put c_ins_cursor using pa_var1, pa_var2, pa_arrval2[i] if sqlca.sqlcode < 0 then display error message set return code exit for end if end for if return_code is OK commit work else rollback work exit foreach end if end foreach close c_ins_cursor .................................................................... According to the manual, this would constitute an insert cursor, but would this be a decent level of transaction protection? I'm committing after each subset, so it appears that I'm safe. I'm checking sqlcode after each PUT, so if something gets a bit flaky, I should be able to catch it and rollback the transaction and executing a controlled abort of the program. My understanding of the manual is that any uncommitted rows will be written to the database on a COMMIT WORK or a CLOSE. Is this how it works in real life? I attempted to check sqlca.sqlerrd[3] after the COMMIT WORK, but nothing showed up. I guess if I need to do so, I could go back to straight inserts or maybe a prepared insert. This way just looks so good, . . . . and it's fast. I'll be manipulating almost 6 millions rows, so speed is a factor also. It's just balancing speed with data integrity. . . . example code clipped . . . Thanks all, John Carlson Informix DBA WH Smith, Inc.