Re: Stored Procedure error is not catched
- From: Joe Weinstein <joeNOSPAM@xxxxxxx>
- Date: Thu, 25 Aug 2005 18:19:40 -0700
Joe Weinstein wrote:
Rizwan wrote:
I also test this code with JTDS JDBC Driver and it works. Dont you think this is a bug?
That is a good point. The fundamental issue is whether the driver will buffer up everything the DBMS may be sending from the procedure before the user processes it, in order to find an process the error message that may be somewhere coming. If the procedure does some queries before the failing update, what does the jTDS driver do? In my example it would be:
Hi. I just downloaded the jtds driver, and found that for the general procedure it will behave as I expect, and would need the full-processing code I recommend. I changed my example code to do one select before a failed insert, and you will not get the exception from the jtds driver either, until you process it:
Driver d = new net.sourceforge.jtds.jdbc.Driver();
c = d.connect("jdbc:jtds://joe:1433", props ); 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') "
+ " select 1 "
+ " insert into joetable values(NULL) "
+ " insert into joetable values('2') "
+ " end "); PreparedStatement ps = c.prepareStatement("{call joeproc()}");
boolean getResultSet = ps.execute();
System.out.println("done");It runs without complaint: C:\jtds_driver>java foo Driver version is 0.7 Database Major version is 8 Database Minor version is 0 done
If I do the right thing:
boolean getResultSet = ps.execute();
int updateCount = -1; while (true) { // handle all in-line results from any procedure
if (getResultSet) {
ResultSet r = ps.getResultSet();
System.out.println("got my query return...");
while (r.next()) {
// process result set
}
r.close();
} else {
updateCount = ps.getUpdateCount();
if (updateCount != -1) {
System.out.println("got a valid update count of " + updateCount );
}
}
if ((!getResultSet) && (updateCount == -1)) break; // done with loop
System.out.println("getting next return..." );
getResultSet = ps.getMoreResults();
} System.out.println("done");I get:
C:\jtds_driver>java foo
Driver version is 0.7
Database Major version is 8
Database Minor version is 0
got a valid update count of 1
getting next return...
got my query return...
java.sql.SQLException: Cannot insert the value NULL into column 'bar', table 'CCTEST.dbo.joetab
le'; column does not allow nulls. INSERT fails.
at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(SqlMessage.java:85)
at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(SQLWarningChain.java:99)
at net.sourceforge.jtds.jdbc.Tds.goToNextResult(Tds.java:836)
at net.sourceforge.jtds.jdbc.Tds.fetchRow(Tds.java:3921)
at net.sourceforge.jtds.jdbc.TdsResultSet.fetchNextRow(TdsResultSet.java:432)
at net.sourceforge.jtds.jdbc.TdsResultSet.internalFetchRows(TdsResultSet.java:765)
at net.sourceforge.jtds.jdbc.TdsResultSet.haveMoreResults(TdsResultSet.java:747)
at net.sourceforge.jtds.jdbc.TdsResultSet.next(TdsResultSet.java:391)
at foo.main(foo.java:50)Joe Weinstein at BEA Systems
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') " + " select * from sysobjects " + " select * from sysobjects " + " select * from sysobjects " + " select * from sysobjects " + " select * from sysobjects " + " select * from sysobjects " + " select * from sysobjects " + " insert into joetable values(NULL) " + " insert into joetable values('2') " + " end ");
PreparedStatement ps = c.prepareStatement("{ call joeproc() }"); boolean getResultSet = ps.execute();
If you can try this and let me know? thanks
"Rizwan" <hussains@xxxxxxxxxxxx> wrote in message news:e0CMXBcqFHA.564@xxxxxxxxxxxxxxxxxxxxxxx
"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?
.
- Follow-Ups:
- Re: Stored Procedure error is not catched
- From: Rizwan
- Re: Stored Procedure error is not catched
- References:
- Stored Procedure error is not catched
- From: Rizwan
- Re: Stored Procedure error is not catched
- From: Joe Weinstein
- Re: Stored Procedure error is not catched
- From: Rizwan
- Re: Stored Procedure error is not catched
- From: Rizwan
- Re: Stored Procedure error is not catched
- From: Joe Weinstein
- Stored Procedure error is not catched
- Prev by Date: Re: Stored Procedure error is not catched
- Next by Date: Re: Stored Procedure error is not catched
- Previous by thread: Re: Stored Procedure error is not catched
- Next by thread: Re: Stored Procedure error is not catched
- Index(es):
Relevant Pages
|
Loading
