@SqlCall - MS Sql Exec Stored Procedure - getMoreResults error

[Leonardo Ribeiro]

Guys, can you help me with the below issue?

I'm trying to run a Stored Procedure that returns an Integer in MS SQL Server 2014.

This is my DAO code:

@SqlCall(":id = exec Create_Client " +
        "  @source = :c.source," +
        "  @manager_user_id = :c.source," +
        "  @status_id = :status," +
        "  @name = :c.name")
@OutParameter(name="id", sqlType = Types.INTEGER)
OutParameters spCreateClient(@BindBean("c") Client newClient,
                             @Bind("status") int status);

When I try to access the above method I receive this error:

ERROR [2016-10-05 01:02:07,232] io.dropwizard.jersey.errors.LoggingExceptionMapper: Error handling a request: 03a6bcc932db8c37

! java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().

! at net.sourceforge.jtds.jdbc.ParamInfo.getOutValue(ParamInfo.java:159)

! at net.sourceforge.jtds.jdbc.JtdsCallableStatement.getOutputValue(JtdsCallableStatement.java:124)

! at net.sourceforge.jtds.jdbc.JtdsCallableStatement.getInt(JtdsCallableStatement.java:250)

! at org.skife.jdbi.v2.Call$OutParamArgument.map(Call.java:149)

! at org.skife.jdbi.v2.Call$1.munge(Call.java:94)

! at org.skife.jdbi.v2.Call$1.munge(Call.java:88)

! at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1344)

! ... 71 common frames omitted

! Causing: org.skife.jdbi.v2.exceptions.ResultSetException: Exception thrown while attempting to traverse the result set [statement:":id = exec Create_Client   @source = :c.source,  @manager_user_id = :c.source,  @status_id = :status,  @name = :c.name", located:":id = exec Create_Client   @source = :c.source,  @manager_user_id = :c.source,  @status_id = :status,  @name = :c.name", rewritten:"/* ClientDAO.spCreateClient */ ? = exec Create_Client   @source = ?,  @manager_user_id = ?,  @status_id = ?,  @name = ?", arguments:{ positional:{}, named:{c.class:class com.company.v2.core.clients.Client,c.hashId:'null',c.source:1,id:org.skife.jdbi.v2.Call$OutParamArgument@160be7af,c.id:null,c.name:'Leo-Testing-sp#7',c.status:TESTING,status:2}, finder:[]}]

! at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1352)

! at org.skife.jdbi.v2.Call.invoke(Call.java:88)

! at org.skife.jdbi.v2.sqlobject.CallHandler.invoke(CallHandler.java:56)

! at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:212)

! at org.skife.jdbi.v2.sqlobject.SqlObject$2.intercept(SqlObject.java:109)

! at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$6bf36f34.spCreateClient(<generated>)

What can I do to solve that issue?

Thank you,

Leo

[Steven Schlansker]

Hi,

When you say "returns an Integer" -- you mean the procedure
actually returns it? In that case, you'd likely want to
use a @SqlQuery("SELECT * FROM Create_Client(...)") or something like that.

There is also JDBC's call syntax "{? = call CreateClient(?)}" which you should
try various permutations from.

OutParameters are, as the name implies, for retrieving parameters that have been
modified by the stored procedure. It wouldn't be used for retrieving the result set
of a stored procedure, so it's not surprising that that doesn't work.

If none of this helps, figure out how to correctly invoke it with raw JDBC
(or provide us with a test case?) and we can figure out how to get the @SqlCall
or whatever working.

Hope that helps

> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Matthew Hall]

We should try to capture this vendor-specific knowledge somewhere in our docs.

> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "jDBI" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

[Leonardo Ribeiro]

Thanks for the help Steven.

These are the instructions from Microsoft site through JDBC: https://msdn.microsoft.com/en-us/library/ms378371.aspx

I've tried some stuff like your suggested Select, JDBC escaping call, but it does not work too...

Now this is the error:

java.sql.SQLException: Parameter #1 has not been set.

! at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1049)

! at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:563)

! at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:787)

! at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1327)

! ... 67 common frames omitted

! Causing: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.SQLException: Parameter #1 has not been set. [statement:"{? = call Create_Client(?,?,?,?)}", located:"{? = call Create_Client(?,?,?,?)}", rewritten:"{? = call Create_Client(?,?,?,?)}", arguments:{ positional:{1:org.skife.jdbi.v2.Call$OutParamArgument@307780d,2:1,3:1,4:2,5:'leo-test-sp-#1'}, named:{}, finder:[]}]

! at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1338)

! at org.skife.jdbi.v2.Call.invoke(Call.java:88)

and this is my code:

Handle h = jdbi.open();

Call call = h.createCall("{? = call Create_Client(?,?,?,?)}")// +
        .registerOutParameter(1,Types.INTEGER)
        .bind(2, newClient.getSource())
        .bind(3, newClient.getSource())
        .bind(4, newClient.getStatus().getValue())
        .bind(5, newClient.getName());

    OutParameters outParameters = call.invoke();
    newClient.setId(outParameters.getLong(1));
    h.close();
return newClient;

PS: select * from sp -> does not work on MSSQL...

Thank you,

[Leonardo Ribeiro]

Finally It's working with the below code...

Handle h = jdbi.open();
Connection con = h.getConnection();
try {
    CallableStatement cs = con.prepareCall("{call Create_Client(?,?,?,?)}");
    cs.setLong(1, newClient.getSource());
    cs.setLong(2, newClient.getSource());
    cs.setInt(3, newClient.getStatus().getValue());
    cs.setString(4, newClient.getName());

    ResultSet rs = cs.executeQuery();
    if(rs.next()) {
        newClient.setId(rs.getLong("client_id"));
        cs.close();
    } else {
        cs.close();
        throw new WebApplicationException("Fail to create client");
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    h.close();
}

Any suggestions in how to do that with pure JDBI? 

Thank you,

[Steven Schlansker]

Unfortunately at this point I am at a loss.
I realized recently that I don't understand the Call stuff myself all that
well -- I've never used stored procedures (somehow managed to avoid that ball of sad!)
and there is definitely some mismatch between the parameter indexing.

I would recommend getting the source code to your JDBC driver and stepping through the call
flow in the debugger. Hopefully you'd be able to see exactly what isn't getting set,
and from there, work backwards to how you get it set.

Sorry I can't be more specific.