Filter Table Based on Data in different DB source

[nate_macs]

Hello,

I have a perspective table bound to a named query referencing one DB connection (DB1). Users write additional data against keys from named query and the written data is stored in a different DB connection (DB2) - ie the newly written data is not accessible via the named query. Users would like to filter the table by data they are writing to DB2.

Imagine DB1 contains:
key1, property1, property,2

DB2 contains:
key1, property 3

How can I filter or otherwise reference property 3 while using a table bound to a query referencing DB1?

[dkhayes117]

How are you displaying this information to the user? Why not join DB1 and DB2 data sources in your query and display both in one table, or are you already doing that?

[lrose]

@nate_macs

How can I filter or otherwise reference property 3 while using a table bound to a query referencing DB1?

You will have to do the join and filter manually in a script. Or if you are able to, use @pturmel's

Automation Professionals' Integration Toolkit Module

Automation Professionals is pleased to announce the immediately availability of a major upgrade to its free Simulation Aids Integration Toolkit module. The new feature justifying the "major upgrade" language is the implementation of eleven thirty-one new expression functions which use iteration to accomplish complex transforms of datasets and lists, to or from other datasets and lists, applying filtering, ordering, and computed new columns along the way. While there are certain operations that…

to do it in an expression.

[nate_macs]

I'm displaying via table component with data property bound to a named query. The named query is using DB1 as a database connection and referencing a table within DB1. The users are writing to a different table in a different database connection in DB2.

Can I still do a SQL join in the named query if I try to join on a table in DB2 when the database connection referenced is DB1?

My other idea was scripting a transform...

[dkhayes117]

@nate_macs

Can I still do a SQL join in the named query if I try to join on a table in DB2 when the database connection referenced is DB1?

If DB1 and DB2 are the same db flavor, that is both MySQL or both postgres etc, then yes you can join them in a single query. If one is MySQL and one postgres, then no.

In that case, I highly recommend the Integration Toolkit that @lrose linked to. This toolkit allows you to gain a lot of performance by using an expression over a script transform.

[lrose]

@dkhayes117

If DB1 and DB2 are the same db flavor, that is both MySQL or both postgres etc, then yes you can join them in a single query

I don't think that is true, they are different connections implying different databases. Doing a cross connection join, at the very least isn't supported from a Named Query. You could potentially do it in a stored procedure, assuming that both Databases are located in the same Database server, but again. Not from a named query, named queries only support a single connection.

[dkhayes117]

@lrose

I don't think that is true

You absolutely can do it in a named query, I do it all the time with MariaDB. One database schema, is the right terminology?, with mulitple databases using a database connection for each database (in my case).

[lrose]

@dkhayes117

You absolutely can do it in a named query

How do you get a single named query to reference a second ignition connection? Without actually seeing the query, I can't say for sure, but this feels like it is venturing into the neighborhood of a SQL script.

[dkhayes117]

db connection called cutting and db connection called staging. Set default connection to cutting

select *
from cut_history c
left join staging.stage_history s
    on s.serial = c.serial
where
...

EDIT
You are probably right that the DBs would have to be on the same server/schema as it's probably not acutally using the other db connection in the query. It works because the databases live in the same place and is using the default connection only.