randy.p.ho@gmail.com wrote:

> Thanks for the reply. I meant "multiple output parameters".
>
> Here is how I execute the stored procedure:
>
> declare @ErrorID int
> declare @ErrorStr varchar(255)
> exec procName
> @customerId = '1234567890',
> @customerName = 'some name',
> @error_code = @ErrorID,
> @error_state = @ErrorStr
>
>
> Here is the procedure:
>
> create procedure uxt1.procName
> @customerId char(15) output,
> @customerName char(64) output,
> @error_code int output,
> @error_state varchar(255) output
> ... ...
> /* all the business logic */
> ... ...
> return (@error_state)
> GO
>
>
> Here's what SQL server gives me if I do a "Script object as Execute":
> DECLARE @RC int
> DECLARE @customerId char(15)
> DECLARE @customerName char(64)
> DECLARE @error_code int
> DECLARE @error_state varchar(255)
> EXEC @RC = [uxt1].[procName] @customerId, @customerName, @error_code
> OUTPUT , @error_state OUTPUT
>
>
> The following is what I've tried in a Java program:
> ...
> CallableStatement cs = conn.prepareCall(" {? = call
> uxt1.procName(?,?,?,?)}" );
> cs.registerOutParameter(1,java.sql.Types.INTEGER);
> cs.setString(2,"some ID");
> cs.setString(3,"some Name");
> cs.registerOutParameter(4,java.sql.Types.INTEGER);
> cs.registerOutParameter(5,java.sql.Types.VARCHAR);
> ResultSet rs = cs.executeQuery();
> ...
>
> My code doesn't throw any exception; but the procedure was not executed
> correctly (i.e. it's not doing what it's supposed to do, which is to
> simply insert some values into a table).
> Any help is appreciated. Thanks in advance.

Is the procedure executing at all? You shouldn't be calling executeQuery()
unless the first thing the procedure does is a select. Use execute() and
then loop:

cs.execute();
while (true)
{
int update_count = ps.getUpdateCount();
ResultSet rs = ps.getResultSet();
if ((rs == null && (update_count == -1)) break; // done

if (rs != null) process rs;
ps.getMoreResults();
}
// after processing inline results, call ps.getXXX() to get output parameters.

Whatever jdbc driver you're suing is pretty flakey if it
returns a result set from executeQuery() and the
procedure didn't do a select for data to go to the caller...

Joe Weinstein at BEA