--
David Cornelius
CorneliusConcepts.com

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

> 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

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]

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

> 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