Query Builder and multiple MySQL schemas

[Onjai]

JMP 16.2 Query Builder does not allow for multiple OBDC connections to MySQL schemas within one query.  If I choose a DSN connection and then select the table as t1, I cannot choose a second DSN connection to another table in another schema within the same query build.  It would be very useful if Query Builder could perform this function.

[Eric_Hill]

Hey, @Onjai ,

We just spoke at JMP Discovery Online.

This may be a two-part answer:

1. Before JMP 17 (which will come out this October), JMP did not support schemas in MySQL (MySQL support for schemas is relatively recent).  However, with JMP 17, if you have a MySQL ODBC driver that supports schemas (the current MySQL ODBC version is 8.0.30 for example), JMP and Query Builder will support schemas in MySQL.
2. There is only one ODBC connection associated with a given query in Query Builder.  So, for you to perform queries with tables in different schemas, both tables would need to be accessible from a single ODBC connection.  In most other databases, such as SQL Server and PostgreSQL, you can use tables from any schema in a database from the same ODBC connection.  I would hope MySQL works the same way.

Tagging @bryan_boone here in case he can confirm what I've said here.

Thanks!

Eric

[bryan_boone]

Yes. In JMP 17, schema support for MySQL is dynamically determined for MySQL ODBC drivers 5+.

Additionally, the MySQL Server has to support schemas.

I downloaded the MySQL Workbench and it complained about the server being incompatible with the MySQL Workbench, if the server is 5.5 and the Workbench is 5.6 and above, just like the latest driver.

It seems there are 2 moving parts, the driver version as well as the server version.

[Onjai]

Hi Bryan,

We, along with Eric Hill, discussed this at JMP Summit this fall.  I have upgraded to JMP 17 and ODBC driver to 8.0 ANSI driver.  I created 2 System DSN connections using the new driver to each schema within the server.   I can load both DSN connections into Query Builder but cannot choose a database from one connection and join it to another database in the second connection.  JMP 17 Query Builder does not allow for multiple connections.  I find this very odd. The next screen (Query Builder) shows only the tables within the highlighted DSN connection.  Any thoughts?

[Jeff_Perkinson]

Hi @Onjai,

The issue here is that the query is actually being done in the database, not in JMP. Since you're using two different DSNs there is no database to submit the query to. The two databases don't know about each other and don't have access to the same schemas.

The only way that could work is to bring both tables completely into JMP and have JMP execute the query. In fact you can can do that by bring ALL of the two tables from the two different databases into JMP as tables and then use the JMP Query Builder (which runs against JMP data tables) to build the query.

I'm sure that @Eric_Hill and @bryan_boone will correct me if I'm wrong. 

[Onjai]

Hi @Jeff_Perkinson 

Thank you.

I am thinking of using a script for running multiple queries posted by @ih Run queries in background, wait for them to complete, and reference them in the remaining JSL script

JMP 17's new Work Flow feature may also be an interesting step to help me automate these queries.

Cheers!