New post
Question
· Jan 8

MySQL JDBC connection, Linked Table issue

#Databases #ObjectScript #SQL #InterSystems IRIS for Health

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 uses
Parameter CONNECTION = "test,NOCREATE";

/// Specifies the external database that this class uses
Parameter 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 refers
Parameter 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>
}

}

ObjectScript
ObjectScript


Is there work around this issue?

Product version: IRIS 2023.1
$ZV: IRIS for Windows (x86-64) 2023.1 (Build 229U) Fri Apr 14 2023 17:17:41 EDT
Discussion (6)4
Log in or sign up to continue
Andreas Schneider · Jan 8

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! 😊

0 0
Dmitrii Baranov · Jan 10

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()
0 0