Calling stored procedures with output parameters

[isc-solon]

I have a stored procedure that itself returns a value, as well as returning values for the 3rd and 4th arguments. DBeaver seems to understand this:

But when I try to call the stored procedure it doesn't recognize the difference between input arguments and return/output parameters. This doesn't work:
? = FCE_Operations.ATM_GetAmountOutAndFee(100,0.5,?,?)

Instead, DBeaver asks me to supply values for the 3 ?'s and I get java.lang.NullPointerException.

[serge-rider]

What is your database?
Could you post full SQL snippet (table DDL + SQL)?

(bug with NullPointerException will be fixed in the next version).

[isc-solon]

I am connecting to the Caché database using its JDBC driver. Everything works great: SELECT, DML, calling procedures, ER diagrams. The only thing that doesn't work is stored procedure return values, either as the return value of the procedure itself, or as OUT or INOUT parameters. So the snippet above is the only snippet that there is. I'm trying to execute this:
? = FCE_Operations.ATM_GetAmountOutAndFee(100,0.5,?,?)

FCE_Operations is not a table; it's a container for stored procedures only.

[serge-rider]

You could try this:

1. Disable anonymous parameters (Preferences->General->Editors->SQL Editor->SQL Processing). Thus DBeaver won't ask you to enter parameter value for every question mark.
2. Execute query as CALL FCE_Operations.ATM_GetAmountOutAndFee(100,0.5,?,?)

Unfortunately I can't test this because there is no Caché server in our test environment.

[isc-solon]

Thanks for the suggestion. Changing that preference and running as you suggested still doesn't work. I don't have to enter parameter values for the ?, but I get "Not all parameters bound/registered."

I'd like to try to separate this issue from any particular database. Can you write a simple stored procedure using any DB in your environment that takes two integers and returns their sum and difference, plus an extra string, and run that? The sql would looks something like this:
select (:op1 + :op2), (:op1 - :op2), 'fred' into :sum, :diff, :extra
op1 and op2 are the first 2 IN arguments. sum and diff are OUT arguments, and extra is the stored procedure's returned value. I want to call it like this:
? = Test.SumDiff(4, 5, ?, ?)
or this:
:a = Test.SumDiff(4, 5, :b, :c)

There should be a way for DBeaver to display the 3rd and 4th arguments and the return value of the procedure that are returned from the DB. This is not mission-critical for me, by the way. I've just seen this ability in other products.

[isc-solon]

I am interested in revisiting this issue. Here is an example of T-SQL that exhibits the problem. It doesn't require any tables. Run this statement while connected to MS SQL Server to create the procedure.

CREATE PROCEDURE storproc.test @a INTEGER, @b INTEGER, OUT @c INTEGER, OUT @d INTEGER
BEGIN
SET @c = @a - @b;
SET @d = @a + @b;
RETURN '1234';
END

Once created, right-click the storproc.test procedure and click Edit Procedure. You'll see a display very much like the screenshot in my original post for this issue: there's a RETURN, two INs, and two OUTs. Now right-click the procedure again and click Execute Stored Procedure. You get a SELECT statement that calls the procedure, and you can supply values for the @a and @b variables. When you run the procedure, it correctly returns 1234. But there's no way to get the @c or @d variables to return the results of the calculations. At least the SELECT returns 1234.

If you try to run the procedure using CALL or EXEC, you can't get the @c or @d variables, nor do you get the return value of 1234.

If you download WinSQL from Synametrics, and you create and run the same procedure, you'll see the following behavior. I think DBeaver should do this also.

After clicking Execute, you'll see this:

[isc-solon]

@tati-kru @serge-rider: Is it possible that this issue is not getting attention because it is still marked "can't reproduce"? Do you have MS SQL Server or Sybase so that you can recreate this behavior, using the example from my previous post?

[serge-rider]

Sorry, mostly it is not getting attention because of huge amount of new tickets.
However this one sounds reasonable for next releases. Moving to 5.1.5

[serge-rider]

I can't make it work in SQL Server Management Studio (it doesn't return output params).
Syntax { call ... } is supported only by jTDS driver, native MSSQL driver doesn't understand it.

Anyhow, this seems to be SQL Server specific. We'll have a huge improvement in 5.2.5 (see #810).
Will return to this ticket in 5.3.

[geuss]

Hi Serge, the same issue is present in Teradata Enviroment:

In the example above, "a" and "b" are output parameters. The complete error returned to the Result View is (one for each parameter):

org.jkiss.dbeaver.model.exec.DBCException: Errore SQL [301] [HY000]: [Teradata JDBC Driver] [TeraJDBC 16.00.00.32] [Error 301] [SQLState HY000] Invalid output parameter number: 1
at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCAbstractValueHandler.fetchValueObject(JDBCAbstractValueHandler.java:55)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetDataReceiver.fetchRow(ResultSetDataReceiver.java:122)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.fetchQueryData(SQLQueryJob.java:742)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:475)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:402)
at org.jkiss.dbeaver.model.DBUtils.tryExecuteRecover(DBUtils.java:1633)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:400)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:835)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2593)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:103)
at org.jkiss.dbeaver.model.DBUtils.tryExecuteRecover(DBUtils.java:1633)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:101)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:101)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: [Teradata JDBC Driver] [TeraJDBC 16.00.00.32] [Error 301] [SQLState HY000] Invalid output parameter number: 1
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:95)
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:70)
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:99)
at com.teradata.jdbc.jdbc_4.ParameterProperties.verifyOutParam(ParameterProperties.java:665)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.getInt(TDPreparedStatement.java:2886)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCCallableStatementImpl.getInt(JDBCCallableStatementImpl.java:335)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCResultSetCallable.getInt(JDBCResultSetCallable.java:89)
at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCNumberValueHandler.fetchColumnValue(JDBCNumberValueHandler.java:116)
at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCAbstractValueHandler.fetchValueObject(JDBCAbstractValueHandler.java:49)
... 13 more

Thanks!

[serge-rider]

Looks very similar to #4202 and #5068
Stored procedures invocation will be significantly improved in 5.3.4.

[artem-emelin]

Hi all, still getting this issue with the latest 5.3.5

[serge-rider]

@artem-emelin Please provide more info (what database it is and some SQL sample)

[h4diismanto]

hi all, maybe this will help.
am use Oracle database, Store procedure with out param cursor.

when i use DBEaver 5.3.3
call Store procedure with out param cursor, doesnt work.

when i use DBEaver 5.3.5
call Store procedure with out param cursor, work well.

[artem-emelin]

Sorry for delay, let me share more light.

1. Using version 6.0.1 with Oracle DB
2. DB procedure has 3 INPUT and 5 OUTPUT parameters.
3. Pick procedure from particular package, click right mouse button on it: http://joxi.ru/eAONwvJCxJe1w2
4. When I set input parameters and try to execute the stored procedure I get next: http://joxi.ru/EA4ZlVqCwbkdJm

[serge-rider]

@artem-emelin please create separate ticket, this is a different issue

[souravsaraf123]

I am facing the same issue as artem-emelin.
Since, I am seeing that there are similar issues like #917 and this one itself #2190, I dont know whether I should open a separate issue or not.

Do we have a solution for this?

[serge-rider]

@souravs-2pirad please create a separate ticket.

[isc-solon]

I'm using v6.0.2.201904071344, and I just wanted to report that this issue, for me, is almost fixed. When I run this statement: CALL FCE_Operations.ATM_GetAmountOutAndFee(100,0.5,?,?)
I get my two output parameters back!

The only remaining issues are 1) the false error message above, and 2) DBeaver has placed the return values in the wrong columns.

• 2.50 should be in the Fee column instead of the _isc_sp_ret_val column
• 48.75 should be in the AmountOut column instead of the Fee column
• Since I'm calling the procedure using CALL and not ? = the _isc_sp_ret_val column should be empty

As I pointed out in my original post, DBeaver has the correct metadata:

[serge-rider]

@joelsolon could you also make a separate ticket? It is impossible to track all issues related to plsql in a single ticket.

[liqili]

Same issue occurred in Version 7.3.0.202011291229 when executing ms-sql SP. It will execute straightly without asking to fill any parameters.

[3rdey3]

In dbeaver 7.3.1 I am getting the same error when calling procedure.

[serge-rider]

@liqili @3rdey3 Fixed in #10772.
Please test the fix in the Early Access version.