Virtual Tables, External Data, and Aggregates
Page 4 of 6

External Virtual Tables and External Databases

NOTE: This is an advanced topic.

Cloudscape provides another class, called COM.cloudscape.vti.ExternalQuery, that fulfills the read-only VTI requirements, which you learned about in External Virtual Tables. The ExternalQuery class uses a JDBC URL and text string representing a query to get data out of any RDBMS for which there is a JDBC driver, including Cloudscape databases. An application that uses this class is responsible for starting the appropriate JDBC driver and for using the correct protocol in the URL to specify the data source.

The ExternalQuery class is a good example of how you can write your own class to gain access to external data. You may want to copy or extend this class in your own applications. Cloudscape provides source code for the class in on our Web site at http://www.cloudscape.com/support/Downloads/.

In this section, you will run queries that access data in a Microsoft Access database.

NOTE: You can do this task only if you are running in a Windows environment and only if you have Microsoft Access installed.

Before running the program, you will need to configure the ODBC data source for the database you will be accessing, as described in the following task.

Configure the ODBC Data Source for the Access Database

Accessing ODBC databases requires that you configure a data source for your database using the ODBC Data Source Administrator. You need only configure the data source once in your environment.

Quiz: How does this requirement compare to Cloudscape's configuration requirements?

Answer: Cloudscape databases do not require any setup or configuration on a user's machine; you can deploy them without having to worry about your users' going through such a step.

  1. Choose Settings->Control Panel from the Start menu.
  2. Double-click the ODBC icon.
  3. Click Add to add a new data source.
  4. Select the driver for Microsoft Access and click Finish.
  5. For Data Source Name, enter:

    CloudscapeTutorial

  6. Under Database, click Select.
  7. Navigate to the your_tutorial_home directory (which contains all the files copied from the scripts directory).
  8. Select Music1.mdb.
  9. Click OK until all the windows are closed.

Load the ODBC-JDBC Bridge Driver and Import Data

  1. Start ij, following the instructions in Start ij.
  2. The Cloudscape JDBC driver should already be running; now you need to start the JDBC driver for ODBC data sources, called a JDBC-ODBC bridge. This driver is a part of the core JDK and is called sun.jdbc.odbc.JdbcOdbcDriver.
  3. Use the ij driver command:

    driver 'sun.jdbc.odbc.JdbcOdbcDriver';

    Quiz: Would this command work in a client/server environment?

    Answer: This command starts the bridge. Cloudscape is the software that needs the bridge. In an embedded environment, this command works, because ij and Cloudscape are sharing the same JVM, so loading the driver in the client application loads it in Cloudscape as well. In a client/server environment, however, ij and Cloudscape would be running in different JVMs. In that situation, you would have to use the following SQL-J statement (which works in embedded mode too):

    VALUES (new sun.jdbc.odbc.JdbcOdbcDriver() IS NOT NULL);

  4. Now connect to the HelloWorldDB:

    connect 'HelloWorldDB';

  5. Turn off auto-commit:

    autocommit off;

    An important use of ij is to run SQL scripts. You can use the Run command to run a script containing a batch of SQL statements.

  6. Run a script that creates four tables and imports data from the ODBC data source using ij's Run command:

    run 'GetODBCData.sql';

    ij echoes the scripts as it executes each command, so you will be able to see what is happening. Because we have already loaded the appropriate JDBC driver, the ExternalQuery is able to access data from the Access database by using the URL that you pass as a parameter.

    The script created four tables and imported data into those tables from the corresponding tables in the Access database.

  7. Test that the data is in the local tables with the following query:

    SELECT RecordingArtist, Title FROM RecordingArtists, Recordings
    WHERE RecordingArtists.artist_id = Recordings.artist_id;

  8. Disconnect:

    disconnect;

  9. Exit ij:

    exit;

Other Example VTI Classes

JBMSTours contains some example VTI classes. Looking at the classes as well as the source code for ExternalQuery will help you understand how VTIs work. These examples are:

You will also find the source code for ExternalQuery in the JBMSTours.vti.jdbc1_2 directory.

Finally, the class JBMSTours.ArchiveData uses a read-write VTI to insert data into an external database. It calls a static method in the class JBMSTours.serializabletypes.HotelStay that calls the following SQL-J statement:

INSERT INTO NEW JBMSTours.vti.jdbc1_2.ExternalCloudscapeTable( 'jdbc:cloudscape:History','HotelBookings')
SELECT * FROM HotelBookings WHERE arrival < ?

Such a statement shows the power of being able to reference a table in an external database within an SQL-J statement.