Complete Example Employee Report

Complete Example: Employee Report

Here's a full program that connects to a database, queries employees, and displays a report:

employee_report.sqlrpgle:

**FREE

DCL-DS emp QUALIFIED;
  id     INT(10);
  name   VARCHAR(50);
  salary PACKED(9:2);
END-DS;

DCL-S connStr VARCHAR(200);
DCL-S total   PACKED(11:2) INZ(0);
DCL-S count   INT(10) INZ(0);

// Connect using environment variable or default
connStr = %GETENV('DATABASE_URL');
IF connStr = '';
  connStr = 'Driver={SQLite3};Database=company.sqlite;';
ENDIF;

EXEC SQL CONNECT USING :connStr;

IF SQLCOD < 0;
  DSPLY 'Failed to connect to database';
  RETURN;
ENDIF;

// Declare and open cursor
EXEC SQL DECLARE empCur CURSOR FOR
  SELECT id, name, salary FROM employees
  ORDER BY salary DESC;

EXEC SQL OPEN empCur;

// Fetch and display
DSPLY '--- Employee Report ---';
EXEC SQL FETCH empCur INTO :emp.id, :emp.name, :emp.salary;

DOW SQLCOD = 0;
  DSPLY (%CHAR(emp.id) + '  ' + %TRIM(emp.name) + '  $' + %CHAR(emp.salary));
  total += emp.salary;
  count += 1;
  EXEC SQL FETCH empCur INTO :emp.id, :emp.name, :emp.salary;
ENDDO;

EXEC SQL CLOSE empCur;

DSPLY '-----------------------';
DSPLY ('Employees: ' + %CHAR(count));
DSPLY ('Total Payroll: $' + %CHAR(total));

EXEC SQL DISCONNECT;

*INLR = *ON;
RETURN;

./rpgc employee_report.sqlrpgle
./employee_report