Join tables from different databases with tomcat datasource

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Join tables from different databases with tomcat datasource

Jerry Malcolm
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Join tables from different databases with tomcat datasource

Chris Cheshire-2


> 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.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [OT] Join tables from different databases with tomcat datasource

Christopher Schultz-2
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]

Reply | Threaded
Open this post in threaded view
|

Re: [OT] Join tables from different databases with tomcat datasource

Jerry Malcolm
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]