Database Connections and SQLJ Statements

Host Variables and Expressions

Host variables allow SQLJ programs to exchange information between the embedded SQL statements and the rest of the Java program. A host variable is any Java variable that is declared in the Java program. Host variables may be referenced within a SQLJ statement, and SQLJ takes care of the details of moving data back and forth between the SQL and Java environments.

When you use a host variable in a SQLJ statement to hold the value of a column retrieved from the database, the type of the host variable must be convertible into a type that is compatible with the database column type. For example, if the database column contains an integer value, then you should use a Java host variable of type int. Table 1 lists some of the more common type mappings.

You can also use the oracle.sql.NUMBER type to represent numbers, which is useful if you are dealing with large numbers, because there is no loss of precision like that which may occur when using the Java double or float types. The oracle.sql.ROWID type may be used to represent Oracle rowids, which are pointers to rows in a table.

In addition to using compatible types, you must ensure that the host variable is within the scope of the SQLJ statement in which the reference to the host variable is located.

Host variables are placed within SQLJ host expressions; a host expression is the "glue" that binds the host variables to the SQLJ executable statement. The syntax for a host expression that uses a host variable is as follows:

:[mode] host_variable

The syntax elements are as follows:

mode (optional)

Specifies the mode of the host expression, and may be set to one of the following:

IN

Your SQLJ statement may only read the value stored in the host variable (the value may not be changed).

OUT

Your SQLJ statement should write a new value to the host variable.

INOUT

Your SQLJ statement may both read and write the value of the host variable.

The default mode is OUT if the host expression is part of an INTO list, or if the expression is an assignment in a SET statement; otherwise, the default mode is IN. The mode is not case sensitive, and therefore may be upper or lower case. A space is required immediately after the mode.

host_variable

The name of a Java variable in the program. The variable must be within scope, and must be type-compatible with the corresponding Oracle data type.

In SQLJ statements, host expressions are always preceded by a colon (:) character. The following are examples of host expressions that could be used in SQLJ statements:

:IN variable1

:OUT variable2

:INOUT variable3

A host expression may also contain other Java storage constructs, including array elements and object attributes. The following two host expressions, which must be placed in brackets, show how to reference an object attribute and an array element respectively:

:(object.attribute1)

:(array[1])

You can also call Java functions from a host expression. For example, the following host expression calls a Java function named calc_new_price():

:((calc_new_price(1, 1.2)))

Assume the Java function calc_new_price() multiplies the original price of a product (the ID of which is passed as the first parameter) by the value specified in the second parameter. The new price returned by the function acts as an input to the host expression.

I hope you've enjoyed reading this column on how to connect to a database and embed SQL statements in your Java programs with SQLJ. Next month, I'll go over how to add SQLJ to Java programs that use embedded SQL Data Manipulation Language (DML) statements. See you then!

Jason Price is currently employed as a product manager with Oracle Corporation. He has contributed to many products at Oracle, including the database server, the application server, and several CRM applications.

Return to ONJava.com.