A SELECT clause that specifies the storing of selected values in host
variables.
Synopsis
The
INTO clause of a
SELECT statement
uses the field values retrieved (or calculated) by the
SELECT to
set a corresponding set of host variables. This optional
INTO clause
appears after the
select-item list and before the
FROM clause.
Note:
The
INTO clause and host variables are only used in
Embedded SQL. They are not used in
Dynamic
SQL. In Dynamic SQL, similar functionality for output variables is provided
by %Library.ResultSet.
The following rules apply when using host variables and the
INTO clause:
-
If the host language declares data types for variables, all host variables
must be declared in the host language prior to invoking the
SELECT statement.
The data types of the retrieved field values must match the host variable declarations.
(Caché ObjectScript does not declare data types for variables.)
-
The number of host variables in the
INTO clause
must match the number of fields specified in the
select-item list.
If the number of selected fields and host variables differs, SQL returns a “cardinality
mismatch” error.
-
Selected fields and host variables are matched by relative position.
Therefore, the corresponding items in these two lists must appear in the same sequence.
-
A host variable can contain only a single value. Therefore, a
SELECT in
embedded SQL only retrieves one row of data. This defaults to the first row of the
table. You can select a specific row by specifying a unique data value in the
WHERE clause.
A
SELECT which returns an aggregate value (such as a count, sum,
or average) also returns a single value.
If you specify an
INTO clause, SQL does not display selected
field values.
For important restrictions on the use of host variable values in the containing
program, refer to
Host Variable.
The following example uses embedded SQL within a Caché ObjectScript program.
The SQL selects three fields from the first record in the table (embedded SQL always
retrieves a single record), and uses
INTO to set three corresponding
host variables. These variables are then used by the Caché ObjectScript
WRITE commands.
It is considered good program practice to immediately test the SQLCODE variable upon
returning from embedded SQL.
NEW SQLCODE
WRITE !,"Going to get the first record"
&sql(SELECT Home_State, Name, Age
INTO :state, :name, :age
FROM Sample.Person)
IF SQLCODE=0 {
WRITE !," Name=",name
WRITE !," Age=",age
WRITE !," Home State=",state }
ELSE {
WRITE !,"SQL error ",SQLCODE }
The following embedded SQL example passes a host variable (today) into the SELECT
statement, where a calculation results in the INTO clause variable value (:tomorrow).
This host variable is passed out to the containing program.
SET today=$HOROLOG
&sql(SELECT :today+1
INTO :tomorrow
FROM Sample.Person)
IF SQLCODE=0 {
WRITE !,"Tomorrow is: ",$ZDATE(tomorrow) }
ELSE {
WRITE !,"SQL error ",SQLCODE }
For restrictions on the use of input and output host variable values, see
Host Variable.
The following example uses embedded SQL within a Caché ObjectScript program
to count the records in two tables. The SQL counts the records using the
COUNT aggregate
function, then specifies an
INTO clause to set a subscripted variable
for each count. These variables are then used by the Caché ObjectScript
WRITE commands.
WRITE !,"Counting the records"
&sql(SELECT COUNT(*)
INTO :total(1)
FROM Sample.Person)
&sql(SELECT COUNT(*)
INTO :total(2)
FROM Sample.Employee)
IF SQLCODE=0 {
WRITE !,"Total Person records=",total(1)
WRITE !,"Total Employee records=",total(2) }
ELSE {
WRITE !,"SQL error ",SQLCODE }
-
-
Caché ObjectScript:
SET command