Re: Stored Procedure error is not catched


"Joe Weinstein" <joeNOSPAM@xxxxxxx> wrote in message
news:430DF786.3010002@xxxxxxxxxx
>
>
> Rizwan wrote:
>
>> I am using MS SQL Server JDBC Driver. I call a stored procedure from my
>> java code. The Stored Procedure does some inserts. One of the insert
>> failed but in my java code the SQLException is not thrown. Can anybody
>> tell me how to fix this bug?
>>
>> Thanks
>
> Hi. The problem is that the SQLException (or really the error message
> from the DBMS, that will be turned into a SQLException) is still out on
> the network for the statement, until it is read by the Statement. This
> isn't happening immediately because your procedure is doing multiple
> inserts, and some of the first ones succeed, so the first things on
> the line are successful update counts.
> You need to add processing after the execute() to get all the
> returns, and then you will get your exception. Here is an example which
> contains the ideal code for processing all the inline returns from
> any stored procedure. With this, you do get your expected exception.
>
> Joe Weinstein at BEA Systems
>
> c = d.connect("jdbc:bea:sqlserver://joe", props);
>
> System.out.println("Driver version is " +
> c.getMetaData().getDriverVersion() );
>
> DatabaseMetaData dd = c.getMetaData();
> System.out.println("Driver version is " +
> dd.getDriverVersion() );
> System.out.println("Database Major version is " +
> dd.getDatabaseMajorVersion() );
> System.out.println("Database Minor version is " +
> dd.getDatabaseMinorVersion() );
>
> Statement s = c.createStatement();
>
> try { s.executeUpdate("drop procedure joeproc"); } catch
> (Exception ignore){}
> try { s.executeUpdate("drop table joetable"); } catch (Exception
> ignore){}
>
> s.executeUpdate("create table joetable (bar varchar(30) not
> null)");
>
> s.executeUpdate("create procedure joeproc as "
> + " begin "
> + " insert into joetable values('1') "
> + " insert into joetable values(NULL) " //
> second insert will fail
> + " insert into joetable values('2') "
> + " end ");
>
> PreparedStatement ps = c.prepareStatement("{ call joeproc() }");
> boolean getResultSet = ps.execute();
> int updateCount = -1;
>
> while (true) { // handle all in-line results from any procedure
> if (getResultSet) {
> ResultSet r = ps.getResultSet();
> while (r.next()) {
> // process result set
> }
> r.close();
> } else {
> updateCount = ps.getUpdateCount();
> if (updateCount != -1) {
> ;// process update count
> }
> }
> if ((!getResultSet) && (updateCount == -1)) break; // done
> with loop
> getResultSet = ps.getMoreResults();
> }
>
> I get:
>
> Driver version is 3.40.57 (012747.007227.008728)
> Driver version is 3.40.57 (012747.007227.008728)
> Database Major version is 8
> Database Minor version is 0
> java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]Cannot
> insert the value NULL into
> column 'bar', table 'CCTEST.dbo.joetable'; column does not allow nulls.
> INSERT fails.
>
> The exception arives during the getMoreResults() call.
>

My java code is pretty simple:

Connection conn = null;
CallableStatement stmt = null;
try {
conn = ConnectionHelper.getConnection();
stmt = conn.prepareCall("{call ta_ProcessPayroll()}");
stmt.execute();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
...
}

I tried the same code with Type 1 Driver (JDBC-ODBC Bridge) and error was
caught by the SQLException. So I think this is bug with Microsoft SQL Server
JDBC Driver. What do you think?



.

Relevant Pages

  • RE: JDBC 2.0 driver and raiserror not throwing SQLException in Jav
    ... Have since found the reason for this behaviour in driver versions 1.2+.... ... The stored procedure in question did not have 'set nocount on'. ... this caused the insert exception to be raised properly to the java code in ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Callablestatement.execute() and clearParameter() causing excep
    ... Joe Weinstein at BEA Systems ... Driver version is 1.0.809.102 ... A server cursor cannot be opened on the given ... on a remote stored procedure or stored procedure with more than one SELECT statement. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: odbccommand cant interpret some /*xxx*/ comments
    ... I have tried your query (creating stored procedure) with the API ... SQLExecDirect in the driver "SQL Server". ... The problem doesn't reproduce. ...
    (microsoft.public.data.odbc)
  • Invalid parameter binding
    ... I am having a problem with calling a stored procedure. ... The java code invoking the SP is as follows: ... [SQLServer 2000 Driver for JDBC]Invalid parameter binding. ...
    (comp.lang.java.help)
  • Invalid parameter binding
    ... I am having a problem with calling a stored procedure. ... The java code invoking the SP is as follows: ... [SQLServer 2000 Driver for JDBC]Invalid parameter binding. ...
    (comp.lang.java.databases)