[top]
[prev]
[next]

Road Map
Glossary
Table of Contents
Index
Documentation Top
Global Index
|
External Virtual Tables
Cloudscape provides a construct for presenting external data--from a flat file, from another vendor's database, from a news feed--as a virtual table to an SQL-J SELECT, INSERT, or DELETE statement. External data presented in this way constitutes an external virtual table that can be used within the scope of an SQL-J statement in the same way as other virtual or derived tables.
An external virtual table is created by an instance of any class, which you program yourself, that fulfills the requirements of Cloudscape's virtual table interface (VTI). For example, if the class MyVTIClass fulfilled the VTI requirements, you could use it in an SQL-J statement creating an instance of it; you instantiate the class instead of specifying a real table name:
SELECT * FROM NEW MyVTIClass(arguments) AS ExternalTable
As with other virtual tables, you must specify a correlation name for the external virtual table.
Cloudscape supports two kinds of VTIs; each type has different requirements.
- read-write VTIS
Instances of read-write VTIs can be used in SELECT, INSERT, or DELETE statements. Cloudscape does not provide transaction support for INSERTs into and DELETEs from external virtual tables.
The requirements for a read-write VTI class are:
- It implements the JDBC 2.0 java.sql.PreparedStatement interface. For more information, see the Cloudscape Developer's Guide.
- read-only VTIs
Instances of read-only VTIs can be used only in SELECT statements.
The requirements for a read-only VTI class are:
Cloudscape provides a class that fulfills the requirements of a read-only VTI, COM.cloudscape.tools.FileImport. An instance of this class can present data from a flat text file to an SQL-J as an external virtual table. However, you can write any class that fulfills the above requirements to "build your own" external virtual table. Later in this chapter, you will look at another class that fulfills those requirements to provide a gateway to any vendor's database that has a JDBC driver.
Import Data from a Flat File Using the Built-In Utility
- Close the connection to the toursDB database from Cloudview.
- Open a connection to the HelloWorldDB database as described in Connect to HelloWorldDB.
- Create a new table called WorldCupStatistics with the following columns:
- RANK, SMALLINT
- TEAM, VARCHAR(20)
- P, SMALLINT
- W, SMALLINT
- D, SMALLINT
- L, SMALLINT,
- PTS, SMALLINT
- PERCENT, DECIMAL(3,1)
You can use Cloudview to create the table, following the instructions for table creation in Create the Sayings Table. Or, if you like, you can just cut and paste the following SQL-J statement into the SQL window:
CREATE TABLE WorldCupStatistics (Rank SMALLINT, Team VARCHAR(20), P SMALLINT, W SMALLINT, D SMALLINT, L SMALLINT, Pts SMALLINT, Percent DECIMAL(3,1));
- After you create the table, select its icon.
- Choose Edit->Import.
- Navigate to the your_tutorial_home directory. Select wc_stat.dat.
- Click Open. If you are working on a PC platform, skip to step 9.
- If you are working on a non-PC platform only, select the Advanced tab and select CR-LF as the record separator. (For users on PC platforms, CR-LF is assumed to be the default record separator.) Click Save to save the control file, which specifies non-default settings, for the import. Type mycontro.ctl as the name of your control file. Click Save. The file should be saved into the system directory.
- Click OK.
Data is imported into the table.
- Examine the data by clicking the Data tab.
Import Data from a Flat File Using an SQL-J Statement
- Delete all the data from the WorldCupStatistics table using the following statement in the SQL window:
DELETE FROM WorldCupStatistics
- Click the lightning bolt to execute.
- Import data from the same file by instantiating FileImport in an SQL-J statement. Use one of the following SQL-J statements, substituting the path to the your_tutorial_home directory where indicated.
Users on PC platforms should use this statement:
INSERT INTO WorldCupStatistics SELECT * from NEW FileImport( 'YOURTUTORIALHOME/wc_stat.dat') AS myExternalData;
Users on non-PC platforms should use this statement (which specifies a control file):
INSERT INTO WorldCupStatistics SELECT * from NEW COM.cloudscape.tools.FileImport( 'YOURTUTORIALHOME/wc_stat.dat', 'mycontrol.ctl') AS myExternalData;
NOTE: Do not split the string containing the file URL into two lines. Do not use a relative path to the directory.
Data is imported into the table.
- Examine the data by selecting the WorldCupStatistics table icon and clicking the Data tab.
- Exit Cloudview.
NOTE: Cloudscape also provides an export utility. In addition, Cloudview allows you to use alternate file formats for import and export. See the Cloudview on-line help for more information.
|