Re: [2.0.4] SQLTransformer and j2ee vs. jdbc datasource

[Renaud Bruyeron <br...@fullsix.com>]

ok I think I've identified the problem. I am copying the DEV list too.

Tomcat 4.1.12 uses commons-dbcp to implement its connection pool.
There is a mechanism in DBCP to track abandonned objects, so that when a
Connection is closed, the associated Statements *AND* ResultSets are closed
too.

The problem therefore is with the SQLTransformer.Query inner class which
closes the Connection at the end of execute(), but the SQLTransformer still
tries to access the resultset to read the rows.
This does not work with DBCP because the ResultSet.close() is called when
Connection.close() is called (thanks to the AbandonnedTrace object).

I think this is a bug in the SQLTransformer: it should be modified so that
the connections are closed after the data has been read out of the
ResultSet.

I will look into fixing this now.

 - Renaud

"Renaud Bruyeron" <br...@fullsix.com> wrote in message
news:avi9h9$mtp$1@main.gmane.org...
>
> I am facing a weird problem with the SQLTransformer.
> This happens with cocoon 2.0.4 on jdk1.3.1 running in Tomcat 4.1.12
>
> I have a J2EE Datasource defined via Tomcat's JNDI (the db is MySQL) bound
> at "java:comp/env/jdbc/Publisher"
>
> I am trying to get cocoon to use that datasource via the
> <datasources><j2ee/></datasources> element
> in the cocoon.xconf:
>
> ...
>   <datasources>
>     <j2ee name="publisher">
>       <dbname>Publisher</dbname>
>     </j2ee>
>   </datasources>
> ...
>
> (I guess this should be for the Avalon-Excalibur list, but here we go:
> <rant>I had to read the source of Excalibur's J2eeDataSource to understand
> that this component prepends "java:comp/env/jdbc" to what I put in the
> <dbname/>...This is counter-intuitive, I must say. Either document it
> or make it use the full JNDI name...</rant>)
>
> I can't get the SQLTransformer to process the following xml using this
> "publisher" datasource:
>
> ...
> <sql:execute-query>
>           <sql:query name="news">
>  SELECT publish.headline, publish.bodytext, publish.urllink,
>         publish.URLtitle, publish.imgloc, publish.imgALT, publish.section
>         FROM publish,publish_sections
>         WHERE publish_sections.section = 'homenews' AND
> publish_sections.live_id = publish.id
>           </sql:query>
> </sql:execute-query>
> ...
>
> It insists on turning this into <rowset nrofrows="0" name="news"
> xmlns="http://apache.org/cocoon/SQL/2.0" />
>
> By modifying SQLTransformer with debug statements, I *know* that the SQL
> query went through and did return a line at least. But the Transformer
fails
> to send the corresponding sax events down the pipe...
>
> Even stranger, if instead of using the J2eeDataSource I use the
> JdbcDataSource component (which defines a cocoon-internal connection pool)
> with the same name and MySQL parameters, it all works:
>
> <datasources>
>     <jdbc name="publisher">
>       <pool-controller min="5" max="10"/>
>       <!-- identical setup as Tomcat's own connection pool -->
>       <dburl>jdbc:mysql://localhost:3306/xxxx?autoReconnect=true</dburl>
>       <user>xxxx</user>
>       <password>xxxx</password>
>     </jdbc>
>   </datasources>
>
> This is what really gets me: using the j2eedatasource, it does not work,
but
> using the jdbcdatasource, it works. and I *know* that even with the
> j2eedatasource, the SQL part of the transformer works (i.e the ResultSet
is
> not empty). Weird...
>
> Anyone has an idea? I am about to dive into SQLTransformer even further to
> nail down this problem, but if anyone has an idea to put me on the right
> track, I would be most grateful :)
>
> To be fully complete, this is the output of the LogTransformer for the
> <rowset/> element:
> [startPrefixMapping] prefix=,uri=http://apache.org/cocoon/SQL/2.0
> [startElement]
uri=http://apache.org/cocoon/SQL/2.0,local=rowset,raw=rowset
> [            ] 1.
>
uri=http://apache.org/cocoon/SQL/2.0,local=nrofrows,qname=nrofrows,type=CDAT
> A,valu
> e=0
> [            ] 2.
>
uri=http://apache.org/cocoon/SQL/2.0,local=name,qname=name,type=CDATA,value=
> news
> [endElement]
uri=http://apache.org/cocoon/SQL/2.0,local=rowset,qname=rowset
> [endPrefixMapping] prefix=
>
>  - Renaud
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.437 / Virus Database: 245 - Release Date: 06/01/2003
>
>
>
>
> ---------------------------------------------------------------------
> Please check that your question  has not already been answered in the
> FAQ before posting.     <http://xml.apache.org/cocoon/faq/index.html>
>
> To unsubscribe, e-mail:     <co...@xml.apache.org>
> For additional commands, e-mail:   <co...@xml.apache.org>
>
>




---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org