|
The original title was "Poll: Does anyone fully understand how cursors work?" but that really wasn't what I wanted to know. The problem is this: The runtime operation of cursors in SQL Anywhere is governed by an extremely complex and ever-changing set of rules, and I claim that no one (outside a small number of SQL Anywhere engineers, and I have my doubts there) fully understands every single one of those rules. I certainly don't, and I have never met anyone who does (outside of Sybase that is, and again with the doubts about that). Certainly, the documentation is incomplete, and it does not answer important questions like, "How do I tell when SQL Anywhere has silently decided to do something different from what I told it to do?" with respect to sensitivity and other issues. That is just an example! Please don't focus on that single ""How do I..." question, but on the larger topic: the lack of a large, coherent, well-written chapter on cursor semantics. No need to cover earlier versions, just do Version 12. For the record, here is the original question... Title: Does anyone fully understand how cursors work? "Cursor Semantics" is another way of saying "The Devil Is In The Details"... at least, that's the way it seems to me. For example, here is a comment posted on the Help website at http://dcx.sybase.com/index.html#1200en/dbreference/for-statement.html: There seems to be a conflict between this statement: "INSENSITIVE clause - A cursor declared INSENSITIVE ... does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor. and this one: "Insensitive and asensitive cursors are not updatable." Aren't UPDATE/INSERT WHERE CURRENT disallowed when a cursor is not updatable? FWIW cursor semantics are a complete mystery to me... the more I study, the less I understand. |
|
Note: This answer does not respect the "poll" characteristics of the question... I agree that the quoted statements for insensitive cursors w.r.t. the FOR statement seem contradictory, and I guess the first statement is wrong. The corresponding description for the more general DECLARE CURSOR statement definetely states that insensitive cursors don't get any updates:
Maybe the following effect is the missing link:
And a value-sensitive cursor would see the changes as specified in the first quoted statement for the FOR statement. When INSENSITIVE and FOR UPDATE are used together, that should be a syntax error IMO. @Breck: It's seems to be one of the mis-leading features where the engine is smarter than wanted (just as the nasty ADD FOREIGN KEY with forgetting to put parantheses around the column name and as such, automatically creating a new column, cf. the comments to http://sqlanywhere-forum.sap.com/questions/1013). @Breck: Otherwise, there is a (possibly not so obvious) statement that the requested cursor semantics are a kind of hint, and the engine tries to fulfill that, as quoted from a different DCX page (cf. http://dcx.sybase.com/index.html#1200en/dbprogramming/types-sqlapp.html): "When you request a cursor of a given type, SQL Anywhere tries to match those characteristics. - There are some occasions when not all characteristics can be supplied." |
|
As to your re-edited question: YES! I would welcome a whitepaper on these topics, not only since the docs seem somewhat contradictory or incomplete. Though I still would argue (see my comments on the question) that I have used cursors for years without too much understanding and too much trouble. Obviously I haven't tried to extend their usage beyond quite simple code... But in the end, I still wait for one of those highly skilled iAnywhere engineers to share their knowledge here:) @Breck: No, please don't accept my answers - I get along "unbountied" well enough:) @Volker: My experience is like yours: Lots of FOR loops, occasional DECLARE FETCH loop which SEEM to work OK... but there are very real problems with multi-connection conflicts that lurk below the surface, with symptoms than can (almost always?) go unnoticed. With great power (FOR loops) comes great responsibility... to understand every aspect of how your tools work. re bounty: In THIS environment (SQLA versus StackOverflow) folks seem embarrassed by bounties... which is what makes it fun for me! :)... but no worries, mate, I've already embarrassed you. @Breck: I fully agree - and I remember having been very surprised by the WITH HOLD nature of FOR loops - undocumented until v12 and disclosed by Bruce here: http://sqlanywhere-forum.sap.com/questions/510#511 @Breck: Are you about to test if your score can go below zero? |
FWIW I am NOT criticising the Help team... by far the WORST chapter in my book is the one on cursors so the "bricks in glass houses" rule applies.
Counter argument: Does anybody (except the fine iAnywhere folks, of course!) NEED to fully understand how cursors work? - Frankly, I have tried for years not to need to do so...
I would appreciate a pointer to the first statement that you listed above:
INSENSITIVE clause - A cursor declared INSENSITIVE ... does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.
as this is old text from a time when INSENSITIVE cursors were updateable. They are no longer.
@Glenn: Scroll up, just a teeny bit, see the link to DCX? ...yeah, I know, you're reading this one of them there teeny tiny smartphones :)
@Glenn: Sorry for the sarcasm, here's the link to the Version 12 FOR Statement help topic: http://dcx.sybase.com/index.html#1200en/dbreference/for-statement.html
@Volker: You never write a FOR statement? If you do, you're writing a cursor fetch loop.
@Breck: I preferably use FOR loops (they are so much easier to use than DECLARE CURSOR...), and I do have an understanding of cursors (i.e. I take care of semantics like insensitive/value-sensitive - usually more in ODBC terms like snapshot/keyset and the like). But a full understanding? So far I have not needed that...