Hello!
i am trying out the sql transformer from cocoon 2.2, and, hm, let us say
i am a little confused:
i have one matcher:
<map:match pattern="get">
<map:generate src="vis-sql.xml" />
<map:transform type="sql" label="debug">
<map:parameter name="use-connection" value="intern" />
<map:parameter name="show-nr-of-rows" value="true" />
<map:parameter name="clob-encoding" value="UTF-8" />
<map:parameter name="id" value="{request-param:id}" />
</map:transform>
<map:serialize type="xml" />
</map:match>
and the vis-sql.xml looks like that:
<?xml version="1.0"?>
<page xmlns:sql="http://apache.org/cocoon/SQL/2.0"; >
<sql:execute-query >
<sql:query name="aksessliste">
select id, application, operation,tlf, email,
sign_name, sign_title, orderer_name, orderer_username, time from
superbruker_form
where id='<sql:substitute-value sql:name="id"/>'
</sql:query>
</sql:execute-query>
</page>
and that works fine!
Now i wrote something more interesting: First the matcher:
<map:match pattern="exportuserdata/*/*">
<map:generate src="userdata_sql.xml" label="debug1"/>
<map:transform type="sql" label="debug">
<map:parameter name="use-connection" value="intern" />
<map:parameter name="show-nr-of-rows" value="true" />
<map:parameter name="org" value="{2}" />
</map:transform>
<map:serialize type="xml" />
</map:match>
and the userdata.sql:
<?xml version="1.0"?>
<page xmlns:sql="http://apache.org/cocoon/SQL/2.0";>
<sql:execute-query >
<sql:query name="person">
select pn_navn "fullnavn",pn_fornavn "given",pn_etternavn
"family",PN_MNAVN "middlename",pe_brukerid "userid" , PN_INIT "initial",
p.PE_ID "perid"
from <sql:substitute-value sql:name="org"/>.pernavn n,
<sql:substitute-value sql:name="org"/>.person p
where n.pn_peid_pe = p.pe_id
and p.pe_id > 0
order by pe_brukerid
</sql:query>
<sql:execute-query>
<sql:query name="address">
select AK_TYPE "type", AK_NAVN "fullnavn", AK_POSTADR "postadresse",
AK_POSTNR_PO "postnr", AK_POSTSTED_PO "poststed", AK_EPOST "epost",
AK_TLF "tlf", AK_MOBIL "mobil"
from <sql:substitute-value sql:name="org"/>.adrperson ap,
<sql:substitute-value sql:name="org"/>.adressekp a
where ap.ap_adrid_ak = a.ak_adrid
and ap.ap_peid_pe = <sql:ancestor-value sql:name="perid" sql:level="1"/>
</sql:query>
</sql:execute-query>
</sql:execute-query>
</page>
And for some reason that does not work as expected :-(
her is the snippet from the log:
parameters=Parameters[r/w]:{show-nr-of-rows=true, org=hin,
use-connection=ephprod}
2009-08-28 10:08:59,924 DEBUG transformation.SQLTransformer -
setupTransforming
2009-08-28 10:08:59,924 DEBUG transformation.SQLTransformer -
setupTransforming
2009-08-28 10:08:59,925 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,925 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,926 DEBUG transformation.SQLTransformer - End text
recording. Text=select pn_navn "fullnavn",pn_fornavn
"given",pn_etternavn "family",PN_MNAVN "middlename",pe_brukerid "userid"
, PN_INIT "initial", p.PE_ID "perid"
from
2009-08-28 10:08:59,926 DEBUG transformation.SQLTransformer - End text
recording. Text=select pn_navn "fullnavn",pn_fornavn
"given",pn_etternavn "family",PN_MNAVN "middlename",pe_brukerid "userid"
, PN_INIT "initial", p.PE_ID "perid"
from
2009-08-28 10:08:59,926 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,926 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,926 DEBUG transformation.SQLTransformer - End text
recording. Text=.pernavn n,
2009-08-28 10:08:59,926 DEBUG transformation.SQLTransformer - End text
recording. Text=.pernavn n,
2009-08-28 10:08:59,926 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,926 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,927 DEBUG transformation.SQLTransformer - End text
recording. Text=.person p
where n.pn_peid_pe = p.pe_id
and p.pe_id > 0
order by pe_brukerid
2009-08-28 10:08:59,927 DEBUG transformation.SQLTransformer - End text
recording. Text=.person p
where n.pn_peid_pe = p.pe_id
and p.pe_id > 0
and pe_brukerid not in ('EPHORTE','EPHSYS')
order by pe_brukerid
2009-08-28 10:08:59,927 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,927 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,927 DEBUG transformation.SQLTransformer - End text
recording. Text=select AK_TYPE "type", AK_NAVN "fullnavn", AK_POSTADR
"postadresse", AK_POSTNR_PO "postnr", AK_POSTSTED_PO "poststed",
AK_EPOST "epost", AK_TLF "tlf", AK_MOBIL "mobil"
from
2009-08-28 10:08:59,927 DEBUG transformation.SQLTransformer - End text
recording. Text=select AK_TYPE "type", AK_NAVN "fullnavn", AK_POSTADR
"postadresse", AK_POSTNR_PO "postnr", AK_POSTSTED_PO "poststed",
AK_EPOST "epost", AK_TLF "tlf", AK_MOBIL "mobil"
from
2009-08-28 10:08:59,927 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,927 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,928 DEBUG transformation.SQLTransformer - End text
recording. Text=.adrperson ap,
2009-08-28 10:08:59,928 DEBUG transformation.SQLTransformer - End text
recording. Text=.adrperson ap,
2009-08-28 10:08:59,928 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,928 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,928 DEBUG transformation.SQLTransformer - End text
recording. Text=.adressekp a
where ap.ap_adrid_ak = a.ak_adrid
and ap.ap_peid_pe =
2009-08-28 10:08:59,928 DEBUG transformation.SQLTransformer - End text
recording. Text=.adressekp a
where ap.ap_adrid_ak = a.ak_adrid
and ap.ap_peid_pe =
2009-08-28 10:08:59,928 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,928 DEBUG transformation.SQLTransformer - Start text
recording
2009-08-28 10:08:59,929 DEBUG transformation.SQLTransformer - End text
recording. Text=
2009-08-28 10:08:59,929 DEBUG transformation.SQLTransformer - End text
recording. Text=
2009-08-28 10:08:59,973 INFO transformation.SQLTransformer$Query -
Failed to execute query
org.apache.cocoon.transformation.sqltransformer$qu...@9b3b0c
java.sql.SQLException: ORA-00923: FROM keyword not found where expected
If i replace all the <sql:substitute-value sql:name="org"/> with hin, as
the schema name in this example, even that query runs sucessfully.
Someone who has an idea? Maybe just a hint where i can switch on the
logging of the inner class Query of the SQLTransformer, that one is
supposed to log out the whole sql-query before it is send to the database...
Grateful for any hint :-), that must be something stupid i do not see.
Is it not possible to use the same substitute value twice? Cannot
imagine that...
--
Med vennlig hilsen
Søren D. Krum
Systemutvikler/system developer
UNINETT FAS
+ 47 73557859
There are 10 different kind of people in the world, those who understand binary
and those who don't
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@cocoon.apache.org
For additional commands, e-mail: users-h...@cocoon.apache.org
