7

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, ?);

KC Baltz
  • 1,498
  • 1
  • 13
  • 22

9 Answers9

7

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.

farbgeist
  • 101
  • 1
  • 3
  • Thanks, that seems to work well. I can't mark it as accepted because it doesn't technically answer the original question, but it's a useful answer so I've voted it up. – KC Baltz Aug 18 '16 at 18:24
  • 1
    @KC Baltz: Why is that not the right answer for the original question? The answer basically is: You can't. After that I just show an alternative solution as a bonus :-) – farbgeist Aug 20 '16 at 11:04
3

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.

Chrias
  • 39
  • 2
  • 1
    Does not work for me! Squirrel SQL: 3.5.0 Err message: Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=NUMBER;DECLARE outParam ;END-OF-STATEMENT, DRIVER=3.53.95 SQLState: 42601 ErrorCode: -104 Error occured in: DECLARE outParam NUMBER – Ajay Gautam Jun 11 '13 at 14:32
  • Agreed. With the Declare outside the BEGIN, it complains that NUMBER isn't expected. If I move it inside the BEGIN, it complains that BEGIN isn't expected. I changed the statement separator to # and didn't have luck either. I'm guessing your answer works for some DB other than DB2/400 – KC Baltz Jul 04 '14 at 18:15
  • I have made a number of fixes here: 1) the `DECLARE` needs to be inside the block. 2) `NUMBER` is only supported on DB2 in certain versions when compatibility is enabled. 3) The `/` after the statement is Oracle specific. You need to change the statement separator in Squirrel to run this. This should work for DB2 now. –  Dec 09 '15 at 06:35
  • I was able to get this to work by setting the separator to # and then adding "CALL" in front of the sproc name. However, I wasn't able to view the result set or the out params, so this is still not an effective solution for me. – KC Baltz Mar 17 '16 at 16:57
2

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, ?);
indybee
  • 1,507
  • 13
  • 17
1

through trial and error, I was able to see the results in Squirrel.

create or replace variable var4 char(1);
create or replace variable var5 decimal(3,0);
create or replace variable var6 char(60);
call getthedata('XXX',123456789,'1234567',var4,var5,var6);
select var4,var5,var6 from sysibm.sysdummy1;  -- displays OUT parms
Lefty Frizzell
  • 11
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 26 '22 at 06:46
0

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()@

ratm
  • 913
  • 1
  • 11
  • 20
0

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

user6828436
  • 1
  • 1
  • I just tried to get this to work and it's giving me an error, "Error: [SQL0101] SQL statement too long or complex. SQLState: 54001 ErrorCode: -101" . – KC Baltz Oct 09 '16 at 21:26
0

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)
Leslie
  • 3,604
  • 7
  • 38
  • 53
  • 1
    The problem is not getting the results. I think the last time I tried a sproc that didn't have any OUT parameters, the results came back just like a direct query. The issue is dealing with the OUT parameters. – KC Baltz Feb 21 '11 at 16:10
  • isn't an OUT parameter the result of the SP? – Leslie Feb 21 '11 at 16:28
  • No, they aren't the same thing. A sproc can produce output in three ways: via a resultset, an OUT parameter, and a third way that I'm going to call a return value (not sure the correct name). So you might have something like this: ?= GetListOfCheckedOutBooks( IN userID, OUT overdue). This might return some kind of flag for the first ?, a boolean in the OUT parameterindicating if the user has at least one overdue book, and a resultset of all the books they currently have checked out. – KC Baltz Feb 23 '11 at 18:22
0

I was able to cobble together some amalgamation of all of the above answers and came up with this which worked for me. I'm using Squirrel SQL 2018 connecting to an IBM AS/400 DB2 database. I did have to declare a statement separator, I used "#".

    BEGIN
        DECLARE success CHAR(1); -- output parameters
        DECLARE message CHAR(300);
        SET success = ' '; 
        SET message = ' ';
        CALL myProc('some', 'params', 4, success, message); 
        DECLARE GLOBAL TEMPORARY TABLE session.myTmp(s_res CHAR(1), m_res CHAR(300)) WITH REPLACE; 
        INSERT INTO session.myTmp(s_res, m_res) VALUES(success, message);
    END
    # -- <- statement separator needs to be set to something other than ";" in this case it's set to "#"
    SELECT * FROM session.myTmp;
Homer2029
  • 135
  • 1
  • 1
  • 7
-2

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

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