SQL Server JDBC - Stored Procedure error is not catched

Author

Stored Procedure error is not catched

Rizwan

2005-08-25, 11:23 am

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

Joe Weinstein

2005-08-25, 1:23 pm

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. getDatabaseMajorVers
ion() );
System.out.println("Database Minor version is " + dd. getDatabaseMinorVers
ion() );

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]& #91;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.

Radovan Biciste

2005-08-25, 8:23 pm

Hello,
You might consider exception handling in your procedure.

I have following utility procedure:
-- Checks if @error_code is bigger than 0 and eventually fetches system
error messages and procedure name based on @procid
-- and raises exception.
-- @error_code - use @@ERROR
-- @procid - use @@PROCID
-- example:
-- create procedure safe_delete as
-- begin
-- delete from table_a where id = 233
-- exec dbUtil_pkg$check_err
or(@@ERROR, @@PROCID)
-- end
create procedure dbUtil_pkg$check_err
or(@error_code int, @msg
varchar(300)) as
declare
@complete_msg varchar(400)
begin
if @error_code > 0
begin
set @complete_msg = @msg + ': failed with error code ' + cast(
@error_code as varchar)
raiserror ( @complete_msg , 16, 1)
end
end
go

And then in the code I use it:
-- mark rows for syncing
UPDATE searchable_object_wa
SET sync_flag = 1;

-- remove all rows from searchable_attribute
for objects
that need update
delete from searchable_attribute

where exists (select 1
from searchable_object_wa
wa WITH (NOLOCK)
where searchable_attribute
.lookup_id =
wa.lookup_id
and wa.sync_flag = 1);
exec dbUtil_pkg$check_err
or @@ERROR, @v_signature;

-- update master rows in searchable_object table
-- handle so deletes
delete from searchable_object
where exists (select wa.id
from searchable_object_wa
wa WITH (NOLOCK)
where wa.lookup_id = searchable_object.lookup_id
and wa.sync_flag = 1
and wa.deleted = 1);
exec dbUtil_pkg$check_err
or @@ERROR, @v_signature;

-- delete all deleted entries in wa
delete from searchable_object_wa

where deleted = 1
AND sync_flag = 1;
exec dbUtil_pkg$check_err
or @@ERROR, @v_signature;

It sort of mimics behaviour of stored procedures from other more
advanced databases. :-)
Hope that helps,
Radovan

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
>
>

Rizwan

2005-08-25, 8:23 pm

"Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
news:430DF786.3010002@bea.com...
>
>
> Rizwan wrote:
>
>
> 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. getDatabaseMajorVers
ion() );
> System.out.println("Database Minor version is " +
> dd. getDatabaseMinorVers
ion() );
>
> 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]& #91;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?

Rizwan

2005-08-25, 8:23 pm

I also test this code with JTDS JDBC Driver and it works. Dont you think
this is a bug?

"Rizwan" <hussains@pendylum.com> wrote in message
news:e0CMXBcqFHA.564@TK2MSFTNGP10.phx.gbl...
>
> "Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
> news:430DF786.3010002@bea.com...
>
> 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?
>
>
>

Joe Weinstein

2005-08-25, 8:23 pm

Rizwan wrote:
> "Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
> news:430DF786.3010002@bea.com...
>
>
>
> 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?

Right. Your code is too simple. Sorry to be a pain, but never rely on the
old buggy unsupported JDBC-ODBC bridge as a validity test. Your procedure
will return an update count for every separate update (update/insert/delete)
statement it does. If the first one fails you should get your exception
from the execute() call, but later ones won't. In order for the jdbc-odbc
bridge to act the way it does, it would have to buffer up all the incoming
data, including possibly multiple result sets that could come before the
failure, and would blow up memory. I know the issue, and the code example
I showed for processing the inline returns from any stored procedure will
always work. Your code should be:

Connection conn = null;
CallableStatement stmt = null;
try {
conn = ConnectionHelper.getConnection();
stmt = conn.prepareCall("{call ta_ProcessPayroll()}
");
boolean getResultSet = stmt.execute();
int updateCount = -1;

while (true) { // handle all in-line results from any complex procedure
if (getResultSet) {
ResultSet r = ps.getResultSet();
while (r.next()) {
// process result set if expected/wanted
}
r.close();
} else {
updateCount = ps.getUpdateCount();
if (updateCount != -1) {
;// process update count if expected/wanted
}
}
if ((!getResultSet) && (updateCount == -1)) break; // done with loop
getResultSet = ps.getMoreResults();
}

} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
...
}

Joe Weinstein

2005-08-25, 8:23 pm

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:

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@pendylum.com> wrote in message
> news:e0CMXBcqFHA.564@TK2MSFTNGP10.phx.gbl...
>
>
>
>

Joe Weinstein

2005-08-25, 8:23 pm

Joe Weinstein wrote:

>
>
> Rizwan wrote:
>
>
>
> 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. getDatabaseMajorVers
ion() );
System.out.println("Database Minor version is " + dd. getDatabaseMinorVers
ion() );

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(SqlMe
ssage.java:85)
at net.sourceforge.jtds.jdbc.SQLWarningChain. addOrReturn(SQLWarni
ngChain.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(TdsResu
ltSet.java:432)
at net.sourceforge.jtds.jdbc.TdsResultSet. internalFetchRows(Td
sResultSet.java:765)
at net.sourceforge.jtds.jdbc.TdsResultSet. haveMoreResults(TdsR
esultSet.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

2005-08-26, 8:23 pm

Thanks Joe. You are right. I am going to change my code to accomodate all
scenarios.
Right now I have another problem. In my stored proc I have begin
transaction, commit transaction and rollback transaction. Now does this
thing suits ok with java?

The problem I am having is that my stored proc inserts data in 2 tables. If
the first table (Table1) insert is successful and the second table (Table2)
is failure, the Table1 gets locked.
It only gets released when i shuts down JBoss. On shutting down the data
(which is enterd by stored proc) gets rollback which is good.
If I dont shutdown but instead go to another jsp which retrieves data from
some other table then the lock on Table1 is released. But in this scenario
the data (which is enterd by stored proc) stays which is not good.

I am not sure why Table1 gets locked. The stored proc looks ok. It starts
with begin transaction. After every insert is check @@error and if its not 0
then i rollback transaction and RAISEERROR. I have check this stored proc
with Query Analyzer and it works perfect.

Any reason?

Thanks

"Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
news:e4hS0vdqFHA.3600@tk2msftngp13.phx.gbl...
>
>
> Joe Weinstein wrote:
>
>
>
> 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. getDatabaseMajorVers
ion() );
> System.out.println("Database Minor version is " +
> dd. getDatabaseMinorVers
ion() );
>
> 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(SqlMe
ssage.java:85)
> at
> net.sourceforge.jtds.jdbc.SQLWarningChain. addOrReturn(SQLWarni
ngChain.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(TdsResu
ltSet.java:432)
> at
> net.sourceforge.jtds.jdbc.TdsResultSet. internalFetchRows(Td
sResultSet.java:765)
> at
> net.sourceforge.jtds.jdbc.TdsResultSet. haveMoreResults(TdsR
esultSet.java:747)
> at
> net.sourceforge.jtds.jdbc.TdsResultSet.next(TdsResultSet.java:391)
> at foo.main(foo.java:50)
>
> Joe Weinstein at BEA Systems
>
>

Joe Weinstein

2005-08-27, 3:23 am

Rizwan wrote:

> Thanks Joe. You are right. I am going to change my code to accomodate all
> scenarios.
> Right now I have another problem. In my stored proc I have begin
> transaction, commit transaction and rollback transaction. Now does this
> thing suits ok with java?
>
> The problem I am having is that my stored proc inserts data in 2 tables. If
> the first table (Table1) insert is successful and the second table (Table2)
> is failure, the Table1 gets locked.
> It only gets released when i shuts down JBoss. On shutting down the data
> (which is enterd by stored proc) gets rollback which is good.
> If I dont shutdown but instead go to another jsp which retrieves data from
> some other table then the lock on Table1 is released. But in this scenario
> the data (which is enterd by stored proc) stays which is not good.
>
> I am not sure why Table1 gets locked. The stored proc looks ok. It starts
> with begin transaction. After every insert is check @@error and if its not 0
> then i rollback transaction and RAISEERROR. I have check this stored proc
> with Query Analyzer and it works perfect.
>
> Any reason?

If you mix transactional control in the procedure with JDBC transactional
calls, such as setAutoCommit(false)
, or even worse, sending SQL like
"set chained mode on", etc, you'll weave a rats nest. As long as you
always start and end your transactions at the same level of the application
you will be able to manage. I suggest you just do your procedure work
without any 'begin tran' or commit calls etc. Just do the SQL work.
Then call your procedures from Java, after starting a transaction
in JDBC (setAutoCommit(false
)), then catch any exception and call
conn.rollback() in Java, or call conn.commit() in Java.
Joe Weinstein at BEA Systems

>
> Thanks
>
> "Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
> news:e4hS0vdqFHA.3600@tk2msftngp13.phx.gbl...
>
>
>

Sponsored Links