Add MySQL JDBC Drivers 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.

MySQL

If your source data is stored in a MySQL database, then you need to configure SuperCHANNEL to use the MySQL JDBC driver.

Step 1 - Download and Install the Driver

The MySQL jdbc driver is called Connector/J. Download the latest version from http://www.mysql.com/downloads/connector/j/

Choose the Platform Independent option from the drop-down list and select the ZIP or TAR archive.
Extract the driver file from the download. The driver file is a .jar file, and will be named something like mysql-connector-java-5.1.7-bin.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\mysql-connector-java-5.1.7-bin.jar;

Notes

Replace mysql-connector-java-5.1.7-bin.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:

:com.mysql.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 - Start SuperCHANNEL

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

CODE

jdbc:mysql://<servername>:<port>/<databasename>

Where:

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

You will also need to provide user credentials (for MySQL authentication):

Note About Reverse Channelling to MySQL

The relaxAutoCommit switch is required when writing data to MySQL using SuperCHANNEL. A failover error will occur during the upload if you do not use it in the connection string, as follows:

CODE

jdbc:mysql://127.0.0.1:3306/upload?relaxAutoCommit=true

The ability to use reverse channelling will depend on the database structure.

Step 4 - 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.

Further Details on using MySQL with SuperCHANNEL

The following is some additional information if you are using MySQL with SuperCHANNEL. The following are guidelines only, and will need to be reviewed for specific circumstances.

Installation of MySQL

Install the software in accordance with instructions and guidance provided by the setup program.
If prompted, avoid using the 'InnoDB' engine model. Use the 'MyISAM' engine.
When configuring the Server Instance using the configuration wizard:
- Opt to perform a "Detailed Configuration" (not 'Standard').
- Select to setup a "Server Machine" (not 'Developer' or 'Dedicated').
- Select the usage option for 'Non-Transactional Database Only' (not Transactional or Multifunctional).
When specifying concurrent connections, select the option to use a 'Manual Setting' and set the concurrent connection count to 5.
For Networking Options select to "Enable TCP/IP Networking" and use the default port 3306.
For SQL mode activate the option to "Enable Strict Mode".
For Default Character Set select "ASCII"
For Windows Options select to "Install As Windows Service".
In the Security Options, define an administrative root password.

Post-Installation Configuration

There are some tuning parameters in the my.ini configuration file that you should adjust. Some of these changes improve the channelling performance, and some (such as setting the SQL mode to use ANSI quotes) are required changes.

The my.ini configuration file is located in the MySQL program data directory (for example: C:\ProgramData\MySQL\MySQL Server 5.6).

Stop the MySQL server instance before editing my.ini

Open my.ini in a text editor and inspect the following switches. Change them as necessary. These settings are a guide only and you may find they need different values to suit the characteristics of your database.

In the [mysqld] section change these values:

TEXT

default-character-set=ascii
query_cache_size=49M
tmp_table_size=51M
myisam_sort_buffer_size=102M
key_buffer_size=151M
skip-innodb (remove the # character to uncomment this setting)

In the [mysqld] section, add the following keys.
TEXT
```
lower-case-table-names=0
default-character-set=ascii 
```
The default setting on Windows for the lower-case-table-names parameter is 1, which means MySQL will convert all table names to lowercase on storage and lookup. If you do not change this setting then the SuperCHANNEL registry tables will not operate correctly. Changing this setting to 0 configures MySQL to use the case as specified when the table and database were created. Please note that after changing this setting, you must always use the exact case to access table names, otherwise index corruption may result. See the MySQL documentation for more information.

You should also specify a path to a log file, since this can be useful in debugging any problems.
TEXT
```
log=C:\Program Files\MySQL\MySQL Server 5.1\log.txt
```

It is also essential to set the MySQL SQL mode to use ANSI quotes.

If this is not done, the SQL code generated by SuperCHANNEL will fail and you will see error messages similar to the following when attempting to channel your database:
TEXT
```
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"F_Accounts"' at line 1
```
To prevent this issue, check the [mysqld] section of my.ini, and ensure that the sql-mode parameter includes ANSI_QUOTES:
TEXT
```
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES"
```

When saving changes to my.ini ensure that Word Wrap/Line Wrap is switched off in your text editor.