Connecting to both local storage and production DB

Toad Data Point
1

I am using TDP with an ODBC connection to an Informix database (although I don't think that particularly matters). In SQL editor, I ran a query and stored the results in local storage. Now I need to join those results with a second query from my production database ("where LocalStorage.table.value1 = ProdDB.table.value1)". In SQL Editor I have selected my prod database, and I get an error when executing the statement, presumably because ODBC doesn't recognize "LocalStorage.table". When I connected to LocalStorage, I get a similar error "ProdDb.table1 doesn't exist". How do I run my query, recognizing these two different database connections?

Thanks.

2

You'll need to connect within TDP to both LocalStorage and to Informix.
The Object Explorer should display the valid name of the LocalStorage table, but it probably wouldn't be prefixed with "LocalStorage", unless that's what you labeled your connection to it.

It might be good to use the graphical Query Builder to have TDP create your query, or close to it, so you can see the proper naming convention. Connect to both your LocalStorage and your Informix, drag a table into the palette from each connection, drag column to column to join the tables, checkbox a few columns, and look at the query TDP generates. I did that with a quick query from my Local Storage and my SQL Server, and this is what I see TDP creating query-wise for me. Note that the connection labels are used as prefixes to correctly qualify each table...hope this helps:

image
image1104×676 213 KB

3

Thank you Gary. Here's what happens: I have both db connections visible in in object explorer. Using Query Builder, I drag in my prod db table and choose columns, run the query, get results. Then I drag in a local storage table, join with my prod table, choose columns; execute. I get an error that "Can't find database object ProdBiz.transaction: This can occur for two reasons:

  1. The object ProdBiz.transaction does not exist.
  2. The object does exist but Toad Data Point has not mapped the metadata. To refresh the metadata for this database, use the refresh action in Object Explorer."

This confuses me because I had just gotten rows back from this table before joining with local storage. I right-clicked on the prod DB connection and chose 'refresh item' but I continue to get the same error message when I execute the statement.

I get the same error if I first connect to local storage, and successfully retrieve rows, but when I add in the ProdBiz table, then I get the error. I then deleted the local storage table, and just ran a query with the remaining ProdBiz table, and now it gets data.