Skip to main content
Skip table of contents

MySQL JDBC Driver

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; they are not supplied with SuperCHANNEL.

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

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

    Choose the Platform Independent option from the drop-down list and select the ZIP or TAR archive.

  2. 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

  3. SuperCHANNEL can access the driver file from anywhere on your system, but you are recommended to copy it 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

  1. 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.

  2. Open config.txt in a text editor.

  3. Locate the line that defines the Java class path property (java.class.path=), and add the following to the end of the definition:

    TEXT 
    $(SNU_PROGRAM_HOME)\jar\drivers\mysql-connector-java-5.1.7-bin.jar;

    You will need to replace mysql-connector-java-5.1.7-bin.jar with the filename of the driver you installed. Each entry in the Java class path definition must be separated by a semi colon.

    $(SNU_PROGRAM_HOME) is a predefined variable that refers to the SuperCHANNEL program files directory. If you have chosen not to store the driver in the SuperCHANNEL drivers directory then you will need to specify the full path to the driver location instead.

  4. Locate the line that defines the jdbc.drivers property and add the following to the end of the definition:

    TEXT 
    :com.mysql.jdbc.Driver

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

  5. Save config.txt.

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

Step 3 - Start SuperCHANNEL

Start SuperCHANNEL, select File > Connect to Source, and use the following connection string to connect to MySQL:

TEXT 
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

If you are using reverse channelling (for example you are channelling from an SXV4 database to MySQL), then you must specify the relaxAutoCommit switch in the connection string. For example:

TEXT 
jdbc:mysql://127.0.0.1:3306/MyDatabase?relaxAutoCommit=true

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

Step 4 - Update the SNU Class Path

You may also need to add your driver to the SNU class path. This step is only required if:

  • You have saved the driver JAR file somewhere other than the SuperCHANNEL drivers directory; and
  • You intend to run SuperCHANNEL from the command line or via scripting using SNU, the SuperCHANNEL Command Line Utility.

The SNU class path is defined in snu.bat. By default this file is located in C:\ProgramData\STR\SuperCHANNEL\bin

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

Locate the following section:

JAVA 
"%JAVA_HOME%\bin\java" %JAVA_OPTS% -Xmx%SNU_JVM_HEAP% 
    -classpath "%CLASSPATH%;.;%SNU_PROGRAM_HOME%\jar\*;%SNU_PROGRAM_HOME%\jar\drivers\*;%SNU_PROGRAM_HOME%\jar\channel\*" 
    "-Djava.library.path=%SNU_PROGRAM_HOME%\bin" "-Dsxv4driver.home=%SNU_DATA_HOME%\bin" 
    "-Dsnu.data.home=%SNU_DATA_HOME%" "-Dsnu.program.home=%SNU_PROGRAM_HOME%" str.snu.snu %1 %2 %3 %4 %5 %6 %7 %8 %9

To use SNU with this source, the -classpath setting must include the location of your driver. As you can see, the class path already includes all files in the %SNU_PROGRAM_HOME%\jar\drivers\ directory, so this step is only required when you have stored the driver somewhere else.

Add the driver location to the class path. For example:

JAVA 
"%JAVA_HOME%\bin\java" %JAVA_OPTS% -Xmx%SNU_JVM_HEAP% 
    -classpath "%CLASSPATH%;.;%SNU_PROGRAM_HOME%\jar\*;%SNU_PROGRAM_HOME%\jar\drivers\*;%SNU_PROGRAM_HOME%\jar\channel\*;E:\drivers\mysql-connector-java-5.1.7-bin.jar"
    "-Djava.library.path=%SNU_PROGRAM_HOME%\bin" "-Dsxv4driver.home=%SNU_DATA_HOME%\bin" 
    "-Dsnu.data.home=%SNU_DATA_HOME%" "-Dsnu.program.home=%SNU_PROGRAM_HOME%" str.snu.snu %1 %2 %3 %4 %5 %6 %7 %8 %9

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 (Windows) or my.cnf (Linux) 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 configuration file is located in the MySQL program data directory (for example: C:\ProgramData\MySQL\MySQL Server 5.6 on Windows or /etc/mysql/ on Linux.

Stop the MySQL server instance before editing my.ini/my.cnf.

Open my.ini/my.cnf 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 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 the configuration file, ensure that Word Wrap/Line Wrap is switched off in your text editor.

Additional Configuration Adjustment when Channelling from Large Databases

By default, MySQL loads the entire result set into memory, which may create problems if you are channelling from very large databases. The workaround to this issue is to change a setting in the SuperCHANNEL config.txt configuration file.

If you encounter this issue, set the jdbc.resultset.fetchsize property to -2147483648. This will instruct SuperCHANNEL to load the records one at a time, which will be slower but will allow the build to complete.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close