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.
midrange.com
