SQL-J Language Reference
Page 30 of 121

EXECUTE STATEMENT statement

Executes stored prepared statements created with CREATE STATEMENT. EXECUTE STATEMENT locates the specified stored prepared statement and executes it (without recompiling if possible). The statement returns the same ResultSet or update counts that it would if it were prepared directly. If the statement has been invalidated, it is automatically recompiled upon execution, and the new execution information is updated in the relevant system tables.

Syntax

EXECUTE STATEMENT StatementName
[ USING SingleRowResultSet ]

A SingleRowResultSet is a query that returns a single row, usually a VALUES expression or a SELECT. If used, the SingleRowResultSet supplies the parameters for the execution of the statement. See Supplying Parameters.

NOTE: Avoid using stored prepared statements for positioned updates and deletes. They are useful only if the cursor that the stored prepared statement uses to position itself exists at the time the stored prepared statement is created and will exist at the time the stored prepared statement is executed, a situation that will probably never occur!

Working with EXECUTE STATEMENT in JDBC

Within a Java program, you can execute a stored prepared statement using the EXECUTE STATEMENT statement in one of the following ways:

  • preparing the statement as a PreparedStatement, then executing it one or more times

    When an EXECUTE STATEMENT is prepared, the stored prepared statement is retrieved. The prepared statement can then be executed as if it had been prepared "from scratch." If the statement is to be executed many times by the application, prepare the EXECUTE STATEMENT statement only once to avoid repeated Cloudscape system table scans to retrieve a particular stored prepared statement.

  • executing the statement once as a java.sql.Statement

    If you need to supply parameters, you will have to use the USING clause with a SingleRowResultSet (see Supplying Parameters). This is useful within ij or when testing your query. Performance-wise, it is much better to prepare the statement and supply parameters with JDBC (see previous bulleted item).

Supplying Parameters

There are two different ways to pass parameters to an EXECUTE STATEMENT statement:

  • explicitly setting each value using the JDBC setXXX methods in java.sql.PreparedStatement

    To explicitly set the parameter values, create a prepared statement for the EXECUTE STATEMENT statement. Then set the parameters by position using the setXXX methods on the prepared statement. Execute the statement using the execute method on the prepared EXECUTE STATEMENT statement.

  • using the USING clause with a SingleRowResultSet

    To supply a SingleRowResultSet set as an argument to the EXECUTE STATEMENT statement, use any statement that returns a single row. The elements in the SingleRowResultSet are mapped directly to the parameters in the statement by position. For example, if there are two parameters for a given statement and a VALUES clause with two elements, the first parameter is set to the first element in the VALUES clause, and so on.

    If the argument to an EXECUTE STATEMENT evaluates to a multiple row ResultSet, an exception is raised.

NOTE: The SQL-J statement that provides the SingleRowResultSet has its own compilation and execution costs, so it is more costly than explicitly setting parameter values.

EXECUTE STATEMENT Examples

// Execute a prepared statement and supply paramters via JDBC
PreparedStatement getDirectFlights = conn.prepareStatement(
    "EXECUTE STATEMENT GETDIRECTFLIGHTS");
// getDirectFlights now points to the GETDIRECTFLIGHTS statement // Set the parameters using the setXXX() methods getDirectFlights.setString(1, 'SFO'); getDirectFlights.setString(2, 'JFK'); ResultSet rs = getDirectFlights.executeQuery(); while(rs.next()) { /* retrieve data, etc. */ } // try Miami, too getDirectflights.setString(2, 'MIA'); ResultSet rs = getDirectFlights.executeQuery();

-- Execute a prepared statement using parameters passed
-- in with a VALUES clause
EXECUTE STATEMENT getFullFlightInfo
USING VALUES ('AA1111', 1);

-- Execute a prepared statement using parameters from
-- a SELECT statement. Explicitly specify the schema.
EXECUTE STATEMENT APP.getDirectFlights
USING SELECT customized_tour.getCity1(
    getCurrentConnection()).getAirport(),     customized_tour.getCity2(
        getCurrentConnection()).getAirport()
FROM CUSTOMIZEDTOURS WHERE group_id = 1