[jboss-user] [Datasource Configuration] - SQLException encountered while trying to use a DB connection

[mou_gc]

mou_gc [https://community.jboss.org/people/mou_gc] created the discussion

"SQLException encountered while trying to use a DB connection"

To view the discussion, visit: https://community.jboss.org/message/792713#792713

--------------------------------------------------------------
Hi all,

We have configured a read datasource in our JBOSS 4.2.3 AS, for reading from one of the tables in the Oracle 10g DB of our application. In the business logic of our application, first, we create a Query Object, which has a Connection object as its component variable. This Connection object is initialised by obtaining a conncetion from the mentioned data source. In the next step, we try to do a read from the DB, and this read uses the stored connection object to execute the query.

Initially, this logic was not working and we received error messages like "Caused by: java.sql.SQLException: Connection is not associated with a managed ***@6acfc9" in our application log and "Closing a connection for you" in the server.log. After going through the link - https://community.jboss.org/docs/DOC-9325 https://community.jboss.org/wiki/ConfigCachedConnectionManager, we set SpecCompliant as true in jbossjca-service.xml, to resolve this issue, although we didn't turn off debugging. This made things to work in our dev set up, but in the production servers, almost all the times it fails. We still see the exception -
java.sql.SQLException: Connection is not associated with a managed ***@37408cb8

Can anybody help us to understand if there is any timeout for connections or if any additional configuration change is needed? Thanks in advance.
(Attaching the ds xml file)
Mou
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/792713#792713]

Start a new discussion in Datasource Configuration at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077]

[Wolf-Dieter Fink]

Wolf-Dieter Fink [https://community.jboss.org/people/wdfink] created the discussion

"Re: SQLException encountered while trying to use a DB connection"

To view the discussion, visit: https://community.jboss.org/message/792822#792822

--------------------------------------------------------------
I'm not certain that I understand your problem.
Could you share the related code?
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/792822#792822]

Start a new discussion in Datasource Configuration at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077]

[mou_gc]

mou_gc [https://community.jboss.org/people/mou_gc] created the discussion

"Re: SQLException encountered while trying to use a DB connection"

To view the discussion, visit: https://community.jboss.org/message/793156#793156

--------------------------------------------------------------
Hi Wolf-Dieter,

The following class has the methods for creation and use of the query object -
public class QueryHandler
{
  private final Map<Integer,PagedQuery> pageQueries =
    Collections.synchronizedMap(new HashMap<Integer,PagedQuery>());

  public void createPagedQuery(String[] attributes, String condition)
  {
    PagedQuery pagedQuery = new PagedQuery(attributes, condition);
    int queryId = pagedQuery.getQueryId();
    pageQueries.put(queryId, pagedQuery);

    System.out.println("Created paged query " + queryId);
  }

   public PageResult getPage(int id, int rows)
   {
     PagedQuery pagedQuery = pageQueries.get(id);
     if (pagedQuery != null)
     {
       AttributesList page = pagedQuery.getPage(rows);
       //return PageResult after processing the page data obtained
     }
   }
}

And the query class is as follows -
public class PagedQuery
{
    protected final Connection conn;
    private final String query;
    private int retrievedRows;

    PagedQuery(String attributes[], String condition) throws DatabaseException
    {
        Context ctx = new InitialContext();
    Datasource dataSource = (DataSource) ctx.lookup("java:Datasources/fmread");
        conn = dataSource.getConnection();
        query = "SELECT " + attributes + " FROM READ_TABLE WHERE " + condition;
    }

    AttributesList getPage(int rows) throws DatabaseException
    {
          AttributesList page = getData(rows);
          retrievedRows += page.size();
          return page;
    }
    int getQueryId()
    {
          return hashCode();
    }

    public final AttributesList getData(int rows) throws DatabaseException
    {
        try
    {
      currentStatement = conn.createStatement();
        ResultSet resultSet = currentStatement.executeQuery(query);
        //Process resultset to AttributesList and return reqd number of rows
    }
        catch (SQLException e)
    {
        throw new DatabaseException("Error executing Oracle query: " +
                        e.getMessage());
    }
    }
}

This is not the actual code as I have simplified it for your understanding. The client object first makes a call to createPagedQuery() method of the QueryHandler class and then does one or more getPage() calls based on the number of records desired. It is during the second call that we encounter SQLException most times  -

java.sql.SQLException: Connection is not associated with a managed ***@37408cb8
        at org.jboss.resource.adapter.jdbc.WrappedConnection.lock(WrappedConnection.java:81)
        at org.jboss.resource.adapter.jdbc.WrappedConnection.createStatement(WrappedConnection.java:170)
        at xxxQuery.executeQuery
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/793156#793156]

Start a new discussion in Datasource Configuration at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077]

[mou_gc]

mou_gc [https://community.jboss.org/people/mou_gc] created the discussion

"Re: SQLException encountered while trying to use a DB connection"

To view the discussion, visit: https://community.jboss.org/message/794778#794778

--------------------------------------------------------------
Hi Wolf-Dieter/all,

Still awaiting an answer, could anybody please help in understanding this issue?

Thanks in advance,
Mou
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/794778#794778]

Start a new discussion in Datasource Configuration at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077]