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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.