Is there any purpose to the FOR for-loop-name and cursor-name fields?

[Breck_Carter]

There isn't any purpose, is there? 'cause if there WAS, then the following code wouldn't work (which it does)...

FOR f_pointless AS c_meaningless INSENSITIVE CURSOR FOR
SELECT row_num AS @row_num FROM RowGenerator WHERE row_num IN ( 1, 2 ) ORDER BY row_num
FOR READ ONLY
DO
   MESSAGE STRING ( 'RowGenerator.row_num = ', @row_num ) TO CONSOLE;
END FOR;

FOR f_pointless AS c_meaningless INSENSITIVE CURSOR FOR
SELECT row_num AS @row_num FROM RowGenerator WHERE row_num IN ( 1, 2 ) ORDER BY row_num
FOR READ ONLY
DO
   MESSAGE STRING ( 'RowGenerator.row_num = ', @row_num ) TO CONSOLE;
END FOR;

FOR f_pointless AS c_meaningless INSENSITIVE CURSOR FOR
SELECT row_num AS @row_num FROM RowGenerator WHERE row_num IN ( 1, 2 ) ORDER BY row_num
FOR READ ONLY
DO
   MESSAGE STRING ( 'RowGenerator.row_num = ', @row_num ) TO CONSOLE;
END FOR;

RowGenerator.row_num = 1
RowGenerator.row_num = 2
RowGenerator.row_num = 1
RowGenerator.row_num = 2
RowGenerator.row_num = 1
RowGenerator.row_num = 2

Can anyone say "COBOL"? 🙂

[VolkerBarth]

Why should the code fail? IMHO it does work because the loop name and cursor name are local to the FOR statement, and therefore they don't conflict with each other.

Don't know for COBOL, I'm too young:) (That's not the whole story, I surely had some lessons in that PL a long time ago...)

[Breck_Carter]

"local to the FOR statement"... I'm pretty sure you are correct BUT it isn't discussed in the docs AFAIK.

I grew up on PL/I which included all of COBOL+FORTRAN+ALGOL without any reserved words ( none, none at all, decent parsers don't need no steenking reserved words :)... ahhh, memories.

[VolkerBarth]

FWIW, I share some distant rememberances of PL/1 and FORTRAN, too:)

The "local scope" discussion however is tied to C++ for me: I remember the C++ standard changed the scope of loop variables in for-statements from being block-local to statement-local, i.e. you could now re-use the same loop variables in adjacent for-statements - the former behavour was often a PITA:) - I'm glad that SQL Anywhere does it right again:)

Some credits to Bruce, methinks:)

[Breck_Carter]

A little bird said, "Be careful trying to prove something using dbisql!"

...but I rarely pay attention to little birds, so I missed the fact that dbisql processes each statement (compound or otherwise) as a separate batch.

Watch what happens when dbisql is forced to send all the FOR statements to the server as one batch (which mimics how a stored procedure works with its own BEGIN END block):

BEGIN -- put all the FOR statements inside one block

FOR f_pointless AS c_meaningless INSENSITIVE CURSOR FOR
SELECT row_num AS @row_num FROM RowGenerator WHERE row_num IN ( 1, 2 ) ORDER BY row_num
FOR READ ONLY
DO
   MESSAGE STRING ( 'RowGenerator.row_num = ', @row_num ) TO CONSOLE;
END FOR;

FOR f_pointless AS c_meaningless INSENSITIVE CURSOR FOR
SELECT row_num AS @row_num FROM RowGenerator WHERE row_num IN ( 1, 2 ) ORDER BY row_num
FOR READ ONLY
DO
   MESSAGE STRING ( 'RowGenerator.row_num = ', @row_num ) TO CONSOLE;
END FOR;

FOR f_pointless AS c_meaningless INSENSITIVE CURSOR FOR
SELECT row_num AS @row_num FROM RowGenerator WHERE row_num IN ( 1, 2 ) ORDER BY row_num
FOR READ ONLY
DO
   MESSAGE STRING ( 'RowGenerator.row_num = ', @row_num ) TO CONSOLE;
END FOR;

END;

Could not execute statement.
Item 'c_meaningless' already exists
SQLCODE=-110, ODBC 3 State="42S01"
Line 1, column 1

---

So... the cursor-name has to be unique, for no apparent purpose. However, the for-loop-name can be anything; here's a test that works using w, w, w and x, y, z as names:

BEGIN -- put all the FOR statements inside one block

FOR w AS x INSENSITIVE CURSOR FOR
SELECT row_num AS @row_num FROM RowGenerator WHERE row_num IN ( 1, 2 ) ORDER BY row_num
FOR READ ONLY
DO
   MESSAGE STRING ( 'RowGenerator.row_num = ', @row_num ) TO CONSOLE;
END FOR;

FOR w AS y INSENSITIVE CURSOR FOR
SELECT row_num AS @row_num FROM RowGenerator WHERE row_num IN ( 1, 2 ) ORDER BY row_num
FOR READ ONLY
DO
   MESSAGE STRING ( 'RowGenerator.row_num = ', @row_num ) TO CONSOLE;
END FOR;

FOR w AS z INSENSITIVE CURSOR FOR
SELECT row_num AS @row_num FROM RowGenerator WHERE row_num IN ( 1, 2 ) ORDER BY row_num
FOR READ ONLY
DO
   MESSAGE STRING ( 'RowGenerator.row_num = ', @row_num ) TO CONSOLE;
END FOR;

END;

RowGenerator.row_num = 1
RowGenerator.row_num = 2
RowGenerator.row_num = 1
RowGenerator.row_num = 2
RowGenerator.row_num = 1
RowGenerator.row_num = 2

---

No, I don't go looking for trouble... I stepped on that SQLCODE -110 in a real stored procedure with a whole series of FOR loops... sassenfrassenfrickenfracken! 🙂

[VolkerBarth]

Did I say *"Breck, thanks for raising the question...":)

Yes, obviously I've stumbled again over a "test with dbisql" anti-pattern:(

So cursor names do not truly fit in my understanding of scope, as the docs on DECLARE CURSOR clearly state:

In SQL Anywhere, all cursors in a given scope must have unique names.

If I put the different FOR statements from your new sample in their own begin/end blocks (which would mean they should have "extra block scope), I still get that error message. That should not happen, as the docs also tell:

If a cursor is declared inside a compound statement, it exists only for the duration of that compound statement (whether it is declared in a Watcom SQL or Transact-SQL compound statement).

Or is that another pitfall of testing with dbisql?

[VolkerBarth]

I can't tell for the loop name, however, there is a usage for the cursor name, as it can be used for positioned updates/deletes, i.e. for

UPDATE foo SET bar = 1 WHERE CURRENT OF c_not_so_meaningless;

FWIW, the cursor name may be omitted - the loop will still work. That's not officially documented in the grammar but the [last sample on this doc page][1] obviously does work without one... and omitting the *c_meaningless* in your sample does work, too.

EDIT based on Breck's comment: The cursor name is required, the sample is irritating as it does use "SCROLL" as cursor name - "SCROLLY" would work, too. I've dropped a DCX note:)

Besides that, I share your impression: I'm used to name the loop and the cursor rather mechanically (say, with some kind of dummy names)...

[Breck_Carter]

If you remove "AS c_meaningless" you get a syntax error.

If you remove "c_meaningless" but leave the "AS", you probably get "INSENSITIVE" as the cursor name... even though in theory "insensitive" is a reserved word... that wouldn't be the first hole in the parser 🙂

[Breck_Carter]

I guess you've answered my real question, "I don't have to sweat the FOR loop and cursor names, do I? I can just use 'x' for both of them and be done with it, since they don't have scope outside the FOR loop itself."

For years I've worried about this, possibly confusing FOR with the rules for old-school DECLARE CRAPPY CURSOR.

[Breck_Carter]

"WHERE CURRENT OF"... doh!

Since I almost always code that as "WHERE CURRENT OF CURSOR" and have to go back and put in the name, it should be burned into my brain 🙂

OTOH some folks remember pain, others not, and I'm one of the "nots".

[VolkerBarth]

EDIT: According to Breck's further testing, this answer seems fine but does make wrong assumptions about what is tested when using dbisql: It simply sends each statement as a separate batch making my test case misleading... and therefore the statement to cursor-name re-use is apparently wrong. Handle with care!

As to your underlying question: "Can I re-use loop names and cursor names for subsequent FOR statements?" - the docs do answer that - cf. that excerpt from the "Remarks" section:

The FOR statement is equivalent to a compound statement with a DECLARE for the cursor and a DECLARE of a variable for each column in the result set of the cursor followed by a loop that fetches one row from the cursor into the local variables and executes statement-list once for each row in the cursor.

So each FOR statement is a compound statement in itself and has its own block scope, and names won't conflict with each other.

That handling does it permit to use

• FOR statements anywhere in a compund statement (as long as after all DECLARE statements) and
• FOR statement outside of a procedure/trigger/SQL batch - as in your sample. The same would not work with DECLARE/OPEN CURSOR/FETCH as that "old-school approach" requires an enclosing compound statement.

If you've got the impression the docs could be more elaborate here, I'd second that:)

---

And no, I wasn't aware of that particular feature of the beloved FOR statement, either - thanks for raising the question, Breck!

[Breck_Carter]

The docs are probably talking about the alias names on the SELECT list being local to the loop. IMO the cursor-name SHOULD also be local because, AFAIK, the cursor does not exist before the FOR and it does not persist after the END FOR... but it's not local, as SQLCODE -110 will attest 🙂

[VolkerBarth]

FWIW, Ivan has documented the required uniqueness of cursor names (independent of their scope) within one single processed statement in DCX:

DECLARE CURSOR statement [ESQL] [SP]