Connect-Star for JDBC

Overview

Connect-Star for JDBC is a type 4 Java driver for the Java environment.

Connect-Star for JDBC supports Connect-Star functionality to access Model 204. It includes all SQL and RCL statement syntax.

See Model 204 documentation for related Model 204 documentation.

Connect-Star for JDBC environment setup

Limitations and specifications

Limitations

A complete listing of the Connect-Star for JDBC limitations is provided in the Help File on the API, which you can access from the Start menu with your Connect Star for Model 204 JDBC application.

JDBC API specifications

JDBC API incorporates JDBC 2.5 with no extended functionality.

Platforms tested

The following platforms have been tested successfully for connectivity with Model 204 using Connect-Star for JDBC:

Microsoft^® Windows^® 7, Windows 8.1, and Windows 10
Windows Server^® 2008, Windows Server 2012

These platforms are supported by Rocket Software.

Environment requirements

Before you install Connect-Star for JDBC you must have the following installed in your working environment:

Java Runtime 32-bit JRE (minimum version 1.6)
Model 204 Online, version 7.4 or higher with TCP/IP and SQL.

Removing a previous version of Connect-Star for JDBC

To remove a previous version of Connect-Star for JDBC, do the following:

Go to the Control Panel > Add/Remove Programs.
Click on Connect Star for JDBC and remove.
Go to the installation folder for the previous version of J204 and delete the j204.jar file.

Installing Connect-Star for JDBC

Ensure that you have fulfilled the preinstallation requirements as described in Preparing to install.
With your Rocket M204 user ID and password handy, go to the Rocket M204 Customer Care page.
Click the Download client (workstation) files link to go to the Client files for Rocket Software page.
The Connect-Star drivers are available in zip format. Click the Download ZIP file link for the Connect-Star drivers.
Click Save as to save the cstar.zip file in your preferred directory. (You will later be prompted for the directory where you want to actually install Connect-Star.)
Expand the file in an unzip application, such as WinZip, and double-click the setup file: setup_jdbc.exe.
The Connect Star Setup Wizard appears. Click Next and select an installation folder, or use the default folder (C:\Program Files\CCA\Connect Star for Model 204\JDBC\). Click Next.
Click Install to begin the installation.
Click Finish to complete the installation.

Connection parameters

To connect a J204 client application to a Model 204 server, the application must specify connection parameters in the URL and/or java.util.Properties object supplied as parameters to DriverManager.getConnection().

See J204 API for DriverManager and J204Driver classes. If a parameter is specified in both the URL and the properties object, the value in the properties object takes precedence.

The URL includes both keyword and positional parameters. Positional parameters must be entered in the specified order, while keyword parameters may appear in any order. All keyword parameters are optional.

Note: If you specify parameters in the properties object, you must enter the key entirely in uppercase or lowercase. Otherwise, the property will not be found. The case of parameters in the URL can be upper, lower, or mixed; it does not matter.

The URL has the following format:

Syntax

JDBC:J204:// {hostname} : {port} {/connection_type} [/user/password] [/eoc=eoc_character] [/cursors=commit_option] [/dirty data=dirty_data_option] [/isolation level=isolation_level_option] [/trace=trace_option]

Where

hostname specifies the server's TCP/IP name, entered in dotted decimal or string format.
Required; in the URL
port specifies the port entered for the SERVPORT parameter in the DEFINE LINK command for the Model 204 server.
Required; in the URL
connection_type is either SQL or RCL.
Required; in either the URL or properties list
Properties key: connection type
user and password specify a valid Model 204 user name and password.
Optional; no defaults
Properties keys: user, password
The eoc_character for the eoc parameter specifies the end-of-line character for RCL connection.
Default character is a semicolon (;)
Properties key: eoc
The commit_option for the cursors parameter determines the effect of commit on cursors. Choose one of the following options:
- DROP specifies to close the cursor and drop the statement.
- PRESERVE specifies to preserve open cursors and prepared statements.
Default is DROP
Properties key: cursors
The dirty_data_option for the dirty data parameter specifies the treatment of dirty data by a Fetch statement. Choose one of the following options:

DEFAULT uses the server default specified by the SQLCNVER parameter in the CCAIN stream; this is the default.
SKIP specifies to bypass a record containing dirty data and continue on to the next.
ERROR specifies to return an error message whenever dirty data is encountered.

Default is DEFAULT
Properties key: dirty data

The isolation_level_option for the isolation level parameter is one of the following:

Option	Specifies
RC, the default	Read Committed (cursor stability)
RU	Read Uncommitted (dirty read)
SR	Serializable

Properties key: isolation level

The trace parameter controls logging. The trace_option is one of the following:

Option Specifies

ALL Write all trace messages to the log file

NONE Write no trace messages

No log file is created if this parameter is omitted. For more information, see Data source default parameters.

Usage

You must specify the host name and port in the URL. You can specify the other parameters in the URL or in the properties object. If you specify a parameter in both places, the value specified in the properties list takes precedence.
Positional parameters must be entered in the specified order. Hostname, port, and connection type must be specified in the URL before other options.
All keyword parameters are optional.
When specifying parameters in a properties list, you must enter the key (property name) in either uppercase or lowercase characters, but not mixed case. If the key is in mixed case, the property cannot be found. When specifying parameters in the URL, you can enter uppercase, lowercase, or mixed case.

Verifying your JDBC connection

To verify your JDBC connection:

From the Start menu, navigate to the Connect Star for Model 204 JDBC application and open the Database Connector for JDBC.
Complete the Connection Information screen and click the Test Connection button.

Connect-Star for JDBC Compilation and Execution

Execution prerequisite

Before executing an SQL statement, check with your Connect-Star administrator to make sure that either the demonstration database is installed and has been defined to the SQL catalog file, CCACAT, or you have other available tables defined in CCACAT. (See SQL catalog population.)

Online help

Connect-Star for JDBC includes a Help File that provides complete documentation of the J204 JDBC driver classes. To access the Help File, open Connect-Star for JDBC from the Start menu.

RCL and JDBC

Java CallableStatements provide a means of calling stored procedures. Model 204 SQL does not support stored procedures; therefore, Connect-Star for JDBC does not implement the CallableStatement interface.

Calling any CallableStatement method will result in an Unsupported Method SQLException.

An alternative to CallableStatement is the RCLStatement class, which allows execution of SOUL statements and procedures. You can use the following methods for RCL processing:

close() createStatement() execute() executeQuery() getResultSet() getResultSetType() getTransactionIsolation() nativeSQL()

BLOB and CLOB support

Connect-Star for JDBC supports Model 204 BLOB and CLOB data types in SQL update and retrieval statements.

Use the following ResultSet methods to retrieve large object column data after SELECT statement execution.

BLOB byte[] getBytes() Object getObject() InputStream getBinaryStream() CLOB byte[] getBytes() Object getObject() String getString() InputStream getAsciiStream() InputStream getBinaryStream() java.io.Reader getCharacterStream() InputStream getUnicodeStream()

In INSERT and UPDATE statements, use parameters for large object columns. First, create a prepared statement, then set the values of the parameters with the following PreparedStatement methods.

BLOB setBytes() setObject() setBinaryStream() CLOB setBytes() setObject() setAsciiStream() setBinaryStream() setCharacterStream() setUnicodeStream()

j204.jar file

You must include the j204.jar file in the classpath for both compilation and execution. See your Java documentation for a complete list of options.

Note that the j204.jar file can be placed anywhere as long as the classpath points to its directory. The j204.jar file can be used in any Java SDK environment.

Compilation example

javac -classpath c:\jdbc\yourClasses;c:\jdbc\j204.jar yourProgram.java

Execution example

java -cp .;c:\jdbc\yourClasses;c:\jdbc\j204.jar yourProgram

Debug tracing for JDBC

To create a trace log file, use the TRACE=ALL parameter in the connection string. See Connection parameters.

J204Driver log file location

For applications run under Windows 7, the driver places the log file in the user's home directory:

C:\Users\user_name

For other operating systems, the log file is placed in the same directory as the j204.jar file.

Connection pooling

Connect-Star version 7.5 and higher for JDBC offers a "connection pooling" class to interface with other pooling infrastructures provided by pooling packages and web application servers. This new feature allows for a pool of connections that remain active and open during execution of an SQL and RCL (SOUL) statement.

Using connection pooling helps eliminate the overhead of creating initial connections from scratch or trying to manage each connection using the JDBC API.

Each Connection Pool can create a pool of active connections maintainable throughout the connection cycle of the pooling service.

Connect-Star version 7.5 and higher for JDBC has been tested with the JNDI, Apache DBCP^TM, BoneCP, and C3PO pooling packages.

Connection pooling example

Below is an example of pooling connections using the Apache Commons DBCP^TM component.

/** * Example: Pooling Connection program using Apache DBCP (Database Connection * Pooling) Package */ package com.cca.j204.tests; import javax.sql.DataSource; import java.sql.Connection; import java.sql.Statement; import java.sql.ResultSet; import java.sql.SQLException; // Using the Apache DBCP BasicDataSource. // The example is using the following libraries: // commons-dbcp-1.x.jar // commons-pool-1.x.jar import org.apache.commons.dbcp.BasicDataSource; public class SimpleDBCPExample { public static void main(String[] args) { BasicDataSource basicDatasource = new BasicDataSource(); basicDatasource.setDriverClassName("com.cca.j204.J204Driver"); basicDatasource.setUsername("user"); basicDatasource.setPassword("password"); basicDatasource.setUrl("jdbc:j204://mainframe.domain.com:2403/rcl"); basicDatasource.setInitialSize(5); // Set up 5 connections at initialization DataSource dataSource = basicDatasource; String statement = "VIEW ALL;"; // SOUL Statement // Show statistics before the connections are established System.out.println("Statistics before the connections are established."); showStatistics(dataSource); // Now, we can use JDBC DataSource as we normally would. Connection connectionHandle = null; Statement statementHandle = null; ResultSet results = null; try { System.out.println("Creating connection."); connectionHandle = dataSource.getConnection();//Start all 5 connections at once. // Show statistics while the connections are established System.out.println("Statistics after initializing the connections."); showStatistics(dataSource); System.out.println("Creating statement."); statementHandle = connectionHandle.createStatement(); System.out.println("Executing statement."); results = statementHandle.executeQuery(statement); System.out.println("Show Results:"); int numcols = results.getMetaData().getColumnCount(); while(results.next()) { for(int i=1;i<=numcols;i++) { System.out.print("\t" + results.getString(i)); } System.out.println(""); } // Show statistics while the connections are established System.out.println("Statistics while the connections are established."); showStatistics(dataSource); results.close(); statementHandle.close(); connectionHandle.close(); // Show statistics after the connections have been closed System.out.println("Statistics after the connections have been closed."); showStatistics(dataSource); // Clean up and close all of the pooled connections basicDatasource.close(); } catch(SQLException ex1) { System.out.println("SQLException in main(): " + ex1.getMessage()); } } // Print out the statistics from the BasicDataSource class public static void showStatistics(DataSource ds) { BasicDataSource bds = (BasicDataSource) ds; System.out.println("Number of Active Connections: " + bds.getNumActive()); System.out.println("Number of Idle Connections: " + bds.getNumIdle()); } }

The sample code generates this output:

Statistics before the connections are established. Number of Active Connections: 0 Number of Idle Connections: 0 Creating connection. Statistics after initializing the connections. Number of Active Connections: 0 Number of Idle Connections: 5 Creating statement. Executing statement. Show Results: VERSION 7.4.0K RELEASE OF MODEL 204 CMSVERSN VERSION OF THE CMS INTERFACE SYSOPT X'AB' SYSTEM OPTIONS SYSOPT2 X'00' SYSTEM OPTIONS OPSYS X'D209E8' OPERATING SYSTEM SYSID RS26 SMF SYSTEM IDENTIFICATION JOBNM M204MWD JOB NAME STEPNM ONLINE STEP NAME JOBSTEP MPONLINE JOB STEP NAME JESID J0919076 JES JOB ID LOCATION D204 LOCATION LSERVPD 8176 LENGTH OF SCHEDULER PDL NUSERS 432 NUMBER OF USERS NSERVS 15 NUMBER OF SERVERS SERVNSA X'00000000' NON SWAPPABLE SERVER AREAS SERVNSSZ 0 NON SWAPPABLE SERVER AREA SIZE NJBUFF 36 NUMBER OF JOURNAL BUFFERS .... (continue output) Statistics while the connections are established. Number of Active Connections: 1 Number of Idle Connections: 4 Statistics after the connections have been closed. Number of Active Connections: 0 Number of Idle Connections: 5