Host Variable Returning Field Values

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.