Java Host Expressions, Context Expressions, and Result Expressions

There are three categories of Java expressions used in SQLJ code: host expressions, context expressions, and result expressions. Host expressions are the most frequently used and merit the most discussion.

SQLJ uses Java host expressions to pass arguments between your Java code and your SQL operations. This is how you pass information between Java and SQL. Host expressions are interspersed within the embedded SQL operations in SQLJ source code.

The most basic kind of host expression, consisting of only a Java identifier, is referred to as a host variable.

A context expression specifies a connection context instance or execution context instance to be used for a SQLJ statement.

A result expression specifies an output variable for query results or a function return.

(Result expressions and the specification of connection context instances and execution context instances were first introduced in "Overview of SQLJ Executable Statements".)

Overview of Host Expressions

Any valid Java expression can be used as a host expression. In the simplest case, which is typical, the expression consists of just a single Java variable. Other kinds of host expressions include: arithmetic expressions, Java method calls with return values, Java class field values, array elements, conditional expressions (a ? b : c), logical expressions, or bitwise expressions.

Java identifiers used as host variables or in host expressions can represent any of the following:

• local variables

• declared parameters

• class fields (such as myclass.myfield)

• static or instance method calls

Local variables used in host expressions can be declared anywhere that other Java variables can be declared. Fields can be inherited from a superclass.

Java variables that are legal in the Java scope where the SQLJ executable statement appears can be used in a host expression in a SQL statement, presuming its type is convertible to and from a SQL datatype.

Host expressions can be input, output, or input-output.

See "Supported Types for Host Expressions" for information about data conversion between Java and SQL during input and output operations.

Basic Host Expression Syntax

A host expression is preceded by a colon. If the desired mode of the host expression (input, output, or input-output) is not the default, then the colon must be followed (before the host expression itself) by IN, OUT, or INOUT, as appropriate. These are referred to as mode specifiers. The default is OUT if the host expression is part of an INTO-list or is the assignment expression in a SET statement. Otherwise, the default is IN. (When using the default, you can still include the mode specifier if desired.)

Any OUT or INOUT host expression must be assignable (an l-value, meaning something that can logically appear on the left side of an equals sign).

The SQL code that surrounds a host expression can use any vendor-specific SQL syntax, therefore no assumptions can be made about the syntax when parsing the SQL operations and determining the host expressions. Therefore, to avoid any possible ambiguity, any host expression that is not a simple host variable (in other words, that is more complex than a non-dotted Java identifier) must be enclosed in parentheses.

To summarize the basic syntax:

• For a simple host variable without a mode specifier, put the host variable after the colon, as in the following example:

  :hostvar
  
  

• For a simple host variable with a mode specifier, put the mode specifier after the colon, and white space (space, tab, or newline) between the mode specifier and the host variable, as in the following example:

  :INOUT hostvar
  
  

  The white space is required to distinguish between the mode specifier and the variable name.

• For any other host expression, enclose the expression in parentheses and place it after the mode specifier, or after the colon if there is no mode specifier, as in the following examples:

  :IN(hostvar1+hostvar2)
  :(hostvar3*hostvar4)
  :(index--)
  
  

  White space is not required after the mode specifier in the above example, because the parenthesis is a suitable separator, but it is allowed.

  An outer set of parentheses is needed even if the expression already starts with a begin-parenthesis, as in the following examples:

  :((x+y).z)
  :(((y)x).myOutput())
  
  

Examples of Host Expression

The following examples will help clarify the preceding syntax discussion. (Some of these examples use SELECT INTO statements, which are described in "Single-Row Query Results--SELECT INTO Statements".)

1. In this example, two input host variables are used--one as a test value for a WHERE clause, and one to contain new data to be sent to the database.

   Presume you have a database employee table emp with an ename column for employee names and a sal column for employee salaries.

   The relevant Java code that defines the host variables is also shown in the example.

   String name = "SMITH";
   double salary = 25000.0;
   ...
   #sql { UPDATE emp SET sal = :salary WHERE ename = :name };
   
   

   IN is the default, but you can state it explicitly as well:

   #sql { UPDATE emp SET sal = :IN salary WHERE ename = :IN name };
   
   

   As you can see, ":" can immediately precede the variable when not using the IN token, but ":IN" must be followed by white space before the host variable.

2. This example uses an output host variable in a SELECT INTO statement, where you want to find out the name of employee number 28959.

   String empname;
   ...
   #sql { SELECT ename INTO :empname FROM emp WHERE enum = 28959 };
   
   

   OUT is the default for an INTO-list, but you can state it explicitly as well:

   #sql { SELECT ename INTO :OUT empname FROM emp WHERE enum = 28959 };
   
   

   This looks in the enum column of the emp table for employee number 28959, selects the name in the ename column of that row, and outputs it to the empname output host variable, which is a Java string.

3. This example uses an arithmetic expression as an input host expression. The Java variables balance and minPmtRatio are multiplied, and the result is used to update the minPayment column of the creditacct table for account number 537845.

   float balance = 12500.0;
   float minPmtRatio = 0.05
   ...
   #sql { UPDATE creditacct SET minPayment = :(balance * minPmtRatio) 
          WHERE acctnum = 537845 };
   
   

   Or, to use the IN token:

   #sql { UPDATE creditacct SET minPayment = :IN (balance * minPmtRatio) 
          WHERE acctnum = 537845 };
   
   

4. This example displays the use of the output of a method call as an input host expression, and also uses an input host variable. This statement uses the output from getNewSal() to update the sal column in the emp table for the employee (in the ename column) who is specified by the Java variable ename. Java code initializing the host variables is also shown.

   String ename = "SMITH";
   double raise = 0.1;
   ...
   #sql {UPDATE emp SET sal = :(getNewSal(raise, ename)) WHERE ename = :ename};
   
   

Overview of Result Expressions and Context Expressions

A context expression is an input expression that specifies the name of a connection context instance or an execution context instance that is to be used in a SQLJ executable statement. Any legal Java expression that yields such a name can be used.

A result expression is an output expression that is used for query results or a function return. It can be any legal Java expression that is assignable, meaning that it can logically appear on the left side of an equals sign (this is sometimes referred to as an l-value).

The following examples can be used for either result expressions or context expressions:

• local variables

• declared parameters

• class fields (such as myclass.myfield)

• array elements

Result expressions and context expressions appear lexically in the SQLJ space, unlike host expressions, which appear lexically in the SQL space (inside the curly brackets of a SQLJ executable statement). Therefore, a result expression or context expression must not be preceded by a colon.

Evaluation of Java Expressions at Runtime

This section discusses the evaluation of Java host expressions, connection context expressions, execution context expressions, and result expressions when your application executes.

Here is a simplified representation of a SQLJ executable statement that uses all of these types of expressions:

#sql [connctxt_exp, execctxt_exp] result_exp = { SQL with host expression };

Java expressions can be used as any of the following:

• connection context expression (optional; evaluated to specify the connection context instance to be used)

• execution context expression (optional; evaluated to specify the execution context instance to be used)

• result expression (when appropriate; to receive results from a stored function, for example)

• host expression

The evaluation of Java expressions does have side effects in a Java program because they are evaluated by Java, not by the SQL engine. Furthermore, the order of evaluation of these expressions can be critical if any of the expressions have side effects.

The following is a summary of the overall order of evaluation, execution, and assignment of Java expressions for each statement that executes during runtime.

1. If there is a connection context expression, then it is evaluated immediately (before any other Java expressions are evaluated).

2. If there is an execution context expression, then it is evaluated after any connection context expression but before any result expression.

3. If there is a result expression, then it is evaluated after any context expressions but before any host expressions.

4. After evaluation of any context or result expressions, host expressions are evaluated from left to right as they appear in the SQL operation. As each host expression is encountered and evaluated, its value is saved to be passed to SQL.

   Each host expression is evaluated once and only once.

5. IN and INOUT parameters are passed to SQL, and the SQL operation is executed.

6. After execution of the SQL operation, the output parameters--Java OUT and INOUT host expressions--are assigned output in order from left to right as they appear in the SQL operation.

   Each output host expression is assigned once and only once.

7. The result expression, if there is one, is assigned output last.

"Examples of Evaluation of Java Expressions at Runtime", has a series of examples that clarifies this sequence and discusses a number of special considerations.

Once the expressions in a statement have been evaluated, input and input-output host expressions are passed to SQL and then the SQL operation is executed. After execution of the SQL operation, assignments are made to Java output host expressions, input-output host expressions, and results expressions as follows: 1) OUT and INOUT host expressions are assigned output in order from left to right; 2) The result expression, if there is one, is assigned output last.

Note that during runtime all host expressions are treated as distinct values, whether or not they share the same name or reference the same object. The execution of each statement is treated as a remote method, and each host expression is taken as a distinct parameter. Each input or input-output parameter is evaluated and passed as it is first encountered, before any output assignments are made for that statement, and each output parameter is also taken as distinct and is assigned exactly once.

It is also important to remember that each host expression is evaluated only once. An INOUT expression is evaluated when it is first encountered, and the expression itself is not re-evaluated, nor any side-effects repeated, when the output assignment is made.

In discussing the evaluation order of host expressions, several points must be highlighted, as discussed in the remainder of this section.

Examples of Evaluation of Java Expressions at Runtime

This section discusses some of the subtleties of how Java expressions are evaluated when your application executes, and provides examples. (Some of these examples use SELECT INTO statements, which are described in "Single-Row Query Results--SELECT INTO Statements"; some use assignment statements, which are described in "Assignment Statements (SET)"; and some use stored procedure and function calls, which are described in "Stored Procedure and Function Calls".)

Prefix Operators Act Before Evaluation; Postfix Operators Act After Evaluation

When a Java expression contains a Java postfix increment or decrement operator, the incrementation or decrementation occurs after the expression has been evaluated. Similarly, when a Java expression contains a Java prefix increment or decrement operator, the incrementation or decrementation occurs before the expression is evaluated.

This is equivalent to how these operators are handled in standard Java code.

Consider the following examples.

Example 1: postfix operator

int indx = 1;
...
#sql { ... :OUT (array[indx]) ... :IN (indx++) ... };

This is evaluated as follows:

#sql { ... :OUT (array[1]) ... :IN (1) ... };

The variable indx is incremented to 2 and will have that value the next time it is encountered, but not until after :IN (indx++) has been evaluated.

Example 2: postfix operators

int indx = 1;
...
#sql { ... :OUT (array[indx++]) ... :IN (indx++) ... };

This is evaluated as follows:

#sql { ... :OUT (array[1]) ... :IN (2) ... };

The variable indx is incremented to 2 after the first expression is evaluated but before the second expression is evaluated. It is incremented to 3 after the second expression is evaluated and will have that value the next time it is encountered.

Example 3: prefix and postfix operators

int indx = 1;
...
#sql { ... :OUT (array[++indx]) ... :IN (indx++) ... };

This is evaluated as follows:

#sql { ... :OUT (array[2]) ... :IN (2) ... };

The variable indx is incremented to 2 before the first expression is evaluated. It is incremented to 3 after the second expression is evaluated and will have that value the next time it is encountered.

Example 4: postfix operator

int grade = 0;
int count1 = 0;
...
#sql { SELECT count INTO :count1 FROM staff
       WHERE grade = :(grade++) OR grade = :grade };

This is evaluated as follows:

#sql { SELECT count INTO :count1 FROM staff
       WHERE grade = 0 OR grade = 1 };

The variable grade is incremented to 1 after :(grade++) is evaluated and has that value when :grade is evaluated.

Example 5: postfix operators

int count = 1;
int[] x = new int[10];
int[] y = new int[10];
int[] z = new int[10];
...
#sql { SET :(z[count++]) = :(x[count++]) + :(y[count++]) };

This is evaluated as follows:

#sql { SET :(z[1]) = :(x[2]) + :(y[3]) };

The variable count is incremented to 2 after the first expression is evaluated but before the second expression is evaluated; it is incremented to 3 after the second expression is evaluated but before the third expression is evaluated; it is incremented to 4 after the third expression is evaluated and will have that value the next time it is encountered.

Example 6: postfix operator

int[] arr = {3, 4, 5};
int i = 0;
...
#sql { BEGIN
         :OUT (arr[i++]) := :(arr[i]);
       END; };

This is evaluated as follows:

#sql { BEGIN
         :OUT (a[0]) := :(a[1]);
       END; };

The variable i is incremented to 1 after the first expression is evaluated but before the second expression is evaluated, therefore output will be assigned to arr[0]. Specifically, arr[0] will be assigned the value of arr[1], which is 4. After execution of this statement, array arr will have the values {4, 4, 5}.

IN vs. INOUT vs. OUT Makes No Difference in Evaluation Order

Host expressions are evaluated from left to right. Whether an expression is IN, INOUT, or OUT makes no difference in when it is evaluated; all that matter is its position in the left-to-right order.

Example 7: IN vs. INOUT vs. OUT

int[5] arry;
int n = 0;
...
#sql { SET :OUT arry[n] = :(++n) };

This is evaluated as follows:

#sql { SET :OUT arry[0] = 1 };

One might expect input expressions to be evaluated before output expressions, but that is not the case. :OUT arry[n] is evaluated first because it is the left-most expression. Then n is incremented prior to evaluation of ++n, since it is being operated on by a PREFIX operator. Then ++n is evaluated as 1. The result will be assigned to arry[0], not arry[1], because 0 was the value of n when it was originally encountered.

Expressions in PL/SQL Blocks Are Evaluated Before Statements Are Executed

Host expressions in a PL/SQL block are all evaluated in one sequence, before any have been executed.

Example 8: evaluation of expressions in a PL/SQL block

int x=3;
int z=5;
...
#sql { BEGIN :OUT x := 10; :OUT z := :x; END; };
System.out.println("x=" + x + ", z=" + z);

This is evaluated as follows:

#sql { BEGIN :OUT x := 10; :OUT z := 3; END; };

Therefore it would print x=10, z=3

All expressions in a PL/SQL block are evaluated before any are executed. In this example, the host expressions in the second statement, :OUT z and :x, are evaluated before the first statement is executed. In particular, the second statement is evaluated while x still has its original value of 3, before it has been assigned the value 10.

Example 9: evaluation of expressions in a PL/SQL block (with postfix)

Consider an additional example of how expressions are evaluated within a PL/SQL block.

int x=1, y=4, z=3;
...
#sql { BEGIN
         :OUT x := :(y++) + 1;
         :OUT z := :x;
       END; };

This is evaluated as follows:

#sql { BEGIN
          :OUT x := 4 + 1;
          :OUT z := 1;
       END; };

The postfix increment operator is executed after :(y++) is evaluated, so the expression is evaluated as 4 (the initial value of y). The second statement, :OUT z := :x, is evaluated before the first statement is executed, so x still has its initialized value of 1. After execution of this block, x will have the value 5 and z will have the value 1.

Example 10: statements in one block vs. separate SQLJ executable statements

This example demonstrates the difference between two statements appearing in a PL/SQL block in one SQLJ executable statement and the same statements appearing in separate (consecutive) SQLJ executable statements.

First, consider the following, where two statements are in a PL/SQL block.

int y=1;
...
#sql { BEGIN :OUT y := :y + 1; :OUT x := :y + 1; END; };

This is evaluated as follows:

#sql { BEGIN :OUT y := 1 + 1; :OUT x := 1 + 1; END; };

The :y in the second statement is evaluated before either statement is executed, so y has not yet received its output from the first statement. After execution of this block, both x and y have the value 2.

Now, consider the situation where the same two statements are in PL/SQL blocks in separate SQLJ executable statements.

int y=1;
#sql { BEGIN :OUT y := :y + 1; END; };
#sql { BEGIN :OUT x := :y + 1; END; };

The first statement is evaluated as follows:

#sql { BEGIN :OUT y := 1 + 1; END; };

Then it is executed and y is assigned the value 2.

After execution of the first statement, the second statement is evaluated as follows:

#sql { BEGIN :OUT x := 2 + 1; END; };

This time, as opposed to the PL/SQL block example above, y has already received the value 2 from execution of the previous statement; therefore, x is assigned the value 3 after execution of the second statement.

Expressions in PL/SQL Blocks Are Always Evaluated Once and Only Once

Each host expression is evaluated once and only once, regardless of program flow and logic.

Example 11: evaluation of host expression in a loop

int count = 0;
...
#sql {
   DECLARE
      n NUMBER
   BEGIN
      n := 1;
      WHILE n <= 100 LOOP
         :IN (count++);
         n := n + 1;
      END LOOP;
   END;
};

The Java variable count will have the value 0 when it is passed to SQL (since it is operated on by a postfix operator, as opposed to a prefix operator), then will be incremented to 1 and will hold that value throughout execution of this PL/SQL block. It is evaluated only once as the SQLJ executable statement is parsed and then is replaced by the value 1 prior to SQL execution.

Example 12: evaluation of host expressions in conditional blocks

This example demonstrates how each expression is always evaluated, regardless of program flow. As the block is executed, only one branch of the IF...THEN...ELSE construct can be executed. Before the block is executed, however, all expressions in the block are evaluated, in the order that the statements appear.

int x;
...
(operations on x)
...
#sql {
   DECLARE
      n NUMBER
   BEGIN
      n := :x;
      IF n < 10 THEN
         n := :(x++);
      ELSE
         n := :x * :x;
      END LOOP;
   END;
};

Say the operations performed on x resulted in x having a value of 15. When the PL/SQL block is executed, the ELSE branch will be executed and the IF branch will not; however, all expressions in the PL/SQL block are evaluated before execution regardless of program logic or flow. So x++ is evaluated, then x is incremented, then each x is evaluated in the (x * x) expression. The IF...THEN...ELSE block is, therefore, evaluated as follows:

IF n < 10 THEN
   n := 15;
ELSE
   n := :16 * :16;
END LOOP;

After execution of this block, given an initial value of 15 for x, n will have the value 256.

Output Host Expressions Are Assigned Left to Right, Before Result Expression

Remember that OUT and INOUT host expressions are assigned in order from left to right, and then the result expression, if there is one, is assigned last. If the same variable is assigned more than once, then it will be overwritten according to this order, with the last assignment taking precedence.

Example 13: multiple output host expressions referencing the same variable

#sql { CALL foo(:OUT x, :OUT x) };

If foo() outputs the values 2 and 3, respectively, then x will have the value 3 after the SQLJ executable statement has finished executing. The right-hand assignment will be performed last, thereby taking precedence.

Example 14: multiple output host expressions referencing the same object

MyClass x = new MyClass();
MyClass y = x;
...
#sql { ... :OUT (x.field):=1 ... :OUT (y.field):=2 ... };

After execution of the SQLJ executable statement, x.field will have a value of 2, not 1, because x is the same object as y, and field was assigned the value of 2 after it was assigned the value of 1.

Example 15: results assignment taking precedence over host expression assignment

This example demonstrates the difference between having the output results of a function assigned to a result expression and having the results assigned to an OUT host expression.

Consider the following function, with an input invar, an output outvar, and a return value:

CREATE FUNCTION fn(invar NUMBER, outvar OUT NUMBER)
   RETURN NUMBER AS BEGIN
      outvar := invar + invar;
      return (invar * invar);
   END fn;

Now consider an example where the output of the function is assigned to a result expression:

int x = 3;
#sql x = { VALUES(fn(:x, :OUT x)) };

The function will take 3 as the input, will calculate 6 as the output, and will return 9. After execution, the :OUT x will be assigned first, giving x a value of 6. But finally the result expression is assigned, giving x the return value of 9 and overwriting the value of 6 previously assigned to x. So x will have the value 9 the next time it is encountered.

Now consider an example where the output of the function is assigned to an OUT host variable instead of to a result expression:

int x = 3;
#sql { BEGIN :OUT x := fn(:x, :OUT x); END; };

In this case there is no result expression and the OUT variables are simply assigned left to right. After execution the first :OUT x, on the left side of the equation, is assigned first, giving x the function return value of 9. Proceeding left to right, however, the second :OUT x, on the right side of the equation, is assigned last, giving x the output value of 6 and overwriting the value of 9 previously assigned to x. So x will have the value 6 the next time it is encountered.

Restrictions on Host Expressions

Do not use "in", "out", and "inout" as identifiers in host expressions unless they are enclosed in parentheses. Otherwise they might be mistaken for mode specifiers. This is case-insensitive.

For example, you could use an input host variable called "in" as follows:

:(in)

or:

:IN(in)

---