RPG - EXEC SQL - RPG School

EXEC SQL is used to put an SQL script embedded in your RPG code and have it executed as part of the program logic.

You need to prefix your RPG variables with a : in order to access them within the SQL statement. These are also called Host varibles.
It has to do with how the compiler works. This is because the SQL pre-compiler runs before the RPG compiler.

The SQL statement ends with a semi-colon ( ; ).

Example

dcl-s FirstName varchar(128)&nbsp; inz(' ');
dcl-s Person_Id int;
dcl-s Message   varchar(52);

Person_Id = 1;

exec sql
SELECT Name INTO :FirstName FROM TABLE
WHERE Id = :Person_Id;

Message = 'The persons name is ' + %trim(FirstName);
dsply Message;

dcl-s FirstName varchar(128)  inz(' ');

dcl-s Person_Id int;

dcl-s Message varchar(52);

Person_Id = 1;

exec sql

SELECT Name INTO :FirstName FROM TABLE

WHERE Id = :Person_Id;

Message = 'The persons name is ' + %trim(FirstName);

dsply Message;