esql: return value from a stored procedure?

[Lars Huttar <la...@sil.org>]

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
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: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org