SQL - How to return a value from a SELECT back to the web

Hi,

I have a stored procedure called from the web (Lotus Notes via ADO).

I am doing pagination via web.

I got it to work but I would like to know when I reach the EOF so I can
disable the Next button.

I am trying to do a record count and I believe that is where my issue is.

This is the error message I get in Lotus Notes when the stored procedure is
called:

Error message: IBMDA400 Command: SQL0440: Routine TEST_MB in *N not found
with specified parameters.
Cause . . . . . : A function or procedure with the specified name and
compatible arguments was not found. This could be due to an authorization
failure. Recovery . . . : Specify the correct number and type of
parameters on the CALL statement, CREATE OR REPLACE statement, or function
invocation. Try the request again.

---------------------------------------------------------------------------------------------------------------------------------------------------------------

Here is the Stored Procedure:

drop procedure
BPCSO/TEST_MB

create procedure
BPCSO/TEST_MB

( In
DEC(3,0),

InOut
DEC(3,0),

InOut
DEC(3,0),

Out
CHAR(01),

Out
CHAR(01),

Out
CHAR(02)

)

language
rpgle

parameter style
general

not
deterministic

READS SQL
DATA

result sets
1

external name 'BPCSO/PUR027WS(PUR027)'

---------------------------------------------------------------------------------------------------------------------------------------------------------------


Code:
**FREE

CTL-OPT NOMAIN OPTION (*SRCSTMT : *NODEBUGIO);

DCL-PROC PUR027 EXPORT;
DCL-PI PUR027 EXTPROC(*DCLCASE);
StartingRow PACKED(3:0);
NbrOfRows PACKED(3:0);
TotalRows PACKED(10:0);
TotalRows2 PACKED(10:0);
BOF CHAR(1);
EOF CHAR(1);
RSL CHAR(2);
END-PI;


EXEC SQL SELECT COUNT(*)
INTO :TotalRows2
FROM CDPL03;

//TotalRows = TotalRows2;


EXEC SQL Declare RSCURSOR cursor for
SELECT CDEPT, CDESC, ROW_NUMBER() OVER(ORDER BY CDESC, CDEPT) as ROWNUMBER
FROM CDPL03
ORDER BY CDESC, CDEPT
OFFSET (:StartingRow - 1) * :NbrOfRows ROWS
FETCH NEXT :NbrOfRows ROWS ONLY;


// Begin & End of File
IF StartingRow = 1;
BOF = '1';
EOF = '0';
ELSE;
BOF = '0';
EOF = '0';
ENDIF;

// Validate for SQL errors
IF SQLSTATE = '00000';
RSL = '00';
ELSEIF SQLSTATE = '02000';
RSL = '10';
ELSE;
RSL = '20';
ENDIF;


EXEC SQL Open RSCURSOR;

EXEC SQL SET RESULT SETS Cursor RSCURSOR;


RETURN;


END-PROC PUR027;


// To create the service program:
// CRTSRVPGM SRVPGM(BPCSO/PUR027WS)
// MODULE(BPCSO/PUR027W)
// SRCFILE(BPCSS/PURBNDF) SRCMBR(PUR027WB)


---------------------------------------------------------------------------------------------------------------------------------------------------------------


Can someone take a look and maybe see what I am doing wrong? I don't have
much experience with SQL. I don't know how to get the return value of an
SQL SELECT back to my web call.

Thank you