SQL inside programs

An RPG program can contain SQL directly in the code. The source takes member type SQLRPGLE, a precompiler turns the EXEC SQL statements into calls before the compiler sees the code, and the result is an ordinary program object.

CRTSQLRPGI OBJ(PRODLIB/INVOICES) SRCFILE(PRODLIB/QRPGLESRC) +
           COMMIT(*NONE) DBGVIEW(*SOURCE)

How it is written

**FREE
dcl-s total packed(11:2);
dcl-s customer char(10) inz('C0001');

exec sql
  SELECT SUM(ORDAMT)
    INTO :total
    FROM ORDERS
   WHERE ORDCUS = :customer
     AND ORDDAT >= CURRENT DATE - 1 YEAR;

Program variables are used inside the SQL prefixed with a colon: they are called host variables, and they are the bridge between the two worlds. They work both inbound (like :customer above) and outbound, with INTO.

More than one row needs a cursor:

exec sql
  DECLARE C1 CURSOR FOR
  SELECT ORDNUM, ORDAMT
    FROM ORDERS
   WHERE ORDCUS = :customer
   ORDER BY ORDDAT DESC;

exec sql OPEN C1;

dow sqlstate < '02000';
  exec sql FETCH C1 INTO :number, :amount;
  if sqlstate < '02000';
    // process
  endif;
enddo;

exec sql CLOSE C1;

How the outcome is checked

And this is where people go wrong most often. After every SQL statement the system fills two variables, which the precompiler declares by itself:

• SQLCODE: numeric: 0 fine, 100 no rows found, negative an error.
• SQLSTATE: five characters: '00000' fine, '02000' no rows, everything else worth a look.

On an UPDATE or DELETE, moreover, "no error" does not mean "it did something": the number of rows affected is in SQLERRD(3) (or ROW_COUNT with GET DIAGNOSTICS), and a change that found nothing to change is a case to handle, not a success.

The options that matter

exec sql SET OPTION
  commit    = *none,
  closqlcsr = *endmod,
  datfmt    = *iso,
  naming    = *sys;

Four lines that settle four categories of surprise:

• commit: with no commitment control, *none. The default is *CHG: a program compiled without saying so runs under commitment control, and the rows it changes stay locked until somebody issues COMMIT or ROLLBACK.
• closqlcsr: when cursors and prepared statements close by themselves. For ILE the default is *ENDACTGRP, at the end of the activation group; *endmod closes them when the module is exited. It is a choice against the default, giving every call a clean state, and IBM advises keeping the default in general until an analysis has been done.
• datfmt: the date format. *iso removes the ambiguity between day and month, which otherwise depends on the job's settings.
• naming: *sys uses LIBRARY/OBJECT and the library list; *sql uses SCHEMA.OBJECT. Choosing beats inheriting: where the program looks for its files depends on this.

Dynamic SQL, and injection

When the query is composed at run time you need dynamic SQL, and here the risk is exactly that of any other platform:

// Wrong: the contents of :filter end up inside the statement
sql = 'SELECT * FROM ORDERS WHERE ORDCUS = ''' + filter + '''';
exec sql PREPARE S1 FROM :sql;

// Right: the value stays a value
sql = 'SELECT * FROM ORDERS WHERE ORDCUS = ?';
exec sql PREPARE S1 FROM :sql;
exec sql DECLARE C2 CURSOR FOR S1;
exec sql OPEN C2 USING :filter;

When it is worth it and when not

Embedded SQL does not replace native reads: it sits alongside them, and the choice is made case by case.

It is right for totals and groupings, for reading from several files at once, for filters spanning many columns, and for every set operation: an UPDATE touching ten thousand rows is one line of SQL against a whole loop.

It is not right for fetching one specific record whose key you know. There CHAIN is more direct and more readable.

Sources

• IBM: SQLCODEs and SQLSTATEs
• IBM: Defining the SQL communication area in ILE RPG
• IBM: SET OPTION
• IBM: Create SQL ILE RPG Object (CRTSQLRPGI)