By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,275 Members | 1,498 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,275 IT Pros & Developers. It's quick & easy.

cursor in procedure

P: n/a
Hello Folks,

I tried to create a stored procedure on the AS/400 - but it doesn't work ;o(

At first I will validate a specific value by a Param-value and after that
update it.
At least I want to return a resultset. But the DB2 tells me "[SQL0104] Token
C1 not valid"

When I compile the sp without the both first querys and without the
if-statement
or without the courser - it still works and it cannot be a syntaxerror.

I still guess that coursers only work as standalone!? Hopefully not!
Perhaps I could write i into two SP, but I want it in one SP.

Who can help me?
Regards - Bas
#### SOURCE ###########################################

CREATE PROCEDURE myfunc(in iUSTID integer)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

BEGIN
DECLARE iUSRID integer;

select USRID into iUSRID
from session
where USTID = iUSTID
and CTRLSTATUS = 1
and SESS_START between CURRENT TIMESTAMP-1 MINUTE and CURRENT
TIMESTAMP;

update session
set SESS_END = CURRENT TIMESTAMP,
CTRLSTATUS = 0
where USTID = iUSTID;

if iUSRID>0 then
declare c1 cursor for
select USRID, NAME
from user
where USRID = iUSRID;
OPEN c1;
end if;
END;
Sep 20 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Hello.
Try this:

#### SOURCE ###########################################

CREATE PROCEDURE myfunc(in iUSTID integer)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

BEGIN
DECLARE iUSRID integer;

declare c1 cursor with return for
select USRID, NAME
from user
where USRID = iUSRID;

select USRID into iUSRID
from session
where USTID = iUSTID
and CTRLSTATUS = 1
and SESS_START between CURRENT TIMESTAMP-1 MINUTE and CURRENT
TIMESTAMP;

update session
set SESS_END = CURRENT TIMESTAMP,
CTRLSTATUS = 0
where USTID = iUSTID;

if iUSRID>0 then
OPEN c1;
end if;
END;

Sincerely,
Mark B.
Hello Folks,

I tried to create a stored procedure on the AS/400 - but it doesn't work ;o(

At first I will validate a specific value by a Param-value and after that
update it.
At least I want to return a resultset. But the DB2 tells me "[SQL0104] Token
C1 not valid"

When I compile the sp without the both first querys and without the
if-statement
or without the courser - it still works and it cannot be a syntaxerror.

I still guess that coursers only work as standalone!? Hopefully not!
Perhaps I could write i into two SP, but I want it in one SP.

Who can help me?
Regards - Bas
#### SOURCE ###########################################

CREATE PROCEDURE myfunc(in iUSTID integer)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

BEGIN
DECLARE iUSRID integer;

select USRID into iUSRID
from session
where USTID = iUSTID
and CTRLSTATUS = 1
and SESS_START between CURRENT TIMESTAMP-1 MINUTE and CURRENT
TIMESTAMP;

update session
set SESS_END = CURRENT TIMESTAMP,
CTRLSTATUS = 0
where USTID = iUSTID;

if iUSRID>0 then
declare c1 cursor for
select USRID, NAME
from user
where USRID = iUSRID;
OPEN c1;
end if;
END;
Sep 20 '06 #2

P: n/a
<4.****@mail.ruwrote:
Hello.
Try this:

#### SOURCE ###########################################
<SNIP />

Thank you - it works!

But can you tell me why I have to set up the cursor at the top?
Is it caused on to set a declare at the top of a sp?

Bas
Sep 20 '06 #3

P: n/a
Check the strucure description of sql compound statement at
http://publib.boulder.ibm.com/infoce...mpoundstmt.htm

But can you tell me why I have to set up the cursor at the top?
Is it caused on to set a declare at the top of a sp?
Sep 20 '06 #4

P: n/a
4.****@mail.ru wrote:
Check the strucure description of sql compound statement at
http://publib.boulder.ibm.com/infoce...mpoundstmt.htm

>But can you tell me why I have to set up the cursor at the top?
Is it caused on to set a declare at the top of a sp?
You may be able to do what you want by wrapping the DECLARE and OPEN
into it's own block:
.... THEN BEGIN
DECLARE ...
OPEN ...
END

Not sure if DB2 iSeries supports nested compounds.
keep in mind that cursors opened in a nested compound are implicitly
closed when their scope is left unless they are defined WITH RETURN

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 20 '06 #5

P: n/a
Yes, iSeries (at least V5R3) supports nested compounds.
You may be able to do what you want by wrapping the DECLARE and OPEN
into it's own block:
... THEN BEGIN
DECLARE ...
OPEN ...
END

Not sure if DB2 iSeries supports nested compounds.
keep in mind that cursors opened in a nested compound are implicitly
closed when their scope is left unless they are defined WITH RETURN

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 20 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.