cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Markus Vaterlaus <mvaterl...@gmail.com>
Subject Re: Running a sequence of SQL queries with a single result
Date Mon, 08 Nov 2004 12:47:11 GMT
Hi Derek, hi list,

ASFAIK you can only send one SQL-statement at a time. However, with
ESQL you have the possibility to use several SQL-Statements in one
xsp. E.q.

   <esql:execute-query>
        <esql:query>
                   select * from  sometable
	</esql:query>
        ....
     </esql:execute-query>
   <esql:execute-query>
        <esql:query>
                   select * from othertable
	</esql:query>
          ....
     </esql:execute-query>

HTH

Markus






On Mon, 08 Nov 2004 13:44:56 +0200, Derek Hohls <dhohls@csir.co.za> wrote:
> For more complex queries, I'd like to be able to
> run chained queries and get a single result.  For
> example, the mySQL manual has this series of
> steps:
> 
> CREATE TEMPORARY TABLE tmp (
>         article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
>         price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);
> 
> LOCK TABLES shop READ;
> 
> INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
> 
> SELECT shop.article, dealer, shop.price FROM shop, tmp
> WHERE shop.article=tmp.article AND shop.price=tmp.price;
> 
> UNLOCK TABLES;
> 
> DROP TABLE tmp;
> 
> When I run these steps from the mySQL command
> line interface, the results are as expected.
> 
> However, when I try to insert this into an
> <sql:execute-query>
>    <sql:query>
> type of page, and run this via a normal
> SQL transformer eg.
> 
>    <map:match pattern="shop-sql.xml">
>      <map:generate src="shop-sql.xml" type="jx"/>
>      <!-- === SQL Connector ====       -->
>      <map:transform type="sql">
>        <map:parameter name="use-connection" value="myDB"/>
>        <map:parameter name="show-nr-of-rows" value="true"/>
>        <map:parameter name="clob-encoding" value="UTF-8"/>
>      </map:transform>
>      <map:transform src="stylesheets/default.xsl"/>
>      <map:serialize type="xml"/>
>    </map:match>
> 
> I get the following error:
> 
> Syntax error or access violation, message from server:
> "You have an error in your SQL syntax. Check the manual
> that corresponds to your MySQL server version for the right
> syntax to use near '; LOCK TABLES ....
> 
> I assume this means the SQL generator can only work with
> one query - what are alternative ways of getting to a result?
> 
> Thanks
> derek
> 
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
> MailScanner thanks transtec Computers for their support.
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
> 
>

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


Mime
View raw message