cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Naquin, Beth" <Beth.Naq...@morpho.com>
Subject Stored Procedure with input parameters using esql?
Date Thu, 30 May 2002 21:26:56 GMT
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