Embedded SQL

Embedded SQL

Programs using embedded SQL should use the .sqlrpgle file extension. This tells OpenRPG to automatically link the ODBC library.

Basic SQL Operations

**FREE

DCL-S connStr VARCHAR(200);
DCL-S empName VARCHAR(50);
DCL-S empId   INT(10);

// Connect to database
connStr = 'Driver={SQLite3};Database=myapp.sqlite;';
EXEC SQL CONNECT USING :connStr;

// Create a table
EXEC SQL CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(9,2)
);

// Insert data using host variables
empId = 1;
empName = 'Alice';
EXEC SQL INSERT INTO employees (id, name, salary)
  VALUES(:empId, :empName, 75000.00);

// Query data
EXEC SQL SELECT name INTO :empName
  FROM employees WHERE id = :empId;
DSPLY empName;

// Check SQLCODE after operations
IF SQLCOD <> 0;
  DSPLY ('SQL error: ' + %CHAR(SQLCOD));
ENDIF;

// Clean up
EXEC SQL DROP TABLE employees;
EXEC SQL DISCONNECT;

*INLR = *ON;
RETURN;

Cursors

Use cursors to iterate over result sets:

**FREE

DCL-S connStr VARCHAR(200);
DCL-S name    VARCHAR(30);
DCL-S salary  PACKED(9:2);

connStr = 'Driver={SQLite3};Database=myapp.sqlite;';
EXEC SQL CONNECT USING :connStr;

// Declare a cursor
EXEC SQL DECLARE empCur CURSOR FOR
  SELECT name, salary FROM employees
  ORDER BY name;

// Open and fetch in a loop
EXEC SQL OPEN empCur;

EXEC SQL FETCH empCur INTO :name, :salary;
DOW SQLCOD = 0;
  DSPLY (name + ': ' + %CHAR(salary));
  EXEC SQL FETCH empCur INTO :name, :salary;
ENDDO;

EXEC SQL CLOSE empCur;
EXEC SQL DISCONNECT;

Cursors with Data Structures

You can fetch directly into a qualified data structure:

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

EXEC SQL DECLARE c1 CURSOR FOR
  SELECT id, name, salary FROM employees ORDER BY id;

EXEC SQL OPEN c1;

EXEC SQL FETCH NEXT FROM c1 INTO :emp;
DOW SQLSTATE < '02000';
  DSPLY (%CHAR(emp.id) + ' ' + %TRIM(emp.name));
  EXEC SQL FETCH NEXT FROM c1 INTO :emp;
ENDDO;

EXEC SQL CLOSE c1;

Parameterized Cursors

Host variables in the WHERE clause are bound when the cursor is opened:

DCL-S minSalary PACKED(9:2);

EXEC SQL DECLARE salCur CURSOR FOR
  SELECT name, salary FROM employees
  WHERE salary > :minSalary;

minSalary = 70000.00;
EXEC SQL OPEN salCur;

EXEC SQL FETCH salCur INTO :name, :salary;
DOW SQLCOD = 0;
  DSPLY name;
  EXEC SQL FETCH salCur INTO :name, :salary;
ENDDO;

EXEC SQL CLOSE salCur;

Dynamic SQL

DCL-S sqlStr VARCHAR(256);
DCL-S id     INT(10);
DCL-S name   VARCHAR(50);

// Execute Immediate — for DDL or one-off statements
sqlStr = 'CREATE TABLE temp (id INTEGER, name TEXT)';
EXEC SQL EXECUTE IMMEDIATE :sqlStr;

// Prepare + Execute — for repeated statements with parameters
sqlStr = 'INSERT INTO temp VALUES(?, ?)';
EXEC SQL PREPARE ins1 FROM :sqlStr;

id = 1;
name = 'Alice';
EXEC SQL EXECUTE ins1 USING :id, :name;

id = 2;
name = 'Bob';
EXEC SQL EXECUTE ins1 USING :id, :name;

Multi-Row Operations

DCL-S ids      INT(10) DIM(10);
DCL-S names    VARCHAR(50) DIM(10);
DCL-S nRows    INT(10);

// Multi-row insert from arrays
ids(1) = 1;
names(1) = 'Alice';
ids(2) = 2;
names(2) = 'Bob';
ids(3) = 3;
names(3) = 'Charlie';

nRows = 3;
EXEC SQL INSERT INTO employees (id, name)
  VALUES(:ids, :names)
  FOR :nRows ROWS;

// Multi-row fetch into arrays
EXEC SQL DECLARE c1 CURSOR FOR
  SELECT id, name FROM employees ORDER BY id;
EXEC SQL OPEN c1;

nRows = 10;
EXEC SQL FETCH c1 FOR :nRows ROWS
  INTO :ids, :names;

EXEC SQL CLOSE c1;

Transaction Control

EXEC SQL INSERT INTO accounts (id, balance) VALUES(1, 1000.00);
EXEC SQL INSERT INTO accounts (id, balance) VALUES(2, 500.00);

// Savepoint for partial rollback
EXEC SQL SAVEPOINT before_transfer;

EXEC SQL UPDATE accounts SET balance = balance - 200 WHERE id = 1;
EXEC SQL UPDATE accounts SET balance = balance + 200 WHERE id = 2;

// Something went wrong? Rollback to savepoint
EXEC SQL ROLLBACK TO SAVEPOINT before_transfer;

// Or commit everything
EXEC SQL COMMIT;

GET DIAGNOSTICS

DCL-S rowCount INT(10);

EXEC SQL UPDATE employees SET salary = salary * 1.1
  WHERE department = 'ENG';

EXEC SQL GET DIAGNOSTICS :rowCount = ROW_COUNT;
DSPLY ('Updated ' + %CHAR(rowCount) + ' employees');

SQLCODE and SQLSTATE

After each SQL statement, you can check:

Variable	Description
SQLCOD	Numeric return code (0 = success, 100 = not found, negative = error)
SQLSTT	5-character SQLSTATE code ('00000' = success, '02000' = not found)

EXEC SQL SELECT name INTO :empName FROM employees WHERE id = 999;

IF SQLCOD = 100;
  DSPLY 'Employee not found';
ELSEIF SQLCOD < 0;
  DSPLY ('SQL error: ' + %CHAR(SQLCOD));
ENDIF;