Can I write a query that joins tables in two databases?

[Leslie Software <le...@yahoo.com>]

I found some indications that Java XA will allow me to do what I want but I cannot seem to find any examples that retrieve data from multiple databases into one result set.  Am I barking up the right tree?

Here is what I am trying to do:  I have an application that ships with a .jar'ed database of cards from a collectible card game (Magic the Gathering).  I want customers to be able to create inventory databases to record the cards they own and then I want to perform queries that combine data from the two databases.  That way they can search for cards and find out which ones they already own vs. which they do not.  Or they can determine how many cards they need to buy / trade for to create a specific deck.  I am running derby embedded in my application.

Any help or advice would be appreciated.

Thanks,

Ian


      __________________________________________________________________
Looking for the perfect gift? Give the gift of Flickr! 

http://www.flickr.com/gift/

Re: Can I write a query that joins tables in two databases?

[Rick Hillegas <Ri...@Sun.COM>]

Hi Ian,

You can use Derby as an XA data source. There is a small section on this 
topic ("XA and the Network Server") in the Server and Administration 
Guide. There is also some related material in the section titled "J2EE 
Compliance: Java Transaction API and javax.sql Interfaces" in the 
Reference Manual.

XA, by itself, will not solve the following problem, which may be the 
crux of your question: The Derby optimizer does not optimize joins 
across multiple databases. Furthermore, the Derby SQL syntax only 
supports a two-tiered namespace (schema then table) and will not let you 
address a table outside the database that you are connected to. 
Effectively, this means that you will probably have to perform the 
distributed join in your application. That means multiple ResultSets in 
your application and not the single ResultSet that you want to loop through.

You may want to investigate whether Table Functions can help you here. 
Table Functions let you treat external data like local tables in the 
database you are connected to. It is easy to write a Table Function 
which pulls in data from a second database and then join that data to 
tables in the database you are currently connected to. With Table 
Functions you can write a poor-man's distributed join. This will give 
you the application experience you want: a single ResultSet. Optimizer 
support for Table Functions, however, is limited--you will need to 
experiment a bit to see whether you can get adequate performance out of 
this solution. For more information, please see the section titled 
"Programming Derby-style table functions" in the Developer's Guide.

Hope this helps,
-Rick


Leslie Software wrote:
> I found some indications that Java XA will allow me to do what I want but I cannot seem to find any examples that retrieve data from multiple databases into one result set.  Am I barking up the right tree?
>
> Here is what I am trying to do:  I have an application that ships with a .jar'ed database of cards from a collectible card game (Magic the Gathering).  I want customers to be able to create inventory databases to record the cards they own and then I want to perform queries that combine data from the two databases.  That way they can search for cards and find out which ones they already own vs. which they do not.  Or they can determine how many cards they need to buy / trade for to create a specific deck.  I am running derby embedded in my application.
>
> Any help or advice would be appreciated.
>
> Thanks,
>
> Ian
>
>
>       __________________________________________________________________
> Looking for the perfect gift? Give the gift of Flickr! 
>
> http://www.flickr.com/gift/
>