Host Variable Returning Field Values
The following Embedded SQL example selects three fields from the first record in the table (Embedded SQL always retrieves a single record), and uses INTO to set three corresponding unsubscripted host variables. These variables are then used by the ObjectScript WRITE commands. It is considered good program practice to immediately test the SQLCODE variable upon returning from Embedded SQL. If SQLCODE is not equal to 0, the values of output host variables are initialized to the empty string.
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 returns field values from a row resulting from the join of two tables. You must use a host variable list when returning fields from more than one table:
&sql(SELECT P.Name,E.Title,E.Name,P.%TABLENAME,E.%TABLENAME
INTO :name(1),:title,:name(2),:ptname,:etname
FROM Sample.Person AS P LEFT JOIN
Sample.Employee AS E ON E.Name %STARTSWITH 'B'
WHERE P.Name %STARTSWITH 'A')
IF SQLCODE=0 {
WRITE ptname," = ",name(1),!
WRITE etname," = ",title,!
WRITE etname," = ",name(2) }
ELSE {
WRITE !,"SQL error ",SQLCODE }
For restrictions on the use of input and output host variable values, refer to Host Variables.