How to call Sybase stored procedure with named params using JDBC

I have a stored procedure in Sybase which I can invoke from my favourite SQL client like this:

exec getFooBar @Foo='FOO', @Bar='BAR'

It returns a table of results, so its like a query. It has actually many parameters, but I only want to call it with Foo and sometimes with Foo and Bar specified.

Sybase is ASE 15.0, I am using jConnect 6.0.5.

I can invoke this using a PreparedCall if I specify only the first parameter:

CallableStatement cs = conn.prepareCall("{call getFooBar(?) }");
cs.setString(1,"FOO");

However I can't use the @Foo notation to pass my params:

conn.prepareCall("{call getFooBar @Foo='FOO' }");
conn.prepareCall("call getFooBar @Foo='FOO' ");
conn.prepareCall("{call getFooBar @Foo=? }"); // + setString(1,'FOO')

In all these cases I get exception from the DB telling me that the Foo parameter is expected:

com.sybase.jdbc3.jdbc.SybSQLException: Procedure getFooBar expects
parameter @Foo, which was not supplied

Ideally I'd like to do this with Spring JdbcTemplate or SimpleJdbcCall, but with those I could not even get to the point where I could with plain old JDBC.

So to summarize, I'd like to avoid ending up with:

{ call getFooBar(?,null,null,null,null,null,?,null,null) }

Is that possible using JDBC or better Spring?

1 Answer

Not the most efficient solution, but, have you tried using the plain Statement itself with EXEC.
eg.
String mySql = "EXEC getFooBar @Foo='FOO', @Bar='BAR'"; ResultSet rs = conn.getConnection().createStatement().executeQuery(mySql);