Transaction no longer active exception

[Vikas Lamba]

CROSS POST - ALREADY POSTED TO weblogic.developer.interest.ejb.cmp

I am using Weblogic 6.0, MS SQL Server and i-net JDBC drivers for SQL
Server. I am facing the following exception from my EJB...

ERROR - SQLDatabaseAccess: Error in executing query - Select
office_id,office_city From tblOffice
ERROR -
java.sql.SQLException: The transaction is no longer active (status =
Committed). No further JDBC access is allowed within this
transaction.
at weblogic.jdbcbase.jts.Connection.checkIfRolledBack(Connection.java:468)
at weblogic.jdbcbase.jts.Statement.executeQuery(Statement.java:41)
at weblogic.jdbc.rmi.internal.StatementImpl.executeQuery(StatementImpl.java:34)
at weblogic.jdbc.rmi.SerialStatement.executeQuery(SerialStatement.java:39)
at com.drkc.coral.db.SQLDatabaseAccess.executeQuery(SQLDatabaseAccess.java:78)

The above problem occurs if I try to make 2 calls to the EJB.
Basically, in the first call to the EJB, I update some tables within a
transaction and in the second method, I fire a select query which
appears in the above error...

Is it a problem with the driver or am I missing some weblogic setting
here? I am using the Weblogic connection pool and have set the number
of active connections to 2...

I read the documentation of the i-net drivers and they mention this
known bug on their website:
"With Version 6.0 and 6.0 Sp1, the WebLogic Server close all
Statements and ResultSet on a commit of a UserTransaction. This is a
limit with all XaDataSources."

Does this mean I'll have to look for a different driver or does it
mean that Weblogic 6.0 cannot support such a feature at all...

Vikas Lamba

[Joseph Weinstein]

Hi. The issue is that we provide you a pooled JDBC connection
in the context of an EJB transaction, and in order to guarantee
EJB transactional semantics and not allow any means of one user
retaining a JDBC reference that allows them to corrupt another
user's subsequent transaction, whenever that transaction
ends, either by commit or by rollback, that connection and
any of it's subordinate objects are no longer useable.
Because getting a connection from a pool is fast anyway,
every EJB method should obtain it's own connection, use it,
and close it without fail in a finally block.
Joe

[Vikas Lamba]

Hi

Many thanks for clearing up some of my doubts. I tried to follow your
advice regarding closing of a connection at the end of every EJB
method and the next time I call the EJB, the following exception comes
up:
java.sql.SQLException: Connection already closed

I use the following code to obtain a database connection...

Context ctx = null;

try {
ctx = new InitialContext();

DataSource ds = (javax.sql.DataSource)
ctx.lookup(dataSourceName);
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
.
.
.
}
catch (NamingException ex) {
ex.printStackTrace();
}
catch (SQLException ex) {
ex.printStackTrace();
}
finally {
try {
ctx.close();
}
catch (NamingException ex) {
ex.printStackTrace();
}
}

How can I create a new JDBC Connection here using the Datasource
semantics? Do you mean that I should do a DriverManger.getConnection()
for every EJB method instead of the Datasource.getConnection that I am
doing now...

Also, I'll list down the entries in my config.xml...
<JDBCTxDataSource EnableTwoPhaseCommit="true"
JNDIName="StaticData"
Name="StaticData" PoolName="StaticData" Targets="myserver"/>
<JDBCConnectionPool CapacityIncrement="2"
DriverName="com.inet.tds.XDataSource" InitialCapacity="2"
LoginDelaySeconds="1" MaxCapacity="100" Name="StaticData"
Properties="dataSourceName=StaticData;port=1433;user=sa;password=;ServerName=<DBServerName>;databaseName=MyDatabase;"
RefreshMinutes="1" Targets="myserver"
TestTableName="sysobjects" URL="jdbc:inetdae7:<DBServerName>"/>

As you can see, I have specified the initial capacity as 2... Does
that have any bearing on the problems I am facing?

Vikas

Joseph Weinstein <j...@bea.com> wrote in message news:<3BD84CBC...@bea.com>...

[Joseph Weinstein]

You're not closing the jdbc connection in the finally block. do this:

if (conn != null) try {conn.close();}catch(Exception ignore){}

ie:

finally {
if (conn != null) try {conn.close();}catch(Exception ignore){}
}

You should define the connection object at the same time as the context,
and obtain it in the main try block.