You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Ig...@pass-consulting.com on 2003/01/07 15:46:29 UTC

Problem with return-value from store procedure in a XSP page

Hello

I have a cocoon version number 2.0.3, a tomcat version number 4.0.16 and an
Oracle 8i database

I have a store procedure in my database called sp_add_user.
I tested the store procedure manually and it doesn't have any problem.  It
has 3 parameters.  
The first and the second are IN parameters, and must be String.  The third
is an OUT parameter and must 
be integer.

The source of this page is

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsp:page
   language="java"
   xmlns:xsp="http://apache.org/xsp"
   xmlns:xsp-request="http://apache.org/xsp/request/2.0"
   xmlns:esql="http://apache.org/cocoon/SQL/v2"
>

.
.
.

      <esql:connection>
        <esql:pool>mydb</esql:pool>
        <esql:execute-query>
        
          <esql:call>{call sp_add_user(<esql:parameter direction="in"
type="String"><xsp-request:get-parameter name="username"/></esql:parameter>,
            <esql:parameter direction="in"
type="String"><xsp:expr>password</xsp:expr></esql:parameter>,
            <esql:parameter direction="out" type="Int"></esql:parameter>)}
          </esql:call>

          <esql:results>
         
            <esql:row-results>
                <xsp:logic>
                    if(<xsp:expr><esql:get-int column="1"/></xsp:expr> ==
-1) {

                       <xsp:content>Sorry, user is ready
registered</xsp:content>
                    } else {

                       <xsp:content>User added with key: <esql:get-int
column="1"/>.</xsp:content>
                    }
                </xsp:logic>
            </esql:row-results>
          </esql:results>

          <esql:error-results> 
            <para>the following error occured: <esql:get-message/></para>
          </esql:error-results>  
            
          <esql:no-results> 
            <para>problem with database</para>
          </esql:no-results>      
        
        </esql:execute-query>
      </esql:connection>

.
.
.

</xsp:page>




When I tried to load the page I have always this problem:
problem with database

          <esql:no-results> 
            <para>problem with database</para>
          </esql:no-results>    


The source of stored procedure is:

PROCEDURE SP_ADD_USER(
name IN varchar2 default NULL,
pass IN varchar2 default NULL,
outer OUT NUMBER)
as
temp_Int int;
begin
  temp_Int :=888;
  outer := temp_Int;

/*  select 888 into outer from DUAL; */

end SP_ADD_USER;


Please help me!!!! 
Thank you!



Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte
Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail
irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und
vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte
Weitergabe dieser Mail ist nicht gestattet.

This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorized copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.



Re: Problem with return-value from store procedure in a XSP page

Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 07.Jan.2003 -- 03:46 PM, Igor.Kozlov@pass-consulting.com wrote:
> Hello
> 
> I have a cocoon version number 2.0.3, a tomcat version number 4.0.16 and an
> Oracle 8i database

Cocoon 2.0.4 is released and fixes a number of bugs and Tomcat 4.1.18
is out containing security related fixes.

> I have a store procedure in my database called sp_add_user.
> I tested the store procedure manually and it doesn't have any problem.  It
> has 3 parameters.  
> The first and the second are IN parameters, and must be String.  The third
> is an OUT parameter and must 
> be integer.
> 
> The source of this page is

>           <esql:call>{call sp_add_user(<esql:parameter direction="in"
> type="String"><xsp-request:get-parameter name="username"/></esql:parameter>,
>             <esql:parameter direction="in"
> type="String"><xsp:expr>password</xsp:expr></esql:parameter>,
>             <esql:parameter direction="out" type="Int"></esql:parameter>)}
>           </esql:call>
> 

Now, since your SP apparently does not return a result set, you need to use 

              <esql:call-results>

instead. This is executed regardless of a result set. Without a result
set, there are no row-results. So...

>                 <xsp:logic>
>                     if(<xsp:expr><esql:get-int column="1"/></xsp:expr> ==
> -1) {

And since your SP does not return a result set, you need to add the
attribute 'from-call="yes"' to the <esql:get-int/> tag.

> 
>                        <xsp:content>Sorry, user is ready
> registered</xsp:content>
>                     } else {
> 
>                        <xsp:content>User added with key: <esql:get-int
> column="1"/>.</xsp:content>

dito

>                     }
>                 </xsp:logic>

              </esql:call-results>

> 
>           <esql:error-results> 
>             <para>the following error occured: <esql:get-message/></para>
>           </esql:error-results>  
>             

This does not work here:

>           <esql:no-results> 
>             <para>problem with database</para>
>           </esql:no-results>      


>         
>         </esql:execute-query>
>       </esql:connection>
> 
> .
> .
> .
> 
> </xsp:page>

	Chris.
-- 
C h r i s t i a n       H a u l
haul@informatik.tu-darmstadt.de
    fingerprint: 99B0 1D9D 7919 644A 4837  7D73 FEF9 6856 335A 9E08

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

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