SQL-J Language Reference
Page 8 of 121

CALL statement

The CALL statement executes a public method call associated with a Java class (a static method) or an object (a non-static method). The CALL statement is the only way to execute methods with void return types.

The ? = syntax allows you to retrieve a return value from the method call. It is provided for use within a JDBC CallableStatement; however, it also works from a Statement or PreparedStatement. In Cloudscape, it is more typical to use the VALUES expression to retrieve a return value from a method call. The following two statements are similar:

-- this statement returns a single-column, single-row
-- ResultSet
VALUES city.findCity(getCurrentConnection(), 35)

-- this statement does not return a result set
-- but it does return a value through an OUT parameter
? = CALL city.findCity(getCurrentConnection(), 35)

The method can return a void type or any valid Java type. If its return type is not void, the returned value is simply ignored unless the ? = syntax is used. If the ? = syntax is used, the method must return a value.

To iterate a method call over a number of rows, use a SelectExpression. The CALL statements operates on only a single parameter set at a time.

Syntax

[ ? = ] CALL
{
     Method Invocation |
    Work Unit Invocation
}

CALL Statement Usage

You can use a CALL statement in all of the following cases:

  • to execute a static method that does not return a value, the most typical use

    CALL HotelStay.archiveRecords(
        DATE'1998-02-01')

  • to execute a static method that does return a value

    ? = CALL City.findCity(getCurrentConnection(), CAST (? AS INT))

    (? = CALL could be replaced by VALUES in this instance.)

  • to execute a method alias (an alias for a static method of a class)

    CALL cleanOutFlightAvailability(getCurrentConnection())

    If the method returns a value that you want to retrieve, use a VALUES expression or the ? = syntax.

  • to execute a method associated with an object not serialized in the database (the object is instantiated within the statement)

    CALL NEW JBMSTours.inserters.InsertCountries().confirmInsert(
        getCurrentConnection())

  • to execute a non-static method of an object serialized in the database

    CALL (SELECT customized_tour
        FROM CustomizedTours WHERE group_id = 1).printTourInfo(
            getCurrentConnection())

    You must use a scalar subquery to limit the number of objects on which the method is called to one.

  • to execute a static method of an object serialized in the database

    CALL (SELECT customized_tour.getStay1()
        FROM CustomizedTours
        WHERE group_id = 1).archiveRecords(DATE'1998-02-01')

    You must use a scalar subquery to limit the number of objects on which the method is called to one. You do not use a WHERE clause.

    Note that it makes more sense to execute the static method off the class, not off the object. The above example is a lot simpler when executed off the class:

    CALL (CLASS JBMSTours.serializabletypes.HotelStay).archiveRecords(
        DATE'1998-02-01')

  • in a Cloudsync system, to execute a work unit, a type of method alias registered with the system in which only the method call and target parameter values are replicated, not any underlying statements

CALL myWorkUnit(getCurrentConnection(), ?, ?)

The parameters are applied at the target when CALL is applied at the target, and they are saved and sent to the source for use during the next refresh. For more information about work unit invocation, see the Cloudscape Synchronization Guide.

For information on method resolution, see Method Resolution and Type Correspondence.

The CALL Statement and JDBC

Although the CALL statement works with Statements, PreparedStatements, and CallableStatements, you must use the latter if you want to use JDBC OUT or INOUT parameters. (See Chapter 6, "JDBC Reference".)

Dependency System

The CALL statement depends on all the tables named in the Java expression, if one is used, and on all aliases used in the query. Dropping an alias invalidates a prepared CALL statement if the statement uses the alias. A DROP WORK UNIT statement invalidates a statement that uses the work unit.

There is no tracking of dependencies on any Java classes referenced in the CALL statement.

Modification of Java Object Parameters

Parameters to a CALL statement are Java objects. In embedded mode, if a Java object is passed in and modified within the method, changes to it are visible to the invoker of the method because the application and Cloudscape share the same JVM. The same holds true for any method, not just those executed with a CALL statement.

This is not true when running in client/server mode because a client and a server do not share the same JVM. (To see changed values in client/server mode, use INOUT parameters as described in Chapter 6, "JDBC Reference".)

Consider the following example method, which alters the value of a City object parameter:

public static void alterMe(City acity) throws SQLException {
    acity.name = "Altered Name";
}

An application creates a City object, then executes that method within a CALL statement, passing in that City object as a parameter:

s = conn.createStatement();
City mycity = new City(1, "Amsterdam", "Dutch", "NL", "AMS");
/* the toString() method shows the value of the name field, which 
should be "Amsterdam" at this point*/
System.out.println(mycity.toString());
PreparedStatement ps = conn.prepareStatement(
    "CALL (CLASS JBMSTours.City).alterMe(?)");
ps.setObject(1, mycity);
ps.execute();
System.out.println(mycity.toString());

Here is the output in embedded mode:

Loaded the embedded JDBC driver
Amsterdam, Netherlands
Altered Name, Netherlands

Here is the output in client/server mode:

Loaded the client JDBC driver
Amsterdam, Netherlands
Amsterdam, Netherlands

In embedded mode, the value of the "name" field of the mycity variable was changed in the application's memory. In client/server mode, the value of the "name" field was not changed.