2

I am trying to call a SQLServer stored Scalar function through Apache Common DbUtils. I tried something like this:

run.query("SELECT [MyDB].[dbo].[test] ('testParam')", new ScalarHandler());

But, I get this exception:

com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: Unable to identify the table SELECT [MyDB].[dbo].[test] ('testParam') for the metadata.

While, running the same query in the SQLServer returns a valid Scalar value.

I am wondering how can I call a Scalar function using Apache DbUtils.

UPDATE: For Table-valued functions it works if I ues "SELECT * FROM..."

Afshin Moazami
  • 2,092
  • 5
  • 33
  • 55
  • Have you tried with the exec command instead of select? http://msdn.microsoft.com/pt-br/library/ms188332.aspx – Jorge Campos Nov 27 '13 at 21:25
  • Yes, EXEC doesn't return anything. – Afshin Moazami Nov 27 '13 at 21:29
  • I don't have experience with Apache Common DBUtils, but I would try to execute the code as I'd have a recordset with one row and one column. – bjnr Dec 01 '13 at 19:19
  • run is the instance of QueryRunner? It has argument pmdKnownBroken in constructor. Try to set it to true. – WadimX Dec 02 '13 at 07:49
  • Have you tried setting the ResultSet parameter in ScalarHandler? – NickyvV Dec 02 '13 at 13:04
  • @Dd2, What I mentioned in the UPDATE of my question to use a Table-valud function, which has recordset/resultset, is your solution. However, I want to know if we can call a scalar-valued table through DbUtils – Afshin Moazami Dec 02 '13 at 19:30
  • @WadimX, yes it is QuerryRunner, and it works with your solution. Would you please add it as an answer? – Afshin Moazami Dec 02 '13 at 19:36

2 Answers2

3

It looks that there is a bug in Apache Common DbUtils (v. 1.5) AbstractQueryRunner (base class of QueryBuilder) code. It has parameter pmdKnownBroken, which desribed as follows:

if pmdKnownBroken is set to true, we won't even try it; if false, we'll try it, and if it breaks, we'll remember not to use it again.

But does not work as described, becausefillStatementmethod does not catch an exception when calling java.sql.getParameterMetaData.

So you should set pmdKnownBroken to true.

Afshin Moazami
  • 2,092
  • 5
  • 33
  • 55
WadimX
  • 317
  • 1
  • 6
0

I am not sure if it is possible to run a SQL Server scalar function with DBUtils, try by this way

Object[] params = {"inputparam"};
ScalarHandler<T> scalar = new ScalarHandler<T>();
run.query("{CALL DB.dbo.test(?)}", scalar, params);

If you don't find a solution with DBUtils yo can use a CallableStatement

String url = "jdbc:sqlserver://....";
SQLServerDataSource sqls = new SQLServerDataSource();
sqls.setURL(url);
sqls.getConnection();

CallableStatement callStatement = sqls.getConnection().prepareCall("{? = CALL DB.dbo.f_promedio(?,?)}");
callStatement.setDouble(2, 1.8);
callStatement.setDouble(3, 6.8);
callStatement.registerOutParameter(1, java.sql.Types.DECIMAL);
callStatement.execute();

double d = callStatement.getDouble(1);
vzamanillo
  • 9,905
  • 1
  • 36
  • 56