db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: querying two databases in one statement
Date Sun, 19 Apr 2009 08:36:11 GMT
Iwud H8u <iwudh8u@hotmail.com> writes:

> Hi,
>
> I have data in two different databases D1 and D2. The data in both these
> databases is different and except for a single use case, there is no need to
> query both databases to compute the results of a single query. The use case
> in question is the following:
>
> INSERT INTO D1.TABLE_A (SELECT COL_X FROM D2.TABLE_B WHERE COL_Y = 'EEEE')
>
> I am able to run this query happily and get a result back when I am using
> MySQL, however when I migrate the tables to Derby, Derby complains that the
> schema D2 does not exist. I have tried opening connections to both the
> databases D1 and D2 separately but I still get the same error. 
>
> Is this query possible in Derby without actually migrating table TABLE_B
> from database D2 to D1?

Hi Jay,

MySQL and Derby mean different things by "database". What MySQL calls a
database, is called a schema in Derby. So I think the best way would be
to import each of the MySQL databases into different schemas in the same
Derby database.

That said, it is possible to use table functions (see [1],[2]) to
perform queries involving two Derby databases. I think the code would
look something like this (untested):

public class ForeignTable {
  public static ResultSet execute() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:derby:d2");
    return conn.createStatement().executeQuery(
        "SELECT COL_X FROM TABLE_B WHERE COL_Y = 'EEEE'");
  }
}

CREATE FUNCTION FOREIGN_TABLE() RETURNS TABLE (COL_X INT)
  LANGUAGE JAVA PARAMETER STYLE DERBY_JDBC_RESULT_SET
  NO SQL EXTERNAL NAME 'ForeignTable.execute'

INSERT INTO TABLE_A SELECT COL_X FROM TABLE(FOREIGN_TABLE()) T


[1] http://db.apache.org/derby/docs/10.4/devguide/cdevspecialtabfuncs.html
[2] http://db.apache.org/derby/docs/10.4/ref/rrefcreatefunctionstatement.html

-- 
Knut Anders

Mime
View raw message