You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Derek Hohls <DH...@csir.co.za> on 2004/11/08 12:44:56 UTC

Running a sequence of SQL queries with a single result

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

Re: Running a sequence of SQL queries with a single result

Posted by Markus Vaterlaus <mv...@gmail.com>.
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 <dh...@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