How can I call a DB2 stored procedure with OUT par

问题:

I really like SQuirreL SQL as a SQL query tool, but I've never been able to get it to call stored procedures in our AS/400 DB2 database. I always get the error "The number of parameter values set or registered does not match the number of parameters." I've double-checked the number of params and had no luck. This is the syntax I've tried for a procedure that takes one IN and one OUT:

call SOMESPROC(12345, ?);

回答1:

It seems that SQuirrel currently is not capable of doing that on AS/400 DB2.

Using the open source "SQL Workbench/J" (http://www.sql-workbench.net/) I was able to call a procedure:

wbcall SOMESPROC(12345, ?);

It has its own command for calling a procedure "wbcall". Use ? for out parameters.

Note: While installing SQL Workbench/J make sure to download the right DB2 driver from IBM and also add the licence file while adding the driver inside SQL Workbench/J.

回答2:

In Squirrel you can use something like this. You'll want to make sure the type of the declared variable matches the type of your out parameter in the stored procedure.

BEGIN
    DECLARE outParam INT;
    STORED_PROC_NAME(outParam);
END

If you also need to provide input for the procedure you could do this.

BEGIN
    DECLARE outParam INT;
    STORED_PROC_NAME('input', outParam);
END

You also need to change the statement separator to something other than ;. Otherwise it will break up the statement and try to send each piece individually.

回答3:

In the pro version of DbVisualizer, with the "Process Parameter Markers in SQL" under the SQL Commander menu option enabled, it will allow the "?" param

call SOMESPROC(12345, ?);

回答4:

I would think that if there is one in then the call should be:

CALL SomeSProc(12345)

to get a result maybe try:

SELECT * FROM SomeSProc(12345)

回答5:

Here is an tested example which works on Squirrel 3.7 with a db2 stored procedure . The trick is to passe with an transitional stored procedure MY_PROC_TEST to call the real stored procedure PROC_TEST.

change statement separator in squirrel > session > session properties > SQL : @

DROP PROCEDURE MY_PROC_TEST()@
CREATE PROCEDURE MY_PROC_TEST()
RESULT SETS 1 -- out resultset (call product)
LANGUAGE SQL
BEGIN
  DECLARE flag SMALLINT; -- out parameter
  CALL MY_PROC('2015', flag);
END @
CALL MY_PROC_TEST()@
END @

Then you can call the sored procedure like this :

CALL MY_PROC_TEST()@

回答6:

This will work in Squirrel if you change the delimiter (as specified above). However, to see what the variable is, you need to do the following...

In my example, I will set the delimiter to a tildy (~). Include after last "end", before "select". Code begins here...

begin
declare inoutParm numeric(2,0);
call spMyStoredProcedure(
             1234567                                     
           , inoutParm                                           
       );
declare global temporary table session.myTempTbl  
       (MyResult   char(1024) )                                         
with replace ;
insert into session.myTempTbl
  (myResult)
   values(inoutParm) ;  
end
~
select myResult from session.myTempTbl

---

Mic Keeley as400(db2) SQL Developer

回答7:

change statement separator in squirrel > session > session properties > SQL : '#'

BEGIN
    DECLARE inOutParam varchar(200);
    set inOutParam =  'a value';
    STORED_PROC_NAME(outParam);
END;
#