You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Frank Rennekamp <fr...@tanto.de> on 2005/01/28 13:49:02 UTC
Big Problems with the SQL-Transformator
Hi,
I'm using Cocoon 2.1.6 on a debian system and discovered serious
problems with the SQL-Transformer.
The documentation to the SQL-Transformer mentioned, that I can have a
sequence of queries I want to execute. But when I try something like:
test.xml
========
<?xml version="1.0" encoding="ISO-8859-1"?>
<result>
<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<sql:query name="person">SELECT lastname FROM person WHERE
id=123</sql:query>
<sql:query name="address">SELECT street,zipcode,city FROM address
WHERE person=123</sql:query>
</sql:execute-query>
</result>
And in my pipeline looks like:
==============================
<map:match pattern="xxx">
<map:generate type="file" src="test.xml"/>
<map:transform type="sql">
<map:parameter name="use-connection" value="myDB"/>
<map:parameter name="show-nr-of-rows" value="true"/>
</map:transform>
<map:serialize type="xml"/>
</map:match>
Then I get an error:
====================
<?xml version="1.0" encoding="ISO-8859-1"?>
<result>
<rowset>
<error>
Unexpected token: SELECT in statement [SELECT lastname FROM person WHERE
id=123SELECT street,zipcode,city FROM address WHERE person=123]
</error>
</rowset>
</result>
The log is saying:
==================
ERROR (2005-01-28) 11:59.08:467 [sitemap.transformer.sql]
(/cocoon/test/xxx) http-8080-Processor24/SQLTransformer.Q
uery: Caught a SQLException
java.sql.SQLException: Unexpected token: SELECT in statement [SELECT
name FROM personen WHERE id=123select street,zipcode,city FROM address
WHERE person = 123]
at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown
Source)
at sun.reflect.GeneratedMethodAccessor83.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
org.apache.avalon.excalibur.datasource.AbstractJdbcConnection.invoke(AbstractJdbcConnection.java:353)
at $Proxy14.prepareStatement(Unknown Source)
at
org.apache.cocoon.transformation.SQLTransformer$Query.execute(SQLTransformer.java:1068)
at
org.apache.cocoon.transformation.SQLTransformer.executeQuery(SQLTransformer.java:317)
...
Can anyone help me? Any suggestions?
Frank
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Big Problems with the SQL-Transformator
Posted by Frank Rennekamp <fr...@tanto.de>.
Hi Ben,
well, yeah, it's working. Don't know what I made wrong before, because I
tried this, too. Mea culpa.
But actually this was only the simple form of the real problem, my
SQL-Statement looks like:
<?xml version="1.0" encoding="ISO-8859-1"?>
<result>
<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<sql:query name="person">
SELECT id,lastname,firstname,title
FROM person
WHERE username='frank'
AND password='xxxx'
</sql:query>
<sql:execute-query>
<sql:query name="role">
SELECT b.name
FROM person_role pb, role b
WHERE b.id = pb.role_id
AND pb.person_ID = <sql:ancestor-value sql:name="id"
sql:level="1"/>
</sql:query>
<sql:query name="address">
SELECT institution,street,PLZ,Ort,Tel,Fax,Email
FROM Address
WHERE person = <sql:ancestor-value sql:name="id"
sql:level="1"/>
</sql:query>
</sql:execute-query>
</sql:execute-query>
</result>
Here is isn't working, when I write
</sql:execute-query><sql:execute-query> between the two queries. - If I
only use one subquery, it's ok.
Any idea?
Frank
Am Fre, den 28.01.2005 schrieb Ben Anderson um 13:53:
> I think you just need to add end the sql:execute-query tag and restart
> it again...
>
> > <?xml version="1.0" encoding="ISO-8859-1"?>
> > <result xmlns:sql="http://apache.org/cocoon/SQL/2.0">
> > <sql:execute-query>
> > <sql:query name="person">SELECT lastname FROM person WHERE
> > id=123</sql:query>
> </sql:execute-query>
> <sql:execute-query>
> > <sql:query name="address">SELECT street,zipcode,city FROM address
> > WHERE person=123</sql:query>
> > </sql:execute-query>
> > </result>
>
> ---------------------------------------------------------------------
> 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
Re: Big Problems with the SQL-Transformator
Posted by Ben Anderson <be...@gmail.com>.
I think you just need to add end the sql:execute-query tag and restart
it again...
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <result xmlns:sql="http://apache.org/cocoon/SQL/2.0">
> <sql:execute-query>
> <sql:query name="person">SELECT lastname FROM person WHERE
> id=123</sql:query>
</sql:execute-query>
<sql:execute-query>
> <sql:query name="address">SELECT street,zipcode,city FROM address
> WHERE person=123</sql:query>
> </sql:execute-query>
> </result>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org