SQL Server JDBC - Weblogic JDBC driver seems to ignore 'set xact

Author

Weblogic JDBC driver seems to ignore 'set xact_abort on'

Radovan Biciste

2005-09-02, 8:23 pm

Hello,
I know it might not be the right group by Joe's messages were very
helpful to me.
It is concerning Weblogic SQL Server JDBC driver. We are using Weblogic
8.1 SP4. It seems to ignore 'set xact_abort on' settings and return
always exit code 0 from call of stored procedure even the procedure
obviously fails.
Here is my test:
I create my table and stored procedure as follows:
drop table joetable
go
drop procedure joeproc
go
create table joetable (a varchar(5) not null primary key)
go
create procedure joeproc as
begin
insert into joetable values('1')
insert into joetable values('1')
insert into joetable values('2')
select 1
insert into joetable values(NULL)
insert into joetable values('3')
end
go

Then I use my little test program to call to:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class SQLTest {

public SQLTest() {
}

public static void main(String[] args) throws Exception{
SQLTest sqlTest = new SQLTest();
// DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
DriverManager.registerDriver (new
net.sourceforge.jtds.jdbc.Driver());
DriverManager.registerDriver (new
weblogic.jdbc.sqlserver.SQLServerDriver());
DriverManager.registerDriver (new com.inet.tds.TdsDriver());

//Connection con =
DriverManager.getConnection(" jdbc:jtds:sqlserver:
//orcas:1433/Jericho14",
"wluser14", "wluser14" );
Connection con =
DriverManager.getConnection("jdbc:bea:sqlserver:// orcas:1433;databaseN
ame=Jericho14",
"wluser14", "wluser14" );
//Connection con =
DriverManager.getConnection(" jdbc:inetdae7a:orcas
:1433 ", "wluser14",
"wluser14" );

String sql = null;
Statement s = null;
CallableStatement cs = null;
try {
con. setAutoCommit(false)
;
s = con.createStatement();

sql = "set xact_abort on";
s.execute(sql);

sql = "{call dbo.joeproc}";

cs = con.prepareCall(sql);
cs.execute();

sql = "set xact_abort off";
s.execute(sql);

} finally {
cs.close();
s.close();
con.close();
}
}
}

Other drivers like jTDS and inet would throw SQL Exception, Weblogic's
just carries and exits the class with exit code 0.
Is this desired behaviour? I'm contacting BEA support too.
Thank you,
Radovan

Joe Weinstein

2005-09-02, 8:23 pm

Radovan Biciste wrote:

> Hello,
> I know it might not be the right group but Joe's messages were very
> helpful to me.
> It is concerning Weblogic SQL Server JDBC driver. We are using Weblogic
> 8.1 SP4. It seems to ignore 'set xact_abort on' settings and return
> always exit code 0 from call of stored procedure even the procedure
> obviously fails.
> Here is my test:
> I create my table and stored procedure as follows:
> drop table joetable
> go
> drop procedure joeproc
> go
> create table joetable (a varchar(5) not null primary key)
> go
> create procedure joeproc as
> begin
> insert into joetable values('1')
> insert into joetable values('1')
> insert into joetable values('2')
> select 1
> insert into joetable values(NULL)
> insert into joetable values('3')
> end
> go
>
> Then I use my little test program to call to:
> import java.sql.CallableStatement;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.Statement;
>
> public class SQLTest {
>
> public SQLTest() {
> }
> public static void main(String[] args) throws Exception{
> SQLTest sqlTest = new SQLTest();
> // DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
> DriverManager.registerDriver (new
> net.sourceforge.jtds.jdbc.Driver());
> DriverManager.registerDriver (new
> weblogic.jdbc.sqlserver.SQLServerDriver());
> DriverManager.registerDriver (new com.inet.tds.TdsDriver());
>
> //Connection con =
> DriverManager.getConnection(" jdbc:jtds:sqlserver:
//orcas:1433/Jericho14",
> "wluser14", "wluser14" );
> Connection con =
> DriverManager.getConnection("jdbc:bea:sqlserver:// orcas:1433;databaseN
ame=Jericho14",
>
> "wluser14", "wluser14" );
> //Connection con =
> DriverManager.getConnection(" jdbc:inetdae7a:orcas
:1433 ", "wluser14",
> "wluser14" );
>
> String sql = null;
> Statement s = null;
> CallableStatement cs = null;
> try {
> con. setAutoCommit(false)
;
> s = con.createStatement();
>
> sql = "set xact_abort on";
> s.execute(sql);
>
> sql = "{call dbo.joeproc}";
>
> cs = con.prepareCall(sql);
> cs.execute();
>
> sql = "set xact_abort off";
> s.execute(sql);
>
> } finally {
> cs.close();
> s.close();
> con.close();
> }
> }
> }
>
>
> Other drivers like jTDS and inet would throw SQL Exception, Weblogic's
> just carries and exits the class with exit code 0.
> Is this desired behaviour? I'm contacting BEA support too.
> Thank you,
> Radovan

Hi. I do see the desire for the behavior you want. However,
I understand the driver vendor's point of view, which is that
really, your procedure is returning several things, potentially,
including update counts for the first successful update, and
*then* in this case, after the first update-count, there is an
error packet. Some drivers may buffer up the update counts
(but not some other things) and if they find an error packet
while they are buffering, they immediately throw the exception.
Our driver doesn't. You may want the update counts coming back,
even if an exception is later thrown. In fact with our driver,
you will know which statement in the procedure threw the exception
because you get an update count for all the preceeding
successful ones. The other drivers lose that information.
NB: The DBMS is the only element that responds to
"set xact_abort on", and I can assure you that it did respond
in this case. You just didn't do the processing to see the
returns, and as you know (or can try), if you alter your
procedure to do the select first, or at least before any
failing insert, all the drivers you've cited will behave
like the driver we provide, so in the general case, you
need to do what I had suggested previously for any driver:
If you can see your way clear to put in code to fully
process the returns from the statement, you will always
get your exception, and with our driver you will also get
the information about which updates did succeed:

// The 'canonical form' for processing all the returns
// of any statement/stored procedure of standard JDBC.

boolean getResultSet = ps.execute();
int updateCount = -1;

// handle all in-line results from any statement/procedure
while (true) {
if (getResultSet) {
ResultSet r = ps.getResultSet();
// fully process and close result set here
while (r.next()) {}
r.close();
} else {
updateCount = ps.getUpdateCount();
if (updateCount != -1) {
// it's a real update count
}
}
// The JDBC spec says that (only) when we get -1 and null, are we done
if ((!getResultSet) && (updateCount == -1)) break;
getResultSet = ps.getMoreResults();
}
// If the statement is a CallableStatement, get output parameters now...

Joe

Sponsored Links