The Result set is already returned (and open!).
ALLOCATE positions the Cursor.
OPEN is NOT needed, and may be not even allowed.
Did you try to define the receiving Data Structure as Array data structure
(keyword DIM) instead of an MODs (OCCUR)
Looks like one of my examples :)
Receiving/Returning all columns is never a good idea
In either way creating a VIEW or UDTF and selecting only the necessary data
would be a better solution.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Bruce
Collins
Gesendet: Tuesday, 22.3 2016 23:14
An: RPG400-L@xxxxxxxxxxxx
Betreff: SQL0504 when returning a result set.
Guys, I have been pulling my hair out with this and I hope you can help
me with it.
Below is the code to an SQLRPGLE program and a stored procedure.
The program calls the Stored Procedure pass First and Last Name. The SP
returns a result set. I am getting an SQL error SQL0504 Stating that I have
not declared the cursor but it gets declared in the ALLOCATE statement. See
below highlighted in yellow.
Thanks in advance for the help.
Bruce "Hoss" Collins
Wallace Community College
Director, MIS
1141 Wallace Drive
Dothan, AL 36303
Phone 334-556-2447
1-800-543-2426, Ext. 2447
bcollins@xxxxxxxxxxx
// This program will call a stored procedure passing two
parameters"First Name" and "Last Name".
// The stored procedure well return a single result set
// Into a multiple occurance data structure.
Ctl-Opt Dftactgrp(*NO) Actgrp(*NEW;
Ctl-Opt Option(*NoDebugIO : *SrcStmt : *ShowCpy);dsprt2) usropn;
Dcl-s MySelect varchar(1024);
Dcl-S V_Db2_Sql_AttR_CURSOR_TYPE Char(1);
Dcl-S V_Db2_Number_ROWS Int(10);
Dcl-S V_Row_Count_SUMMARY Int(10);
Dcl-S V_Row_Count Int(10);
Dcl-S MyResultSet SQLTYPE(RESULT_SET_LOCATOR);
Dcl-Ds AD01DS Extname('AD01') Occurs(100) Qualified;
END-DS;
// *** Note ADFNAME and ADLNAME are from a
file that I did not include for this posting.
// ADFNAME is CHAR(20) and ADLNAME is
CHAR(30)
//
// uspGetMatch() is an SQL Stored
Procedure that I will include that the bottom of this code
//
SQLCODE = *Zero;
SQLSTATE = '00000';
EXEC SQL CALL uspGetMatch(:ADFNAME, :ADLNAME); //Stored Procedure
If SQLCODE <> 466;
// do something here if no result set returned.
Endif;
Exec SQL
ASSOCIATE RESULT SET LOCATORS
(:MyResultSet)
WITH PROCEDURE uspGetMatch;
Exec SQL
ALLOCATE MYCURSOR CURSOR
FOR RESULT SET :MyResultSet;
Exec SQL
Open MYCURSOR;
SQL0504: Position 23 Cursor MYCURSOR not declared.
If SQLcode =
0;
Exec SQL Fetch next from MYCURSOR for 100 rows into :AD01DS;
Exec SQL get diagnostics :v_Row_Count = ROW_COUNT;
Endif;
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////
///// Store Procedure Code
////////////////////////////////////////////////////////////////////////////
/////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////
-- Generate SQL
-- Version: V7R1M0 100423
-- Generated on: 03/21/16 05:42:36
-- Standards Option: DB2 for i
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","DATACUST" ,"ACCCUST";
DROP PROCEDURE ACCCUST.uspGetMatch;
CREATE PROCEDURE ACCCUST.uspGetMatch (
IN FNAME CHAR(20) ,
IN LNAME CHAR(30) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC ACCCUST.uspGetMatch
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
PROGRAM TYPE SUB
ALLOW DEBUG MODE
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN ATOMIC
DECLARE MYFNAME CHAR(22);
DECLARE MYLNAME CHAR(32);
SET MYFNAME = '%' || TRIM(FNAME) || '%';
SET MYLNAME = '%' || TRIM(LNAME) || '%';
BEGIN
DECLARE CURSOR1 CURSOR WITH
RETURN FOR select * from data400.ad01 where DATA400.AD01.AD01_REVERSE_NAME
like FNAME AND DATA400.AD01.AD01_REVERSE_NAME like LNAME;
OPEN CURSOR1 ;
END;
END;
COMMENT ON PARAMETER SPECIFIC PROCEDURE uspGetMatch ( FNAME IS 'First Name'
,
LNAME IS 'Last Name' ) ;
LABEL ON SPECIFIC PROCEDURE uspGetMatch
IS 'Get Potential Match' ;
----------------------------------------------------------------------------
-----------
This email has been scanned for email related threats and delivered safely
by Mimecast.
For more information please visit
http://www.mimecast.com
----------------------------------------------------------------------------
-----------
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
