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

[Brian Parkins <goodprophet.bp@xxxxxxxxx>]

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