Mailing-List: contact users-help@cocoon.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@cocoon.apache.org
Received-SPF: pass (hermes.apache.org: local policy)
Message-Id: <s18f88e7.067@cs-emo.csir.co.za>
Date: Mon, 08 Nov 2004 14:55:18 +0200
From: "Derek Hohls" <DHohls@csir.co.za>
To: <users@cocoon.apache.org>
Subject: Re: Running a sequence of SQL queries with a single result
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Thanks Markus

Yes, I have worked with ESQL before... I was just hoping to
avoid it as it adds to the complexity of the application.
(plus I am trying to not use XSP as a "matter of principle"...
but, in the end, if the designers do not offer any other choice
here, then I guess my principles take a back seat to the 
needs at hand!)

>>> mvaterlaus@gmail.com 2004/11/08 02:47:11 PM >>>
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 


-- 
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