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 22:16:49 GMT
I am just going on the SQL Error that you have from the database Beth,

java.sql.SQLException: JZ0SA:
 >  > Prepared Statement: Input parameter not set, index: 1.

That is returned by the database so the issue is in the parameters, I 
have not programmed calls from java for a couple of years but is was a 
pain then where you using oracle ?? if so the call below works.


  CallableStatement l_stmt =
           m_connection.prepareCall(" begin 
get_hotel_details(?,?,?,?,?,?); end ;");

Paul


Naquin, Beth wrote:
> Thanks for the quick reply! but I am still confused :(
> 
> The input arguments will eventually be user-entered data, but the user 
> will only enter one of the four inputs, as they are mutually exclusive. 
> (You could send data for all four to the stored procedure and it would 
> work fine; but functionally it doesn't make sense.) Right now I am just 
> trying to get it working by hard-coding a value for the last input, 
> which is the most often supplied.  The stored procedure is used for 
> other things and works fine (also why I can't change it right now) ;)
> 
> Also, even if I enter something other than NULL, I still get the same 
> error.
> 
> <esql:execute-query>
> <!-- Call Stored Procedure to retrieve person data-->
>    <esql:call needs-query="true">
>        {? = call get_person(
>        <esql:parameter direction="In" type="String">'234'</esql:parameter>,
>          <esql:parameter direction="In" 
> type="String"><xsp:expr>'333'</xsp:expr></esql:parameter>,
>        <esql:parameter direction="In" 
> type="String"><xsp:expr>'333'</xsp:expr></esql:parameter>,
>        <esql:parameter direction="In" 
> type="String"><xsp:expr>'46465445'</xsp:expr></esql:parameter>)}
>     </esql:call>
> 
> 
> Any ideas or example would be greatly appreciated !!
> 
> Thx,
> Beth
> 
> 
> -----Original Message-----
> From: Paul Gilligan [mailto:pdgilligan@oyap.net]
> Sent: Thursday, May 30, 2002 2:33 PM
> To: cocoon-users@xml.apache.org
> Subject: Re: Stored Procedure with input parameters using esql?
> 
> 
> 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>
> 



---------------------------------------------------------------------
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