473,692 Members | 2,260 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Error in nested SQL Procedure

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
Nov 12 '05 #1
18 7640
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
Nov 12 '05 #2
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
Nov 12 '05 #3

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:3l******** *****@individua l.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
Nov 12 '05 #4

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:3l******** *****@individua l.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
Nov 12 '05 #5
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
Nov 12 '05 #6
"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.
Nov 12 '05 #7
"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.

Nov 12 '05 #8
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
Nov 12 '05 #9

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:3l******** *****@individua l.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
Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3267
by: WSeeger | last post by:
When creating a new class, is it encouraged to always include error handling routines within your LET and GET procedures? It's seems that most text books never seem to include much about error handling within classes. Just hoping to hear some programmer's thoughts on error handling.
0
2048
by: Chi | last post by:
This is a problem that has been nagging me for a while, and I cannot figure out how to best solve the problem: I have a stored procedure that returns multi-level "nested" XML and inline XDR. My goal is to have a .NET application read the XML into a DataSet using the inline schema, and the relations automatically created according to the nesting of the elements. My query in the stored procedure looks similar to this:
7
5673
by: Anthony Robinson | last post by:
Have been encountering an odd issue. Every now and again, certain packages of stored procedures just become invalid. I'm aware that dropping or altering an underlying table would render a package invalid, but we are doing no such thing... After banging my head on the wall for a bit I noticed that the two stored procedures that are experiencing this behavior are procedures that are called from within another procedure (they're not both...
0
2963
by: Dmitry Tolpeko | last post by:
Hello, Could anybody please explain me why DB2 z/OS 8.1 fails on the simple procedure below: Error: DSNHSMS2 LINE 12 COL 6 NESTED COMPOUND STATEMENTS NOT ALLOWED It failes on DECLARE EXIT HANDLER FOR SQLEXCEPTION
2
2347
by: Quinnie | last post by:
Hi, I have a homework assignment that I'm so confused and really need help with. Here's the description, any help would be appreciated. Thanks! Assume we have a statically-scoped language with nested procedures. That is, a procedure (or function) can contain local procedures (and functions). Procedures can be nested arbitrarily deep. The scoping rules for procedure names (i.e., the ability to call
2
36157
by: Scarab | last post by:
Hi,all, When I use following sql, an error occurs: insert into #tmprep EXECUTE proc_stat @start,@end There is a "select * from #tmp " in stored procedure proc_stat, and the error message is : Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42 An INSERT EXEC statement cannot be nested.
0
2443
by: jeethsu | last post by:
Hi, I have Java application running on Websphere Application Server 6.0 This application connects to Mainframe DB2 using CLI type 2 driver. The application uses websphere connection pool mechanism. I have stored procedures which are called from the application and one particular stored procedure call throws the below error: SystemErr R SQL exception: com.ibm.websphere.ce.cm.StaleConnectionException: CLI0108E Communication link...
0
1507
by: rgettman | last post by:
Hello, I'm attempting to use Pro*C to create a nested table and send that data to a stored procedure as a parameter. However, I'm getting a Pro*C compiler error that I'll describe below. I'm using Oracle 10.2 on Unix. 1. First I created a nested table type in the database: CREATE TYPE varchar2_list AS TABLE OF VARCHAR2(15); 2. Then I created the input file for "ott", called "acttest.typ":
0
1412
by: aravindalapat | last post by:
Hi All, I am facing an error when I try to invoke a remote nested stored proc. Please find the details below. 1) SP TEST1 is defined in DB2 instance DB2A. It is calling a stored procedure REMOTE1 which is defined in another instance DB2B. 2) Before actually calling REMOTE1, the calling stored procedure is connecting to DB2B instance using connect statement and the remote storedprocedure is called with the name...
0
8610
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8810
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8810
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7633
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6462
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5821
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4325
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2242
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1961
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.