<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<body link="#355491" alink="#4262a1" vlink="#355491" style="background: #e2e2e2; margin: 0; padding: 20px;">
<div>
<table cellpadding="0" bgcolor="#FFFFFF" border="0" cellspacing="0" style="border: 1px solid #dadada; margin-bottom: 30px; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" style="border: solid 2px #ccc; background: #dadada; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td bgcolor="#000000" valign="middle" height="58px" style="border-bottom: 1px solid #ccc; padding: 20px; -moz-border-radius-topleft: 3px; -moz-border-radius-topright: 3px; -webkit-border-top-right-radius: 5px; -webkit-border-top-left-radius: 5px;">
<h1 style="color: #333333; font: bold 22px Arial, Helvetica, sans-serif; margin: 0; display: block !important;">
<!-- To have a header image/logo replace the name below with your img tag -->
<!-- Email clients will render the images when the message is read so any image -->
<!-- must be made available on a public server, so that all recipients can load the image. -->
<a href="https://community.jboss.org/index.jspa" style="text-decoration: none; color: #E1E1E1">JBoss Community</a></h1>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" style="font: normal 12px Arial, Helvetica, sans-serif; color:#333333; padding: 20px; -moz-border-radius-bottomleft: 4px; -moz-border-radius-bottomright: 4px; -webkit-border-bottom-right-radius: 5px; -webkit-border-bottom-left-radius: 5px;"><h3 style="margin: 10px 0 5px; font-size: 17px; font-weight: normal;">
Re: SQLException encountered while trying to use a DB connection
</h3>
<span style="margin-bottom: 10px;">
created by <a href="https://community.jboss.org/people/mou_gc">mou_gc</a> in <i>Datasource Configuration</i> - <a href="https://community.jboss.org/message/793156#793156">View the full discussion</a>
</span>
<hr style="margin: 20px 0; border: none; background-color: #dadada; height: 1px;">
<div class="jive-rendered-content"><p>Hi Wolf-Dieter,</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>The following class has the methods for creation and use of the query object -</p><p>public class QueryHandler </p><p>{</p><p>  private final Map<Integer,PagedQuery> pageQueries = </p><p>    Collections.synchronizedMap(new HashMap<Integer,PagedQuery>());</p><p>    </p><p>  public void createPagedQuery(String[] attributes, String condition)</p><p>  {</p><p>    PagedQuery pagedQuery = new PagedQuery(attributes, condition);</p><p>    int queryId = pagedQuery.getQueryId();</p><p>    pageQueries.put(queryId, pagedQuery);</p><p>    </p><p>    System.out.println("Created paged query " + queryId);</p><p>  }</p><p>  </p><p>   public PageResult getPage(int id, int rows)</p><p>   {</p><p>     PagedQuery pagedQuery = pageQueries.get(id);</p><p>     if (pagedQuery != null)</p><p>     {</p><p>       AttributesList page = pagedQuery.getPage(rows);</p><p>       //return PageResult after processing the page data obtained</p><p>     }</p><p>   }</p><p> }</p><p>  </p><p>And the query class is as follows - </p><p>public class PagedQuery</p><p>{</p><p>    protected final Connection conn;</p><p>    private final String query;</p><p>    private int retrievedRows;</p><p>      </p><p>    PagedQuery(String attributes[], String condition) throws DatabaseException</p><p>    {</p><p>        Context ctx = new InitialContext();</p><p>    Datasource dataSource = (DataSource) ctx.lookup("java:Datasources/fmread");</p><p>        conn = dataSource.getConnection();</p><p>        query = "SELECT " + attributes + " FROM READ_TABLE WHERE " + condition;</p><p>    }</p><p>    </p><p>    AttributesList getPage(int rows) throws DatabaseException</p><p>    {</p><p>          AttributesList page = getData(rows);</p><p>          retrievedRows += page.size();</p><p>          return page;</p><p>    }</p><p>    int getQueryId()</p><p>    {</p><p>          return hashCode();</p><p>    }</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>    public final AttributesList getData(int rows) throws DatabaseException</p><p>    {</p><p>        try</p><p>    {</p><p>      currentStatement = conn.createStatement();</p><p>        ResultSet resultSet = currentStatement.executeQuery(query);</p><p>        //Process resultset to AttributesList and return reqd number of rows</p><p>    }</p><p>        catch (SQLException e)</p><p>    {</p><p>        throw new DatabaseException("Error executing Oracle query: " +</p><p>                        e.getMessage());</p><p>    }</p><p>    }</p><p>}</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>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  -</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>java.sql.SQLException: Connection is not associated with a managed connection.org.jboss.resource.adapter.jdbc.jdk5.WrappedConnectionJDK5@37408cb8</p><p>        at org.jboss.resource.adapter.jdbc.WrappedConnection.lock(WrappedConnection.java:81)</p><p>        at org.jboss.resource.adapter.jdbc.WrappedConnection.createStatement(WrappedConnection.java:170)</p><p>        at xxxQuery.executeQuery</p></div>
<div style="background-color: #f4f4f4; padding: 10px; margin-top: 20px;">
<p style="margin: 0;">Reply to this message by <a href="https://community.jboss.org/message/793156#793156">going to Community</a></p>
<p style="margin: 0;">Start a new discussion in Datasource Configuration at <a href="https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077">Community</a></p>
</div></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>