Using Caché with JDBC
Establishing JDBC Connections
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter provides some detailed examples of Java code that uses the Caché JDBC driver to accomplish basic tasks.

Using CacheDataSource to Connect
Use com.intersys.jdbc.CacheDataSource to load the driver and then create the java.sql.Connection object. This is the preferred method for connecting to a database and is fully supported by Caché.
Note:
In earlier versions of JDBC, it was necessary to load the driver before connecting. For example:
   Class.forName ("com.intersys.jdbc.CacheDriver").newInstance();
   Connection dbconnection = DriverManager.getConnection(url,user,password);
JDBC versions 4.0 and higher use driver autoloading, which makes the Class.forName()call unnecessary for either DriverManager or CacheDataSource.
Here are the steps for using CacheDataSource:
  1. Import the java.sql and com.intersys.jdbc packages:
    import java.sql.*;
    import com.intersys.jdbc.*;
  2. Load the driver, then use CacheDataSource to create the connection and specify username and password:
    try{
       CacheDataSource ds = new CacheDataSource();
       ds.setURL("jdbc:Cache://127.0.0.1:1972/Samples");
       ds.setUser("_system");
       ds.setPassword("SYS");
       Connection dbconnection = ds.getConnection();
    }
    Note:
    On some systems, Java may attempt to connect via IPv6 if you use localhost in the URL rather than the literal address, 127.0.0.1. This applies on any system where the hostname resolves the same for IPv4 and IPv6.
  3. Create a java.sql.Statement using the connection. The Statement object can be used to execute queries.
    Statement stmt = dbconnection.createStatement()
  4. Catch the exceptions thrown by the above methods:
    catch (SQLException e){
    System.out.println(e.getMessage());
    }
    catch (ClassNotFoundException e){
    System.out.println(e.getMessage());
    }
Using DriverManager to Connect
The standard DriverManager class can also be used to create a connection. The following code demonstrates one possible way to do so:
   Class.forName ("com.intersys.jdbc.CacheDriver").newInstance();
   String  url="jdbc:Cache://127.0.0.1:1972/SAMPLES";
   String  username = "_SYSTEM";
   String  password = "SYS";
   dbconnection = DriverManager.getConnection(url,username,password);
You can also pass connection properties to DriverManager in a Properties object, as demonstrated in the following code:
   String  url="jdbc:Cache://127.0.0.1:1972/SAMPLES";
   java.sql.Driver drv = java.sql.DriverManager.getDriver(url);

   java.util.Properties props = new Properties();
   props.put("user",username);
   props.put("password",password);
   java.sql.Connection dbconnection = drv.connect(url, props);
See Caché JDBC Connection Properties for a complete list of the properties used by the Caché JDBC driver.
Defining a JDBC Connection URL
A java.sql.Connection URL supplies the connection with information about the host address, port number, and namespace to be accessed. The Caché JDBC driver also allows you to use several optional parameters.
Required Parameters
The URL specifies the host address, port number, and namespace to be accessed.
host IP address or Fully Qualified Domain Name (FQDN). For example, both 127.0.0.1 and localhost indicate the local machine.
port TCP port number on which the Caché SuperServer is listening. The default is 1972 (or the first available number higher than 56772 if more than one instance of Caché is installed — see DefaultPort in the Caché Parameter File Reference).
namespace Caché namespace to be accessed. For example, Samples is the namespace containing Caché sample programs.
The minimal required URL syntax is:
   jdbc:Cache://<host>:<port>/<namespace>
For example, the following URL specifies host as 127.0.0.1, port as 1972, and namespace as Samples:
   jdbc:Cache://127.0.0.1:1972/Samples
Optional Parameters
The Caché JDBC driver also allows you to specify several optional parameters. The full syntax is:
   jdbc:Cache://<host>:<port>/<namespace>/<logfile>:<eventclass>:<nodelay>:<ssl>
where the optional parameters are defined as follows:
logfile specifies a JDBC log file (see Enabling Logging for JDBC).
eventclass sets the transaction Event Class for this CacheDataSource object. See the CacheDataSource setEventClass() method for a complete description.
nodelay sets the TCP_NODELAY option if connecting via a CacheDataSource object. Toggling this flag can affect the performance of the application. Valid values are true and false. If not set, it defaults to true. Also see the getNodelay() and setNodelay() methods of CacheDataSource.
ssl enables SSL/TLS for both CacheDriver and CacheDataSource (see Using SSL/TLS with Caché in the Caché Security Administration Guide). Valid values are true and false. If not set, it defaults to false.
Each of these optional parameters can be defined individually, without setting the others. For example, the following URL sets only the required parameters and the nodelay option:
   jdbc:Cache://127.0.0.1:1972/Samples/::false
Setting the Port Parameter at the Command Line
The com.intersys.port property can be used to set the port parameter of the URL at the command line. Even if a program hard-codes the port number in the connection string, it can be changed in the command line. For example, assume that program myJdbcProgram sets the port to 1972 in a hard-coded connection string. The following command line will still allow it to run on port 9523:
   java -cp .:../lib/cache-db-2.0.0.jar -Dcom.intersys.port=9523 myJdbcProgram
The current value of this property can be retrieved programmatically with the following code:
   String myport = java.lang.System.getProperty ("com.intersys.port");
Alternate Username and Password Parameters
For the preferred ways to specify username and password, see Using CacheDataSource to Connect and Using DriverManager to Connect at the beginning of this chapter. However, it is also possible to specify the username and password in the URL string, although this is discouraged.
If password and username are supplied as part of the URL string, they will be used in order to connect. Otherwise, other mechanisms already in place will be invoked. The syntax is:
   jdbc:Cache://<host>:<port>/<namespace>/<options>?username=<string1>&password=<string2>
For example, the following URL string sets the required parameters, the nodelay option, and then the username and password:
   "jdbc:Cache://127.0.0.1:1972/Samples/::false?username=_SYSTEM&password=SYS"
The username and password strings are case sensitive.
Using a Connection Pool
The com.intersys.jdbc.CacheConnectionPoolDataSource class implements the javax.sql.ConnectionPoolDataSource interface, providing a connection pool for your Java client applications.
Note:
This implementation is intended only for testing and development. It should not be used in production.
Here are the steps for using a connection pool with Caché:
  1. Import the needed packages:
    import com.intersys.jdbc.*;
    import java.sql.*;
  2. Instantiate a CacheConnectionPoolDataSource object. Use the reStart() method to close all of the physical connections and empty the pool. Use setURL() to set the database URL (see Defining a JDBC Connection URL) for the pool's connections.
    CacheConnectionPoolDataSource pds = new CacheConnectionPoolDataSource();
       pds.restartConnectionPool();
       pds.setURL("jdbc:Cache://127.0.0.1:1972/Samples");
       pds.setUser("_system");
       pds.setPassword("SYS");
  3. Initially, getPoolCount returns 0.
    System.out.println(pds.getPoolCount()); //outputs 0.
  4. Use CacheConnectionPoolDataSource.getConnection() to retrieve a database connection from the pool.
    Connection dbConnection = pds.getConnection();
    Caution:
    Caché driver connections must always be obtained by calling the getConnection() method (inherited from CacheDataSource). Do not use the getPooledConnection() methods, which are for use only within the Caché driver.
  5. Close the connection. Now getPoolCount returns 1.
    dbConnection.close();
    System.out.println(pds.getPoolCount()); //outputs 1
Caché JDBC Connection Properties
The Caché JDBC driver supports several connection properties, which can be set by passing them to DriverManager (as described in Using DriverManager to Connect).
The following properties are supported:
user Required. String indicating Username. Default = ""
password Required. String indicating Password. Default = ""
TCP_NODELAY Optional. Boolean indicating TCP/IP NoDelay Flag. Default = true.
SO_SNDBUF Optional. Integer indicating TCP/IP SO_SNDBUF value (SendBufferSize). Default = 0 (use system default value).
SO_RCVBUF Optional. Integer indicating TCP/IP SO_RCVBUF value(ReceiveBufferSize). Default = 0 (use system default value).
TransactionIsolationLevel Optional. java.sql.Connection constant indicating Transaction Isolation Level. Valid values are TRANSACTION_READ_UNCOMMITTED (the default) or TRANSACTION_READ_COMMITTED.
service principal Optional. String indicating Service Principal Name. Default = null.
connection security level Optional. Integer indicating Connection Security Level. Valid levels are 0, 1, 2, 3, or 10. Default = 0.
0 - Caché login (Password)
1 - Kerberos (authentication only)
2 - Kerberos with Packet Integrity
3 - Kerberos with Encryption
10 - SSL/TLS
Listing Connection Properties
Code similar to the following can be used to list the available properties for any compliant JDBC driver:
   java.sql.Driver drv = java.sql.DriverManager.getDriver(url);
   java.sql.Connection dbconnection = drv.connect(url, user, password);
   java.sql.DatabaseMetaData meta = dbconnection.getMetaData();

   System.out.println ("\n\n\nDriver Info: ==========================");
   System.out.println (meta.getDriverName());
   System.out.println ("release " + meta.getDriverVersion() + "\n");

   java.util.Properties props = new Properties();
   DriverPropertyInfo[] info = drv.getPropertyInfo(url,props);
   for(int i = 0; i < info.length; i++) {
      System.out.println ("\n" + info[i].name);
      if (info[i].required) {System.out.print ("   Required");}
         else {System.out.print ("   Optional");}
      System.out.println (", default = " + info[i].value);
      if (info[i].description != null) 
         System.out.println ("   Description: " + info[i].description);
      if (info[i].choices != null) {
         System.out.println ("   Valid values: ");
         for(int j = 0; j < info[i].choices.length; j++)
            System.out.println ("      " + info[i].choices[j]);
      }
   }