Re: [OT] Join tables from different databases with tomcat datasource
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 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
Re: [OT] Join tables from different databases with tomcat datasource
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 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
Re: Join tables from different databases with tomcat datasource
> On Jan 11, 2021, at 1:19 PM, Jerry Malcolm 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. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Join tables from different databases with tomcat datasource
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? - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
