Basic JDBC driver operations

This chapter describes how to set up and get started using the FairCom DB SQL JDBC Driver.

Required Java Environment

You must have a supported Java development or runtime environment on each system that uses the FairCom DB JDBC Driver. The FairCom DB JDBC Driver requires the following development (or compatible runtime) environments:

• OpenJDK Version 8 or higher (A Java 1.5 compatible version of the JDBC driver compatible is available upon request for applications still tied to this legacy Java framework.)

(See Java Requirements for FairCom DB SQL for the JDK version required for your FairCom DB SQL environment,)

You must have one of these environments or their associated Java runtime environments to use the JDBC Driver. For details on obtaining this software, visit Click here to download.

Connection String

Beginning with FairCom DB V11.2 and FairCom RTG V2, the connection string is in the following format:

jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[&param=value]...]

The valid values for param are:

• characterEncoding - Replace encoding with a valid Java encoding name (e.g., US‑ASCII, ISO‑8859-1, UTF‑8, etc.).
• password
• User
• ssl - Values of basic (no peer certificate authentication) or peerAuthentication (server certificate authentication)

When peerAuthentication is requested, the client’s trust store must contain the server’s certificate as shown in the example below.

• sock_timeout - specifies a socket timeout in milliseconds.
• tcpKeepaliveInterval - The default value is 0 (no keepalive). When a value greater than 0 is specified, a tcp client connection requests a TCP level keepalive probe that is sent after the TCP link has been idle for the specified interval in seconds. If the host does not respond to the keepalive probe within 10 seconds, the connection will be treated as broken. This keepalive probe provides periodic activity on the link to help achieve 2 goals.1) prevent network hardware from identifying the connection as idle and silently terminating it. 2) distinguish a broken network link from a server operation that takes a long time to respond (unlike a socket timeout).

NOTE: tcpKeepaliveInterval requires Java 11, and is not supported on all platforms.

The tutorials use a connection string that is set for the default configuration:

"jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN"

TLS/SSL Examples

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=basic");

System.setProperty("javax.net.ssl.trustStore","TrustStore.key");

System.setProperty("javax.net.ssl.trustStorePassword","mypassword""");

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=peerAuthentication");

For backward compatibility, the older format ("jdbc:ctree:6597@localhost:ctreeSQL", "ADMIN", "ADMIN") is still supported but should be considered deprecated.

Connecting to a Database

The topics in this section explain the two steps FairCom DB SQL JDBC applications must perform to connect to a database:

1. Load the JDBC driver.
2. Connect to the driver.

This section provides a sample in An Example Connection.

Load the JDBC Driver Using Class.forName

The Class.forName() method takes as its argument the fully-qualified class name for the FairCom DB SQL JDBC Driver. If it finds the class, the method loads and links the class, and returns the Class object representing the class.

The fully-qualified class name for the FairCom DB SQL JDBC Driver is ctree.jdbc.ctreeDriver. To load the JDBC Driver, use it as the argument to the Class.forName() method:

// Load the driver

Class.forName ("ctree.jdbc.ctreeDriver");

Connect to the JDBC Driver Using DriverManager.GetConnection

To connect to a FairCom DB SQL database through the FairCom DB SQL JDBC Driver, an application specifies:

• A database connection string in the form of a JDBC URL
• User authentication detail (user name and password)

Applications specify this information as arguments to the DriverManager.GetConnection() method.

JDBC Support for TLS

In FairCom DB V11.2 and later, FairCom DB SQL JDBC supports TLS connections per the JDBC standard. Enable TLS in a JDBC connection URL using the ssl=value parameter string.

TLS connections are enabled in the JDBC connection URL using the new format (it is not supported on the old URL format) and a new parameter ssl.

The new URL format is:

jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[&param=value]...]

The valid param values are:

• characterEncoding - Replace encoding with a valid Java encoding name (e.g., US‑ASCII, ISO‑8859-1, UTF‑8, etc.).
• password
• user
• ssl - The valid values for ssl are:

basic

peerAuthentication

• trustStore - The name of a local trust store that includes the server's CA certificate.
• trustStorePassword - The password for the specified trust store.
• keyStore - The name of a local key store that has the user certificate and private key.
• keyStorePassword - The password for the specified keyStore.

NOTE: For backward compatibility, the older format ("jdbc:ctree:6597@localhost:ctreeSQL", "ADMIN", "ADMIN") is still supported but should be considered deprecated.

Basic TLS with JDBC clients

Traffic to the server is encrypted, but there is no assurance of the server's identity.

Basic SSL encryption on the client is enabled by the URL parameter ssl, for example:

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=basic");

Peer Authenticated TLS with JDBC clients using System properties

If the client wants to authenticate the server, then the client's trust store must contain the server's CA certificate (or a self-signed server certificate). A Java keystore must first be created that contains this CA certificate.

For example, the following adds the trusted CA certificate ctsrvr.pem to a keystore named server.store

keytool -importcert -file ctsrvr.pem -keystore server.store

keytool is part of the Java distribution, and will prompt you for a password used to encrypt the trust store. In this example we used mypassword as the password.

Client SSL with server authentication is enabled by the URL parameter ssl set to peerAuthentication.

You must set the system properties javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword to reference the trust store for the desired database server.

Example:

System.setProperty("javax.net.ssl.trustStore","server.store");

System.setProperty("javax.net.ssl.trustStorePassword","mypassword");

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=peerAuthentication");

Full TLS authentication with JDBC clients

The FairCom database server may be configured to allow or require client TLS authentication in place of password based authentication. The client needs to both authenticate the server (with the same requirements as for peerAuthentication), and provide proof of identity to the server by providing a client certificate that is trusted by the server. This may be set in the connection string by specifying a trustStore for the server and keyStore for the user.

Example:

Create the trust store with the trusted CA certificate ctsrvr.pem to a keystore named server.store. Keytool is part of the Java distribution, and will prompt you for a password used to encrypt the trust store. In this example we use mypassword as the password.

keytool -importcert -file ctsrvr.pem -keystore server.store

The client must possess a keystore containing their certificate and private key. Here we assume a keystore exists named johndoe.pkcs12 protected with password secret.

NOTE: The default keystore format is controlled by the java.security configuration file under the property keystore.type. The PKCS12 keystore format is supported by all Java implementations, but prior to Java9 JKS was the default format. You may need to adjust this configuration for your keystore to be accessed by Java.

The following connection string will attempt to connect using these certificate sets for TLS authentication.

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?trustStore=server.store&trustStorePassword=mypassword&keyStore=johndoe.pkcs12&keyStorePassword=secret");

See Also:

Java keytool documentation provides examples of how to generate a key pair, request a certificate from a CA, or generate certificates for a server.

User Authentication Detail

DriverManager.GetConnection() accepts three variants of user authentication detail:

• User name and password passed as two character string arguments:

Connection con = DriverManager.getConnection (url, "ADMIN" "ADMIN" );

• User name and password passed as a single Properties object:

Connection con = DriverManager.getConnection (url, prop );

Note that the FairCom DB SQL JDBC Driver expects the keys of the Properties object to be named user and password when it processes the object. Application code must use those names when it populates the Properties object:

prop.put("user", userid);

prop.put("password", passwd);

• User name and password omitted. The FairCom DB SQL JDBC Driver connects to the database with a blank username and null password:

Connection con = DriverManager.getConnection (url);

An Example Connection

The following example shows a code excerpt that illustrates loading the driver and connecting to the default server and database. The following example uses the form of DriverManager.GetConnection() that takes authentication information as a single Properties object.

Example Loading the JDBC Driver and Connecting to a Database

String url = "jdbc:ctree://localhost:6597/ctreeSQL";

String userid = "ADMIN";

String passwd = "ADMIN";

// Load the driver

Class.forName ("ctree.jdbc.ctreeDriver");

// Attempt to connect to a driver. Each one

// of the registered drivers will be loaded until

// one is found that can process this URL.

java.util.Properties prop = new java.util.Properties();

prop.put("user", userid);

prop.put("password", passwd);

Connection con = DriverManager.getConnection (url, prop);

Scrollable Cursors Within ResultSets

FairCom DB JDBC supports two different scrolling options for ResultSets. The default option is TYPE_FORWARD_ONLY. When a ResultSet is opened with TYPE_FORWARD_ONLY, only forward scrolling cursors are supported. Dharma JDBC also supports TYPE_SCROLL_INSENSITIVE. When a ResultSet is opened with TYPE_SCROLL_INSENSITVE, the cursor can scroll forwards or backwards as well as be positioned using relative or absolute positions. While a scrollable cursor offers greater flexibility, there is a possible performance penalty in that the implementation of the scrollable cursor requires that the entire result set be fetched when the first fetch call is made. With a TYPE_FORWARD_ONLY cursor, the JDBC Driver only fetches a set of rows at a time, and much of the result set may never be fetched if the application does not require it.

CONCUR_READ_ONLY is the only supported concurrency mode.

Improved Scrollable Cursor Support in V11

Scrollable cursor support was introduced in FairCom DB SQL V11. In V11.5, server-side logic and JDBC driver-side logic have been enhanced to include the following support:

• The methods isBeforeFirst(), isFirst(), isLast(), and isAfterLast() now return appropriate values for an empty resultset.
• Using a negative value with absolute() is now supported.
• Wrong results from isBeforeFirst()/isAfterLast() after calls to various positioning functions (next, prev, absolute, relative...) now provide proper results.

These modifications should greatly enhance scrollable cursor usage.

Connection Pooling Support in JDBC

The IBM’s Websphere 4.0 Application server uses the JDBC APIs DataSource, PooledConnection and ConnectionPoolDataSource to connect to any data source. These APIs are part of the javax.sql package. It is mandatory to support them when working with IBM's Websphere 4.0 middleware.

javax.sql Package

The FairCom DB SQL JDBC javax.sql package provides the APIs for server side data source access and processing. This is included in the Java 2 SDK version 1.6, Standard Edition.

The javax.sql package provides the following:

• The DataSource interface as an alternative to the DriverManager for establishing a connection with a data source
• Connection pooling

Using A DataSource Object To Make A Connection

The <FairCom DB SQL JDBC javax.sql package provides the preferred way to make a connection with a data source. The DriverManager class, the original mechanism, is still valid, and code using it will continue to run. However, the newer DataSource mechanism is preferred because it offers many advantages over the DriverManager mechanism.

The following are the main advantages of using a DataSource object to make a connection:

• Applications do not need to hard code a driver class.
• Changes can be made to a data source’s properties, which means that it is not necessary to make changes in application code when something about the data source or driver changes.
• Connection pooling and distributed transactions are available through a DataSource object that is implemented to work with the middle-tier infrastructure. Connections made through the DriverManager do not have connection pooling or distributed transaction capabilities.

A particular DataSource object represents a particular physical data source, and each connection the DataSource object creates is a connection to that physical data source. A DataSource object can be implemented to work with the middle tier infrastructure so that the connections it produces will be pooled for reuse. An application that uses such a DataSource implementation will automatically get a connection that participates in connection pooling. A DataSource object can also be implemented to work with the middle tier infrastructure so that the connections it produces can be used for distributed transactions without any special coding.

Connection Pooling

Connections made via a DataSource object that is implemented to work with a middle tier connection pool manager will participate in connection pooling. This can improve performance dramatically because creating new connections is very expensive. Connection pooling allows a connection to be used and reused, thus reducing the number of new connections that need to be created.

Connection pooling is totally transparent. It is done automatically in the middle tier of a J2EE Environment, and hence from an application’s viewpoint, no change in code is required. An application simply uses the DataSource.getConnection() method to get the pooled connection and uses it the same way it uses any Connection object.

Implementation in FairCom DB SQL JDBC Driver

To support working with IBM's Websphere 4.0 Application server, the following interfaces from the package javax.sql have been implemented.

• javax.sql.DataSource

This is a factory for connections to the physical data source that this DataSource object represents. An object that implements the DataSource interface will typically be registered with a naming service based on the Java Naming and Directory (JNDI) API.

• javax.sql.PooledConnection interfaces

An object that provides hooks for connection pool management. A PooledConnection object represents a physical connection to a data source. The connection can be recycled rather than being closed when an application is finished with it, thus reducing the number of connections that need to be made.

• javax.sql.ConnectionPoolDataSource

This is a factory for PooledConnection objects. An object that implements this interface will typically be registered with a naming service that is based on the Java Naming and Directory Interface (JNDI).

Adding FairCom DB JDBC to a Third-Party JDBC Tool

The FairCom DB JDBC API can be used with third-party applications such as ReadyAPI application from SmartBear. The steps below explain how to install and configure the FairCom DB JDBC Driver to work with this application.

You will need to know how to load a new JDBC Driver within the ReadyAPI application, as explained on their website: https://support.smartbear.com/readyapi/docs/testing/data-driven/drivers/install/index.html

Steps 1 and 2 from the link shown above are accomplished by copying the ctreeJDBC.jar file to the ReadyAPI bin/ext directory, such as:

C:\Program Files\SmartBear\ReadyAPI-2.4.0\bin\ext

Make sure you have Administrator privileges to be able to copy there.

You can find the ctreeJDBC.jar file in your FairCom DB PRO package in this default location: \FairCom\v11.5.0\winX64\lib\sql.jdbc

Once you restart the ReadyAPI, you can configure the FairCom DB JDBC Driver as shown below:

This corresponds to the following FairCom DB JDBC connection string:

jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[&param=value]...]

The image below shows a select * from one of the FairCom DB sample tables:

Managing Transactions Explicitly to Improve Performance

By default, new connections in FairCom DB SQL JDBC applications are in autocommit mode.

In autocommit mode every FairCom DB SQL statement executes in its own transaction:

• After successful completion, the JDBC Driver automatically commits the transaction.
• If the statement execution fails, the JDBC Driver automatically rolls back the transaction.

Note: In autocommit mode, the JDBC Driver does not issue a commit after SELECT and CALL statements. The driver assumes these statements generate result sets and relies on the application to explicitly commit or roll back the transaction after it processes any result set and closes the statement.

You can change transaction mode to manual commit by calling the Connection.setAutoCommit() method. In manual commit mode, applications must commit a transaction by using the Connection.commit() method. Similarly, applications must explicitly roll back a transaction by invoking the Connection.rollback() method.

You will improve the performance of your programs by setting autocommit to false after creating a Connection object with the Connection.setAutoCommit() method:

Connection con = DriverManager.getConnection ( url, prop);

.

.

.

con.setAutoCommit(false);

Query Timeout Options

The FairCom DB SQL supports a timeout option for an executing query. This feature can ensure that an unintended query statement does not consume excessive processing time.

With the FairCom DB SQL JDBC Driver, use the setQueryTimeout() method of the java.sql.Statement interface as shown here.

c-treeSQL JDBC Example

Class.forName ("ctree.jdbc.ctreeDriver");

Connection myConnection = DriverManager.getConnection("jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN");

Statement myStatement = myConnection.createStatement();
 

String query = "SELECT TOP 50000 FROM my_big_table WHERE this < that AND this_string = 'that_string' ORDER BY foo"

myStatement:setQueryTimeout(5);

myStatement.executeUpdate(query);

Driver Socket SEND/RECV Timeout

A send/recv timeout option allows a FairCom DB SQL JDBC client to request a timeout for a socket. If the client experiences a lengthy wait for the server to reply, the client can continue to work after closing the connection.

In FairCom DB V11, the default JDBC socket timeout has been changed from 30 minutes to 0 (no timeout).

With the FairCom DB SQL JDBC Driver, it is possible at connection time to set a timeout value in milliseconds using the DriverManager.getConnection(string, properties) method as follows.

Example

Properties info = new Properties();
 

info.setProperty("user","ADMIN");

info.setProperty("password","ADMIN");

info.setProperty("sock_timeout","30000");

conn = DriverManager.getConnection ("jdbc:ctree://localhost:6597/ctreeSQL", info);

Note: The time-out is an integer it is specified as a string.

JDBC Version Information

Version information is available from the FairCom DB SQL JDBC driver. The Java JDBC getDriverMajorVersion() and getDriverMinorVersio() methods can be used to retrieve these values.

JDBC Version Example

Connection con = DriverManager.getConnection ( url, prop);

// Get the DatabaseMetaData object and display

// some information about the connection

DatabaseMetaData dma = con.getMetaData ();
 

o.println("\nConnected to " + dma.getURL());

o.println("Driver " +

dma.getDriverName());

o.println("Version " +

dma.getDriverVersion());

Setting Up the JDBC Driver: Application Server

In an application server environment, the system on which the JDBC application runs also has the JDBC driver installed. This configuration provides good performance when users are on the same system or can execute the JDBC application across a network.

To set up the JDBC Driver, you must have access to a system (Unix or Windows) where the FairCom DB SQL libraries and executable files have been built. On both Windows and Unix, the JDBC class files are created in the classes\ctreeJDBC.jar file.

If the application server is a different system than the system used for those FairCom DB SQL libraries and executables, you may need to copy the jar file to the application server. Then, you need to set the CLASSPATH environment variable to include the directory where the class files reside.

The following sections describe these steps.

Copying JDBC Driver Files to the Application Server (If Necessary)

This step is not necessary if the application server has access to the disk containing the class files (through a Windows network or NFS-mounted disks on Unix).

Otherwise, you must first copy files to the application server. On Unix and Windows systems, use any available utility to copy the jar files to the application server.

Setting Environment Variables

Whether the JDBC Driver class files reside locally or on network-served disks, you must set the CLASSPATH environment variable to point to the class files.

On both Windows and Unix the CLASSPATH environment variable must point to the jar file ctreeJDBC.jar.

With Windows, you can set the CLASSPATH environment variable at the Windows command prompt, as in the following example:

set CLASSPATH=%CLASSPATH%;

.;

c:\<jdk_version>\jre\lib\rt.jar;

c:\FairCom\V10.0.0\win32\lib\sql.jdbc\ctreeJDBC.jar

(For environment variables to persist across different processes, set them using the Windows Control Panel’s System utility, and set them as system variables.)

Also, make sure the PATH environment variable includes the appropriate Java compiler directory. Example syntax could be as follows:

PATH=%PATH%;

c:\<jdk_version>\bin;

c:\<jdk_version>\jre\bin\classic

(Again, for the environment variables to persist across different processes, set them using the Windows Control Panel’s System utility, and set them as system variables.)

Setting Up the JDBC Driver: Web Server

In a Web server environment, the FairCom DB SQL JDBC driver and Java applets that use it reside on a host system. No additional driver software is required on the client machine. Client applications must support a Java virtual machine compatible with the Java JDK Version 1.6 or later.

Client applications invoke a JDBC applet through a Web page on the server. The browser downloads both the applet and the JDBC Driver from the server (usually in compressed format) and runs the applet. The Java applet opens a database connection (see "Connecting to a Database") and accesses the database using the JDBC API.

A general JDBC security restriction is that applets can only open a database connection from the server from which they are downloaded. That means the host system must be running both the HTTP Web server and the FairCom DB SQL Server process.

To set up the JDBC Driver for an applet on a Web server, complete these steps:

• Copy compiled class files for the FairCom DB SQL JDBC Driver and the applet to a directory accessible to the Web page that will invoke the applet.
• Compress all the class files into a single Java Archive (JAR) file.
• Create the Web page that will invoke the applet.

The following sections describe these steps.

Copying JDBC Driver and Applet Class Files

On both Windows and Unix, the class files for the JDBC Driver are created in the classes\ctreeJDBC.jar file. Copy the jar files from that directory to a directory accessible to the Web page. Do the same for the applet’s class file.

For example:

systpe@mydomain% cd $webroot

/vol6/webroot

systpe@mydomain% mkdir test

systpe@mydomain% cd test

/vol6/webroot/test

systpe@isis% cp -i $TPEROOT/classes/ctreeJDBC.jar

systpe@isis% cp -i /applet_test/ctreeJDBCApplet.class

Creating a Web Page That Invokes the Applet

At a minimum, the page must include the APPLET tag that invokes the applet.

For example, the following page includes little else but the APPLET tag. The example’s APPLET tag specifies the c-treeJDBCApplet.class and ctreeJDBCTest.jar files from the preceding sections, as well as class-name and connection parameters to pass to the applet.

<html>

<head>

<title>Test</title>

</head>

<body>

<p>

Here, in all its glory, is the ctreeJDBCApplet test applet!

<center>

<applet code="c-treeJDBCApplet.class"

archive="c-treeJDBCTest.jar" width=500 height=400>

<param name=Driver value="ctree.jdbc.ctreeDriver">

<param name=URL value="jdbc:ctree://localhost:6597/ctreeSQL">

<param name=User value="ADMIN">

<param name=Password value="dummy">

</applet>

</center>

</body>

</html>