[WIKI-UPDATE] Argyn HowToGetResultsetFromSybaseStoredProcedure Sat Mar 13 06:00:07 2004

[st...@outerthought.org]

Page: http://wiki.cocoondev.org/Wiki.jsp?page=Argyn , version: 3 on Fri Mar 13 04:22:19 2004 by Argyn

+ Everyone has a blog these days, I have [a Blog|http://all-things-kazakh.blogspot.com/ ] of my own too.
+ 
+ 


Page: http://wiki.cocoondev.org/Wiki.jsp?page=HowToGetResultsetFromSybaseStoredProcedure , version: 1 on Fri Mar 13 04:20:34 2004 by Argyn

New page created:
+ !!  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|http://cocoon.apache.org/2.1/userdocs/xsp/esql.html#Stored+Procedure+Support ] 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 
+ * Basic Cocoon configuration: cocoon.xconf, web.xml
+ * Resin 2.x or other servlet container
+ * Basic XSP and ESQL
+ * Basic T-SQL for Sybase
+ !! Technical prerequisites 
+ * Cocoon 2.1.4 deployed on Resin 2.x or other servlet container.
+ * Sybase jConnect 5.5 JDBC drivers (jconn2.jar) are configured in pools for Cocoon.
+ * Sybase 12.5 RDBMS.
+ !! Links to other information sources 
+ * [Cocoon ESQL Documentation|http://cocoon.apache.org/2.1/userdocs/xsp/esql.html#Stored+Procedure+Support ]
+ * [Resin 2.1.12|http://caucho.com/download/resin-2.1.12.zip  ] distribution
+ * Sybase [jConnect 4.5 and 5.5 JDBC driver|http://www.sybase.com/detail/1,6904,1009796,00.html ]
+ ----
+ 
+ !!! esql:query tag 
+ 
+ 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.
+ 
+ !!! Setup Sybase database and datasource 
+ 
+ Install Cocoon on your Servlet container following instructions [here|http://cocoon.apache.org/2.1/installing/index.html#Installing+Cocoon ]. 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|http://cocoon.apache.org/2.1/developing/datasources.html#Defining+a+Data+Source ]. 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>\\
+       <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\\
+ 
+ !!! 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"\\
+           xmlns:xsp="http://apache.org/xsp"\\
+           xmlns:esql="http://apache.org/cocoon/SQL/v2">\\
+   <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
+ ----
+ 
+ 
+ !!!  page metadata 
+ - AUTHOR: [Argyn]\\
+ - AUTHOR-CONTACT: [mailto:argyn@cox.net]\\
+