MySQL JDBC connection, Linked Table issue
Hello, community.
I have a problem with running a SQL query on a linked MySQL table.
The connection works fine, but the following query throws an error:
SELECT TOP 10 * FROM linkedinternal_test.persons
[SQLCODE: <-400>:<Fatal error occurred>]
[%msg: <Remote JDBC error: 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 '10 T1.PersonID AS C1,T1.LastName AS C2,T1.FirstName AS C3,T1.Address AS C4,T1.Ci' at line 1. >]
Linked table class:
/// Generated by the Link Table wizard on 2025-01-08 16:09:03. Note that you can access the data in this class only when the external database is accessible.Class LinkedInternal.test.Persons Extends%Library.Persistent [ Owner = {_SYSTEM}, Not ProcedureBlock, SqlRowIdPrivate, SqlTableName = Persons, StorageStrategy = GSQLStorage ]
{
/// Specifies details for the SQL Gateway Connection that this class usesParameter CONNECTION = "test,NOCREATE";/// Specifies the external database that this class usesParameter EXTDBNAME = "MySQL";/// Determines if INSERT statements for this external table attempt to retrieve auto-generated keys. Set this to 0 if this external table does not support auto generated keys.Parameter EXTERNALGENERATEDKEYS = 1;/// Specifies the external table to which this class refersParameter EXTERNALTABLENAME = "Persons";Property Address As%String(EXTERNALSQLNAME = "Address", EXTERNALSQLTYPE = 12, MAXLEN = 255) [ ReadOnly, SqlColumnNumber = 5, SqlFieldName = Address ];Property City As%String(EXTERNALSQLNAME = "City", EXTERNALSQLTYPE = 12, MAXLEN = 255) [ ReadOnly, SqlColumnNumber = 6, SqlFieldName = City ];Property FirstName As%String(EXTERNALSQLNAME = "FirstName", EXTERNALSQLTYPE = 12, MAXLEN = 255) [ ReadOnly, SqlColumnNumber = 4, SqlFieldName = FirstName ];Property LastName As%String(EXTERNALSQLNAME = "LastName", EXTERNALSQLTYPE = 12, MAXLEN = 255) [ ReadOnly, SqlColumnNumber = 3, SqlFieldName = LastName ];Property PersonID As%Integer(EXTERNALSQLNAME = "PersonID", EXTERNALSQLTYPE = 4) [ ReadOnly, Required, SqlColumnNumber = 2, SqlFieldName = PersonID ];
Index MainIndex On PersonID [ IdKey, PrimaryKey ];
Storage GSQLStorage
{
<StreamLocation>^LinkedInternal.test.PersonsS</StreamLocation>
<Type>%Storage.SQL</Type>
}
}
Is there work around this issue?
Comments
Maybe the error is on the declaration of the table, use the parameter NODELIMITEDIDS for the declaration:
CREATE FOREIGN SERVERserver-name FOREIGN DATA WRAPPER jdbc CONNECTION'mysqlconnection' NODELIMITEDIDSMySQL doesn't like TOP. Use LIMIT instead
You have an error in your SQL syntax
SELECT column_name(s)
FROM table_name
WHERE condition
LIMITnumber;found here: https://www.w3schools.com/sql/sql_top.asp
It seems to me that this might be a case of an imperfect implementation of the external tables.
Hi Robert. The problem is that I am executing the query on the linked table, so I have to use IRIS SQL syntax and it does not support "LIMIT"
That's an interesting observation! I would have expected the IRIS SQL syntax to be used here as well, even if it's referring to an external table from a MySQL database.
In IRIS, you need to use TOP x: IRIS Documentation - TOP Clause. As for MySQL, you're absolutely right—the SQL syntax there specifies LIMIT.
It seems to me that the IRIS SQL Engine might not determine the columns precisely before passing them to the external SQL Engine (in this case, MySQL). Ideally, an IRIS SQL translation into the language of the external SQL engine would occur here. This approach ensures true data virtualization. Sounds quite sophisticated! 😊
I faced similar issues with Postgres linked tables because of different SQL syntax and also poorly implemented translation from IRIS dialect to your linked server dialect. The workaround is to add an instance of EnsLib.SQL.Operation.GenericOperation to your production and to execute SQL queries via ODBC/JDBC bypassing IRIS, something like:
Set operation = ##class(EnsLib.SQL.Operation.GenericOperation).%New("NameOfYourProductionComponent")
#Dim rs as EnsLib.SQL.GatewayResultSet
Set status = operation.Adapter.ExecuteQuery(.rs, "select 1", .args)
While (rs.Next()) {
...
}
Do rs.Close()