RE: Stored Procedure with input parameters using esql?

Faulkner, Perry Thu, 30 May 2002 15:05:14 -0700

Title: Stored Procedure with input parameters using esql?

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:[EMAIL PROTECTED]]
Sent: Friday, 31 May 2002 7:27
To: '[EMAIL PROTECTED]'
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>

        <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(ServerPagesGenerator.java:272)
        at org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEventPipeline.java:251)
        at org.apache.cocoon.components.source.SitemapSource.toSAX(SitemapSource.java:351)
        at org.apache.cocoon.sitemap.ContentAggregator.generate(ContentAggregator.java:148)
        at org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEventPipeline.java:251)
        at org.apache.cocoon.components.pipeline.CachingStreamPipeline.process(CachingStreamPipeline.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.java: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.java: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.java: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(ApplicationFilterChain.java:247)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
        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.java:201)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
        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:566)
        at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
        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:566)
        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.generate(C:\Program Files\Apache Tomcat 4.0\work\localhost\cocoon\cocoon-files\org/apache/cocoon/www/otda_1/documents/home\process_person_data_input_xsp.java:607)

        at org.apache.cocoon.generation.ServerPagesGenerator.generate(ServerPagesGenerator.java:260)
        at org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEventPipeline.java:251)
        at org.apache.cocoon.components.source.SitemapSource.toSAX(SitemapSource.java:351)
        at org.apache.cocoon.sitemap.ContentAggregator.generate(ContentAggregator.java:148)
        at org.apache.cocoon.components.pipeline.CachingEventPipeline.process(CachingEventPipeline.java:251)
        at org.apache.cocoon.components.pipeline.CachingStreamPipeline.process(CachingStreamPipeline.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.java: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.java: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.java: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(ApplicationFilterChain.java:247)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
        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.java:201)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
        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:566)
        at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462)
        at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
        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:566)
        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

RE: Stored Procedure with input parameters using esql?

Reply via email to