Setting Up an ODBC/JDBC Linked Table Connection to MySQL from Iris

Due to MySQL's interpretation of SCHEMA differing from the common SQL understanding (as seen in IRIS/SQL Server/Oracle), our automated Linked Table Wizard may encounter errors when attempting to retrieve metadata information to build the Linked Table.

(This also applies to Linked Procedures and Views)

When attempting to create a Linked Table through the Wizard, you will encounter an error that looks something like this:

ERROR #5535: SQL Gateway catalog table error in 'SQLPrimaryKeys'. Error: ' SQLState: (HY000) NativeError: [0] Message: [MySQL][ODBC 8.3(a) Driver][mysqld-5.5.5-10.4.18-MariaDB]Support for schemas is disabled by NO_SCHEMA option
 

To create a Linked Table to a MySQL database which employs “no-schema” structure (the default behavior), please follow the instructions below:

1. Create an SQL Gateway Connection:

• Set up the SQL Gateway connection as usual.
• Ensure the "Do not use delimited identifiers by default" checkbox is checked.
• Click "Test Connection" to confirm the connection is successful.

2. Use the Terminal based API to Create the Linked Table:

• Utilize the following API $SYSTEM.SQL.Schema.CreateLinkedTable() The CreateLinkedTable() method  uses the following parameters:

CreateLinkedTable(dsn As %String, externalSchema As %String, externalTable As %String, primaryKeys As %String, localClass As %String = "User.LinkedClass", localTable As %String, ByRef columnMap As %String = "")

• Example: In this example, we use the MySQL system table help_keyword with name as the primary key:

USER>do $SYSTEM.SQL.Schema.CreateLinkedTable("MyDSN", "", "help_keyword", "name", "User.LinkedClass", "LocalTable")

Please ensure all parameters are correctly specified to avoid any errors during the setup process.