Using JTOPEN to call a Stored proc

Question

I need to call an AS400 stored proc via SQL using a JTOPEN (JDBC) connection which accepts 1 INOUT and 4 IN params. I am doing this with the following SQL

CALL MYLIB.MYSP( ? ,'AJAJA1', '11111111', 'ą, ę, ć, ł, ń, ś, ż, ź', '0')

but it throws an error

[SQL0313] Number of host variables not valid.

If I use '' instead of a question mark then I get

[SQL0469] IN, OUT, or INOUT not valid for parameter 1 in procedure MYSP in MYLIB.

Is this possible?

Answer 1

Since first parameter of the stored procedure is an INOUT parameter, you need to use a CallableStatement object, register the first parameter as an output parameter, and set value of the first parameter.

JTOpen has a JDBC client that uses reflection to call Java methods. Here is an example of calling a stored procedure where the first parameter is an inout parameter. (Note: The program generates a lot of noise because it tries to guess which method to call. If one method fails, it tries another. Also, the 12 in the registerOutParameter call is really java.sql.Type.VARCHAR)

~> java -cp jt400.jar com.ibm.as400.access.jdbcClient.Main jdbc:as400:SYSTENAME USERID PASSWORD  
>CREATE PROCEDURE MYCONCAT(INOUT P1 VARCHAR(512) , P2 VARCHAR(80), P3 VARCHAR(80), P4     VARCHAR(80))  LANGUAGE SQL BEGIN SET P1 = P1 || P2 || P3 || P4; END 

>!SETVAR CSTMT=CON.prepareCall("CALL MYCONCAT(?,'B','C','D')")
CSTMT=STMT0002
...
>!CALLMETHOD CSTMT.registerOutParameter(1,12)
...
>!CALLMETHOD CSTMT.setString(1,'A')
...
>!CALLMETHOD CSTMT.execute()
...
Call returned false
>!CALLMETHOD CSTMT.getString(1)
Call returned ABCD