What specific problems are you having?
Here are some examples that I used in a logicsheet called from some xsp pages.
Example 1: Stored Procedure returns a resultset (ie it's a fancy SELECT statement). It can return one row of data, many rows of data, or possibly no rows. It takes no input parameters.
<!-- Retrieve detailed data from the work queue table -->
<xsl:template match="db_query:work_queue">
<esql:execute-query>
<!-- call sproc -->
<esql:call needs-query="true" >
{call GetWorkQRows()}
</esql:call>
<!-- obtain & format results -->
<esql:results>
<work_queue>
<esql:row-results>
<row>
<esql:get-columns/>
</row>
</esql:row-results>
</work_queue>
</esql:results>
<!-- if query returns no results (not an ERROR) -->
<esql:no-results>
<no_results>There is currently no data in the work queue.</no_results>
</esql:no-results>
<!-- error processing -->
<esql:error-results>
<esql:get-message/><br/>
<esql:get-stacktrace/>
</esql:error-results>
</esql:execute-query>
</xsl:template>
The stored procedure does something like this: SELECT pin, status, action from ....
If there is data, this results in xml like:
<work_queue>
<row>
<pin>99999999999</pin>
<status>OK</status>
<action>Update</action>
</row>
<row>
<pin>88888888888</pin>
<status>OK</status>
<action>Update</action>
</row>
...
</work_queue>
No return code from the stored procedure is processed here. The stored procedure returns one, but I just don't care about it.
-----------------------------------------------------------------------------------------------------
EXAMPLE 2: This is the same as example 1, except I explicitly name the xml tags where I want particular data to go. This sproc can only return one or zero rows.
<xsl:template match="db_query:get_person_data">
<esql:execute-query>
<!-- Call Stored Procedure to retrieve person data-->
<esql:call needs-query="true">
{call get_person(
<esql:parameter direction="in" type="String"><xsp:expr>""</xsp:expr></esql:parameter>,
<esql:parameter direction="in" type="String"><xsp:expr>""</xsp:expr></esql:parameter>,
NULL,
<esql:parameter direction="in" type="String"><xsp-request:get-parameter name="xlpin"/></esql:parameter>)}
</esql:call>
<!-- obtain & format results -->
<esql:results>
<get_person_demo>
<esql:row-results>
<!-- personal info -->
<person_info>
<lname><esql:get-string column="lname"/></lname>
<fname><esql:get-string column="fname"/></fname>
<mi><esql:get-string column="mi"/></mi>
<dob><esql:get-date column="dob" format="MM/dd/yyyy"/></dob>
<sex><esql:get-string column="sex"/></sex>
<ssn><esql:get-double format="000000000" column="ssn"/></ssn> ...
</person_info>
...
</esql:row-results>
....
</xsl:template>
This results in, as you can imagine:
<get_person_demo>
<person_info>
<lname>Smith</lname>
<fname>John</fname>
<mi></mi>
<dob>10/10/1988</dob>
<sex>M</sex>
<ssn>345677890</ssn>
....
-------------------------------------------------------------------------------------------------------
Example 3: Stored procedure does not return a result set (ie it is not a select statement) but it does return various return codes (via an output parameter). The stored procedure also needs two input parameters. In my case, these input parameters were set by the user via a form and are available through request parameters.
<xsl:template match="db_query:launch_search_xlpin">
<esql:execute-query>
<!-- Call sproc; sproc has one output param (return code) -->
<esql:call>
{<esql:parameter direction="out" type="Int"/> = call LaunchSearches(
<esql:parameter direction="in" type="String"><xsp-request:get-parameter name="xlpin"/></esql:parameter>,
<xsp-request:get-parameter name="search_type"/>)}
</esql:call>
<!-- retrieve return code from output parameter -->
<esql:call-results>
<return><esql:get-int column="1" from-call="yes"/></return>
</esql:call-results>
<!-- error processing -->
<esql:error-results>
<esql:get-message/><br/>
<esql:get-stacktrace/>
</esql:error-results>
</esql:execute-query>
</xsl:template>
The esql:call-results section will execute regardless of whether or not a resultset is returned. This is where I capture the output parameter (return code).
This results in xml like the following:
<return>1</return>
or maybe
<return>5</return>
where 1 and 5 mean specific things for my stored procedure.
To use an output parameter, you must register it first. that is what "<esql:parameter direction="out" type="Int"/> = ..." line does. The line "<esql:get-int column="1" from-call="yes"/>" accesses the output parameter (in this case the output parameter is the first parameter (column="1"). I don't know what to do if you have more than one output parameter; I'm not too familiar with JDBC. I think that if this stored procedure had also returned a resultset, I could have accessed the data in the resultset with <esql:row-results> just like in Example 1.
I used cocoon 2.0.2, tomcat 4.0.1 and Sybase 12.5 database.
Hope that helps
Beth
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 24, 2002 12:17 AM
To: [EMAIL PROTECTED]
Subject: Stored Procedure using esql?
Hi,
i have problems processing the resultset returned by my stored procedures.
Do you have an example for me? I tried the example from the apache home
page, but it didn't work.
thanks,
markus sell
Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte
Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail
irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und
vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte
Weitergabe dieser Mail ist nicht gestattet.
This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorized copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.
