Working with SQL-J's Java Extensions
Page 4 of 7

Database-Side JDBC Methods

In the last section, you worked with a getDistanceFrom method that took a City object as a parameter. You constructed a subquery to "return" a City object to the method call. The method itself did not query the database.

The JBMSTours application programmer overloaded that method; the City class contains the method with a slightly different signature: getDistanceFrom(Connection, int), which does query the database. The second parameter to this method is the city's id, which is an int. This method takes the city code, uses the connection (which is the first parameter) to query the database for the corresponding City object, then calculates the distance between the Cities and returns that value.

Since the method takes an int instead of a City object, we don't need to do anything fancy to construct an object to pass to it. We can use a literal integer (in this case, 35, Paris's city code). However, we will need to do something special to pass it a Connection object.

getDistanceFrom(Connection, int) accesses the toursDB database; a method that accesses the database when called within a SQL-J statement is called a database-side JDBC method, because it uses JDBC calls to execute SQL-J statements. A database-side JDBC method needs a Connection object. In most cases, the method uses the same Connection as the SQL-J statement that called it; such a connection is called a nested Connection. In Cloudscape, this can be handled by having the Connection passed in as a parameter. That is why the getDistanceFrom(Connection, int) needs a Connection object as a parameter.

Other methods in the City class access the database. For example, look at the following methods in the javadoc or in the source file:

These methods can be executed in a Java application as application-side JDBC methods or in the DBMS as database-side JDBC methods. In the section after this one, you will run a program that executes these methods. In the section after that, you will execute them from within ij.

Execute Methods in a Program as Application-Side Methods

CityTest2 is another program that should be in your working directory. It is identical to CityTest, with a couple of exceptions:

  • It establishes a connection to the database and assigns it to a variable called conn.
  • There is no city2 object (Paris) initialized in memory. Instead, the application uses the city_id for Paris, 35, or the string for Paris's airport, "CDG".

    The method calls do not use the Paris City object, but instead use the JDBC methods that take the Connection and city_id code or the airport code and then access the database.

    Quiz: Why do those methods take Connections as parameters?

    Answer: Because they need a Connection to access the database.

  • Look at CityTest2.java. Note the missing Paris City object and the new method signatures.
  • Open a command window and change directories to the your_tutorial_home directory.
  • Run your setclasspath script.
  • Compile and run CityTest2:

    javac CityTest2.java

    java -Dcloudscape.system.home= your_tutorial_system CityTest2

    Note that you need to set the cloudscape.system.home variable, because this program interacts with Cloudscape.

    The output should be similar to that for CityTest.

Execute Methods in Statements as Database-Side Methods

Let's take one of the methods executed in CityTest2:

getDistanceFrom(conn, 35)

and execute it in an SQL-J statement within ij.

In an SQL-J statement, you have no way of referring to an application variable for the connection. Instead, Cloudscape supplies a built-in function that gets the current connection and passes it as a nested connection to the calling method. You call that function like this:

GETCURRENTCONNECTION()

Case does not matter.

In this section you will execute that method for all cities in the City database whose city_id is less than 10.

  1. Start ij and get a connection to toursDB as outlined in Start ij and Connect to toursDB.

    You should be connected to toursDB.

    For this lesson, you will turn off auto-commit.

    Auto-commit mode means that when a statement is completed, the method commit is called on that statement automatically.

    Committing a statement means that any changes it made in the database are final. If you never commit a statement, any changes it made in the database are only temporary. The concept of committing a statement or set of statements allows you to group statements together into atomic transactions. You can commit a transaction to make all changes permanent in the database. Or you can roll back a transaction to undo all the changes made by the transaction. Applications typically roll back a transaction if something goes wrong during one of the statements. Cloudscape also rolls back a transaction at runtime if a very serious database error occurs. You will learn more about committing and rolling back a transaction in Lesson 9, "Working with Connections and Transactions".

    Auto-commit in effect makes every SQL-J statement a transaction, including basic queries. You will not want to automatically commit all the statements you execute in this chapter.

  2. Turn off auto-commit using an ij command:

    autocommit off;

  3. Execute the following SQL-J statement, which finds the distance from Paris for those cities for which the city_id is less than 10:

    SELECT city.getName(), city.getDistanceFrom(GETCURRENTCONNECTION(), 35) AS DISTANCE_FROM_PARIS FROM CITIES
    WHERE city_id < 10;

  4. Find those European cities that are within 300 miles of Paris:

    SELECT city.getName()
    FROM Cities JOIN Countries
    ON Cities.country_ISO_code = Countries.country_ISO_code
    WHERE region = 'Europe' AND city.getDistanceFrom(GETCURRENTCONNECTION(), 35) < 300;

  5. Here's another way to get the same results:

    SELECT city.getName() FROM Cities JOIN Countries
    ON Cities.country_ISO_code = Countries.country_ISO_code
    WHERE region = 'Europe'
    AND city.getDistanceFrom(
        (SELECT CITY FROM CITIES WHERE city_id = 35)) < 300;

  6. This one shows the current time difference between Paris (city_id = 35) and New York (airport JFK):

    SELECT city.getName(),    city.getTimeDifference(getCurrentConnection(),
       CURRENT_DATE, 'JFK') AS CurrentTimeDifFromNewYork
       FROM Cities WHERE city_id = 35;

  7. Commit to release resources:

    commit;

  8. Leave your ij connection open for the next section.