z/OS stored procedure doesn't return value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

N61601@gmail.com - 04 Apr 2008 15:11 GMT

I'm running a java program on a PC which calls z/OS SQL stored
procedure. The stored procedure returns an integer which should
contain "2", but is always "0".

I (pre)compile the stored procedure from the PC using "IBM Data
Studio" which automatially refreshes the WLM proc.

Is there something simple I'm missing? Do I need to stop/start the
DB2 procedure?

Thank you in advance,
Serenity

Background -
Running UDB V9.5 on PC
Running DB2 v8.1 on z/OS 1.5

Java snippet -
cstmt =    con.prepareCall("CALL SCRATCHDB.SAMPLEPROCEDURE(?)");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.execute();
int AwCrap = cstmt.getInt(1);
System.out.println("SAMPPROC output - " + AwCrap);

Stored procedure -
CREATE PROCEDURE SAMPLEPROCEDURE
(OUT RETURN_THIS_DAMNIT INTEGER )
RESULT SETS 1
LANGUAGE SQL
EXTERNAL NAME SAMPPROC
FENCED
COLLID SCRATCHDB
WLM ENVIRONMENT WLMENV1
RUN OPTIONS 'NOTEST(NONE,*,*,*)'
BEGIN
        DECLARE RETURN_THIS_DAMNIT INTEGER DEFAULT 1;
        SET RETURN_THIS_DAMNIT = 2;
END

Running hte java program displays -
SAMPPROC output - 0

Reply to this Message

Knut Stolze - 07 Apr 2008 13:24 GMT

> I'm running a java program on a PC which calls z/OS SQL stored
> procedure. The stored procedure returns an integer which should
[quoted text clipped - 32 lines]
> BEGIN
> DECLARE RETURN_THIS_DAMNIT INTEGER DEFAULT 1;

This declaration here is shadowing the input parameter. You're sure you
want to do this?

> SET RETURN_THIS_DAMNIT = 2;
> END

Try this procedure:

CREATE PROCEDURE SAMPLEPROCEDURE (OUT return_this_damnit INTEGER)
LANGUAGE SQL
BEGIN
SET return_this_damnit = 2;
END@

This one works fine for me.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message