Hi community members!
I'm testing some functionalities about Foreign Tables and it works smoothly with PostgreSQL database, but I found out an issue with MySQL database, I followed the documentation:
- Step 1: I've created my SQL Gateway connection to my MySQL database: **WARNING** if the name of the driver file is too long IRIS won't be able to get it.
.png)
- Step 2: Create foreign server:
CREATE FOREIGN SERVER Test.MySQLDB FOREIGN DATA WRAPPER JDBC CONNECTION 'MySQL'
- Step 3: Create foreign table:
CREATE FOREIGN TABLE Test.PatientMySQL SERVER Test.MySQLDB TABLE 'patient'
- Step 4: Query Test.PatientMySQL:
SELECT * FROM Test.PatientMySQL
And this is the error:
[SQLCODE: <-230>:<Foreign table query Execute() failed>]
[%msg: <Foreign Tables - ERROR #8104: Gateway Exception: <GATEWAY> java.sql.SQLSyntaxErrorException com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112) Remote database reported 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 '"patient" T1' at line 1>]
As you can see, the problem is the double quotation used on the query for the table name that IRIS is executing, MySQL doesn't recognize it and throws the error, I've checked the driver and the MySQL versions and they match, version 9.
Is this a bug? Is it a misconfiguration?
Thank you in advance!