[ic] multi database query clarification
paul at gishnetwork.com
Wed Jan 19 00:47:28 UTC 2011
I sent a pretty odd issue to the list last month and it never got resolved
on list, but Peter discovered the non-issue off list for me. I just want
some clarification on a simple question so my mind can move on.
I have Foundation catalog running under Mysql Database A. I add a table into
said catalog, but the table is inside Mysql Database B. In my catalog, I put
the necessary mysql/dbconf files and in the file for this new table I put in
appropriate dsn, user, and pass info.
This new table, residing in Database B works perfectly fine in my catalog
along with all foundation tables running under Database A. I can [query]
this new table using only the name of the table just fine.
However, if I create a JOIN that spans this new table (in Database B) and
any table in Database A, there are problems. IC will only recognize one
table. The fix is to use the full dsn... FROM databaseB.newtable AS foo,
I'm cool with that. However - doesn't Interchange already have this
knowledge at its fingertips? As per my dbconf files, IC can see that this
table is clearly in another Database, so why when using this table inside a
[query], does Interchange not add in the full path itself?
I was surprised to find out this is not the case because I half assumed that
is what interchange was doing even on simple queries. Is that unreasonable
to think that if IC has all the proper info that it shouldn't prep the query
accordingly (at least when using ITL)? After all, that is the assumption for
a query that does not span multiple databases.
I don't care either way, it just bugged me because Interchange in fact had
all the knowledge necessary to make it happen, but it didn't do the magic it
usually does. I spent 8 hours banging my head against a wall (and two hours
trying to fix this problem) because I thought I had already supplied IC the
information it would need.
More information about the interchange-users