It's good to be INSENSITIVE

Here's something you may have heard from time to time:

Don't use cursors to make changes. Use set-oriented SQL statements instead.

But hey, sometimes you HAVE to write a COBOL-style program, one that uses an old-fashioned input-process-repeat loop to manipulate a result set one record, er, row at a time.

Sure, you can spend a couple of days figuring out how WINDOW works with UPDATE, or whether MERGE will work for your convoluted requirements... or you can spend a couple of hours writing a fetch loop.

If you do use a cursor, here's another slogan (this is the short form, the long form comes later):

Always make your cursors INSENSITIVE.

An insensitive cursor is a safe cursor, all other forms are spooky scary. If you like spooky scary, then fine, go ahead. But be prepared for never knowing if your code's going to work in all scenarios... well, you can never know that anyway, but with other kinds of cursors you can be pretty sure you WILL have problems, eventually.

Why is it good to be insensitive? Because an insensitive cursor is a stable cursor, it doesn't matter what goes on around it (in other connections, even the same connection), the result set is fixed when the loop starts. With other types of cursors the rules are fantastically complicated... and the rules change from release to release.

OK, you've got questions, "Always make your cursors INSENSITIVE" is really simplistic. Maybe the long form will help:

If you are going to INSERT, UPDATE or DELETE any of the tables involved in a cursor definition, either directly (your code inside the fetch loop) or indirectly (say, when your connection fires a trigger that makes such a change, or when some other connection, even an EVENT that your code fires, makes such a change), then

• always specify both INSENSITIVE and FOR READ ONLY, and

• never use WHERE CURRENT OF, always use UPDATE and DELETE statements with ordinary WHERE clauses.

If you end up having performance problems, then consider changing INSENSITIVE to something else. Most of the time (80%? 90%? 99%?) you won't have to, and life will be that much safer and easier.

Here's a SQL fetch loop template using the wonderful FOR loop syntax:

FOR [loop name] AS [cursor name] INSENSITIVE CURSOR FOR
SELECT t.primary_key_column_1_of_2  AS @primary_key_column_1_of_2,
       t.primary_key_column_2_of_2  AS @primary_key_column_2_of_2,
       t.[some other column]        AS @[some other column],
       t.[some other column]        AS @[some other column] 
  FROM t
 WHERE [some predicates]
 ORDER BY [some columns]
FOR READ ONLY
DO
   ...
   [references to the @variables implicitly declared in the SELECT]
   ...
   INSERT t ( [some column names] ) VALUES ( [some values] )
   ...
   UPDATE t
      SET t.[some other column] = [some value],
          t.[some other column] = [some value]
    WHERE t.primary_key_column_1_of_2  = @primary_key_column_1_of_2
      AND t.primary_key_column_2_of_2  = @primary_key_column_2_of_2;
   ...
   DELETE t
    WHERE t.primary_key_column_1_of_2  = @primary_key_column_1_of_2
      AND t.primary_key_column_2_of_2  = @primary_key_column_2_of_2;
   ...
END FOR;

---

Question: Isn't FOR READ ONLY redundant when you code INSENSITIVE? You can't use WHERE CURRENT OF with an INSENSITIVE cursor, can you?

Answer: Once upon a time (Version 7) INSENSITIVE cursors could be updatable. Besides, the rules for cursors change all the time, who knows what INSENSITIVE with FOR UPDATE might mean in the future... maybe at runtime it will morph into a value-sensitive cursor without telling you.

If you see the following statement in the Help for Version 8 or later, just ignore it, it's wrong (and it's being fixed):

INSENSITIVE ... It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

Let's beat the point to death, er, drive the point home... if you printed out all the Help files, here's how you could fix them:

Version 5 is OK: A cursor declared INSENSITIVE ... It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

Version 6 is OK: INSENSITIVE cursors ... It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

Version 7 is OK: INSENSITIVE cursors ... It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

Version 8: INSENSITIVE ... It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

Version 9: INSENSITIVE ... It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

Version 10: INSENSITIVE ... It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

Version 11: INSENSITIVE clause ... It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

Version 12: INSENSITIVE clause ... It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

---

More stuff...

Insensitive cursors
FOR statement
DECLARE CURSOR statement
Does anyone else WANT TO KNOW how cursors really work?