cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Faulkner, Perry" <Perry.Faulk...@det.nsw.edu.au>
Subject RE: Stored Procedure with input parameters using esql?
Date Thu, 30 May 2002 22:23:15 GMT
Hi Beth,
 
Just a quick look at the esql stylesheet seems to indicate direction="in"
not "In". XML is case-sensitive!
 
HTH
 
Perry

-----Original Message-----
From: Naquin, Beth [mailto:Beth.Naquin@morpho.com]
Sent: Friday, 31 May 2002 7:27
To: 'cocoon-users@xml.apache.org'
Subject: Stored Procedure with input parameters using esql?



I want to execute a stored procedure that requires input parameters using
the esql logicsheet, but am having difficulty.  I can call stored procedures
that take no inputs just fine.

Is there a working example/samle code of how to do this? I could only find
one brief example in the userdocs and it still does not make sense to me ;(

I have this stored procedure:  {? = call get_person(?,?,?,?)} 

The first 3 input parameters can accept NULL and the last one will be an
actual value. 

I have this code using esql logicsheet: 
----------------------------------------------------------------------------
- 
<esql:execute-query> 
<!-- Call Stored Procedure to retrieve person data--> 
        <esql:call needs-query="true" > 
                        {? = call get_person( 
                        <esql:parameter direction="In"
type="String"><xsp:expr>NULL</xsp:expr></esql:parameter>, 
                        <esql:parameter direction="In"
type="String"><xsp:expr>NULL</xsp:expr></esql:parameter>, 
                        <esql:parameter direction="In"
type="String"><xsp:expr>NULL</xsp:expr></esql:parameter>, 
                        <esql:parameter direction="In"
type="String"><xsp:expr>'46465445'</xsp:expr></esql:parameter>)} 
                </esql:call> 
  
... stuff to process results .... 
</esql:execute-query> 
----------------------------------------------------------------------------
- 
But I receive this error: 

type fatal 
message Exception in ServerPagesGenerator.generate() 
description org.apache.cocoon.ProcessingException: Exception in
ServerPagesGenerator.generate(): java.lang.RuntimeException: Error executing
prepared statement: {? = call get_person( ?, ?, ?, ?)} :
java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set,
index: 1.

sender org.apache.cocoon.servlet.CocoonServlet 
source Cocoon servlet 
stack-trace 
org.apache.cocoon.ProcessingException: Exception in
ServerPagesGenerator.generate(): java.lang.RuntimeException: Error executing
prepared statement:                   {? = call get_person(
?,                  ?,                  ?,                  ?)}
: java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set,
index: 1.

        at
org.apache.cocoon.generation.ServerPagesGenerator.generate(ServerPagesGenera
tor.java:272) 
        at
org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEv
entPipeline.java:251) 
        at
org.apache.cocoon.components.source.SitemapSource.toSAX(SitemapSource.java:3
51) 
        at
org.apache.cocoon.sitemap.ContentAggregator.generate(ContentAggregator.java:
148) 
        at
org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEv
entPipeline.java:251) 
        at
org.apache.cocoon.components.pipeline.CachingStreamPipeline.process(CachingS
treamPipeline.java:399) 
        at org.apache.cocoon.www.otda_1.sitemap_xmap.matchN100FD(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_
xmap.java:1344)

        at org.apache.cocoon.www.otda_1.sitemap_xmap.process(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_
xmap.java:727)

        at org.apache.cocoon.www.otda_1.sitemap_xmap.process(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_
xmap.java:634)

        at org.apache.cocoon.sitemap.Handler.process(Handler.java:222) 
        at org.apache.cocoon.sitemap.Manager.invoke(Manager.java:179) 
        at org.apache.cocoon.www.sitemap_xmap.matchN109E1(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.ja
va:12247)

        at org.apache.cocoon.www.sitemap_xmap.process(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.ja
va:3870)

        at org.apache.cocoon.www.sitemap_xmap.process(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.ja
va:3127)

        at org.apache.cocoon.sitemap.Handler.process(Handler.java:222) 
        at org.apache.cocoon.sitemap.Manager.invoke(Manager.java:179) 
        at
org.apache.cocoon.sitemap.SitemapManager.process(SitemapManager.java:154) 
        at org.apache.cocoon.Cocoon.process(Cocoon.java:575) 
        at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:998) 
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) 
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:247) 
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:193) 
        at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
va:243) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
66) 
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) 
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) 
        at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
va:201) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
66) 
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) 
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) 
        at
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2344) 
        at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164
) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
66) 
        at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
java:170) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
64) 
        at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170
) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
64) 
        at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
64) 
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) 
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) 
        at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
:163) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
66) 
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) 
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) 
        at
org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:
1011) 
        at
org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1106
) 
        at java.lang.Thread.run(Unknown Source) 
java.lang.RuntimeException: Error executing prepared statement:
{? = call get_person(                  ?,                  ?,
?,                  ?)}                 : java.sql.SQLException: JZ0SA:
Prepared Statement: Input parameter not set, index: 1.

        at
org.apache.cocoon.www.otda_1.documents.home.process_person_data_input_xsp.ge
nerate(C:\Program Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1/document
s/home\process_person_data_input_xsp.java:607)

        at
org.apache.cocoon.generation.ServerPagesGenerator.generate(ServerPagesGenera
tor.java:260) 
        at
org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEv
entPipeline.java:251) 
        at
org.apache.cocoon.components.source.SitemapSource.toSAX(SitemapSource.java:3
51) 
        at
org.apache.cocoon.sitemap.ContentAggregator.generate(ContentAggregator.java:
148) 
        at
org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEv
entPipeline.java:251) 
        at
org.apache.cocoon.components.pipeline.CachingStreamPipeline.process(CachingS
treamPipeline.java:399) 
        at org.apache.cocoon.www.otda_1.sitemap_xmap.matchN100FD(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_
xmap.java:1344)

        at org.apache.cocoon.www.otda_1.sitemap_xmap.process(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_
xmap.java:727)

        at org.apache.cocoon.www.otda_1.sitemap_xmap.process(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1\sitemap_
xmap.java:634)

        at org.apache.cocoon.sitemap.Handler.process(Handler.java:222) 
        at org.apache.cocoon.sitemap.Manager.invoke(Manager.java:179) 
        at org.apache.cocoon.www.sitemap_xmap.matchN109E1(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.ja
va:12247)

        at org.apache.cocoon.www.sitemap_xmap.process(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.ja
va:3870)

        at org.apache.cocoon.www.sitemap_xmap.process(C:\Program
Files\Apache Tomcat
4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www\sitemap_xmap.ja
va:3127)

        at org.apache.cocoon.sitemap.Handler.process(Handler.java:222) 
        at org.apache.cocoon.sitemap.Manager.invoke(Manager.java:179) 
        at
org.apache.cocoon.sitemap.SitemapManager.process(SitemapManager.java:154) 
        at org.apache.cocoon.Cocoon.process(Cocoon.java:575) 
        at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:998) 
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) 
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:247) 
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:193) 
        at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
va:243) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
66) 
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) 
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) 
        at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
va:201) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
66) 
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) 
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) 
        at
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2344) 
        at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164
) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
66) 
        at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
java:170) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
64) 
        at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170
) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
64) 
        at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
64) 
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) 
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) 
        at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
:163) 
        at
org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5
66) 
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) 
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) 
        at
org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:
1011) 
        at
org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1106
) 
        at java.lang.Thread.run(Unknown Source) 



Cocoon 2.0.2 
Tomcat 4.0.1 
JDK 1.3.1 
sybase database 

Thanks in advance, 

Beth Naquin 
SAGEM MORPHO Inc. 
1145 Broadway Plaza STE 200 
Tacoma, WA 98402 

253-597-8245 


Mime
View raw message