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

Thank you Bryan. That fixed he error.

I ran the debugger and it is now setting the row count in my variable, but
it will not return back to my web call.

It returns the Cursor, it return the RSL variable which I use the same
setting for TotalRows & TotalRows2.

How do I return the value back to my web call?

On Fri, Aug 3, 2018 at 10:40 AM, Brian Parkins <goodprophet.bp@xxxxxxxxx>
wrote:

The error message suggests you have a parameter mismatch, (which does
appear to be the case) between the CREATE PROCEDURE and the DCL-PI.

Brian


On 03/08/2018 13:44, M Black wrote:

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

--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD