db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Can I write a query that joins tables in two databases?
Date Thu, 15 May 2008 16:11:04 GMT
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,

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/

View raw message