How-to call Sybase stored procedures returning Resultset with ESQL tags in XSP


What you will get from this page

You'll learn how to get resultsets from Sybase stored procedures using ESQL tags in XSP pages. Cocoon User documentation explains how to do it using esql:call tag. Here's another (imho, simpler) way for Sybase database. Additionally, Sybase Connection setup is discussed briefly.

Your basic skills

Technical prerequisites


esql:query tag

In order to retrieve a resultset from a stored procedure, use the esql:query tag as shown below:
<esql:query>

</esql:query>

That's it!

Now I'll explain this in more details.

Setup Sybase database and datasource

Install Cocoon on your Servlet container following instructions here. I am using Resin 2.1.12.
Copy jConn2.jar into your /WEB-INF/lib directory of a deployed Cocoon application.
Configure web.xml to pre-load the JDBC driver class as per instructions http://cocoon.apache.org/2.1/developing/datasources.html#Installing+the+Driver. Sybase driver Class name is: com.sybase.jdbc2.jdbc.SybDriver. Note, that I had a problem when there were more than one driver in load-class parameter. So, my web.xml has only Sybase driver and looks like this:

Now, you have to configure a datasource in the cocoon.xconf file follwoing instructions here. Sybase JDBC url looks like this: jdbc:sybase:Tds:_host_:_port_/_database_?user=_username_?password=_password_. It's the longest url variant, usually user and password are set separately.
Here's my datasource:

In order to test our setup, create a stored procedure like shown below:
CREATE PROCEDURE my_stored_proc
@NAME varchar(30)=null
AS

Sample XSP page

Let's create our test XSP page. If you installed default Cocoon.war built by command "build webapp", then you should have a directory with sample code at \samples\databases\xsp\. Create here one more file called test.xsp. Here's what you should put inside:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsp:page language="java"

<esql:parameter><xsp:expr>"public"</xsp:expr></esql:parameter>

</xsp:page>

The trick is that if you call a stored procedure with "exec _stp_name_ ?,..." syntax, then you can use esql:query tag just as you would do it with a any "select * from..." query. This works for Sybase.

Now, if you hit the page with http://localhost:8080/cocoon/samples/databases/xsp/test (port number and cocoon context may be different in your setup), you should see something like this:


Argyn's sample

System user info below: -2 0 0 public


page metadata

- AUTHOR: Argyn
- AUTHOR-CONTACT: mailto:argyn@cox.net

HowToGetResultsetFromSybaseStoredProcedure (last edited 2009-09-20 23:40:43 by localhost)