'Join' data from different database connections


Currently I am working on a project with two different databases.
The first database is from a ERP system.
The second database is for Ignition.
In the ERP database, a articlecode, articles names and descriptions are stored.
In the Ignition database production information is stored which is refered trough the articlecode.

Now I would like to show the production information and the article information together in 1 table on the screen. However it is only possible to choose one datasource with a sql binding.

Is it possible to ‘Join’ the two tables and show them together in one table?
And how would this be the fastest solution?

Best Regards,
Bart Mans

There’s no way to join across datasources in Ignition – there’s no such capability in the underlying JDBC technology that Ignition uses. You’d need to query from one then the other, programmatically joining the two and manually constructing a result dataset. It’s something I’ve toyed with as an improvement to the view() function of Simulation Aids, but doing it in a suitably general way is daunting.

seems like something you could do in a stored procedure in sql. then call the stored procedure in igntiion.

[quote=“diat150”]seems like something you could do in a stored procedure in sql. then call the stored procedure in igntiion.[/quote]Not under the constraints Bart describes. Now, if you used the features of one of the databases to make a foreign data connection to the other, indirectly importing the data, you could do the join entirely in that database. But then a regular query would work, without needing any stored procedure. Support for foreign data connections varies widely amongst the various database platforms, especially when connecting to a different platform. Bart didn’t provide that detail.

I have tried to put the query in a stored procedure. This works fine. Thanks for the help!