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:
- 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.
- 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_keywordwithnameas 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.
Comments
@Dimitri Olchanyi Is it possible to create a connection from the DSN to execute queries directly in the third-party database without using CreateLinkedTable?
Yes, for ODBC connection you can use the SQL Gateway directly from code, I suggest to start from documentation:
@Enrico Parisi I'm trying to make this connection in Cache 2018 following this documentation
//Create new Gateway connection objectset gc=##class(%SQLGatewayConnection).%New()
If gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
//Make connection to target DSNs pDSN="DSN"s usr="user"s pwd="senha"set sc=gc.Connect(pDSN,usr,pwd,0)
If $$$ISERR(sc) quit sc
if gc.ConnectionHandle="" quit $$$ERROR($$$GeneralError,"Connection failed")sc="0 "_$lb($lb(6022,"DSN/User Connect",,,,,,,,$lb(,"QAAUTO6",$lb("e^zConnect+15^%Library.SQLGatewayConnection.1^2","d^zConnect+13^%Library.SQLGatewayConnection.1^2","e^zteste+8^s02.util.driver.JODBC.1^1","d^zDebugStub+30^%Debugger.System.1^1","d^^^0"))))/* ERRO #6022: Gateway: falhou DSN/User Connect. */
but does not return a connection
Is this on Windows or Linux?
Are you using 32 or 64 bit Cachè/Ensemble?
If Windows, did you configure the DSN named "DSN" in windows? 32 bits or 64 bits?
linux 64 bits @Enrico Parisi
I'm not familiar with ODBC on Linux, however it seems that Caché is unable to use the configured DNS named "DSN".
Is that DSN properly configured and successfully tested in Linux?
For configuring ODBC, have a look to this "Ask AI" answer.
If you still have issues, I suggest to open a new question and provide detailed information on what you have configured/done and the results/errors you had.