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
---------------------------------------------------------------------------------------