cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lars Huttar <>
Subject esql: return value from a stored procedure?
Date Thu, 24 Mar 2005 04:18:14 GMT
Sorry to repost, but I really don't know where to look for answers to 
this. There just aren't many examples of <esql:call> in Cocoon 
docs/wiki/samples/google, and I can't find any examples of getting a 
return value that is not a rowset from a stored procedure call.

------------------------------------ Repost 

Hi all,

I'm using ESQL to call stored procedures from XSP pages. I'm using
<esql:call>, following the documentation and examples I found on the web.
I am able to get the number of rows affected by the stored procedure
(SP) using  <esql:get-update-count/>. (See sample XSP page below.)
But in some cases, I also need to get the return value from the SP.
Here is an example of SP code:

CREATE PROCEDURE dbo.Update_Alternate_Dialect_Name
 @_id int,
 @Is_Pejorative char(1), @Speech_Variety_Name_Id int
 UPDATE Alternate_Dialect_Name
   SET Is_Pejorative = @Is_Pejorative, Speech_Variety_Name_Id =
   WHERE Alternate_Dialect_Name_Id = @_id

Obviously, in this case, the return value of the SP is the same as the
rowcount (number of rows affected). But I will be creating other
examples where the SP has to return, e.g. the ID of a newly-created row.

Here is my XSP page, as it currently stands (some irrelevant parts deleted):

<?xml version="1.0" encoding="ISO-8859-1"?>
       <esql:call result-set-from-object="1">{call
           Update_Ethnologue_Continent(<esql:parameter direction="in">
           <xsp-request:get-parameter name="_id"/>
         </esql:parameter>,<esql:parameter direction="in">
           <xsp-request:get-parameter name="Continent_Name"/>
         <!-- this part is an attempt to get the return value of the SP.
           I'm follow instructions for getting a returned rowset, which
is not
           really what I want, but I can't find instructions for what I
want. So I'm
           somewhat flailing in the dark. -->
            <!-- This part does not give an error, but also gives no
output. -->
column="1" from-call="true"/></xsp:expr></esql:result></fish>
            <!-- I also tried

       <esql:get-string column="1"/>

   which gave the error
Exception in ServerPagesGenerator.generate():
org.apache.avalon.framework.CascadingRuntimeException: Error getting
ascii data from column 1

Caused by: java.lang.NullPointerException

   probably because I'm trying to get a column value out of a scalar. -->

I'm really just shooting in the dark when it comes to getting the return
value from an SP.
Any help on how to find it?
Would it help if I used an "out" parameter instead of the return value?
If so, how to I get the value of the "out" parameter?


To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message