Working with Connections and Transactions
Page 3 of 3

Connecting to Multiple Databases

An application can connect to more than one database in a Cloudscape system. JBMSTours.ArchiveData is an example of such an application. It executes two methods that select data out of one database, inserts it into another (History), then deletes it from the first database (toursDB). These methods are the archiveRecords and the archiveRecordsVTI methods in the JBMSTours.serializabletypes.HotelStay class. The methods accomplish the same tasks in different ways.

ArchiveData executes the methods as database-side JDBC methods (inside an SQL-J statement), but it could just as easily execute them on the application side. It executes them as database-side methods for illustration purposes only.

The archiveRecords method creates two new connections: one to each database. That means that the work accomplished by the method is not encompassed by a single transaction and is not protected by transaction control. It must also handle the results programmatically; it steps through the ResultSet from the first database and inserts into the second database. The archiveRecordsVTI method, on the other hand, makes use of Cloudscape's VTI feature (which you learned about in External Virtual Tables) to transfer the data, and so does not need to handle the results programmatically. In addition, for some of the steps, it can re-use the current connection and so can allow some transaction control.

Run JBMSTours.ArchiveData

  1. Open a command window and change directories to your_tutorial_home.
  2. Run your setclasspath script.
  3. Open and examine JBMSTours.ArchiveData.java.
  4. Run the program, specifying the system directory with the -D parameter:

    java -Dcloudscape.system.home= your_tutorial_system
        JBMSTours.ArchiveData