SQL-J Language Reference
Page 4 of 121

Statements

This section provides manual pages for both high-level language constructs and parts thereof. For example, the CREATE INDEX statement is a high-level statement that you can execute directly via the JDBC interface. This section also includes clauses, which are not high-level statements and which you cannot execute directly but only as part of a high-level statement. The ORDER BY and WHERE clauses are examples of this kind of clause. Finally, this section also includes some syntactically complex portions of statements called expressions, for example SelectExpression and TableSubquery. These clauses and expressions receive their own manual pages for ease of reference.

Unless it is explicitly stated otherwise, you can execute or prepare and then execute all the high-level statements, which are all marked with the word statement, via the interfaces provided by JDBC. This manual indicates whether an expression can be executed as a high-level statement.

NOTE: You cannot execute DDL in a target database in a replication system. DDL stands for data definition language and consists of statements that create or drop dictionary objects. Examples are the CREATE TABLE statement, the DROP SCHEMA statement, and the ALTER STATEMENT statement.

Interaction with the Dependency System

Cloudscape internally tracks the dependencies of prepared statements and stored prepared statements, which are SQL-J statements that are precompiled before being executed. Typically they are prepared (precompiled) once and executed multiple times.

Prepared statements and stored prepared statements depend on the tables, indexes, views, aliases, work units (dictionary objects), and statements they reference. Removing or modifying the dictionary objects or statements on which they depend invalidates them internally, which means that Cloudscape will automatically try to recompile the statement when you execute it. If the statement fails to recompile, the execution request fails. However, if you take some action to restore the broken dependency (such as restoring the missing table), you can execute the same prepared statement, because Cloudscape will recompile it automatically at the next execute request.

Statements depend on one another--an UPDATE WHERE CURRENT statement depends on the statement it references. Removing the statement on which it depends invalidates the UPDATE WHERE CURRENT statement.

In addition, prepared statements prevent execution of certain DDL statements if there are open results sets on them.

Manual pages for each statement detail what actions would invalidate that statement, if prepared.

Here is an example using the Cloudscape tool ij:

ij> CREATE TABLE mytable (mycol INT);
0 rows inserted/updated/deleted
ij> INSERT INTO mytable VALUES (1), (2), (3);
3 rows inserted/updated/deleted
ij> -- this example uses the ij command prepare,
-- which prepares a statement
prepare p1 AS 'INSERT INTO MyTable VALUES (4)';
ij> -- p1 depends on mytable;
execute p1;
1 row inserted/updated/deleted
ij> -- Cloudscape executes it without recompiling
CREATE INDEX i1 ON mytable(mycol);
0 rows inserted/updated/deleted
ij> -- p1 is temporarily invalidated because of new index
execute p1;
1 row inserted/updated/deleted
ij> -- Cloudscape automatically recompiles p1 and executes it
DROP TABLE mytable;
0 rows inserted/updated/deleted
ij> -- Cloudscape permits you to drop table
-- because result set of p1 is closed
-- however, the statement p1 is temporarily invalidated
CREATE TABLE mytable (mycol INT);
0 rows inserted/updated/deleted
ij> INSERT INTO mytable VALUES (1), (2), (3);
3 rows inserted/updated/deleted
ij> execute p1;
1 row inserted/updated/deleted
ij> -- Because p1 is invalid, Cloudscape tries to recompile it
-- before executing.
-- It is successful and executes.
DROP TABLE mytable;
0 rows inserted/updated/deleted
ij> -- statement p1 is now invalid,
-- and this time the attempt to recompile it
-- upon execution will fail
execute p1;
ERROR 42X05: Table 'MYTABLE' does not exist.

Interaction with the Java Data Types

In the current release, there is no dependency checking of Java data types. That is, if a prepared statement uses a Java class, and that class changes or disappears, the dependency system does not detect the change, and the prepared statement is not invalidated. This could cause the prepared statement to get an error or to produce unexpected results. Consider the following example:

CREATE TABLE Cities
    (City SERIALIZE(JBMSTours.serializabletypes.City))

SELECT City.getName()
FROM Cities

If the class JBMSTours.serializabletypes.City changes after the SELECT statement is compiled, but before it is executed, the dependency system does not detect that the class has changed, and the SELECT statement could get an error or unexpected results.

Statement List