Capturing output parameters in Java w/SQL Server 2000 stored procedure

[RebLazer]

I'm trying to get my Java code to capture and manipulate output parameters from a SQL Server 2000 stored procedure. I need it for a much larger application, but I'm trying to get it to work with a much simpler example. And it is not working.

Below is my stored procedure. It's supposed to take 3 input parameters: 2 integers and 1 varchar. The idea is that it should multiply the two numbers. It is then supposed to output 2 parameters: the result of the multiplication (integer) and the same varchar that was passed in.

create procedure MultTest (
@in_num1 int,
@in_num2 int,
@oper varchar(2000))
as begin declare @out_num int
set @out_num = @in_num1 * @in_num2
select @out_num, @oper
end
GO

When I tried calling it (in Query Analyzer) like this:
declare @a int, @b varchar(2000)
exec mallen.MultTest 5, 3, 'multiplication' select @a, @b

I get these results:
15
"multiplication"
That's good.

But ... two "NULL"s for the output variables. That's bad.


Really my goal in all of this, however, is to get this to work from Java. Here is my Java code:

public void doTest(DBConnection dbConn) {

try {

String callText = "call mallen.MultTest 5, 3, 'multiplication', ?, ?";

System.out.println(callText);

CallableStatement cstmt = con.prepareCall(callText);
cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
cstmt.execute();

//WHEN THE FOLLOWING 4 LINES ARE UNCOMMENTED, SQLEXCEPTION THROWN: "NO RESULTSET PRODUCED" - SO LEAVE THEM OUT...
//ResultSet rs = cstmt.executeQuery();
//int first = rs.getInt(1);
//int second = rs.getInt(2);
//String third = rs.getString(3);

int outNum = cstmt.getInt(1);
String operation = cstmt.getString(2);

System.out.println("product = " + outNum + "; operation = " + operation + "\n"
}
catch (SQLException se) {
System.out.println("SQL Exception: " + se.getMessage());
se.printStackTrace(System.out);
}
}

And here's the exception I get:
call mallen.MultTest 5, 3, 'multiplication', ?, ?
java.lang.ArrayIndexOutOfBoundsException
at com.microsoft.jdbc.base.BaseParameters.set(Unknown Source)
at com.microsoft.jdbc.base.BaseCallableStatement.registerOutParameter(Unknown Source)
at sssweb.InsertSequence.doTest(InsertSequence.java:34)
at sssweb.GeneratesSQL.main(GeneratesSQL.java:154)
Exception in thread "main"

I don't think the two (parameter placeholder) question marks are supposed to be there anymore by the time it's sent to the database - but I don't know how to get rid of them.

All "GeneratesSQL.main" does is calls "doTest". "doTest" has no interaction with its own class ("InsertSequence&quot - I just needed somewhere to put it.

Any help you could provide would be totally and completely appreciated!

Thanks,
Lazer

 

[DannyB]

Hi , I think u should use a function to return values, usually a procedure processes stuff on the back end, no beeing supposed to return anything.

That function then would return a recordset (resultset) with whatever u choose to.

Another snag ... a function usually returns only one value or "object" (SQL).

 

[KarunP]

The following should work:

java.sql.CallableStatement stmt = con.prepareCall("{call proc_name (?, ?, ...)}"

stmt.setXXX(1, yyy);
stmt.setXXX(2, yyy);
...

stmt.execute();

rs = (java.sql.ResultSet) stmt.getResultSet();

Access your return values through the first row of the resultset