Naming Convention is set to SQL. Pat, your code executed ok in 'Run SQL Scripts'. How about doing it this way:
http://lk-tech.blogspot.com/2009/05/how-to-create-stored-procedures-using.html
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Pat Landrum
Sent: Wednesday, June 10, 2009 11:12 AM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Stored Procedures
You do have "BEGIN" before the declare statement. Also, and "END" after the sets results statement. The below will compile for me.
CREATE PROCEDURE ODBCLIB.SP_ATTAUDR (
IN INDATE CHAR(10) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC ODBCLIB.SP_ATTAUDR
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN <-----------
DECLARE VIEWIT CURSOR FOR SELECT * FROM LIBPL . PATTUPLOAD WHERE ATUDATE = INDATE ORDER BY ATUDIST , ATUSCHL , ATUTCHID , ATUCOURSE , ATUSECTION , ATUPERIOD , ATUDATE ;
OPEN VIEWIT ;
SET RESULT SETS CURSOR VIEWIT ;
END ; <--------------
With out the "BEGIN and "END" it will not compile. Received SQL State: 42601 and
Vendor Code: -104.
Message: [SQL0104] Token VIEWIT was not valid
Regards,
Pat Landrum
Senior Programmer/Analyst
Hanover County Public Schools
200 Berkley Street
Ashland, VA 23005
Email: plandrum@xxxxxxx
Phone: 804-365-4658 Fax: 804-365-4628
Never trust a computer you can't throw out a window - Steve Wozniak
Notice: This message or any accompanying documents may contain confidential or privileged information of Hanover County Public Schools. If you are not the intended recipient, disclosure, copying or distribution is strictly prohibited by state and federal law. If you received this message in error, please notify the sender as soon as possible.
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of ibm
Sent: Wednesday, June 10, 2009 11:28 AM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Stored Procedures
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
