Does anyone else WANT TO KNOW how cursors really work?

[Breck_Carter]

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.

[Breck_Carter]

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.

[VolkerBarth]

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...

[Former Member]

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.

[Breck_Carter]

@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 🙂

[Breck_Carter]

@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

[Breck_Carter]

@Volker: You never write a FOR statement? If you do, you're writing a cursor fetch loop.

[VolkerBarth]

@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...

[Former Member · Accepted solution]

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:

• API (JDBC, ODBC, embedded SQL, OLEDB, ADO.NET, SOAP/HTTP, OpenClient)
• isolation level
• desired sensitivity to updates from other connections
• desired sensitivity to updates from the same connection
• prefetching
• use of bookmarks
• cursor updatability
• cursor scrollability
• wire protocol (SQL Anywhere's native CMDSEQ protocol versus TDS)

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.

[VolkerBarth · Accepted solution]

I have to contradict: Obviously, the answer for the original question is very short: "My name is Glenn, and I do.". - Besides that, your explanation is helpful, and I agree that improving/clarifying within the docs would be more useful than a separate document.

[VolkerBarth]

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:)

[VolkerBarth]

@Breck: No, please don't accept my answers - I get along "unbountied" well enough:)

[Breck_Carter]

@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.

[Breck_Carter]

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.

[VolkerBarth]

@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

[VolkerBarth]

@Breck: Are you about to test if your score can go below zero?

[VolkerBarth]

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:

INSENSITIVE clause

A cursor declared INSENSITIVE has its membership fixed when it is opened; a temporary table is created with a copy of all the original rows. FETCHING from an INSENSITIVE cursor does not see the effect of any other INSERT, UPDATE, or DELETE statement from concurrently-executing transactions, or any other update operations from within the same transaction. INSENSITIVE cursors are not updatable.

Maybe the following effect is the missing link:

When both an INSENITIVE cursor is requested and FOR UPDATE is specified, then obviously a value-sensitive cursor is used instead.

And a value-sensitive cursor would see the changes as specified in the first quoted statement for the FOR statement.

[Breck_Carter]

When INSENSITIVE and FOR UPDATE are used together, that should be a syntax error IMO.

[VolkerBarth]

@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).

[VolkerBarth]

@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."

[MCMartin]

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?)

[VolkerBarth]

Well, the commententar is free to do so (and will usually change the wording/format a bit, I guess).