Single-Row Query Results--SELECT INTO Statements

When only a single row of data is being returned from the database, SQLJ allows you to assign selected items directly to Java host expressions inside SQL syntax. This is done using the SELECT INTO statement. The syntax is as follows:

#sql { SELECT expression1,..., expressionN  INTO :host_exp1,..., :host_expN  
       FROM datasource <optional clauses> };

Where:

• expression1 through expressionN are expressions specifying what is to be selected from the database. These can be any expressions that are valid for any SELECT statement. This list of expressions is referred to as the SELECT-list.

  In a simple case, these would be names of columns from a database table.

  It is also legal to include a host expression in the SELECT-list (see the examples below).

• host_exp1 through host_expN are target host expressions, such as variables or array indexes. This list of host expressions is referred to as the INTO-list.

• datasource is the name of the database table, view, or snapshot from which you are selecting the data.

• optional clauses are any additional clauses that you want to include that are valid in a SELECT statement, such as a WHERE clause.

A SELECT INTO statement must return one and only one row of data, otherwise an error will be generated at runtime.

The default is OUT for a host expression in an INTO-list, but you can optionally state this explicitly:

#sql { SELECT column_name1, column_name2  INTO :OUT host_exp1, :OUT host_exp2  
       FROM table WHERE condition };

Trying to use an IN or INOUT token in the INTO-list will result in an error at translation time.

Examples of SELECT INTO Statements

The examples below assume the following table definition:

CREATE TABLE EMP (
   EMP_NUM NUMBER,
   EMP_NAME CHAR(30),
   HIRE_DATE DATE );

The first example is a SELECT INTO statement with a single host expression in the INTO-list:

String name;
#sql { SELECT emp_name INTO :name FROM emp WHERE emp_num=28959 };

The second example is a SELECT INTO statement with multiple host expressions in the INTO-list:

String name;
Date hiredate;
#sql { SELECT emp_name, hire_date INTO :name, :hiredate FROM emp 
       WHERE emp_num=28959 };

Examples with Host Expressions in SELECT-List

It is legal to use Java host expressions in the SELECT-list as well as in the INTO-list.

For example, you can select directly from one host expression into another (though this is of limited usefulness):

...
#sql { SELECT :name1 INTO :name2 FROM emptable WHERE emp_num=28959 };
...

More realistically, you may want to perform an operation or concatenation on the data that is selected, as in the following examples (assume Java variables were previously declared and assigned, as necessary):

...
#sql { SELECT salary + :raise INTO :newsal FROM emptable WHERE emp_num=28959 };
...

...
#sql { SELECT :(firstname + " ") || emp_last_name INTO :name FROM emptable 
       WHERE emp_num=28959 };
...

In the second example, firstname is concatenated to " " using a Java host expression and Java string concatenation (the + operator). This result is then passed to the SQL engine which uses SQL string concatenation (the || operator) to append the last name.

---