0

what is proper way to call stored procedure using SQL.callStoredProcedure method.

Using CallableStatement I am able to get return parameter from MSSQL stored procedure but using SQL Scout wrapper class cannot.

I have sample SP called "testproc" with two params, one set as output.

I tried:

IntegerHolder holder = new IntegerHolder();
SQL.callStoredProcedure("testproc @in_par=:inPar, @out_par=:outPar INTO :holder", new NVPair("inPar", 1), new NVPair("outPar", 2), new NVPair("holder", holder));

This is executed, two IN params are registers and one OUT is registered. This is output:

SQL with binds:
*** UNPARSED ***
TESTPROC @IN_PAR = :inPar , @OUT_PAR = :outPar INTO :holder
IN  :inPar => ? [INTEGER 1]
IN  :outPar => ? [INTEGER 2]
OUT :holder => ? [Integer] 
null

Holder value is always null.

I am using Scout v.11.0.24, running on Windows 10 64bit and latest Eclipse. and using MSSQL JDBC:

<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>7.2.1.jre8</version>
</dependency>

I also tried using JDBC syntax using EXEC/CALL, and without INTO, but nothing is working.

What is propper way to call stored procedure in Eclipse Scout using Scout SQL wrapper and using SQL.callStoredProcedure?

Note: When using clear JDBC (CallableStatement) SP is returning value, so SP is working as expected. But I would like to use Scout SQL class to deal with stored procedures.

Thank You

sysdba
  • 57
  • 5

1 Answers1

0

I found it. Syntax is:

IntegerHolder holder = new IntegerHolder();
SQL.callStoredProcedure("testproc @in_par=:inPar, @out_par =:[OUT]returnValue", new NVPair("inPar", 1),  new NVPair("returnValue", holder));
sysdba
  • 57
  • 5