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.
Thanks for the comments. My personal opinion is that it would be difficult to write a whitepaper that tries to answer the original question completely ("Does anyone fully understand how cursors work?") since the topic is so complex. It would be difficult, if not impossible, to separate the notions of "cursors" from the various interfaces that SQL Anywhere supports, since it is specific APIs from these interfaces that determines server behaviour with respect to cursors. Here's a short list (and not exhaustive) of the wealth of parameters that impact cursor behaviour:
and adding to the complexity is that these interfaces - many of which are not under our control - are not static, but are enhanced over time (and features are deprecated to boot).
I would argue that the Version 12 documentation is considerably better on this subject than previous releases, but I would agree that it could be improved. For example, as described above, the documentation for insensitive cursors indicates that a keyset-driven (value-sensitive) cursor may be substituted if the cursor is opened read/write, and a warning is returned to the application. That is true for ODBC (the warning returned is SQLCODE +121, SQLSTATE 01S02) and the application can determine the cursor type that is now being used by issuing a SQLGetStmtAttr() call. However, no such API call is available with embedded SQL. Application profiling captures the cursor type, among other things, in the sa_diagnostic_cursor table but the Application Profiling gui does not format this information for user consumption at the moment - certainly something we can improve.
My team has been heavily involved in improving the documentation for cursors over the past several releases. We will consider developing a whitepaper along the lines suggested, but again my preference would be to include these details with the rest of the SQL Anywhere documentation.
answered 05 Oct '10, 14:34
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.
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:)
answered 02 Oct '10, 13:39
I would expect, that even if you could technically update an insensitive cursor it will not lead to the intended result. The insensitive cursor is using a copy of the result set, because of this it does not see any changes in the "current" data, it is even immune to rollbacks. So yes, I would support your opinion, that the two statements have a conflict.
By the way Glenns comment is already your answer. (Could a comment be changed to become an answer?)
answered 27 Sep '10, 13:09