Re: INSERT cursors

On Fri, 21 Aug 1998 15:49:15 GMT, in article <35DD984B.43A4@bellsouth.net>, "Carlson@WHSmith" <carlson1@bellsouth.net> wrote: >Jonathan Leffler wrote: > >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. It's not wise to close the cursor or end the program without a flush for the puts you're doing. If return code is ok, flush commit work -- for i in databasix primenet ; do ; gburnore@$i ; done --------------------------------------------------------------------------- How you look depends on where you go. --------------------------------------------------------------------------- Gary L. Burnore | '۳'''''''ۺ''''''''''۳ | '۳'''''''ۺ''''''''''۳ DOH! | '۳'''''''ۺ''''''''''۳ | '۳ 3 4 1 4 2 '' 6 9 0 6 9 '۳ spamgard(tm): zamboni | Official Proof of Purchase ===========================================================================