Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
UPDATE immediately affects CURSOR |
Mon, Oct 13 2008 1:19 AM | Permanent Link |
"David Cornelius" | -- David Cornelius CorneliusConcepts.com |
Mon, Oct 13 2008 1:42 AM | Permanent Link |
"David Cornelius" | Let's try that again--without hitting OK too soon...
I have a cursor in a script that selects all records where a field is NULL. In the body of the WHILE NOT EOF(cursor) loop, I check some things and then update the record, setting that field to a valid value. I couldn't figure out why it was skipping records until I stepped through and watched local variables. The update would fire, then the FETCH NEXT statement would be run and immediately, the count of the cursor would decrement! The CURSOR must be acting more like a filtered table than a pre-selected list of records--which I thought the OPEN cursor statement was doing. But the FETCH is when the cursor record count changes. So instead of always doing a FETCH NEXT, I have to instead just call FETCH by itself, check to see if the ID (or some record identifying value) has changed. If it has, I know I'm on a new record, if it has not, then I know I need to call FETCH NEXT. I guess this is one of the subtle differences between a table-based UPDATE and a cursor-based UPDATE. Is this correct? -- David Cornelius CorneliusConcepts.com |
Mon, Oct 13 2008 3:03 AM | Permanent Link |
"David Cornelius" | > So instead of always doing a FETCH NEXT, I have to instead just call
> FETCH by itself, check to see if the ID (or some record identifying > value) has changed. If it has, I know I'm on a new record, if it has > not, then I know I need to call FETCH NEXT. OK--further testing revealed that even this does not work. Calling FETCH by itself does not update the cursor result set. Only (I guess) moving off the record by calling FETCH NEXT or FETCH FIRST. This cannot be right. -- David Cornelius CorneliusConcepts.com |
Mon, Oct 13 2008 3:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | David
What the "cure" is I don't know but I suspect that its a consequence of the way Tim handles sensitive result sets (ie equivalent to a table with a filter). You should (guessing here) be able to resolve it short term by using a non-sensitive result set for the checking and a different UPDATE cursor. Roy Lambert [Team Elevate] |
Mon, Oct 13 2008 2:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< OK--further testing revealed that even this does not work. Calling FETCH by itself does not update the cursor result set. Only (I guess) moving off the record by calling FETCH NEXT or FETCH FIRST. This cannot be right. >> Of course it's correct. A sensitive result set cursor acts just like a TEDBQuery component that returns a sensitive result set. If you update one of the rows so that it falls out of the result set filter criteria, then the current result set will change. That's why it is called a *sensitive* result set. It is sensitive to your changes and the changes of any other user or session. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Oct 13 2008 3:25 PM | Permanent Link |
"David Cornelius" | > Of course it's correct. A sensitive result set cursor acts just like
> a TEDBQuery component that returns a sensitive result set. If you > update one of the rows so that it falls out of the result set filter > criteria, then the current result set will change. That's why it is > called a sensitive result set. It is sensitive to your changes and > the changes of any other user or session. Why do I always find the spot in the manual I needed AFTER these postings? I had looked at OPEN, FETCH, SELECT, and UPDATE and could not figure out how to differentiate between sensitive and insensitve cursors or even if that's what my problem was. But somehow, your response triggered a different thought and I looked up SENSITIVE in the help (doh!) which revealed it's a how a cursor is DECLAREd, not opened or fetched that determines its sensitivity. -- David Cornelius CorneliusConcepts.com |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |