Use a connection cache from JSP

Jaguar provides a very useful cache mechanism to speed up database requests. You can have ODBC, JDBC or Oracle cached connections.

NOTE: Java component can't use cached ODBC connection, you need to use a cached JDBC connection. If there is no JDBC driver available, it's still possible to use the ODBC-JDBC provided by Sun. But performance of such a bridge is poor and the reliability is not good in a multi-threaded environment.

To define a cache using ODBC-JDBC (bridge) connection, follow these steps:

Define a regular System DSN (through the ODBC Administration panel)

In jaguar, you define the cache :

General Tab : Server name = jdbc:odbc:YourSystemDSNName with user/pwd
Driver Tab : DLL or class name = sun.jdbc.odbc.JdbcOdbcDriver with JDBC radio button selected
Cache Tab : checkbox cache-by-name checked

<%@page contentType="text/html"%> 
<html> 
<head><title>JSP Page</title></head> 
<body> 
<%@ page import="java.sql.*" %> 
<% 
  com.sybase.jaguar.jcm.JCMCache  jcmCache= null; 
  java.sql.Connection dbConn = null; 
  String jcmCacheString = "mycachename"; 
  String msg = ""; 
      
  try { 
      jcmCache = 
        com.sybase.jaguar.jcm.JCM.getCacheByName(jcmCacheString); 
      dbConn = 
        jcmCache.getConnection(com.sybase.jaguar.jcm.JCMCache.JCM_WAIT); 

      Statement stmt = dbConn.createStatement(); 
      ResultSet rs = 
        stmt.executeQuery
           ("select msg from messages where msgid='00001'"); 

      if(rs != null) { 
       while(rs.next()) { 
          msg = rs.getString("msg"); 
          } 
       }          
      rs.close(); 
      dbConn.close(); 
  } 
  catch (Exception e) { 
    out.println("OUPS " + e.getMessage() + "<BR>"); 
    } 
%> 
msgtext = 
      <i><%= msg %></i> 
</body> 
</html>

Instead of hard-coding the cache name into your java component, make the name available through a Property (of the Environment) of the Web application. This way your components are more flexible and you are using the "J2EE way" to make a connection. In this example, myconnection contains the cache name to be used.

  javax.naming.InitialContext ctx = new javax.naming.InitialContext();
  javax.sql.DataSource ds = (javax.sql.DataSource)
      ctx.lookup("java:comp/env/jdbc/myconnection");
  java.sql.Connection con = ds.getConnection();
  java.sql.PreparedStatement stmt = con.prepareStatement(sql);
  java.sql.ResultSet rs = stmt.executeQuery();

  while (rs.next()) {
     //do something
  }