Lars Huttar wrote:
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
Well, let me post the progress I've made so far. Maybe it will help somebody else.
I see a few avenues open now for returning values.
(1) Returning a row set. Every SELECT in a stored procedure (in SQLServer 2000 anyway) is available to the caller as a resulting rowset. So I can just end my SP with "SELECT MAX(Alternative_Dialect_Name_Id) as NewID, @@ROWCOUNT as RowsAffected", and these two values would be available as a rowset in my XSP page.
(2) Read up on the underlying java.sql classes to see how it's done. It may be that esql provides the interface, but it just isn't documented well enough in the ESQL documentation. Maybe by adding a little Java code into my XSP I can get actual return values or out parameters.
(3) Look into using SQLTransformer. I don't know whether that will give me more flexibility.
The first avenue looks the easiest for now.
Lars
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
