Re: JDBC and SQL server Stored procedure exception

From: Alin Sinpalean (alin_at_earthling.net)
Date: 04/23/04 

Date: 23 Apr 2004 05:33:09 -0700

Joe Weinstein <joeNOSPAM@bea.com> wrote in message news:<4086BE5F.9070801@bea.com>...
> Steve Qian wrote:
>
> > Hi,
> >
> > We use Java (microsoft JDBC) to call a stored procedure in the SQL server 2000.
> >
> > In the stored procedure, we will build a sql statement, and call sp_executesql.
> > Sometime we will have a @@error coming back, and depends on the error, we will keep going or return an error code to the calling SP.
> >
> > So basically, we do the error handling without using RAISERROR.
> >
> > The interesting thing is the JAVA code called this stored procedure finishes fine, but when we trying to get the out parameter -- Myproc.getString( 9 ), it errors out:
> >
> > 2004-04-21 11:51:19,381 FATAL [ours.server.datalayer.access.DataAccessExecutionObjectAdapter] Unable to execute "PUT" due to execution error.
> > java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'ADDRESS_LINE1'.
> > at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
> > at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> > at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
> > at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
> > at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
> > at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
> > at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
> > at com.microsoft.jdbc.base.BaseStatement.getNextResultType(Unknown Source)
> > at com.microsoft.jdbc.base.BaseCallableStatement.getAndValidateOutParameter(Unknown Source)
> > at com.microsoft.jdbc.base.BaseCallableStatement.getString(Unknown Source)
> > at ours.server.datalayer.access.ExecutionObjectPutImpl.performExecution(ExecutionObjectPutImpl.java:149)
> >
> > Is there any property we can set for this connection or statement to ignore these sql errors?
>
> Hi. This is odd. There's a bug somewhere here. You should not be getting any such exception
> from that getString() call. If I can see the JDBC code that handles the returns from the
> procedure, I can probebly know if you're doing it correctly to handle all the possible returns
> from the procedure for the different execute paths. You need to handle all the results and
> update counts before trying to get output parameter values. Then you should have no problem.
> Does your procedure always set a value to the output parameter?
>
> Joe Weinstein at BEA
>
> >
> > thanks a lot.
> >
> > Steve
> >

The way I see it, the error is generated by SQL Server, not the
driver. The reason why the procedure seems to go through fine is that
the error is thrown probably at the end of the procedure, after the
result sets and update counts have been returned. Try running the
stored procedure from Query Analyzer with the same parameters; you
should get the same error message.

Alin.

Loading