You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Lars Huttar <la...@sil.org> on 2005/03/21 22:47:01 UTC
esql: how to get return value from a stored procedure?
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
Re: esql: how to get return value from a stored procedure?
Posted by Lars Huttar <la...@sil.org>.
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: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org