How do I get the return value from a T-SQL stored procedure in Java?

Question

I have a stored procedure that adds an object to the database and returns the generated ID number.

For reference, its basic structure goes something like this:

ALTER PROCEDURE [dbo].[myProc] 
    @Name    nvarchar(50),
    @Creator nvarchar(50),
    @Text    nvarchar(200),
    @Lat     float,
    @Lon     float,
    @myID    int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO myTable --# blah blah blah
    SELECT @myID = scope_identity(); --# grab the auto-inc key from myTable
    INSERT INTO anotherTable --# blah blah blah
END

I ran this in SQL Server Management Studio and verified that it worked correctly.

Now I want to call that stored procedure from Java. I wrote this code to do it:

CallableStatement cs = con.prepareCall("EXEC myProc "
  + "@Name = ?, @Creator = ?, @Text = ?, @Lat = ?, @Lon = ?, @myID = ? OUTPUT");
cs.setString(1, aString);
cs.setString(2, anotherString);
cs.setString(3, yetAnotherString);
cs.setFloat(4, aFloat);
cs.setFloat(5, anotherFloat);
cs.registerOutParameter(6, java.sql.Types.INTEGER);

cs.execute();

But the execute() call throws an exception:

java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to int.

What is going wrong here? Why is it even trying to convert an nvarchar to an int? I'm not even trying to fetch the return value via getInt() yet (that comes on the next line).

What I've tried:

• Building the query by string manipulation to check whether the problem could possibly be in the input parameters. Same exception. At least that narrows the problem down.
• Changing the type of the output parameter to Types.NVARCHAR, just in case. But that's not even supported by the JdbcOdbcDriver which I am using.
• Messing around with the call syntax (I haven't used SQL Server with Java before). Always ended up with syntax errors. This included trying to make myID a return value instead of an output parameter.
• Searching Google. A lot. Ended up with a bunch of unhelpful forum threads and EE "answers".

Now I'm stumped. Is it really this hard or am I just missing the obvious?

Answer 1

Your code looks ok. The only thing that is wrong is that "float" in SQL Server maps to double in Java. "real" in MS SQL maps to float in Java. But this does not produce the problem.

The problem is the buggy ODBC Bridge. You should use a type 4 JDBC driver for MS SQL. If you want to use the ODBC Bridge then you can test setting a value for the 6th parameter:

cs.setInt(6, 0);

But I does not know if this will work. The message means that the driver is using the data type NVARCHAR for the last parameter. It look like the ODBC Bridge does not forward the type with registerOutParameter.

Answer 2

Can't test your code since I don't have SQL Server, but you can try changing to String since this is Java equivalent for NVARCHAR.