Geek Answers Handbook

How can I call a DB2 stored procedure with OUT parameters from SQuirreL SQL?

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

SOMESPROC call (12345 ,?);

+5
source share
7 answers

It seems that SQuirrel is currently not able to do this in DB2 AS / 400.

open source "SQL Workbench/J" (http://www.sql-workbench.net/) :

wbcall SOMESPROC(12345, ?);

"wbcall". ? .

. SQL Workbench/J DB2 IBM, SQL Workbench/J.

+4

Squirrel - . , out .

BEGIN
    DECLARE outParam INT;
    STORED_PROC_NAME(outParam);
END

, .

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

, ;. .

+3

DbVisualizer " SQL" SQL Commander, "?"

call SOMESPROC(12345, ?);
+2

, , :

CALL SomeSProc(12345)

, :

SELECT * FROM SomeSProc(12345)
0

, Squirrel 3.7 db2. , MY_PROC_TEST PROC_TEST.

squirrel > session > a > 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 @

:

CALL MY_PROC_TEST() @

0

Squirrel, ( ). , , , ...

(~). "", "". ...

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

0

squirrel > session > a > SQL: '#'

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

More articles:


All Articles