cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
0 Kudos

Query Builder and multiple MySQL schemas

Status: New Submitted by on ‎04-28-2022 10:31 AM
5 Comments (5 New)

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.

5 Comments
Eric_Hill
Staff
Staff
‎09-20-2022 02:45 PM
‎09-20-2022 02:45 PM

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
Staff
Staff
‎09-20-2022 03:24 PM
‎09-20-2022 03:24 PM

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
Level III
‎11-21-2022 03:41 PM
‎11-21-2022 03:41 PM

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?

Onjai_0-1669063159806.png

 

Jeff_Perkinson
Community Manager Community Manager
Community Manager
‎11-21-2022 04:20 PM
‎11-21-2022 04:20 PM

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
Level III
‎11-22-2022 10:00 AM
‎11-22-2022 10:00 AM

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!