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/
