The original question was about how to configure a tomcat datasource to
connect to two separate databases on a single mysql query. My confusion
came from the fact that the urls I'm using in the resource definition in
tomcat server.xml include the database name. My original question
assumed that I somehow needed to have a different datasource that
referenced the other database and somehow use both datasources on one
call. I was not aware that I could override the database defined in the
TC datasource with a different database. So my question was indeed a
Tomcat question and was not a sql question. The responses evolved into
sql after the original question was answered.
Thanks for the info. I did get it to work.
Jerry
On 1/12/2021 12:42 PM, Christopher Schultz wrote:
Chris,
Marking as OP since this is really a question about databases.
On 1/11/21 14:15, Chris Cheshire wrote:
On Jan 11, 2021, at 1:19 PM, Jerry Malcolm <techst...@malcolms.com>
wrote:
I have a query that needs to access tables in two different
databases on the same mysql instance. I see how that can be done
with the JOIN syntax in mysql. But datasource pools reference a
single database, correct? I really need to 'join' two datasources
for one sql call. I found info on doing this by embedding TC in the
Spring framework. But I was hoping to not have to do a major
restructuring. Is there a way to get a connection that attaches to
two separate databases?
If the databases are on the same MySQL instance, just adjust the
grants for the user to be able to access tables in both datasources.
+1
You will have to fully-qualify the table references in your queries
like this:
SELECT lt.foo, rt.bar
FROM local_table lt
JOIN other_db.remote_table rt ON lt.fk=rt.pk
;
Hope that helps,
-chris
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
