Re: Stored Procedures -- SYSTEMIDOTNET

Try creating this SQL procedure (with your own tables)

IN Parameter CustomerNumber Integer

This code throws an error CUR1 is not defined for the 1st line...
--------------------------------------
DECLARE CUR1 CURSOR FOR
select * from some_table where cusno = CustomerNumber;
OPEN CUR1;
SET RESULT SETS CURSOR CUR1;
---------------------------------------

-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mira, Antonio
Sent: Wednesday, June 10, 2009 10:20 AM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Stored Procedures

Hmmm...

I use navigator to create the procedures and have found no problems. What problems are you running into?

Thank you,

Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949

-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of ibm
Sent: Wednesday, June 10, 2009 11:00 AM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Stored Procedures

Thanks. I guess using Navigator doesn't really work...

I had to create the procedure from code:

Dim cmd As iDB2Command
cmd = _cn.CreateCommand
cmd.CommandText = "create procedure amflib.Test2 (" & _
"in customernumber integer) result sets 1 language sql " & _
"begin " & _
" declare c1 cursor for select jqglva,extca,coqty,tdate
from " & _
" amflib.mthacte where cusno=customernumber; " & _
" open c1; " & _
" set result sets cursor c1; " & _
"end"

cmd.ExecuteNonQuery()

-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx
[mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mira, Antonio
Sent: Wednesday, June 10, 2009 9:04 AM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Stored Procedures

I have found that this works for me..

SET PATH *LIBL ;

CREATE PROCEDURE MYLIB.SP_GETSVCC (
IN I_SITEID DECIMAL(3, 0) ,
IN I_ACCT DECIMAL(9, 0) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC COHPGMS.SP_GETSVCC
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
RDBCNNMTH = *RUW ,
SRTSEQ = *HEX
BEGIN DECLARE C1 CURSOR FOR SELECT I0NUO9 , I0CEK6 , I0NUPA FROM
CBI0REL1 WHERE I0NROV = I_SITEID AND I0CNBR = I_ACCT ; OPEN C1 ; END ;

COMMENT ON SPECIFIC PROCEDURE COHPGMS.SP_GETSVCC
IS 'Gets the service categories for an account' ;

COMMENT ON PARAMETER SPECIFIC PROCEDURE COHPGMS.SP_GETSVCC
( I_SITEID IS 'Site ID' ,
I_ACCT IS 'Account Number' ) ;

Thank you,

Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949

P Go Green! Print this email only when necessary. Thank you for helping
Time Warner Cable be environmentally responsible.

-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx
[mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of ibm
Sent: Wednesday, June 10, 2009 9:38 AM
To: .net use with the System i
Subject: [SystemiDotNet] Stored Procedures

Hello.

I'm trying to create a stored procedure in Navigator that will return a
result set. How do I define and return the cursor? Also, should I be
closing the cursor?

I have one IN param = CustomerNumber

This does not work for me...

DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR

select * from amflib.mthacte where cusno = CustomerNumber;

OPEN CUR1;

SET RESULT SETS CURSOR CUR1;

Thanks for any insight.