DB2 Stored Procedure Problems

Note: please leave bug reports and feature requests on

DB2 Stored Procedure Problems

Postby Tarias » Thu Jun 11, 2015 8:38 am

Hello DBeaver Team

I have two problems by executing DB2 stored procedure
and I hope you can help me. I'm using DBeaver 3.4.1.

First problem:

There seems to be a problem of the input/output parameters.
If for example I execute this IBM stored procedure :

call sysproc.get_config(null,null,null,null,null,?,?)

I get a SQL -469 error (you can see it in the picture).
I tried different jdbc Drivers, but without any success.

If I execute the same statement in the db2 command window (clp) it works (you can see it in the next Picture).
I think the problem is that the parameter is defined as INOUT and not handled correctly.

Second problem:

DBeaver does not return the values of the output parameters, when a stored procedure is called.

For example I created this simple stored procedure:

CREATE OR REPLACE PROCEDURE
TEST.PROC( IN IN_DM char(2), OUT OUT_DM char(2))
SPECIFIC TEST.PROC
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN
SET OUT_DM = 'AB';
END;

If I execute "call TEST.PROC(null,?);" I get in DBeaver only the values of the Query and Updated Rows.

With db2cmd I also get the value of the output parameter. You can see the output in one of the pictures.

My last question is can you please change the input-mask of the stored-procedure parameter list.
For example if you call a stored-procedure with several ? as parameters,
you get a seperate window where you can fill out every parameter.

The problem is if you don't want to change anything of the parameters you have to hit several times
the OK button, because the cursor jumps from one input field to the next until the end.

I think it is better if the OK button means that you want to execute the stored procedure now.
You can already jump with TAB between the input fields.

Thank you very much

Best Regards
Attachments
DBeaver_DB2CMD_OUTPUT.jpg
DBeaver + DB2CMD Output
DBeaver_DB2CMD_OUTPUT.jpg (31.56 KiB) Viewed 55377 times
OK_STORED_PROCEDURE.JPG
OK stored procedure using db2cmd
OK_STORED_PROCEDURE.JPG (28.45 KiB) Viewed 55377 times
ERROR_STORED_PROCEDURE.JPG
Error stored procedure using DBeaver
ERROR_STORED_PROCEDURE.JPG (24.85 KiB) Viewed 55377 times
Tarias
 
Posts: 18
Joined: Wed May 13, 2015 8:05 am

Re: DB2 Stored Procedure Problems

Postby Serge » Sun Jun 28, 2015 1:20 pm

Currently DBeaver lacks support of stored procedures execution.
I'll increase priority of this feature, hopefully it will be added in one of next versions.
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: DB2 Stored Procedure Problems

Postby Tarias » Wed Jul 01, 2015 5:43 am

Thank you!

Best regards
Tarias
 
Posts: 18
Joined: Wed May 13, 2015 8:05 am

Re: DB2 Stored Procedure Problems

Postby Tarias » Tue Jul 28, 2015 6:45 am

Hi Serge

First thank you for the great support.

I'm using now DBeaver Version 3.4.3 but I have still problems
if I use stored procedure.

The example above (TEST.PROC) works now, but not the stored procedure (sysproc.get_config).
I get an SQL Error -469, which I already posted in one of the pictures.

I think I found the problem. The problem occurs if the procedure have INOUT parameters.
So I changed the procedure TEST.PROC a little:

CREATE OR REPLACE PROCEDURE
TEST.PROC( INOUT INOUT_DM char(2), OUT OUT_DM char(2))
SPECIFIC TEST.PROC
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN
SET OUT_DM = 'AB';
END;

If I call now the procedure in DBeaver I get a SQL -469.
With db2cmd I get the right output in picture "DB2CMD INOUT".

I think that DBeaver uses INOUT parameters in a wrong way.

Again thank you for your support.

Best Regards.
Attachments
DB2CMDINOUT.JPG
DB2CMD INOUT
DB2CMDINOUT.JPG (11.73 KiB) Viewed 55146 times
Tarias
 
Posts: 18
Joined: Wed May 13, 2015 8:05 am

Re: DB2 Stored Procedure Problems

Postby Serge » Tue Jul 28, 2015 8:59 am

Yes, it is a yet another bug in DBeaver. It will be fixed in the next version.
Thanks for report.
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: DB2 Stored Procedure Problems

Postby Tarias » Wed Jul 29, 2015 7:44 am

Thank you Serge
Tarias
 
Posts: 18
Joined: Wed May 13, 2015 8:05 am

Re: DB2 Stored Procedure Problems

Postby Serge » Wed Jul 29, 2015 11:24 am

I've included this fix in hotfix version 3.4.4 - you can download it right now.
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: DB2 Stored Procedure Problems

Postby Tarias » Tue Aug 04, 2015 10:56 am

Hi Serge

Sorry but the last fix does not solve the problem.
You can see the problem in the picture.
The same error also occurs with:

call sysproc.get_config(null,null,null,null,null,?,?)


Best Regards
Attachments
INOUT_PROBLEM.JPG
INOUT_PROBLEM.JPG (42.33 KiB) Viewed 55101 times
Tarias
 
Posts: 18
Joined: Wed May 13, 2015 8:05 am

Re: DB2 Stored Procedure Problems

Postby Serge » Wed Aug 12, 2015 8:52 am

Yes, apparently this issue is more complex than I thought. I'll need to parse SQL statement to be able to count number of actual parameters passed.
Hopefully the fix will be included in one of the next versions.
Thanks for info
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: DB2 Stored Procedure Problems

Postby Serge » Mon Sep 28, 2015 10:42 am

Please check version 3.5.0.
There were some fixes regarding stored procedures execution.
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Next

Return to Support



Who is online

Users browsing this forum: No registered users and 29 guests