querying two databases in one statement

[Iwud H8u <iw...@hotmail.com>]

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?

Thanks,
Jay
-- 
View this message in context: http://www.nabble.com/querying-two-databases-in-one-statement-tp23119975p23119975.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: querying two databases in one statement

[Knut Anders Hatlen <Kn...@Sun.COM>]

Iwud H8u <iw...@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