Stored Procedure with return values

Question

I have a stored procedure "test", which looks like:

CREATE PROCEDURE test
@name varchar(32)  
AS
DECLARE 
        @login_status  TINYINT, 
    @syb_login   varchar(20),
        @syb_pass   varchar(20)
...
..     


BEGIN   
            SELECT @status = 0
            SELECT @login as login,
                   @pass as pass,
                   @status as status   
             RETURN 0 
        END

I need to pass a single input parameter "myName" as input parameter to this procedure and which in turn returns the login, pass and status as output (from only one record) parameters.

In JDBC, I tried to do like below:

 String query = "{call test(?,?,?)}";
    System.out.println(query);
    CallableStatement proc = null;
    ResultSet rs;
    try {
        proc = connection.prepareCall(query);
        proc.setString(1, "myName");

    proc.registerOutParameter(2, java.sql.Types.VARCHAR);
    proc.registerOutParameter(3, java.sql.Types.VARCHAR);

    proc.execute();

    System.out.println(proc.getString(2));

This always gives the exception:

java.sql.SQLException: JZ0SG: A CallableStatement did not return as many output parameters as the application had registered for it.
    at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(Unknown Source)
    at com.sybase.jdbc4.jdbc.ParamManager.nextResult(Unknown Source)
    at com.sybase.jdbc4.jdbc.ParamManager.doGetOutValueAt(Unknown Source)
    at com.sybase.jdbc4.jdbc.ParamManager.getOutValueAt(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybCallableStatement.getString(Unknown Source)

I tried with JDBC execute SQL Server stored procedure with return value and input/output parameters, https://msdn.microsoft.com/en-us/library/ms378108.aspx but this didn't work.

Solution

There are two ways:

1. Redefine your procedure (recommended)

Output paramteres have to be declared when creating a stored procedure:

CREATE PROCEDURE test
    @name varchar(32),           
    @login varchar(32) = null output,
    @pass varchar(32) = null output,
    @status int = -1 output
AS
BEGIN   
        SET @status = 0
        SELECT @login = logincolumn,
               @pass = passcolumn
        FROM usertable

END

Then you can use it with JDBC as follows:

String query = "{call test(?,?,?,?)}";
[...]
proc = connection.prepareCall(query);
proc.setString(1, "myName");
proc.registerOutParameter(2, java.sql.Types.VARCHAR);
proc.registerOutParameter(3, java.sql.Types.VARCHAR);
proc.registerOutParameter(4, java.sql.Types.INTEGER);

Note:

• You have to set or select into the declared variables to give them output values.
• Usually it isn't a good idea to pass passwords around

2. Catch the results from multiple resultsets (not recommended)

Connection con;
CallableStatement proc = null;
ResultSet rs;

String qry = "{call test(?)}";
proc = con.prepareCall(qry);
proc.setString(1, "name");
proc.executeQuery();

// first result set returning the status
rs = proc.getResultSet();
if (rs.next()) {
    System.out.println(rs.getString(1));
}

 // second result set returning login and pass
if (proc.getMoreResults()) {
    rs = proc.getResultSet();
    if (rs.next()) {
        System.out.println(rs.getString("login"));
        System.out.println(rs.getString("pass"));
    }
}

Comments

[Harbinger]

You mean declaring the parameter names. Yes. I have done it already.. Just included the first and last statements of the proc. I have updated it now.

[Harbinger]

I can be sure the proc is working fine. I can directly hit and get the output. But, not from jdbc

[flo]

Updated the solution to provide a way if you are not able to change the procedure.

[flo]

Even if the proc is working fine when working on a console, it is not the way it should be used. See msdn.microsoft.com/en-us/library/ms188655.aspx