How to call SQL Server stored procedure in java with result set, and input, output parameters?

Question

In Oracle, I can use the following:

    String query = "{ ? = call get_config_func( ?, ? ) }";
    try
    {
        CallableStatement cs = con.prepareCall( query );
        cs.registerOutParameter(1, OracleTypes.CURSOR);
        cs.setInt( 2, i );
        cs.registerOutParameter(3, java.sql.types.INTEGER);
        cs.execute();
        ResultSet results = ((OracleCallableStatement)cs).getCursor (1);
        String val = cs.getString(3);

        if( results != null)
        {
            while( results.next() )
            {
             .....

However I am stuck with the reference to Oracle that won't work with SQL Server.

I tried the following:

    String query = "{ call uspGetLastLocation( ?, ? }";
    try
    {
        cs = con.prepareCall( query );
        cs.setLong(1, id );
        cs.registerOutParameter(2, java.sql.Types.VARCHAR );
        rs = cs.executeQuery();

        while(rs.next())
        {
            .....

But I get the following error

Incorrect syntax near '{'.

I've seen examples using PreparedStatement instead of CallableStatement and change the query to

    String query = "EXEC uspGetLastLocation  ?, ?";

But I can't figure out how to handle the Output parameter.

Any suggestions or pointer to a reference?

Thanks.

Solution

looks like you miss one bracket in code. try this

String query = "{ call uspGetLastLocation( ?, ? )}";

Comments

[Burferd]

I noticed that, but now I get the error "The statement did not return a result set."

[Burferd]

Is the rest of the syntax with regard to defining the output parameters and how I am attempting to get the result set?

[user47900]

JDBC API remains the same for both Oracle and SQL Server..so test your procedure first and check if it returns any results. I believe your code executed fine and did not return any results for parameter that you passed.

[Burferd]

Thanks, I finally got it to work. The stored procedure was expecting a null rather than 0 to use the defaults.