Error in nested SQL Procedure

[Rhino]

I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows.

This simple-looking proc is giving me this error message when I try to build
it in the Development Center:

RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token
"<cursor declaration>" was found following "". Expected tokens may include:
"<SQL statement>". LINE NUMBER=18 SQLSTATE=42601

Here is the source code for the proc; line 18 is the line that begins
"declare cursor2 cursor".

-----------------------
CREATE PROCEDURE RHINO.CALLED01 (IN in_sex char(1), IN in_edlevel smallint)
language SQL
dynamic result sets 2
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN

declare cursor1 cursor with return to caller for
select empno, lastname, salary, bonus, comm
from emp
where sex = in_sex
and edlevel = in_edlevel
order by empno;

open cursor1;

declare cursor2 cursor with return to caller for
select count(*)
from emp
where sex = in_sex
and edlevel = in_edlevel;

open cursor2;

END
-----------------------

This proc compiled and ran fine before I added the second cursor. I can't
figure out what's wrong. The two top candidates for the cause are:
1. It is not permissible to have two result sets in an SQL proc. That seems
unlikely because the "dynamic result sets" clause permits any positive
integer, as far as I can tell from the manual.
2. My structure is off somehow: maybe I need to have intervening statements
of some kind between the two cursors. If so, what am I missing?

Can anyone tell me what's wrong with this procedure? I have tried googling
and didn't find anything useful and haven't seen anything that helped in the
manuals.

--
Rhino
---
rhino1 AT sympatico DOT ca
"There are two ways of constructing a software design. One way is to make it
so simple that there are obviously no deficiencies. And the other way is to
make it so complicated that there are no obvious deficiencies." - C.A.R.
Hoare

[Serge Rielau]

Rhino wrote:

I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows.

This simple-looking proc is giving me this error message when I try to build
it in the Development Center:

RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token
"<cursor declaration>" was found following "". Expected tokens may include:
"<SQL statement>". LINE NUMBER=18 SQLSTATE=42601
<snip> Can anyone tell me what's wrong with this procedure? I have tried googling
and didn't find anything useful and haven't seen anything that helped in the
manuals.

Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
....
END
CLOSE
....

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

Rhino wrote:

I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on
Windows.

This simple-looking proc is giving me this error message when I try to
build
it in the Development Center:

RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected
token
"<cursor declaration>" was found following "". Expected tokens may
include:
"<SQL statement>". LINE NUMBER=18 SQLSTATE=42601

<snip>

Can anyone tell me what's wrong with this procedure? I have tried
googling
and didn't find anything useful and haven't seen anything that helped
in the
manuals.

Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
...
END
CLOSE
...

PS: These rules are mandated by the SQL/PSM standard in an attempt to
keep teh language structure. I wonder whether that was too harsh.. maybe
they ought to be relaxed in favor of consumability.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Rhino]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3l*************@individual.net...

Rhino wrote:

I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows.
This simple-looking proc is giving me this error message when I try to build it in the Development Center:

RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=18 SQLSTATE=42601

<snip>

Can anyone tell me what's wrong with this procedure? I have tried googling and didn't find anything useful and haven't seen anything that helped in the manuals.

Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
...
END
CLOSE
...

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

You're absolutely right Serge; as soon as I made sure that both DECLARE
CURSOR statements preceded the two OPEN statements, the procedure compiled
and executed fine. Therefore, the structure of my code was incorrect.

Rhino

[Rhino]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3l*************@individual.net...

Serge Rielau wrote:

Rhino wrote:

I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on
Windows.

This simple-looking proc is giving me this error message when I try to
build
it in the Development Center:

RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected
token
"<cursor declaration>" was found following "". Expected tokens may
include:
"<SQL statement>". LINE NUMBER=18 SQLSTATE=42601

<snip>

Can anyone tell me what's wrong with this procedure? I have tried
googling
and didn't find anything useful and haven't seen anything that helped
in the
manuals.

Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
...
END
CLOSE
...

PS: These rules are mandated by the SQL/PSM standard in an attempt to
keep teh language structure. I wonder whether that was too harsh.. maybe
they ought to be relaxed in favor of consumability.

I've never seen the SQL/PSM standard so I'm not clear on what language
structure it envisions. All I can say is that the sequence of the statements
needed in SQL procedures is not particularly clear from the manuals. I'm
sure that the information in the manuals is accurate but it's not the
easiest information to read, or even find in some cases, and its not always
obvious how the information is to be applied.

For example, I just spent quite a few minutes trying to find the main syntax
diagram for an SQL stored procedure, which I've seen before, and finally
found it again under "Compound SQL (procedure)" in the SQL Reference. Now,
looking at it, I don't find it particularly obvious that all DECLAREs of
cursors must proceed the OPEN of _any_ of the cursors, although I suppose it
is implied by the information on this page.

Having said that though, I'm at a loss to give any concrete suggestions on
how to improve the manuals in this regard. I can't think of a more intuitive
title for the syntax diagram found on this page. I'm also not sure how to
express the information in the diagram so that it is more obvious that all
DECLARE CURSOR statements need to precede any OPEN of those cursors. Maybe
the technical writers at IBM can think of some better way if challenged to
do so?

Unfortunately, getting to the bottom of this ultimately simple problem has
taken me several hours and that is not a good use of my time; it should
have been easier to find the answer.

Rhino

[Serge Rielau]

Rhino wrote:

I've never seen the SQL/PSM standard so I'm not clear on what language
structure it envisions. All I can say is that the sequence of the statements
needed in SQL procedures is not particularly clear from the manuals. I'm
sure that the information in the manuals is accurate but it's not the
easiest information to read, or even find in some cases, and its not always
obvious how the information is to be applied. Appears you may be missing the instructions on how to read the synatx
diagram:
procedure-compound-statement:
<snip>--+---------------------------------+--------------------------> | .-----------------------------. |
| V | |
'---DECLARE-CURSOR-statement--;-+-'
--+--------------------------------+---------------------------> | .----------------------------. |
| V | |
'---| handler-declaration |--;-+-'

.----------------------------.
V |----SQL-procedure-statement--;-+--END--+-------+---------------| '-label-'

The DECLARE cursor syntax comes before "SQL-procedure statement"

Now, let's compare this to CREATE PROCUDURE focussing at the procedure
properties:
<snip>
.-LANGUAGE SQL-. .-EXTERNAL ACTION----.--*--+--------------+--*--+--------------------+--*------------> '-NO EXTERNAL ACTION-'
--+------------------------------+--*--------------------------> '-PARAMETER CCSID--+-ASCII---+-'
'-UNICODE-'
--| SQL-procedure-body |--------------------------------------><
Note that here the various elements are separtede by asterisk ('*').
The presence of an asterisk before and after elemenst means the elements
can occur in any order. SQL-Proceduer-body has no '*' after it, so it
has to be ate the end (which is natural in way).
The compound statement has no asterisks between the elements, so they
have to appear in that exact order (first variables of any flavor, then
crsors, then handlers then the main body)
Unfortunately, getting to the bottom of this ultimately simple problem has
taken me several hours and that is not a good use of my time; it should
have been easier to find the answer.

Duly noted. Maybe it would help to split out all the declares into a
"declare-section":

<compound-statement> ::= [<label>] BEGIN [ATOMIC] [<declare-section>]
[<stmt-section>] END [<label>] ??

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:Au4He.253

Unfortunately, getting to the bottom of this ultimately simple problem has
taken me several hours and that is not a good use of my time; it should
have been easier to find the answer.

Rhino

A few more example procedures in the sample directory would do the trick. A
sample program using a declared temporary table for the output cursor should
be included IMO.

[Mark A]

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:Au4He.253

Unfortunately, getting to the bottom of this ultimately simple problem has
taken me several hours and that is not a good use of my time; it should
have been easier to find the answer.

Rhino

A few more example procedures in the sample directory would do the trick. A
sample program using a declared temporary table for the output cursor should
be included IMO.

[Serge Rielau]

Mark A wrote:

A few more example procedures in the sample directory would do the trick. A
sample program using a declared temporary table for the output cursor should
be included IMO.

Samples is receiving special attention for Vnext. I'm hopeful that the
crowd will be pleased :-)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Rhino]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3l*************@individual.net...

Rhino wrote:

I've never seen the SQL/PSM standard so I'm not clear on what language
structure it envisions. All I can say is that the sequence of the statements needed in SQL procedures is not particularly clear from the manuals. I'm
sure that the information in the manuals is accurate but it's not the
easiest information to read, or even find in some cases, and its not always obvious how the information is to be applied.

Appears you may be missing the instructions on how to read the synatx
diagram:
procedure-compound-statement:
<snip>

>--+---------------------------------+-------------------------->

| .-----------------------------. |
| V | |
'---DECLARE-CURSOR-statement--;-+-'

>--+--------------------------------+--------------------------->

| .----------------------------. |
| V | |
'---| handler-declaration |--;-+-'

.----------------------------.
V |

>----SQL-procedure-statement--;-+--END--+-------+---------------|

'-label-'

The DECLARE cursor syntax comes before "SQL-procedure statement"

Now, let's compare this to CREATE PROCUDURE focussing at the procedure
properties:
<snip>
.-LANGUAGE SQL-. .-EXTERNAL ACTION----.

>--*--+--------------+--*--+--------------------+--*------------>

'-NO EXTERNAL ACTION-'

>--+------------------------------+--*-------------------------->

'-PARAMETER CCSID--+-ASCII---+-'
'-UNICODE-'

>--| SQL-procedure-body |--------------------------------------><

Note that here the various elements are separtede by asterisk ('*').
The presence of an asterisk before and after elemenst means the elements
can occur in any order. SQL-Proceduer-body has no '*' after it, so it
has to be ate the end (which is natural in way).
The compound statement has no asterisks between the elements, so they
have to appear in that exact order (first variables of any flavor, then
crsors, then handlers then the main body)

Thanks for the review ;-)

Unfortunately, getting to the bottom of this ultimately simple problem has taken me several hours and that is not a good use of my time; it should
have been easier to find the answer.

Duly noted. Maybe it would help to split out all the declares into a
"declare-section":

<compound-statement> ::= [<label>] BEGIN [ATOMIC] [<declare-section>]
[<stmt-section>] END [<label>] ??

That might be a bit clearer to me but it might confuse other people more
than the current approach. Maybe you need to set up a focus group to see
what most people find clearer ;-)

Personally, I'd like to see more use of internal links in the documents. If
I could click on 'SQL-variable-declaration' and see the
'SQL-variable-declaration' syntax diagram, perhaps in a popup window, I
think the information would be clearer. I wouldn't need to hunt for it in a
long syntax diagram and I wouldn't see any more than that one explanation:
sometimes, I miss the end of the statement in the diagram and mistakenly
blend two or more fragments together into one. Use of different background
colours behind each of the fragments would also make the exact extent of
each statement easier to read. For instance, on the Compound SQL (Procedure)
page, it would be helpful to see each of the following fragments with a
different background colour:
- procedure-compound-statement
- SQL-variable-declaration
- condition-declaration
- statement-declaration
- return-code-declaration
- handler-declaration
- specific-condition-value
- general-condition-value
- SQL-procedure-statement

Or, if different background colours are a problem - some people are
colour-blind after all - maybe putting each fragment in a separate box would
make the fragments easier to read without running into one another. I'm just
"thinking out loud" so you might have to fine-tune this idea a bit....

Rhino

[Rhino]

"Mark A" <no****@nowhere.com> wrote in message
news:i7********************@comcast.com...

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:Au4He.253

Unfortunately, getting to the bottom of this ultimately simple problem has taken me several hours and that is not a good use of my time; it should
have been easier to find the answer.

Rhino
A few more example procedures in the sample directory would do the trick.

A sample program using a declared temporary table for the output cursor should be included IMO.

Then, the challenge becomes to find the *right* example when you want it ;-)

Rhino

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3l*************@individual.net...

Samples is receiving special attention for Vnext. I'm hopeful that the
crowd will be pleased :-)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

What is Vnext?

[Serge Rielau]

Rhino wrote:

That might be a bit clearer to me but it might confuse other people more
than the current approach. Maybe you need to set up a focus group to see
what most people find clearer ;-) Reminds of the "Fraggles" song (german version).
It was something like: You can make is right for most but never all. But
if you don't like stuff it (actually it was about drilling a hole in
ones knee) ;-)
Personally, I'd like to see more use of internal links in the documents. If
I could click on 'SQL-variable-declaration' and see the
'SQL-variable-declaration' syntax diagram, perhaps in a popup window, I
think the information would be clearer. I wouldn't need to hunt for it in a
long syntax diagram and I wouldn't see any more than that one explanation:
sometimes, I miss the end of the statement in the diagram and mistakenly
blend two or more fragments together into one. Well there is a piece of "technology" I'd love to transfer from Informix.
They have these nice cross linked PDFs (unfortunately it lacks a backup
capability ...). When I was a co-op student in Alamaden I used to
scribble the page numbers next to the clauses.
Use of different background
colours behind each of the fragments would also make the exact extent of
each statement easier to read. For instance, on the Compound SQL (Procedure)
page, it would be helpful to see each of the following fragments with a
different background colour:
- procedure-compound-statement
- SQL-variable-declaration
- condition-declaration
- statement-declaration
- return-code-declaration
- handler-declaration
- specific-condition-value
- general-condition-value
- SQL-procedure-statement

Or, if different background colours are a problem - some people are
colour-blind after all - maybe putting each fragment in a separate box would
make the fragments easier to read without running into one another. I'm just
"thinking out loud" so you might have to fine-tune this idea a bit....

Well, CREATE TABLE certainly has gotten out of hand.

I'll pass your comments to ID.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Mark A wrote:

What is Vnext?

The next version of DB2. Or isn't that what you wanted to know? :-)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:zwdHe.587

Then, the challenge becomes to find the *right* example when you want it
;-)

Rhino

The aren't that many SQL procs in the sample directory, and going through
each of them was a help to me when I first started writing stored
procedures.

The other thing that was very useful was this book chapter (that can be
downloaded for free) on SP Exception Handling:
http://www-128.ibm.com/developerwork...p/splbook.html

IBM has published a couple of good books on Stored Procedures.

[Rhino]

"Mark A" <no****@nowhere.com> wrote in message
news:Qr********************@comcast.com...

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:zwdHe.587

Then, the challenge becomes to find the *right* example when you want it
;-)

Rhino

The aren't that many SQL procs in the sample directory, and going through
each of them was a help to me when I first started writing stored
procedures.

The other thing that was very useful was this book chapter (that can be
downloaded for free) on SP Exception Handling:

http://www-128.ibm.com/developerwork...p/splbook.html
IBM has published a couple of good books on Stored Procedures.

Thank you; those are excellent suggestions!

Rhino

[Serge Rielau]

Rhino wrote:

I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows.

This simple-looking proc is giving me this error message when I try to build
it in the Development Center:

RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token
"<cursor declaration>" was found following "". Expected tokens may include:
"<SQL statement>". LINE NUMBER=18 SQLSTATE=42601
<snip> Can anyone tell me what's wrong with this procedure? I have tried googling
and didn't find anything useful and haven't seen anything that helped in the
manuals.

Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
....
END
CLOSE
....

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Rhino]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3l*************@individual.net...

Rhino wrote:

I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows.
This simple-looking proc is giving me this error message when I try to build it in the Development Center:

RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=18 SQLSTATE=42601

<snip>

Can anyone tell me what's wrong with this procedure? I have tried googling and didn't find anything useful and haven't seen anything that helped in the manuals.

Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
...
END
CLOSE
...

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

You're absolutely right Serge; as soon as I made sure that both DECLARE
CURSOR statements preceded the two OPEN statements, the procedure compiled
and executed fine. Therefore, the structure of my code was incorrect.

Rhino

[Rhino]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3l*************@individual.net...

Serge Rielau wrote:

Rhino wrote:

I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on
Windows.

This simple-looking proc is giving me this error message when I try to
build
it in the Development Center:

RHINO.CALLED01: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected
token
"<cursor declaration>" was found following "". Expected tokens may
include:
"<SQL statement>". LINE NUMBER=18 SQLSTATE=42601

<snip>

Can anyone tell me what's wrong with this procedure? I have tried
googling
and didn't find anything useful and haven't seen anything that helped
in the
manuals.

Hmmm. look closely at the definition of compound statement.
It states that DECLARE cursor has to appear at teh beginning. The proc
body starts with the OPEN in your example.
You have to choices: Move the DECLARE up infront of the OPEN, or push a
new compound like this:
DECLARE
OPEN
BEGIN
DECLARE
OPEN...
...
CLOSE
...
END
CLOSE
...

PS: These rules are mandated by the SQL/PSM standard in an attempt to
keep teh language structure. I wonder whether that was too harsh.. maybe
they ought to be relaxed in favor of consumability.

I've never seen the SQL/PSM standard so I'm not clear on what language
structure it envisions. All I can say is that the sequence of the statements
needed in SQL procedures is not particularly clear from the manuals. I'm
sure that the information in the manuals is accurate but it's not the
easiest information to read, or even find in some cases, and its not always
obvious how the information is to be applied.

For example, I just spent quite a few minutes trying to find the main syntax
diagram for an SQL stored procedure, which I've seen before, and finally
found it again under "Compound SQL (procedure)" in the SQL Reference. Now,
looking at it, I don't find it particularly obvious that all DECLAREs of
cursors must proceed the OPEN of _any_ of the cursors, although I suppose it
is implied by the information on this page.

Having said that though, I'm at a loss to give any concrete suggestions on
how to improve the manuals in this regard. I can't think of a more intuitive
title for the syntax diagram found on this page. I'm also not sure how to
express the information in the diagram so that it is more obvious that all
DECLARE CURSOR statements need to precede any OPEN of those cursors. Maybe
the technical writers at IBM can think of some better way if challenged to
do so?

Unfortunately, getting to the bottom of this ultimately simple problem has
taken me several hours and that is not a good use of my time; it should
have been easier to find the answer.

Rhino