Host Variables and Host Expressions 

Definition

Java host variables and host expressions are used to exchange data between Java (the host language) and SQL (the embedded language). They have the following syntax:

<host expression> ::= (IN | OUT | INOUT)?   ':'( <java variable> |        '(' <java expression> ')' ).

Use

Host variables and host expressions can be used anywhere in an embedded SQL statement where the usage of dynamic parameter markers is permitted by the Open SQL grammar.

To use a Java variable as a host variable, you must use a colon (:) as a prefix to it.

String empl_id = "abc";    #sql [ctx] { DELETE FROM employee              WHERE id = :empl_id };

Host variables. The Java variable empl_id is embedded in the SQL statement text as a host variable. The prefix ":" denotes that empl_id is a host variable.

Any complex Java expression can be directly embedded in a SQL statement as a host expression. A host expression must be enclosed between “:(” and “)”. The host expressions are evaluated from left to right according to the order of their appearance in the statement. A host expression may have side effects.

String[] values = new String[5]; MyClass ref = new MyClass(); int i = 3; #sql [ctx] { SELECT col             INTO :(values[++i])             FROM dbtab             WHERE key = :(ref.getKey()) };

Host Expressions. Here, two Java expressions are embedded in an SQL statement. The expression ref.getKey() is an IN parameter, and the expression values[++i] is an OUT parameter. Both expressions are evaluated after the execution of the statement.

To indicate the parameter mode of a host variable or host expression, that is, the direction of the data flow, the Java expression can optionally be prefixed with a parameter mode indicator “IN”, “OUT” or “INOUT” (from the perspective of the database). The “IN” parameter signifies that data is transferred from the Java variable to the SQL statement, while the “OUT” parameter means that the result of the SQL statement goes back to the Java program. The “INOUT” parameter defines data flow in both directions. These keywords are not case sensitive. In Open SQL for Java, the parameter mode is determined automatically and supported for portability.

#sql [ctx] { SELECT col             INTO :OUT var             FROM dbtab             WHERE key = :IN (ref.getKey()) };

Parameter mode indicators. The optional parameter mode indicators IN and OUT denote whether a host expression is an IN or OUT parameter (from the perspective of the database).

You should be careful when using host expressions, since they are evaluated at specific times. OUT expressions are evaluated after the execution of the SQL statement, while IN expressions are evaluated before the statement is executed.