467,189 Members | 1,330 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,189 developers. It's quick & easy.

set variables to be used in creation of result set.

I was wondering if there is a way to create a stored procedure that I
can
DECLARE Variables
then set these variables using a select statment -- up to here works.
then use a different statement to generate a result set.

I've been trying to do this with using a Cursor but I get an error that
I can't DECLARE a cursor after a SQL statement has been run.

I don't need it to be returned in a Cursor if there is another way. I
just need a result set.

Apr 18 '06 #1
  • viewed: 1637
Share:
10 Replies
el*********@gmail.com wrote:
I was wondering if there is a way to create a stored procedure that I
can
DECLARE Variables
then set these variables using a select statment -- up to here works.
then use a different statement to generate a result set.

I've been trying to do this with using a Cursor but I get an error that
I can't DECLARE a cursor after a SQL statement has been run.
Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.
I don't need it to be returned in a Cursor if there is another way. I
just need a result set.


Result sets and cursors are tied to each other.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Apr 18 '06 #2
thank you.

Apr 18 '06 #3
Knut Stolze <st****@de.ibm.com> wrote:
el*********@gmail.com wrote:
I was wondering if there is a way to create a stored procedure that I
can DECLARE Variables then set these variables using a select statment
-- up to here works.
then use a different statement to generate a result set.

I've been trying to do this with using a Cursor but I get an error that
I can't DECLARE a cursor after a SQL statement has been run.


Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.


Was this added in v8?

I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED"
as a build error when I tried that, but I'm using v7.

--
J. Moreno
Apr 25 '06 #4
J. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote:
el*********@gmail.com wrote:
I was wondering if there is a way to create a stored procedure that I
can DECLARE Variables then set these variables using a select statment
-- up to here works.
then use a different statement to generate a result set.

I've been trying to do this with using a Cursor but I get an error that
I can't DECLARE a cursor after a SQL statement has been run.

Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.


Was this added in v8?

I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED"
as a build error when I tried that, but I'm using v7.

DB2 for zOS presumably V7?
Try a dynamic cursor declaration, that should work.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 26 '06 #5
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote: -snip-
Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.
Was this added in v8?

I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED"
as a build error when I tried that, but I'm using v7.

DB2 for zOS presumably V7?


Yep.
Try a dynamic cursor declaration, that should work.


A stored procedure with this....
-- start here
BEGIN
DECLARE cursor1 INSENSITIVE SCROLL CURSOR WITH RETURN FOR
SELECT count(aColumn) FROM aTable;

-- Cursor left open for client application
OPEN cursor1;
END:
-- end here

compiles. Wrapping that in an extra begin/end gets the above error
message.

--
J. Moreno
Apr 26 '06 #6
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote: -snip- Wrap the DECLARE CURSOR statement in its own BEGIN ... END block.
Was this added in v8?

I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED"
as a build error when I tried that, but I'm using v7.

DB2 for zOS presumably V7?


Yep.
Try a dynamic cursor declaration, that should work.


A stored procedure with this....
-- start here
BEGIN
DECLARE cursor1 INSENSITIVE SCROLL CURSOR WITH RETURN FOR
SELECT count(aColumn) FROM aTable;

-- Cursor left open for client application
OPEN cursor1;
END:
-- end here

compiles. Wrapping that in an extra begin/end gets the above error
message.

Well, yes, that's what you said. Here is what I meant:

CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END
@

CALL P()
@
Result set 1
--------------

IBMREQD
-------
Y

1 record(s) selected.

Return Status = 0

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 27 '06 #7
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote: -snip nested compound statement not building inside stored procedure-
compiles. Wrapping that in an extra begin/end gets the above error
message.

Well, yes, that's what you said. Here is what I meant:


Thanks for the example, I think I understand now.
CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END


Doesn't build. LINE 14 COL 17 UNSUPPORTED DATA TYPE DISTINCT TYPE
ENCOUNTERED IN SQL VARIABLE STMT

Off to do some more googling...

--
J. Moreno
Apr 27 '06 #8
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:

-snip nested compound statement not building inside stored procedure-
compiles. Wrapping that in an extra begin/end gets the above error
message.

Well, yes, that's what you said. Here is what I meant:


Thanks for the example, I think I understand now.
CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END


Doesn't build. LINE 14 COL 17 UNSUPPORTED DATA TYPE DISTINCT TYPE
ENCOUNTERED IN SQL VARIABLE STMT

Off to do some more googling...

Try removing
DECLARE stmt STATEMENT;

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 27 '06 #9
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote: -snip-
CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END


Doesn't build. LINE 14 COL 17 UNSUPPORTED DATA TYPE DISTINCT TYPE
ENCOUNTERED IN SQL VARIABLE STMT

Off to do some more googling...

Try removing
DECLARE stmt STATEMENT;


That works. Thanks, I'll get the hang of this eventually.

--
J. Moreno
Apr 27 '06 #10
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. Moreno wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote: -snip- CREATE PROCEDURE p()
BEGIN
DECLARE var CHAR(1);
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?';
PREPARE stmt FROM txt;
SET var = 'Y';
OPEN cur USING var;
END
Doesn't build. LINE 14 COL 17 UNSUPPORTED DATA TYPE DISTINCT TYPE
ENCOUNTERED IN SQL VARIABLE STMT

Off to do some more googling...

Try removing
DECLARE stmt STATEMENT;


That works. Thanks, I'll get the hang of this eventually.

Me too. I just learned why for DB2 for LUW that DECLARE is optional.
What we do for compatibility with the elder brother... :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 28 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by yb | last post: by
37 posts views Thread by Joshua Ruppert | last post: by
4 posts views Thread by Jim Carlock | last post: by
58 posts views Thread by Jorge Peixoto de Morais Neto | last post: by
19 posts views Thread by Mitesh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.