How do you use embedded SQL in RPG?

Embedded SQL in RPG (specifically ILE RPG) allows you to seamlessly integrate SQL statements directly within your RPG programs. This enables your programs to interact with the database for tasks like retrieving, inserting, updating, and deleting data. Here's a comprehensive guide:

1. Setting up your RPG program:

Compiler Directives: You need to tell the RPG compiler that your program contains embedded SQL. You do this using compiler directives. The most common approach is to use the EXEC SQL preprocessor.

**FREE  // For free-form RPG
CTL-OPT BNDDIR('QC2LE'); // Bind directory for SQL services
/COPY QSYSINC/SQLC  // Include SQL definitions (important!)?

2. Embedding SQL Statements :

You embed SQL statements within your RPG code using the EXEC SQL keywords .

EXEC SQL SELECT * FROM CUSTOMERS;

EXEC SQL INSERT INTO ORDERS (CustNo, OrderDate) VALUES (:CustNo, :OrderDate);

* Host Variables : To pass data between your RPG program and the SQL statements, you use host variables. These are RPG variables preceded by a colon (:) in the SQL statement. They act as placeholders for data.

DCL-S  CustNo       INT(10);
DCL-S  CustName     CHAR(20);
DCL-S  OrderDate    D;

EXEC SQL SELECT CustName INTO :CustName FROM CUSTOMERS WHERE CustNo = :CustNo;

3. Handling SQL Results:

INTO Clause: The INTO clause in a SELECT statement specifies the host variables where the retrieved data will be stored.
Indicators: You can use indicator variables to handle null values. An indicator variable is an integer variable associated with a host variable. It's set to -1 if the corresponding data value is null.

DCL-S  CustName     CHAR(20);
DCL-S  CustNameInd  INT(5);  // Indicator variable

EXEC SQL SELECT CustName INTO :CustName :CustNameInd FROM CUSTOMERS WHERE CustNo = :CustNo;

IF CustNameInd < 0;
  // CustName is null
ELSE;
  // CustName has a value
ENDIF;?

* SQLCA (SQL Communication Area): The SQLCA is a structure that contains information about the execution of SQL statements, including error codes. It's essential for error handling.

DCL-S  SQLCA      SQLCA; // Include the SQLCA

EXEC SQL SELECT * FROM CUSTOMERS;

IF SQLCA.SQLCODE <> 0;
  // Handle the error (SQLCA.SQLCODE contains the error code)
  Dsply ('SQL Error: ' + %char(SQLCA.SQLCODE));
ENDIF;

4. Working with Multiple Rows (Cursors):

If your SELECT statement can return multiple rows, you need to use a cursor.

// Declare the cursor
EXEC SQL DECLARE CustCursor CURSOR FOR SELECT * FROM CUSTOMERS;

// Open the cursor
EXEC SQL OPEN CustCursor;

// Fetch rows from the cursor in a loop
DOWHILE SQLCA.SQLCODE = 0;
  EXEC SQL FETCH CustCursor INTO :CustNo, :CustName;
  IF SQLCA.SQLCODE = 0; // Check after each fetch
    // Process the retrieved data
    Dsply (CustName);
  ENDIF;
ENDDO;

// Close the cursor
EXEC SQL CLOSE CustCursor;

5. Dynamic SQL:

For more flexibility, you can use dynamic SQL, where the SQL statements are built at runtime.

DCL-S  SqlStmt     VARCHAR(200);

SqlStmt = 'SELECT * FROM ' + TableName; // Build the SQL statement

EXEC SQL PREPARE MyStmt FROM :SqlStmt;
EXEC SQL EXECUTE MyStmt;

6. Error Handling:

Always check the SQLCA.SQLCODE after each SQL statement to handle potential errors.

7. Examples:

Inserting Data :

EXEC SQL INSERT INTO ORDERS (CustNo, OrderDate, OrderAmt) VALUES (:CustNo, :OrderDate, :OrderAmt);
IF SQLCA.SQLCODE <> 0;
  // Handle insert error
ENDIF;

Updating Data :

EXEC SQL UPDATE CUSTOMERS SET CustName = :NewName WHERE CustNo = :CustNo;
IF SQLCA.SQLCODE <> 0;
  // Handle update error
ENDIF;

Key Considerations :

Binding: The process of associating RPG variables with SQL host variables is called binding.
Compile Options: Ensure your compile options are set correctly for embedded SQL.
SQL Preprocessor: The EXEC SQL statements are processed by a preprocessor before the RPG compiler runs.
Performance: Be mindful of performance, especially when working with large datasets. Use indexes effectively.

Embedded SQL is a crucial technique for integrating database access into your RPG programs. By mastering its use, you can create powerful applications that leverage the full capabilities of DB2 for i.