Re: Stored Procedures

["ibm" <ibm@xxxxxxxxxx>]

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.