SQL-J Language Reference
Page 31 of 121

ExternalVirtualTable

Cloudscape's Virtual Table Interface (VTI) allows objects that meet Cloudscape VTI requirements to appear as ExternalVirtualTables within SQL-J statements. Cloudscape supports two different kinds of ExternalVirtualTables:

  • Read-write VTI classes can be instantiated within an INSERT statement, a DELETE statement, or as a TableExpression in the the FROM clause of a SELECT statement. (ExternalVirtualTables are not supported in UPDATE statements).

    NOTE: There is no transaction support for inserting into or deleting from an ExternalVirtualTable; rolling back a transaction that executed a statement that inserted into or deleted from an ExternalVirtualTable does not roll back the underlying insert or delete.

    Read-write VTI classes implement java.sql.PreparedStatement. See Programming VTIs in the Cloudscape Developer's Guide.

  • Read-only VTI classes can be instantiated only within a SELECT statement; they are a legal form of a TableExpression within a SelectExpression. As far as the rest of the query is concerned, there this no difference between an ExternalVirtualTable and a table, view, or other virtual table such as a subquery or VALUES clause. The SELECT statement can reference any column from an ExternalVirtualTable in any of its clauses. Like other virtual tables, they require a correlation name. See TableExpression.

    Read-only VTI classes implement java.sql.ResultSet. See Programming VTIs in the Cloudscape Developer's Guide.

The use of a VTI in SQL-J, similar to the way Java objects can be referenced in other constructs, is dynamic, not declarative; there is no CREATE EXTERNAL VIRTUAL TABLE statement. Instead, you call the constructor of the class that implements the VTI requirements, to instantiate an ExternalVirtualTable on the fly within the statement.

The VTI is a powerful tool that can be used to make any external data source, such as a flat text file, a live data feed, or a gateway to another RDBMS, appear as a table to Cloudscape.

NEW: Read-write VTIs are new in Version 3.6.

Syntax

NEW { JavaClassName | ClassAlias }(parameterList)

In a SelectExpression, as with other derived tables, you must give an ExternalVirtualTable a CorrelationName as described in TableExpression. Names of derived columns are optional.

NOTE: Column names returned by a VTI are treated as case-sensitive, delimited identifiers. Provide names of derived columns in the correlation name to treat them as case-insensitive names, or program your VTI to return all uppercase column names.

ExternalVirtualTable Examples

-- FileImport is an alias for the class
-- COM.cloudscape.tools.FileImport is a class
-- provided by Cloudscape that fulfills the read-only
-- VTI requirements.
-- You use the NEW keyword to use it to create an
-- ExternalVirtualTable from data in a flat file
.
INSERT INTO WorldCupStatistics
SELECT * FROM NEW FileImport(
    'c:/cloudscape/demo/programs/tours/scripts/wc_stat.dat') AS myExternalData

-- jarvti is a class alias for a sample VTI class
-- (JBMSTours.vti.jdbc1_2.jarvti)
-- count the number of classes in a jar file.
-- It is a read-only VTI
SELECT COUNT(*)
FROM
NEW jarvti('c:/cloudscape/lib/cloudscape.jar') AS EQ

-- JBMSTours.vti.jdbc1_2.ExternalCloudscapeTable
-- implements the read-write virtual table interface
DELETE FROM NEW JBMSTours.vti.jdbc1_2.ExternalCloudscapeTable(
    'jdbc:cloudscape:history', 'hotelbookings')
WHERE hotel_id = 1000 and group_id = 500

Dependency System

A statement is not dependent on a VTI. The statement that references the VTI is not dependent on any of the objects referenced within the VTI. (As in a java method call, any statements nested within the VTI are dependent on the objects that they reference.)

Inserting from Table in Same Database

Be careful when using an ExternalVirtualTable to reference a table in the current database. If the ExternalVirtualTable is accessing a table in the current database, and you are using that ExternalVirtualTable as the source of an INSERT, the query could result in an infinite loop. Cloudscape does not detect that situation. For example, the following statement would result in an infinite loop:

-- INSERT INTO FlightAvailability
-- SELECT * FROM NEW ExternalQuery(
    -- 'jdbc:cloudscape:toursDB',
    -- 'SELECT * FROM FlightAvailability') AS eq

If the statement involves two ExternalVirtualTables, each of which references the other, the statement cannot be evaluated and is not permitted, as in the following example:

SELECT *
FROM NEW v1(v2.c) AS v1, NEW v2(v1.c) AS v2

NOTE: If a VTI cannot be instantiated more than once and it takes a join column as a parameter, no legal join order is possible, and an exception is thrown.