Cocoon's pooled db connection and/or ESQL logicsheet

[Alfredas Chmieliauskas <al...@sseriga.edu.lv>]

Hello,

I have to use pretty complex sql queries, thus I am using mssql stored 
procedures (so that the queries are stored precompiled in the sql server 
not the  java app/xsp page). ESQL logicsheet does not support calls to 
stored procedures (CallableStatements), as I know it.
Is there a way I could use the cocoon's pooled connection (defined in 
the properrties) instead of opening a new one "manually"? I know how to 
do it with the ESQL, but could anyone tell me a workaround for my own 
XSP-defined query.

Any hints, comments, experiences will be appreciated,

Alfredas


---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>

To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>

Re: Cocoon's pooled db connection and/or ESQL logicsheet

[Berin Loritsch <bl...@apache.org>]

Alfredas Chmieliauskas wrote:
> 
> Hello,
> 
> I have to use pretty complex sql queries, thus I am using mssql stored
> procedures (so that the queries are stored precompiled in the sql server
> not the  java app/xsp page). ESQL logicsheet does not support calls to
> stored procedures (CallableStatements), as I know it.
> Is there a way I could use the cocoon's pooled connection (defined in
> the properrties) instead of opening a new one "manually"? I know how to
> do it with the ESQL, but could anyone tell me a workaround for my own
> XSP-defined query.
> 
> Any hints, comments, experiences will be appreciated,

It's not too difficult.  Every Generator has member names "manager" that
is a reference to the ComponentManager.  Use the following code snippet:

// --------------------------------------------------------------------

ComponentSelector dbselector;
DataSourceComponent dsc;
Connection conn;

try {
    dbselector = (ComponentSelector) manager.lookup(DataSourceComponent.ROLE + "Selector");
    dsc = (DataSourceComponent) dbslector.select("name-entry");
    conn = dsc.getConnection();

    // do your stuff
    conn.commit()
} catch (Exception e) {
    if (null != conn) {
        conn.rollback();
    }
    throw e;
} finally {
    if (null != conn) conn.close(); // this returns it to the pool
    if (null != dsc) dbselector.release(dsc);
    if (null != dbselector) manager.release(dbselector);
}

// ---------------------------------------------------------------------

The reason for the try/catch/finally block is so that resources are cleaned
up properly, and you don't have any resource leaks.  Also note that the
rollback and conn.close methods require that you put them inside try/catch
blocks themselves (kind of annoying).

Please note that most cocoon actions, generators, etc. require autocommit
to be set off.  Using transactions also helps data integrity and performance
of your application.

---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>

To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>