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 <
[hidden email]>
>>> 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:
[hidden email]
> For additional commands, e-mail:
[hidden email]
>
---------------------------------------------------------------------
To unsubscribe, e-mail:
[hidden email]
For additional commands, e-mail:
[hidden email]
Locked
