cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paul Gilligan <pdgilli...@oyap.net>
Subject Re: Stored Procedure with input parameters using esql?
Date Thu, 30 May 2002 21:32:48 GMT
Why are you using NULL??

Does not seem to understand the NULL which is why you get the error.

A better way would be to setup a default NULL argument for the PL/SQL 
and then you can call:

{? = call get_person(?)}

..which Oracle will handle for you :)


Naquin, Beth wrote:
> 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(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
> 



---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>

To unsubscribe, e-mail: <cocoon-users-unsubscribe@xml.apache.org>
For additional commands, e-mail: <cocoon-users-help@xml.apache.org>


Mime
View raw message