SEO

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;

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;

<4.spam@mail.ru> wrote:
> 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

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?

4.spam@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/

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/