Module org.apache.derby.engine
Package org.apache.derby.vti

## 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;

- - ### Nested Class Summary

    - ### Nested classes/interfaces inherited from class org.apache.derby.vti.VTITemplate

      `VTITemplate.ColumnDescriptor`

  - ### Field Summary

    - ### Fields inherited from interface java.sql.ResultSet

      `CLOSE_CURSORS_AT_COMMIT, CONCUR_READ_ONLY, CONCUR_UPDATABLE, FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN, HOLD_CURSORS_OVER_COMMIT, TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE`

  - ### Constructor Summary

    Constructors
    Constructor | Description
    --- | ---
    `ForeignTableVTI​(String foreignSchemaName, String foreignTableName, Connection foreignConnection)` | Construct from the foreign schema and table name and a foreign connection.

  - ### Method Summary

    All Methods Static Methods Instance Methods Concrete Methods
    Modifier and Type | Method | Description
    --- | --- | ---
    `void` | `close()` |
    `static int` | `countConnections()` | This function is useful for verifying that the connection to the foreign database was dropped when the foreignViews tool was unloaded.
    `static void` | `dropConnection​(String connectionURL)` | Remove the cached connection to the foreign database.
    `void` | `initScan​(String[] columnNames, Restriction restriction)` | Initialize a scan of a ResultSet.
    `boolean` | `isClosed()` |
    `boolean` | `next()` |
    `static ForeignTableVTI` | `readForeignTable​(String foreignSchemaName, String foreignTableName, String connectionURL)` | Table function to read a table in a foreign database.

    - ### Methods inherited from class org.apache.derby.vti.ForwardingVTI

      `getAsciiStream, getBigDecimal, getBigDecimal, getBinaryStream, getBlob, getBoolean, getByte, getBytes, getCharacterStream, getClob, getDate, getDate, getDouble, getFloat, getInt, getLong, getMetaData, getObject, getShort, getString, getTime, getTime, getTimestamp, getTimestamp, getWrappedResultSet, wasNull, wrapResultSet`

    - ### Methods inherited from class org.apache.derby.vti.VTITemplate

      `absolute, afterLast, beforeFirst, cancelRowUpdates, clearWarnings, deleteRow, findColumn, first, getArray, getArray, getAsciiStream, getBigDecimal, getBigDecimal, getBinaryStream, getBlob, getBoolean, getByte, getBytes, getCharacterStream, getClob, getConcurrency, getContext, getCursorName, getDate, getDate, getDouble, getFetchDirection, getFetchSize, getFloat, getHoldability, getInt, getLong, getNCharacterStream, getNCharacterStream, getNClob, getNClob, getNString, getNString, getObject, getObject, getObject, getObject, getObject, getRef, getRef, getReturnTableSignature, getRow, getRowId, getRowId, getShort, getSQLXML, getSQLXML, getStatement, getString, getTime, getTime, getTimestamp, getTimestamp, getType, getUnicodeStream, getUnicodeStream, getURL, getURL, getWarnings, insertRow, isAfterLast, isBeforeFirst, isFirst, isLast, isWrapperFor, last, moveToCurrentRow, moveToInsertRow, previous, refreshRow, relative, rowDeleted, rowInserted, rowUpdated, setContext, setFetchDirection, setFetchSize, unwrap, updateArray, updateArray, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateBigDecimal, updateBigDecimal, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBoolean, updateBoolean, updateByte, updateByte, updateBytes, updateBytes, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateClob, updateClob, updateClob, updateClob, updateDate, updateDate, updateDouble, updateDouble, updateFloat, updateFloat, updateInt, updateInt, updateLong, updateLong, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNString, updateNString, updateNull, updateNull, updateObject, updateObject, updateObject, updateObject, updateRef, updateRef, updateRow, updateRowId, updateRowId, updateShort, updateShort, updateSQLXML, updateSQLXML, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestamp`

    - ### Methods inherited from class java.lang.Object

      `equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

    - ### Methods inherited from interface java.sql.ResultSet

      `updateObject, updateObject, updateObject, updateObject`

- - ### Constructor Detail

    - #### ForeignTableVTI

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

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

      Parameters:
        `foreignSchemaName` - Name of the foreign schema
        `foreignTableName` - Name of the foreign table
        `foreignConnection` - Connection to the foreign database

  - ### Method Detail

    - #### 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()
      Returns:
        a VTI which reads the foreign table

    - #### dropConnection

          public static void dropConnection​(String connectionURL)

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

      Parameters:
        `connectionURL` - URL for connecting to foreign database via DriverManager.getConnection()

    - #### 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.

      Returns:
        the number of open connections to foreign databases

    - #### 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

      Description copied from interface: `RestrictedVTI`

      Initialize a scan of a ResultSet. This method is called once before the scan begins. It is called before any ResultSet method is called. This method performs two tasks:

      - Column names - Tells the ResultSet which columns need to be fetched.
      - Limits - Gives the ResultSet simple bounds to apply in order to limit which rows are returned. Note that the ResultSet does not have to enforce all of these bounds. Derby will redundantly enforce these limits on all rows returned by the ResultSet. That is, filtering not performed inside the ResultSet will still happen outside the ResultSet.

      The columnNames argument is an array of columns which need to be fetched. This is an array of the column names declared in the Table Function's CREATE FUNCTION statement. Column names which were double-quoted in the CREATE FUNCTION statement appear case-sensitive in this array. Column names which were not double-quoted appear upper-cased. Derby asks the Table Function to fetch all columns mentioned in the query. This includes columns mentioned in the SELECT list as well as columns mentioned in the WHERE clause. Note that a column could be mentioned in the WHERE clause in a complex expression which could not be passed to the Table Function via the restriction argument.

      The array has one slot for each column declared in the CREATE FUNCTION statement. Slot 0 corresponds to the first column declared in the CREATE FUNCTION statement and so on. If a column does not need to be fetched, then the corresponding slot is null. If a column needs to be fetched, then the corresponding slot holds the column's name.

      Note that even though the array may have gaps, it is expected that columns in the ResultSet will occur at the positions declared in the CREATE FUNCTION statement. Consider the following declaration:

      >       create function foreignEmployeeTable()
      >       returns table
      >       (
      >           id        int,
      >           birthDay  date,
      >           firstName varchar( 100 ),
      >           lastName  varchar( 100 )
      >       )
      >      ...

      and the following query:

      >      select lastName from table( foreignEmployeeTable() ) s

      In this example, the array passed to this method will have 4 slots. Slots 0, 1, and 2 will be null and slot 3 will hold the String "LASTNAME". Last names will be retrieved from the ResultSet by calls to getString( 4 )--remember that JDBC column ids are 1-based.

      The restriction argument is a simple expression which should be evaluated inside the Table Function in order to eliminate rows. The expression is a binary tree built out of ANDs, ORs, and column qualifiers. The column qualifiers are simple comparisons between constant values and columns in the Table Function. The Table Function only returns rows which satisfy the expression. The restriction is redundantly enforced by Derby on the rows returned by the ResultSet--this means that restriction gives the Table Function a hint about how to optimize its performance but the Table Function is not required to enforce the entire restriction.

      Specified by:
        `initScan` in interface `RestrictedVTI`
      Parameters:
        `columnNames` - Names of the columns to be fetched
        `restriction` - Where clause fragment used to filter rows
      Throws:
        `SQLException` - on error