bg0l+@andrew.cmu.edu (Bruce E. Golightly) (04/13/90)
Once more into the breech, dear friends.
I got this from one of our developers today. Anybody got any bright ideas
on this one? The context is, I think, self-explanatory from the memo
I'm including here, but I'll sketch it out for practice.
The application has open a cursor. The set returned by the cursor used
to define the cursor is quite large. We wish to use the rows of that
set to define the criteria for performing delete operations on rows in
the data base. The WHERE qualification on the DELETE is moderately
elaborate.
***************FORWARDED MESSAGE BEGINS HERE***************************
Just a note to complete the discussion on updates or deletes
within a big cursor loop (ie. transaction).
There is no way out !
The "set autocommit" that i saw is only another way to write
"commit" after each sql statements, exactly what i wanted to
avoid since it closes my big cursor !
So, finally, i have to unload the table i want to scan through
with the big cursor into a file (!) so that i can read this
file line by line with normal c statements and do the updates
i want to other tables !!...
It seems very ironical to have all these data into nice tables
and have to use files to read them ? i really wonder why they
are doing this ?!...
**************** END FORWARDED MESSAGE **************************
###############################################################################
!
Bruce E. Golightly ! bg0l@andrew.cmu.edu (BeeGeeZeroEl)
Chief of Data Base Development ! (412)268-8560
Telecommunications Group !
Carnegie Mellon University ! UCC 117, 4910 Forbes Ave.
! Pittsburgh PA 15213-3890
Vice President, Western Penna IUA !
###############################################################################thomasr@cpqhou.UUCP (Thomas Rush) (04/13/90)
1) put the key values returned into an array, then FOR i = 1 TO array_size DO DELETE FROM table WHERE table.keycolumn = array[i] 2)break the big cursor up into smaller peices: SELECT ... WHERE keycolumn BETWEEN 0 and 100 FOREACH cursor DELETE FROM table WHERE keycolumn = cursor.value SELECT ... WHERE keycolumn BETWEEN 101 and 200 FOREACH cursor DELETE FROM table WHERE keycolumn = cursor.value Repeat as necessary. Can be put into a for loop for i - 1 to n do select ... where keycol between (i-1)*100 and i*100 foreace etc. 3) Informix's OnLine engine provides for "persistant cursors." Declare the driving cursor as persistant, and it doesn't get closed until you explicitly close it. Working with transactions does certainly take more work in situations like this. thomas rush uunet!cpqhou!thomasr compaq computer corporation their employee, deep in the hearth of texas not their opinions.