Embedded SQL in RPGLE allows you to incorporate SQL statements directly within your RPG programs. This powerful feature combines the strengths of both SQL and RPGLE, making it easier to perform complex database operations. Here’s an overview of how to use embedded SQL in RPGLE:
1. Introduction to Embedded SQL
- Embedded SQL: SQL statements are embedded within RPGLE code, providing seamless interaction with the database.
- Precompilation: The RPGLE source code containing SQL statements is precompiled to convert SQL statements into native RPG operations.
2. Basic Syntax
Embedded SQL statements are written between EXEC SQL and END-EXEC tags. Here’s a simple example to fetch data from a table:
rpg
**FREE
// Declare variables
DCL-S custID INT(10);
DCL-S custName CHAR(50);
// Fetch a customer name from the database
EXEC SQL
SELECT customer_name INTO :custName
FROM customers
WHERE customer_id = :custID
END-EXEC;
// Display the customer name
DSPLY custName;
*INLR = *ON;
RETURN;
3. Common SQL Operations
SELECT Statement: Retrieve data from the database.
rpg
EXEC SQL
SELECT field1, field2 INTO :var1, :var2
FROM mytable
WHERE condition
END-EXEC;
INSERT Statement: Insert new records into the database.
rpg
EXEC SQL
INSERT INTO mytable (field1, field2)
VALUES (:var1, :var2)
END-EXEC;
UPDATE Statement: Update existing records in the database.
rpg
EXEC SQL
UPDATE mytable
SET field1 = :var1, field2 = :var2
WHERE condition
END-EXEC;
DELETE Statement: Delete records from the database.
rpg
EXEC SQL
DELETE FROM mytable
WHERE condition
END-EXEC;
4. Handling Cursors
Cursors are used to process multiple rows returned by a query.
Declare Cursor:
rpg
EXEC SQL DECLARE myCursor CURSOR FOR
SELECT field1, field2 FROM mytable WHERE condition;
END-EXEC;
Open Cursor:
rpg
EXEC SQL OPEN myCursor; END-EXEC;
Fetch from Cursor:
rpg
DCL-S var1 CHAR(10);
DCL-S var2 CHAR(20);
EXEC SQL FETCH myCursor INTO :var1, :var2; END-EXEC;
Close Cursor:
rpg
EXEC SQL CLOSE myCursor; END-EXEC;
5. Error Handling
Use SQL communication area (SQLCA) to handle errors.
rpg
// Include SQL communication area
DCL-DS SQLCA;
SQLCODE INT(10); // SQL status code
// Additional fields
END-DS;
// Check SQLCODE after SQL operation
IF SQLCODE <> 0;
// Handle the error
DSPLY ‘SQL Error: ‘ + %CHAR(SQLCODE);
ENDIF;
6. Practical Example
Here’s a more complete example that demonstrates fetching and processing multiple records using a cursor:
rpg
**FREE
// Declare variables
DCL-S custID INT(10);
DCL-S custName CHAR(50);
DCL-S custCity CHAR(50);
// Declare cursor
EXEC SQL DECLARE custCursor CURSOR FOR
SELECT customer_id, customer_name, customer_city
FROM customers
WHERE customer_status = ‘Active’;
// Open cursor
EXEC SQL OPEN custCursor;
// Fetch and process records
DOW SQLCODE = 0;
EXEC SQL FETCH custCursor INTO :custID, :custName, :custCity;
IF SQLCODE = 0;
// Process the record
DSPLY ‘ID: ‘ + %CHAR(custID);
DSPLY ‘Name: ‘ + custName;
DSPLY ‘City: ‘ + custCity;
ENDIF;
ENDDO;
// Close cursor
EXEC SQL CLOSE custCursor;
*INLR = *ON;
RETURN;
In this example:
- A cursor custCursor is declared to fetch active customers from the customers table.
- The cursor is opened, and records are fetched in a loop.
- Each record is processed (displayed) until the end of the result set.
Using embedded SQL in RPGLE enhances the flexibility and functionality of your programs, making it easier to perform complex database operations efficiently. If you have specific questions or need more detailed examples, feel free to ask!
