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.
In order to retrieve a resultset from a stored procedure, use the esql:query tag as shown below:
<esql:query>
exec my_stored_proc
<esql:parameter><xsp:expr>"public"</xsp:expr></esql:parameter>
</esql:query>
That's it!
Now I'll explain this in more details.
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:
<init-param>
<param-name>load-class</param-name>
<param-value>
<!-- For Sybase Driver: -->
com.sybase.jdbc2.jdbc.SybDriver
</param-value>
</init-param>
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:
<jdbc logger="argyn.syb" name="syb">
<pool-controller max="10" min="5"/>
<dburl>jdbc:sybase:Tds:localhost:5001/test_db</dburl>BR
<user>argyn</user>
<password>crap</password>
</jdbc>
In order to test our setup, create a stored procedure like shown below:
CREATE PROCEDURE my_stored_proc
@NAME varchar(30)=null
AS
BEGIN
select * from sysusers where name=@NAME
END
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"
xmlns:xsp="http://apache.org/xsp"BR
xmlns:esql="http://apache.org/cocoon/SQL/v2">BR
<page>
<title>Argyn's sample</title>
<content>
<esql:connection>
<esql:pool>syb</esql:pool>
<para>
System user info below:
</para>
<esql:execute-query>
<esql:query>
exec my_stored_proc
<esql:parameter><xsp:expr>"public"</xsp:expr></esql:parameter>
</esql:query>
<esql:results>
<esql:row-results>
<esql:get-columns/>
</esql:row-results>
</esql:results>
</esql:execute-query>
<hr/>
</esql:connection>
</content>
</page>
</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