You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cocoon.apache.org by Marcus Crafter <cr...@dresdnerbank.de> on 2000/11/01 18:04:24 UTC
[C2] esql connections diff
Hi All,
Hope everything is going well around the C2 globe.
Attached is a patch that makes a change to the abilities of the esql
taglib.
Background: In our environment we are interfacing to a legacy database
with a combination of stored procedures and select's.
In our particular case, stored procedures generate temporary tables
which are valid only for the current database session. They are
automatically deleted afterwards. Our typical use of the database is
to call a stored procedure and then do a select on this temporary table.
The issue is that both the stored procedure invocation and select need
to be done with the same jdbc 'Connection' object, otherwise the
temporary table will be already deleted before the sql 'select' starts.
Currently the esql taglib uses a separate connection object for each
query causing us all sorts of bother. The attached patch allows the
user to specify more than one <esql:query>..</esql:query> inside a
<esql:execute-query> tag. The <esql:query>'s are then executed using
the same 'Connection object'.
eg:
<esql:execute-query inner-method="no">
<esql:driver>...</esql:driver>
<esql:dburl>...</esql:dburl>
<esql:username>...</esql:username>
<esql:password>...</esql:password>
<!-- first query executing stored procedure -->
<esql:query>
execute procedure ...
</esql:query>
<!-- second query performing select -->
<esql:query>
select ...
</esql:query>
<esql:results>
<ROW>
<esql:get-columns/>
</ROW>
</esql:results>
</esql:execute-query>
2 things to be aware of though - the results are those from the last
query; all others are dumped (not so bad), however the 'last' query
may be xslt implementation dependant (ie. is the parse order of a
stylesheet defined ?).
Any suggestions or improvements are welcomed.
Cheers,
Marcus
--
.....
,,$$$$$$$$$, Marcus Crafter
;$' '$$$$: Computer Systems Engineer
$: $$$$: Open Software Associates GmbH
$ o_)$$$: 82-84 Mainzer Landstrasse
;$, _/\ &&:' 60327 Frankfurt Germany
' /( &&&
\_&&&&' Email : Marcus.Crafter@osa.de
&&&&. Business Hours : +49 69 9757 200
&&&&&&&: After Hours : +49 69 49086750
Re: [C2] esql connections diff
Posted by Paul Russell <pa...@luminas.co.uk>.
On Thu, Nov 02, 2000 at 10:18:06PM -0500, Donald Ball wrote:
> On Thu, 2 Nov 2000, Paul Russell wrote:
> > Yeah. Looks good - should make connection pooling etc easier
> > to implement, too. Is there an XSD or DTD for esql as it stands
> > anywhere? I've been trying to implement the connection pooling
> > in esql, but suffering due to not understanding the semantics
> > of the taglib.
> nope - that's next up on the list of things to do after the namespace has
> been finalized - and i think we're pretty close now!
Heh. Okay - let me know when you check in the new version using
the above schema (I assume that's the next stage?) and I'll get
on with the connection pooling.
As an aside, I should let you all know that I'm just about to
move into a new flat which won't have a phone line just yet.
I'll only be able to get online when I'm either in the office,
or I'm feeling particularly carefree with my mobile bill (gulp).
Expect slow responses for a week or so.
Paul
--
Paul Russell <pa...@luminas.co.uk>
Technical Director, http://www.luminas.co.uk
Luminas Ltd.
Re: [C2] esql connections diff
Posted by Donald Ball <ba...@webslingerZ.com>.
On Thu, 2 Nov 2000, Paul Russell wrote:
> On Thu, Nov 02, 2000 at 12:34:24AM -0500, Donald Ball wrote:
> > <esql:connection>
> > <esql:driver>...</esql:driver>
> > <esql:execute-query>
> > <esql:query>select * from department_table</esql:query>
> > <esql:results>...</esql:results>
> > </esql:execute-query>
> > <esql:execute-query>
> > <esql:query>select * from user_table</esql:query>
> > <esql:results>
> > <!--connection to other database for nested query-->
> > <esql:connection>
> > <esql:driver>...</esql:driver>
> > <esql:execute-query>
> > <esql:query>select * from password_table</esql:query>
> > <esql:results>...</esql:results>
> > </esql:execute-query>
> > </esql:connection>
> > <!--end connection to other database-->
> > </esql:results>
> > </esql:execute-query>
> > </esql:connection>
>
> Yeah. Looks good - should make connection pooling etc easier
> to implement, too. Is there an XSD or DTD for esql as it stands
> anywhere? I've been trying to implement the connection pooling
> in esql, but suffering due to not understanding the semantics
> of the taglib.
nope - that's next up on the list of things to do after the namespace has
been finalized - and i think we're pretty close now!
- donald
Re: [C2] esql connections diff
Posted by Paul Russell <pa...@luminas.co.uk>.
On Thu, Nov 02, 2000 at 12:34:24AM -0500, Donald Ball wrote:
> <esql:connection>
> <esql:driver>...</esql:driver>
> <esql:execute-query>
> <esql:query>select * from department_table</esql:query>
> <esql:results>...</esql:results>
> </esql:execute-query>
> <esql:execute-query>
> <esql:query>select * from user_table</esql:query>
> <esql:results>
> <!--connection to other database for nested query-->
> <esql:connection>
> <esql:driver>...</esql:driver>
> <esql:execute-query>
> <esql:query>select * from password_table</esql:query>
> <esql:results>...</esql:results>
> </esql:execute-query>
> </esql:connection>
> <!--end connection to other database-->
> </esql:results>
> </esql:execute-query>
> </esql:connection>
Yeah. Looks good - should make connection pooling etc easier
to implement, too. Is there an XSD or DTD for esql as it stands
anywhere? I've been trying to implement the connection pooling
in esql, but suffering due to not understanding the semantics
of the taglib.
Paul
--
Paul Russell <pa...@luminas.co.uk>
Technical Director, http://www.luminas.co.uk
Luminas Ltd.
Re: [C2] xsp in esql ? (was Re: [C2] esql connections diff)
Posted by Donald Ball <ba...@webslingerZ.com>.
On Sat, 4 Nov 2000, Marcus Crafter wrote:
> Hi Donald (and others),
>
> What are your thoughts about being able to parameterize the queries
> with your suggested syntax, perhaps with xsp ? For example:
>
> <esql:connection>
>
> <esql:driver>...</esql:driver>
>
> <xsp:logic>
> for (int i=0; i < sz; i++) {
> <esql:execute-query>
> <esql:query>
> INSERT INTO mytable VALUES
> ('<xsp:expr>field[i]</xsp:expr>');
> </esql:query>
> </esql:execute-query>
> }
> </xsp:logic>
>
> </esql:connection>
>
> This is something we've done here in C1 using the SQL processor (xsp
> is done first, then the sql processor). Such abilities in esql would
> also be very useful.
>
> I guess it's a general question about using xsp within esql.
> What do you think ? (or is this already possible ?)
yes, it's already possible - you want to use prepared statements here so
that you don't have to worry about escaping apostraphes and such. try:
<esql:statement>insert into mytable values (
<esql:parameter><xsp:expr>field[0]</xsp:expr></esql:parameter>
)
</esql:statement>
i'm probably going to merge the statement and query elements, i'll just
consider a query to be a prepared statement if a parameter element appears
inside it. :)
- donald
[C2] xsp in esql ? (was Re: [C2] esql connections diff)
Posted by Marcus Crafter <cr...@dresdnerbank.de>.
Hi Donald (and others),
What are your thoughts about being able to parameterize the queries
with your suggested syntax, perhaps with xsp ? For example:
<esql:connection>
<esql:driver>...</esql:driver>
<xsp:logic>
for (int i=0; i < sz; i++) {
<esql:execute-query>
<esql:query>
INSERT INTO mytable VALUES
('<xsp:expr>field[i]</xsp:expr>');
</esql:query>
</esql:execute-query>
}
</xsp:logic>
</esql:connection>
This is something we've done here in C1 using the SQL processor (xsp
is done first, then the sql processor). Such abilities in esql would
also be very useful.
I guess it's a general question about using xsp within esql.
What do you think ? (or is this already possible ?)
Cheers,
Marcus
--
.....
,,$$$$$$$$$, Marcus Crafter
;$' '$$$$: Computer Systems Engineer
$: $$$$: Open Software Associates GmbH
$ o_)$$$: 82-84 Mainzer Landstrasse
;$, _/\ &&:' 60327 Frankfurt Germany
' /( &&&
\_&&&&' Email : Marcus.Crafter@osa.de
&&&&. Business Hours : +49 69 9757 200
&&&&&&&: After Hours : +49 69 49086750
Re: [C2] esql connections diff
Posted by Donald Ball <ba...@webslingerZ.com>.
On Wed, 1 Nov 2000, Marcus Crafter wrote:
> 2 things to be aware of though - the results are those from the last
> query; all others are dumped (not so bad), however the 'last' query
> may be xslt implementation dependant (ie. is the parse order of a
> stylesheet defined ?).
>
> Any suggestions or improvements are welcomed.
hmm... that's definitely a good idea, i'm just not so sure the grammar is
as i'd like it. i think the query and the results elements should be
coupled more closely. perhaps if connection information were to be put
into its own element, and then whenever a query is executed, the closest
connection information element parent of the query node would be used?
<esql:connection>
<esql:driver>...</esql:driver>
<esql:execute-query>
<esql:query>select * from department_table</esql:query>
<esql:results>...</esql:results>
</esql:execute-query>
<esql:execute-query>
<esql:query>select * from user_table</esql:query>
<esql:results>
<!--connection to other database for nested query-->
<esql:connection>
<esql:driver>...</esql:driver>
<esql:execute-query>
<esql:query>select * from password_table</esql:query>
<esql:results>...</esql:results>
</esql:execute-query>
</esql:connection>
<!--end connection to other database-->
</esql:results>
</esql:execute-query>
</esql:connection>
that way the scope of the connection is explicit... perhaps even paving
the way for support for manual commits and transactions and such. hmm. any
thoughts from other esql/sql users?
- donald