Is there a way to do SQL functions like joins between tables in two different database connections in Ignition? I have two different database servers (One MSSQL and One MySQL) and I'm trying to join data between tables in both databases. Is this possible? It seems like queries are limited to a single database connection and I can't select tables from multiple connections for one query. Any ideas, besides something very inefficient like reading everything into dataset variables in a script or something and then iterating through the entire dataset to select the data I want?
Not possible. (Not an Ignition limitation, but the fundamentals of JDBC connections and database connections in general.) You must make separate queries and script the join operations. Many databases support DB-to-DB "foreign data" connections, which make multiple DBs appear as one, and permit this kind of operation. Generally not between different brands, though.
Since they are located on different servers, I dont think so.
FWIW... (at least in SQL Server, using the SQL server driver, etc.) ...
If the two databases are available on the same server... then you could try using a fully qualified name for each table from your Ignition query.
SELECT *
FROM db1.dbo.tbl1 AS T1
LEFT JOIN db2.dbo.tbl2 AS T2
ON T2.id = T1.id
Your best bet might be to setup a replicated table or view in one of the databases to the table in the second database. That would be a job for your DBA though.
HTH.
-Shane
Obviously the solution is to stand up a third DB using our lord and saviour Postgres, which has FDWs available for both MSSQL and MySQL, supposedly:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers#Specific_SQL_Database_Wrappers
Disclaimer: Don't actually do this. Probably.
1 Like
Huh. Did not know my favorite DB had those. I would do that. No "probably" about it. That would almost certainly smoke any jython join.
7 Likes
CHALLENGE ACCEPTED! Or hold my beer. Not sure which one applies here.
5 Likes
Another option might be something like Denodo that does some of the backend abstraction for you. They do have a JDBC driver it appears.
Full disclosure: I have not worked with Denodo myself, and have no idea how well it works or what it costs.
How about creating a linked server on your MSSQL server that connects to the MySQL database, and then doing the join in MSSQL. I'm pretty sure I've done something similar to that.