Class ForeignTableVTI

java.lang.Object
org.apache.derby.vti.VTITemplate
org.apache.derby.vti.ForwardingVTI
org.apache.derby.vti.ForeignTableVTI
All Implemented Interfaces:
AutoCloseable, ResultSet, Wrapper, AwareVTI, RestrictedVTI
public class ForeignTableVTI extends ForwardingVTI implements RestrictedVTI

This class contains a table function which can be used to bulk-import data from a foreign database. Because the table function is a RestrictedVTI, it can also be used to periodically and efficiently integrate data streams from a foreign database.

If you need to siphon data out of the foreign database on an ongoing basis, you can restrict the data you SELECT. Note that the local views are backed by RestrictedVTIs. That means that the actual query sent to the foreign database will only involve the columns you SELECT. In addition, the query will include the WHERE clause, provided that it is simple enough (see the javadoc for RestrictedVTI):

The following script shows how to use this table function: 

-- create a foreign database with a table in it
connect 'jdbc:derby:memory:db;create=true;user=test_dbo;password=test_dbopassword';

call syscs_util.syscs_create_user( 'test_dbo', 'test_dbopassword' );

create table employee
(
    firstName   varchar( 50 ),
    lastName    varchar( 50 ),
    employeeID  int primary key
);

insert into employee values ( 'Billy', 'Goatgruff', 1 );
insert into employee values ( 'Mary', 'Hadalittlelamb', 2 );

connect 'jdbc:derby:memory:db;shutdown=true';

-- now create the database where we will do our work
connect 'jdbc:derby:memory:db1;create=true';

-- register a table function with the shape of the foreign table
create function employeeFunction
(
    schemaName  varchar( 32672 ),
    tableName   varchar( 32672 ),
    connectionURL        varchar( 32672 )
)
returns table
(
    firstName   varchar( 50 ),
    lastName    varchar( 50 ),
    employeeID  int    
)
language java parameter style derby_jdbc_result_set no sql
external name 'org.apache.derby.vti.ForeignTableVTI.readForeignTable'
;

-- create a convenience view to factor out the function parameters
create view foreignEmployee
as select firstName, lastName, employeeID
from table
(
    employeeFunction
    (
        'TEST_DBO',
        'EMPLOYEE',
        'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword'
    )
) s;

-- now select from the view as though it were a local table
select * from foreignEmployee;
select lastName from foreignEmployee where employeeID = 2;

  • Field Details

    • _connections

      private static HashMap<String, Connection> _connections
      /////////////////////////////////////////////////////////////////////

    • _foreignSchemaName

      private String _foreignSchemaName

    • _foreignTableName

      private String _foreignTableName

    • _connectionURL

      private String _connectionURL

    • _foreignConnection

      private Connection _foreignConnection

    • _columnNames

      private String[] _columnNames

    • _restriction

      private Restriction _restriction

    • _columnNumberMap

      private int[] _columnNumberMap

    • _foreignPreparedStatement

      private PreparedStatement _foreignPreparedStatement

  • Constructor Details

    • ForeignTableVTI

      public ForeignTableVTI(String foreignSchemaName, String foreignTableName, Connection foreignConnection)

      Construct from the foreign schema and table name and a foreign connection.

    • ForeignTableVTI

      protected ForeignTableVTI(String foreignSchemaName, String foreignTableName, String connectionURL)

  • Method Details

    • readForeignTable

      public static ForeignTableVTI readForeignTable(String foreignSchemaName, String foreignTableName, String connectionURL)

      Table function to read a table in a foreign database.

      Parameters:
      foreignSchemaName - Case-sensitive name of foreign schema
      foreignTableName - Case-sensitive name of foreign table
      connectionURL - URL for connecting to foreign database via DriverManager.getConnection()

    • dropConnection

      public static void dropConnection(String connectionURL)

      Remove the cached connection to the foreign database. This method is called by ForeignDBViews.unloadTool().

    • countConnections

      public static int countConnections()

      This function is useful for verifying that the connection to the foreign database was dropped when the foreignViews tool was unloaded.

    • close

      public void close() throws SQLException
      /////////////////////////////////////////////////////////////////////
      Specified by:
      close in interface AutoCloseable
      Specified by:
      close in interface ResultSet
      Overrides:
      close in class ForwardingVTI
      Throws:
      SQLException

    • next

      public boolean next() throws SQLException
      Specified by:
      next in interface ResultSet
      Overrides:
      next in class ForwardingVTI
      Throws:
      SQLException

    • isClosed

      public boolean isClosed()
      Specified by:
      isClosed in interface ResultSet
      Overrides:
      isClosed in class ForwardingVTI

    • initScan

      public void initScan(String[] columnNames, Restriction restriction) throws SQLException
      /////////////////////////////////////////////////////////////////////
      Specified by:
      initScan in interface RestrictedVTI
      Throws:
      SQLException

    • getForeignConnection

      private static Connection getForeignConnection(String connectionURL, Connection foreignConnection) throws SQLException
      /////////////////////////////////////////////////////////////////////
      Throws:
      SQLException

    • makeQuery

      private String makeQuery()

      Build the query which will be sent to the foreign database.

    • delimitedID

      private static String delimitedID(String text)

    • prepareStatement

      private static PreparedStatement prepareStatement(Connection conn, String text) throws SQLException
      Throws:
      SQLException

    • mapColumnNumber

      protected int mapColumnNumber(int derbyNumber)

      Map a 1-based Derby column number to a 1-based column number in the foreign query.

      Overrides:
      mapColumnNumber in class ForwardingVTI