Create JSP in JBuilder to Retrieve ResultSet from EAServer Connection Cache

This document provides step-by-step instructions to create a JSP in JBuilder that retrieves a ResultSet from a database using an EAServer connection cache. The JSP retrieves a list of books from the EAServer sample database and formats the data into columns in an HTML table.

EAServer Plug-in and Server configuration
Create JSP in JBuilder
Deploy JSP to EAServer
Request JSP From Web Browser
Troubleshooting Tips
More Information

EAServer Plug-in and Server Configuration

This sections includes configuration steps for the Sybase EAServer Plug-in for JBuilder, JBuilder project defaults, and creation of an appropriate EAServer connection cache.

1. You have already configured the Sybase EAServer Plug-in for JBuilder using the instructions in the Configuring and Troubleshooting the Sybase EAServer Plug-in for JBuilder X document

2. Your EAServer server process is running.
   To start the EAServer server, either execute the serverstart bat/sh file in your EAServer bin directory, or use the Start | Sybase | EAServer | Jaguar Server program menu item

3. The sample jagdemo.db database is running.
   To start the database, run jagdemo bat/sh file in your EAServer bin directory, or use the Start | Sybase | EAServer | jagdemo program menu item.

4. You have configured an EAServer connection cache named "EASDemo_JNDI" to allow a JNDI lookup on the cache
   1. Start EAServer Manager using Start | Sybase | EAServer | EAServer Manager, or execute jagmgr bat/sh file in your EAServer bin directory.
   2. Choose Tools | Connect | EAServer Manager and connect to the server.
   3. Right-click the Connection Caches folder and choose New Connection Cache.
   4. Enter EASDemo_JNDI as the name and click OK
      • Specify these values on the General tab, replacing "localhost" as appropriate with the name of the machine the jagddemo.db is running on:

        Database Type:	Sybase_ASA
        Server name:	NetworkProtocol=Tds:Server=localhost:Port=2638
        User name:	dba
        Password:	sql

      • Specify these values on the Driver tab:

        Database Driver Type	JDBC
        Connection Library 1PC Class Name:	com.sybase.jdbc2.jdbc.SybConnectionPoolDataSource

      • Click OK to save the cache

5. You can successfully ping the EAServer connection cache from EAServer Manager
   Right-click the EASDemo_JNDI cache you created in the previous step, and choose Ping.
   

Create JSP in JBuilder

1. Run JBuilder

2. Choose File | New Project from the menu
   Specify any name, then step through rest to wizard, accepting all defaults, to create the project

3. Choose File | New from the menu. Select Web | Web Module from the Object Gallery and click OK

4. Specify any name -- such as WebModule1 -- then step through the rest of the wizard, accepting all of the defaults to create the Web Module.

5. In the Project pane, Double-click the Web Module name (WebModule1 in this example) to populate the Structure pane. Then in the Structure pane, double-click the Web Module name (WebModule1 in this example) to display the Web Module DD Editor.
   Click Add to define a new property.

6. Scroll through the properties list.
   Select com.sybase.jaguar.webapplication.resource-ref , and paste this text into the Value field, as one line (do not insert a line break):
   description=,res-sharing-scope=,res-type=javax.sql.DataSource,res-ref-name=jdbc/EASDemo,res-link=EASDemo_JNDI,res-auth=Container

   Click OK.

7. Verify that the new property appears in the list.

8. Choose File | New from the menu. Select Web, JavaServer Page from the Object Gallery and click OK

9. Specify any name -- such as getResultSet -- then step through the rest of the wizard, accepting all of the defaults to create the JSP

10. Expand the Module directory folder, and select the newly created JSP to display the page in the right pane

11. Replace the default content that was generated with the following:

    <%@ page errorPage="errorProcessing.jsp" %> <%@ page import="java.io.*" %> <%@ page import="java.sql.*" %> <%@ page import="java.util.*" %> <%@ page import="javax.naming.*" %> <%@ page import="javax.sql.*" %> <html> <head> <title>Get Data via Connection Cache from JSP</title> </head> <body> <h1>ResultSet from EAServer JDBC cache</h1> <% java.sql.Statement stmt = null; java.sql.ResultSet rs = null; java.sql.Connection conn; try { Properties p = new Properties(); p.put(Context.SECURITY_PRINCIPAL, "jagadmin"); p.put(Context.SECURITY_CREDENTIALS, ""); p.put(Context.INITIAL_CONTEXT_FACTORY, "com.sybase.ejb.InitialContextFactory"); p.put(Context.PROVIDER_URL, "iiop://yourmachinename:9000" ); javax.naming.InitialContext ctx = new javax.naming.InitialContext(p); // lookup cache as Web App resource reference "/jdbc/EASDemo" javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/EASDemo"); conn = ds.getConnection(); stmt = conn.createStatement(); boolean results = stmt.execute("select * from allbooks where title like 'Advance%';"); if (results) { rs = stmt.getResultSet(); } else { rs = null; } if (rs != null) { %> <p> <table border=1> <tr> <th>Title</th> <th>Author</th> <th>Price</th> </tr> <% // loop through resultset to output columns from each row while(rs.next()) { %> <tr> <td><%= rs.getString("title") %></td> <td><%= rs.getString("author") %></td> <td><%= rs.getFloat("price") %></td> </tr> <% } %> <tr><td colspan=3><b>End of data</b></td></tr> </table> <% } else { out.print("<b>Result was null</b>"); } // Clean up statement object to prevent mem leak in cache stmt.close(); stmt = null; // close resultset and connection rs.close(); conn.close(); } catch (Exception e) { out.println("<br>Exception caught:<br>" + e.getMessage() + "<br>"); } %> </body> </html>

12. Click File | Save Project from the menu

13. Right-click the Web Module and choose Make from the popup menu to create the WAR file.

Deploy JSP to EAServer

1. Right-click the Web Module and choose Deploy options | Deploy from the popup menu, to deploy the WAR file to EAServer.

   The Messages output window should display messages similar to the following:

   C:\Sybase\Shared\Sun\jdk\jdk1.4.1_03\bin\javaw -classpath C:\Sybase\eas5\java\lib\easj2ee.jar;C:\Sybase\eas5\java\lib\easclient.jar; C:\Sybase\eas5\java\lib\jagtool.jar;C:\Sybase\eas5\java\classes\crimson.jar;C:\Sybase\eas5\html\classes;C:\Sybase\eas5\java\classes; C:/JBuilderX/lib/ant.jar org.apache.tools.ant.Main -emacs -buildfile C:/JBuilderX/lib/servers/EAServer_Plugin_deployment.xml -Djaguar.user jagadmin -Djaguar.host jfoster -Djaguar.port 9000 -Ddeploy.type war -Ddeploy.file C:\Testing\Test_JBuilderX\jbproject\EAServerJSPresultset\WebModule1.war -Dentity WebApplication:WebModule1 deploy Buildfile: C:\JBuilderX\lib\servers\EAServer_Plugin_deployment.xml -connect:Connected to server at jfoster:9000 as user jagadmin -deploy:Deployed file C:\Testing\Test_JBuilderX\jbproject\EAServerJSPresultset\WebModule1.war Deploying... Deploying Web Components from bundle WebModule1 WARNING: No Servlet Mapping defined Deploying Filters from bundle WebModule1 Deploying WAR elements from WebModule1 Doing sybase-easerver-config.xml configuration WebApplication WebModule1 was configured successfully The configuration from file: WebModule1.war was performed successfully WebApplication WebModule1 was successfully installed into Server Jaguar -update_relationship_components: -refresh:Refreshed WebApplication WebModule1 restartServer-cond-if: restartServer-cond-else: -restart: deploy: BUILD SUCCESSFULTotal time: 15 seconds

Request JSP From Web Browser

Open a Web browser and request the JSP from EAServer using the following syntax:
http://machine:httpPort/webapplication/jspname.jsp For example, assume your EAServer server is running on a machine named jfoster, listening for http requests on port 8080, you specified WebModule1 as the name in step x, and getResultSet as the name of the JSP in step x: http://jfoster:8080/WebModule1/getResultSet.jsp

Troubleshooting Tips

• Review the Configuring and Troubleshooting the Sybase EAServer Plug-in for JBuilder X document to ensure the plug-in is configured properly and the project defaults include the requirements

• If the JSP does not compile:
  • review the content of the JBuilder Messages pane for failure information and more specifics about the compilation problem

• If Web Module deployment fails:
  • review the content of the JBuilder Messages pane for failure information and more specifics about the compilation problem
  • ensure the EAServer server is running
  • Ensure the server name and port number in the project deployment settings match the actual running server and http listener

• If the Web browser displays a page not found error:
  • verify the EAServer server is running
  • verify the http listener port can process requests by requesting the default documentation page as http://server:port/ (for example, http://jfoster:8080)
  • try restarting the EAServer server to refresh the environment

• If the Web browser displays an exception, or a message indicating there is a problem with the page (for example: internal server error 500):
  • Check the EAServer server log files (jaguarhttpservlet.log and jaguar.log) for error details

  • Add an error page to the JSP and rebuild and deploy the project.

    For example, add this line to the top of getResultSet.jsp:
    

    <%@ page errorPage="errorProcessing.jsp" %>
    Create a new JSP named errorProcessing with this content:
    

    <%@ page isErrorPage="true" %> <html> <body> <h1>Error page</h1> <br>Error occurred in the JSP: <%= exception.getMessage() %> <Br>Stack Trace: <% java.io.CharArrayWriter cw = new java.io.CharArrayWriter(); java.io.PrintWriter pw = new java.io.PrintWriter(cw,true); exception.printStackTrace(pw); out.println(cw.toString()); %> </body> </html>

• Remember that if you make any changes to the Web Application, you need to do another Make to update the WAR file before deploying it to EAServer again. Otherwise, you will deploy a older version of the WAR file that does not include your changes

More Information

For more information about EAServer, see the EAServer 5.0 online books

Related Links