Add SQL Server jTDS JDBC Driver to SuperCHANNEL

JDBC (Java Database Connectivity) is a programming interface that lets Java applications access a relational database.

SuperCHANNEL needs a JDBC driver so that it can access the relational database system (e.g. SQL Server, Oracle, etc) where your source data is stored.

Before you can run SuperCHANNEL, you need to install the appropriate JDBC driver and configure SuperCHANNEL so that it can access the driver.

In most cases the JDBC drivers are supplied by the database vendors. STR does not supply the drivers.

Microsoft SQL Server

If your source data is stored in a Microsoft SQL Server database, then you need to configure SuperCHANNEL to use the jTDS JDBC driver. jTDS is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000, 2005, 2008 and 2012) and Sybase Adaptive Server Enterprise (10, 11, 12 and 15).

Step 1 - Download and Install the Driver

Download the latest version of the jTDS driver from http://sourceforge.net/projects/jtds/files/
Extract the driver file from the download. The driver file is a .jar file, and will be named something like jtds-1.3.0.jar
Copy the driver file to the drivers directory in your SuperCHANNEL installation. If you chose the default installation options, the location will be: C:\Program Files\STR\SuperCHANNEL\jar\drivers

Depending on your system configuration, you may need administrator permissions to copy to this directory. If you are prompted to provide administrator permissions, click Continue.

Step 2 - Edit the SuperCHANNEL Configuration

Locate the SuperCHANNEL configuration file, config.txt. If you chose the default installation options, this file will be located in C:\ProgramData\STR\SuperCHANNEL\bin

Make a backup copy of this file before making any changes.
Open config.txt in a text editor.
Locate the line that defines the Java Class Path variable (java.class.path), and add the following to the end of the line:

$(SNU_PROGRAM_HOME)\jar\drivers\jtds-1.3.0.jar;

Notes

Replace jtds-1.3.0.jar with the filename of the driver you installed.

$(SNU_PROGRAM_HOME) is a predefined variable that refers to the SuperCHANNEL program files directory.

Each entry in the Java Class Path definition must be separated by a semi colon, and there must also be a semi colon at the end of the line.
Locate the line that defines the jdbc.drivers and add the following to the end of the line:

:net.sourceforge.jtds.jdbc.Driver

Note

Each entry in the jdbc.drivers definition must be separated by a colon.
Save config.txt.

The following example shows the two additions to the configuration file:

Step 3 - SQL Server Express Only

If you are using SQL Server Express, you may encounter a connection error ("connection refused") when you attempt to connect to the database in SuperCHANNEL. This is because SQL Server Express is configured by default to use dynamic TCP/IP ports.

Before starting SuperCHANNEL, do the following:

Open SQL Server Configuration Manager.
Select SQL Server Network Configuration.
Select Protocols for MSSQLSERVER or Protocols for SQLEXPRESS (depending on your SQL Server version).
Open the TCP/IP Properties and select the IP Addresses tab.
Check at the bottom whether TCP Dynamic Ports has a value. If it does, clear the value and leave the field blank.
Change TCP Port to 1433 (or your preferred port).

Step 4 - Start SuperCHANNEL

Start SuperCHANNEL, and use the following connection string to connect to SQL Server:

CODE

jdbc:jtds:sqlserver://<servername>:<port>/<databasename>;useCursors=true

Where:

<servername> is the name of your host for SQL Server
<port> is the port to use to communicate with SQL Server (the default is 1433).
<databasename> is the name of the source database to connect to.

You will also need to provide user credentials (for SQL Server authentication) and a schema name for connection to succeed:

Step 5 - Add Drivers to SNU

This step is optional. If you intend to run SuperCHANNEL from the command line or via scripting, you should also add the drivers to snu.bat. This is the SuperCHANNEL batch file, located in C:\ProgramData\STR\SuperCHANNEL\bin by default.

Make a backup copy of this file before making any changes.

Add the driver entry to the last statement in the file. This is the Java call to execute the program.