You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Thomas Zehetbauer <th...@hostmaster.org> on 2004/08/08 22:25:26 UTC
Problem with xsp/esql
Hi,
I would like to use the same <esql:results> block for currently three
different queries that return the same fields but use distinct
conditions and parameters. I tried to use a <xsp:logic> block for
choosing the <esql:query> but I only get an error message. Is this kind
of logic possible with XSP/logicsheets?
Tom
--
T h o m a s Z e h e t b a u e r ( TZ251 )
PGP encrypted mail preferred - KeyID 96FFCB89
finger thomasz@hostmaster.org for key
Experience is what you get when you expected something else.
Re: Problem with xsp/esql
Posted by Thomas Zehetbauer <th...@hostmaster.org>.
On Mit, 2004-08-11 at 11:57 +0200, Olivier Billard wrote:
> Additionally some databases (not postgresql) support prepared statements
> > that are much faster because the SQL is parsed and optimized only once
> > and uses dynamic parameters thereafter.
>
> Isn't that true only if you're using your prepared statement many times in the code ? If the statement is created each time you
> call your XSP, I'm not sure there is a gain. But I'm not a JDBC pro.
Basically yes, but some pooling DataSource implementations keep track of
PreparedStatements and return them accordingly.
Tom
--
T h o m a s Z e h e t b a u e r ( TZ251 )
PGP encrypted mail preferred - KeyID 96FFCB89
finger thomasz@hostmaster.org for key
Chemists don't die, they just stop to react.
Re: Problem with xsp/esql
Posted by Olivier Billard <ol...@laposte.net>.
Thomas Zehetbauer wrote:
> Or to directly use the DataSource, Connection, PreparedStatement, and
> ResultSet in a <xsp:logic> block.
So in this case, you would better doing it in flow with a DAO... :)
> I strongly recommend against manually escaping the parameters because
> different databases may need to escape other sequences and employ other
> syntax. The JDBC driver should know best ;-)
Of course, this way is not the best, but for a given database, to make the code look better in XSP, that's what I did.
You can also use a DAO in XSP, anyway.
But I agree that esql:parameter should be used.
> Additionally some databases (not postgresql) support prepared statements
> that are much faster because the SQL is parsed and optimized only once
> and uses dynamic parameters thereafter.
Isn't that true only if you're using your prepared statement many times in the code ? If the statement is created each time you
call your XSP, I'm not sure there is a gain. But I'm not a JDBC pro.
--
Olivier Billard
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Problem with xsp/esql
Posted by Thomas Zehetbauer <th...@hostmaster.org>.
Or to directly use the DataSource, Connection, PreparedStatement, and
ResultSet in a <xsp:logic> block.
I strongly recommend against manually escaping the parameters because
different databases may need to escape other sequences and employ other
syntax. The JDBC driver should know best ;-)
Additionally some databases (not postgresql) support prepared statements
that are much faster because the SQL is parsed and optimized only once
and uses dynamic parameters thereafter.
Tom
--
T h o m a s Z e h e t b a u e r ( TZ251 )
PGP encrypted mail preferred - KeyID 96FFCB89
finger thomasz@hostmaster.org for key
-----BEGIN GEEK CODE BLOCK-----
GS/IT d-- s: a-- C++++ UL++++ P+>$ L++>$ E--- W+ N+ o? !K w++$ O M-
V? PS+++ PE++ Y+ PGP+++ t+ 5 X R- tv b- DI(+) D+ G>++ e h! !r y+
------END GEEK CODE BLOCK------
Re: Problem with xsp/esql
Posted by Olivier Billard <ol...@laposte.net>.
In this particular case, I (humbly and personnaly) only see the solution of using 2 separate execute-query, maybe with collecting
the results and render them separately outside the queries, in this case, the render code is not duplicated. But if the results
are numerous, it is not performant.
--
Olivier
PS : I personnaly don't user <esql:parameter> and escape them via a function... it avoids these brain-twisters...
Thomas Zehetbauer wrote:
> In my opinion this does not look "simple" or "nice" and only allows me
> to execute 2 of the desired 3 queries, how should I reformat this one:
>
> SELECT English,German FROM GermanEnglish WHERE English REGEXP
> <esql:parameter><xsp:expr>query</xsp:expr></esql:parameter> OR German
> REGEXP <esql:parameter><xsp:expr>query</xsp:expr></esql:parameter> ORDER
> BY English
>
> Of course I could simply put the <xsp:logic> outside the <esql:execute-
> query> and copy the <esql:results> block, but I think we agree that copy
> and paste is evil practice and should be avoided. I can think of many
> situations where similar behaviour is desired and wonder if esql cannot
> be used for these.
>
> Tom
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Problem with xsp/esql
Posted by Thomas Zehetbauer <th...@hostmaster.org>.
In my opinion this does not look "simple" or "nice" and only allows me
to execute 2 of the desired 3 queries, how should I reformat this one:
SELECT English,German FROM GermanEnglish WHERE English REGEXP
<esql:parameter><xsp:expr>query</xsp:expr></esql:parameter> OR German
REGEXP <esql:parameter><xsp:expr>query</xsp:expr></esql:parameter> ORDER
BY English
Of course I could simply put the <xsp:logic> outside the <esql:execute-
query> and copy the <esql:results> block, but I think we agree that copy
and paste is evil practice and should be avoided. I can think of many
situations where similar behaviour is desired and wonder if esql cannot
be used for these.
Tom
--
T h o m a s Z e h e t b a u e r ( TZ251 )
PGP encrypted mail preferred - KeyID 96FFCB89
finger thomasz@hostmaster.org for key
If there is a god, you are an authorized representative.
Re: Problem with xsp/esql
Posted by Olivier Billard <ol...@laposte.net>.
This can be done using other variables : orderBy, parameter, etc, that you determine before the <esql:execute-query>, which will
look like :
<esql:query>
<xsp:expr>queryStart</xsp:expr> ... <esql:parameter><xsp:expr>param<xsp:expr></esql:parameter> ...
<xsp:expr>sortBy</xsp:expr>
</esql:query>
--
Olivier
Thomas Zehetbauer wrote:
> Unfortunately I need to use <esql:parameter> and although I request the
> same columns I use different select and order clauses and in one case I
> even need to pass the same parameter twice.
>
> Tom
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Problem with xsp/esql
Posted by Thomas Zehetbauer <th...@hostmaster.org>.
Unfortunately I need to use <esql:parameter> and although I request the
same columns I use different select and order clauses and in one case I
even need to pass the same parameter twice.
Tom
--
T h o m a s Z e h e t b a u e r ( TZ251 )
PGP encrypted mail preferred - KeyID 96FFCB89
finger thomasz@hostmaster.org for key
Those, who are willing to give up essential liberty
for the sake of short-term security
deserve neither liberty nor security.
- Benjamin Franklin
Re: Problem with xsp/esql
Posted by Olivier Billard <ol...@laposte.net>.
Thomas,
There is no pb here, with the code I proposed : since you request the same columns, the code works without change...
Thomas Zehetbauer wrote:
> Hi Olivier,
>
> this may explain why my <esql:execute-query> -> <xsp:logic> ->
> <esql:query> construct does not work. But how then can I use the same
> <esql:results> block for different queries returning the same columns?
>
> Tom
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Problem with xsp/esql
Posted by Thomas Zehetbauer <th...@hostmaster.org>.
Hi Olivier,
this may explain why my <esql:execute-query> -> <xsp:logic> ->
<esql:query> construct does not work. But how then can I use the same
<esql:results> block for different queries returning the same columns?
Tom
--
T h o m a s Z e h e t b a u e r ( TZ251 )
PGP encrypted mail preferred - KeyID 96FFCB89
finger thomasz@hostmaster.org for key
Error reading FAT record: Try the SKINNY one? (Y/N)
Re: Problem with xsp/esql
Posted by Olivier Billard <ol...@laposte.net>.
Hi Thomas,
The ESQL logicsheet (a least in 2.1.4) doesn't use other code than <esql:*> elements in a <esql:execute-query>.
Try to determine your query before getting in the execute-query. Your code will look simpler :
<xsp:logic>
String query = null;
if (cond1) {
query = "";
} else if (cond2) {
query = "";
} else ...
</xsp:logic>
<esql:execute-query>
<esql:query><xsp:expr>query</xsp:expr></esql:query>
....
<esql:execute-query>
--
Olivier Billard
Thomas Zehetbauer wrote:
> Hi,
>
> I would like to use the same <esql:results> block for currently three
> different queries that return the same fields but use distinct
> conditions and parameters. I tried to use a <xsp:logic> block for
> choosing the <esql:query> but I only get an error message. Is this kind
> of logic possible with XSP/logicsheets?
>
> Tom
>
>
>
> ------------------------------------------------------------------------
>
> <?xml version="1.0" encoding="UTF-8"?>
> <xsp:page
> language="java"
> xmlns:esql="http://apache.org/cocoon/SQL/v2"
> xmlns:xsp-request="http://apache.org/xsp/request/2.0"
> xmlns:xsp="http://apache.org/xsp">
> <page>
> <title>German/English Dictionary</title>
> <stylesheet>css/Dictionary.css</stylesheet>
> <content>
> <form>
> <table class="query">
> <tr class="query">
> <td class="queryCaption">Query</td>
> <td class="queryValue"><input class="queryValue" name="query"/></td>
> </tr>
> <tr class="direction">
> <td class="directionCaption">Direction</td>
> <td class="directionValue">
> <select class="directionValue" name="direction">
> <option value="BOTH">both</option>
> <option value="ENGLISH">German -> English</option>
> <option value="GERMAN">English -> German</option>
> </select>
> </td>
> </tr>
> <tr class="submit">
> <td class="submit" colspan="2"><input class="submit" type="submit" value="Go!"/></td>
> </tr>
> </table>
> </form>
> <xsp:logic>
> String query = <xsp-request:get-parameter name="query"/>;
> String direction = <xsp-request:get-parameter name="direction"/>;
> if (query != null && direction != null) {
> <esql:connection>
> <esql:pool>Dictionary</esql:pool>
> <esql:execute-query>
> <xsp:logic>
> if ("ENGLISH".equals(direction)) {
> <esql:query>SELECT English,German FROM GermanEnglish WHERE German REGEXP <esql:parameter><xsp:expr>query</xsp:expr></esql:parameter> ORDER BY English</esql:query>
> } else if ("GERMAN".equals(direction)) {
> <esql:query>SELECT English,German FROM GermanEnglish WHERE English REGEXP <esql:parameter><xsp:expr>query</xsp:expr></esql:parameter> ORDER BY English</esql:query>
> } else {
> <esql:query>SELECT English,German FROM GermanEnglish WHERE English REGEXP <esql:parameter><xsp:expr>query</xsp:expr></esql:parameter> OR German REGEXP <esql:parameter><xsp:expr>query</xsp:expr></esql:parameter> ORDER BY English</esql:query>
> }
> </xsp:logic>
> <esql:results>
> <table class="result">
> <tr class="resultHeader">
> <td class="resultHeaderEnglish">English</td>
> <td class="resultHeaderGerman">German</td>
> </tr>
> <esql:row-results>
> <tr class="result">
> <td class="resultEnglish"><esql:get-string column="English"/></td>
> <td class="resultGerman"><esql:get-string column="German"/></td>
> </tr>
> </esql:row-results>
> </table>
> </esql:results>
> </esql:execute-query>
> </esql:connection>
> }
> </xsp:logic>
> </content>
> </page>
> </xsp:page>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org