Skip to main content
Previous section   Next section

Establishing JDBC Connections

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]);
      }
   }
Previous section   Next section