esql: how to get return value from a stored procedure?

[Lars Huttar]

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
AS
 UPDATE Alternate_Dialect_Name
   SET Is_Pejorative = @Is_Pejorative, Speech_Variety_Name_Id = 
@Speech_Variety_Name_Id
   WHERE Alternate_Dialect_Name_Id = @_id
 RETURN @@ROWCOUNT

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"?>
<xsp:page
 xmlns:esql="http://apache.org/cocoon/SQL/v2"; 
xmlns:xsp="http://apache.org/xsp";
 xmlns:xsp-request="http://apache.org/xsp/request/2.0";>
 <operation>
...
   <esql:connection>
     <esql:pool>GEM-Ethnologue-SQLServer-updater</esql:pool>
     <esql:execute-query>
       <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"/>
         </esql:parameter>)}</esql:call>
       <esql:call-results>
         <!-- 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. -->
         <esql:use-results>
            <!-- This part does not give an error, but also gives no 
output. -->
             <fish><esql:result><xsp:expr>(ResultSet)<esql:get-object 
column="1" from-call="true"/></xsp:expr></esql:result></fish>
            <!-- I also tried

   <esql:results>
     <esql:row-results>
       <esql:get-string column="1"/>
     </esql:row-results>
   </esql:results>
   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
        at 
org.apache.cocoon.components.language.markup.xsp.EsqlHelper.getAscii(EsqlHelper.java:283)
   probably because I'm trying to get a column value out of a scalar. -->
         </esql:use-results>
       </esql:call-results>
       <esql:update-results>
         <rows-affected>
           <esql:get-update-count/>
         </rows-affected>
       </esql:update-results>
     </esql:execute-query>
   </esql:connection>
 </operation>
</xsp:page>
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?

Thanks,
Lars

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]