Connect-Star for JDBC
Overview
Connect★ for JDBC is a type 4 Java driver for the Java environment.
Connect★ for JDBC supports Connect★ functionality to access Model 204. It includes all SQL and RCL statement syntax.
See Model 204 documentation for related Model 204 documentation.
Connect★ for JDBC environment setup
Limitations and specifications
Limitations
A complete listing of the Connect★ for JDBC limitations is provided in the Connect Star for Model 204 program group under the JDBC > J204 API.
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★ for JDBC:
- Microsoft® Windows® 7, Windows 8.1
- Windows Server® 2008, Windows Server 2012
These platforms are supported by Rocket Software.
Environment requirements
Before you install Connect★ for JDBC you must have the following installed in your working environment:
- Java SE Development Kit (JDK) 7.0 or higher.
- Model 204 Online, version 7.4 or higher with TCP/IP and SQL.
Removing a previous version of Connect★ for JDBC
To remove a previous version of Connect★ 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★ for JDBC
- Ensure that you have fulfilled the preinstallation requirements as described in Preparing to install.
- Using your Rocket M204 user ID and password, log in 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* drivers are available in zip format. Click the Download ZIP file link for the Connect* 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*.)
- 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.
- The isolation_level_option for the isolation level parameter is one of the following:
-
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.
Default is DEFAULT
Properties key: dirty data
| Option | Specifies |
|---|---|
| RC, the default | Read Committed (cursor stability) |
| RU | Read Uncommitted (dirty read) |
| SR | Serializable |
Properties key: isolation level
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:
- Navigate to Start > All Programs > Connect Star for Model 204 > JDBC > Database Connector for JDBC.
- Complete the Connection Information screen and click the Test Connection button.
Connect★ for JDBC Compilation and Execution
Execution prerequisite
Before executing an SQL statement, check with your Connect★ 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★ for JDBC includes HTML help files that provide complete documentation of the J204 JDBC driver. For detailed documentation, browse the index.HTML file in the \doc directory where the driver is installed.
RCL and JDBC
Java CallableStatements provide a means of calling stored procedures. Model 204 SQL does not support stored procedures; therefore, Connect★ 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★ 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★ 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★ version 7.5 and higher for JDBC has been tested with the JNDI, Apache DBCPTM, BoneCP, and C3PO pooling packages.
Connection pooling example
Below is an example of pooling connections using the Apache Commons DBCPTM component.
There are other examples provided in the install directory of Connect★ for JDBC:
/** * 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