Embedded SQL

Introduction

Embedded SQL programming in IBM i, empowers developers to seamlessly integrate SQL statements within RPG programs. This integration allows for efficient database interaction and manipulation tasks directly within the AS/400 environment.

Type of Embedded SQL

• Static SQL
  • Static SQL involves SQL statements that are directly embedded within the source code of programs during compilation time.
  • These statements cannot be changed or modified during runtime.
  • Static SQL is suitable for scenarios where the SQL queries are known at compile time and do not need to be dynamically generated based on user input or other runtime conditions.
• Dynamic SQL
  • Dynamic SQL allows for the generation and execution of SQL statements during runtime.
  • Unlike static SQL, dynamic SQL statements can be constructed dynamically within the program based on runtime conditions, user input, or other variables.
  • Dynamic SQL provides greater flexibility and versatility, as it enables programs to adapt to changing requirements or conditions at runtime.

Compilation command

CRTSQLRPG – To Create SQL RPG Program

CRTSQLRPGI – To Create SQL ILE RPG Object

Compilation Process

• Compared to a typical RPG application, embedded SQL requires a different compilation process.
• There are two sections to the compilation:
  • SQL Pre-compilation: To verify the embedded SQL in the program and convert those into dynamic program calls. When a host variable, SQL statement selection field, or other SQL statement-related error is found, the compilation process ends, and a SQL pre-compilation report is produced.
  • Main Program Compilation: Only the main program is built, and a successful compilation report is produced if there are no errors in the SQL pre-compilation.

Host Variable

The values that are retrieved by your program are put into data items such as Standalone variables/arrays/data structures/indicators that are defined by your program and that are indicated by a SELECT INTO or FETCH statement’s INTO clause. The data items are called host variables.

In SQL, a host variable refers to a field(Standalone variables/arrays/data structures/indicators) in your program that you specify within an SQL statement. Typically, it serves as the source or target for the value of a column. The host variable and the corresponding column must have compatible data types. However, it’s important to note that host variables cannot be used to identify SQL objects like tables or views, except in the context of the DESCRIBE TABLE statement

Note: When you utilize a host variable instead of a literal value in an SQL statement, you provide the application program with the flexibility to process various rows in a table or view.

• In a WHERE clause: Host variables allow you to define a value in the predicate of a search condition or to substitute a literal value within an expression. For example, in SQLRPGLE:

  wkEmpID = ;
  exec sql
  Select empname into :wkEmpName from empMaster where empid = :wkEmpID;
  
  

• As a receiving area for column values (named in an INTO clause: When working with SQL, host variables allow you to define a program data area that will hold the column values of a retrieved row. The INTO clause specifies one or more host variables where you want to store the column values returned by the SQL query. This flexibility enables dynamic handling of data within your database operations. For example:

  dcl-s wkEmpID char(6) inz;
  dcl-s wkEmpName char(50) inz;
  
  exec sql
  Select empid, empname into :wkEmpID, :wkEmpName from empMaster where empDept = ‘IT’ fetch first row only;
  
  

  OR

  dcl-ds empds      dim(200) qualified;
   wkempID char(6) inz;
   wkempSal packed(11:2) inz;
  end-ds;
  
  exec sql
  Select empid, empSalary into :empDS from empMaster where empDept = ‘IT’ fetch first 200 rows only;
  
  

SQL Cursor

In IBM i, SQL cursors are essential constructs used to handle the result set returned by SQL queries within embedded SQL statements. A cursor allows programs to iterate over the rows of a result set sequentially, enabling row-level processing and manipulation of data retrieved from the database.

Creation Steps of Cursor:

• Prepare SQL statement (Optional)

  dcl-ds empData extname(‘EMPMASTER’) qualified;
  end-ds;
  
  SQLstring = ‘Select * from empMaster where empid = ’ + wkEmpId;
  exec sql
  prepare SQLstmt from :SQLstring;
  

• Declare the Cursor

  exec sql
  declare emp cursor for SQLstmt;
  
  

• Open the Cursor

  exec sql open emp;

• Fetch from Cursor

          exec sql
  fetch from emp into :empData;
  dow sqlcode =0;
  
  {logic block}
  
  exec sql
  fetch from emp into :empData;
  enddo;
  
  

• After all the records have been fetched, close the Cursor

              exec sql close emp;
  

Type of SQL Cursor:

• Positioning based Cursor
  Placing the cursor dynamically or sequentially at the resulting table rows divides the cursor into two types.
  • Serial/Sequential Cursor
  • Scrollable Cursor
• Data-reflection-based CursorAfter opening the specific cursor, the modified data reflection into the cursor result table separates the cursor into two types.
  • Sensitive Cursor
  • Insensitive Cursor

Fetch for Rows:

To use the multiple-row FETCH statement with the host data structure array, the program must define a host data structure array that can be used by SQL.

• Number of Looping can be lowered down when you can fetch multiple rows at once.
• Declare your data structure with a dimension at the beginning.
• Declaring, opening, and closing the cursor is still necessary
• Fetch the number of rows equal to that data structure array size instead of a loop.

dcl-ds empData extname(‘EMPMASTER’) qualified dim(100);
end-ds;
dcl-s maxRows zoned(3) inz(100);

exec sql declare getData cursor for
 select * from empMaster;
exec sql open getData;
exec sql fetch first from getData for :maxRows into :empData;
rc = SQLER3; //SQLERR3 gives count of impacted rows from sql
dow rc > 0;
for i = 1 to rc;
{logic block}
endfor;
exec sql fetch next from getData for :maxRows into :empData;
rc = SQLER3;
enddo;

Prepare SQL Statement

The PREPARE statement in the AS400 system is a powerful tool used by application programs to dynamically prepare SQL statements for execution.

• The PREPARE statement creates an executable SQL statement, known as a prepared statement, from a character string form of the statement called a statement string.
• It allows you to dynamically construct SQL statements at runtime, which is particularly useful when you need to parameterize your queries or execute dynamic SQL.
• Essentially, it prepares an SQL statement for later execution.

Execute Immediate SQL Statement

The EXECUTE IMMEDIATE statement offers a dynamic approach to executing SQL statements within a program. Unlike prepared SQL statements, which are pre-compiled and parameter-bound before execution, EXECUTE IMMEDIATE enables the execution of dynamically constructed SQL statements at runtime.

• The EXECUTE IMMEDIATE statement accepts a character string containing the SQL statement to be executed. This string can be dynamically constructed within the program.
• EXECUTE IMMEDIATE is particularly useful in scenarios where the structure or content of SQL statements cannot be determined statically at compile time.

Error Handling Indicator

SQLCODE:

• SQLCODE is a variable that stores the result code of the most recently executed SQL statement within an embedded SQL program.
• SQLCODE indicates the outcome of an SQL operation. Different values have different significance for execution of the statement. Most common values are 0 and 100 ( 0 indicates successful execution; 100 indicates end of records/no record impacted)and negative values indicate errors.
• After executing an SQL statement, check the value of SQLCODE to determine the outcome of the operation. Based on the result, appropriate actions can be taken.

SQLSTATE:

• SQLSTATE is a character variable that stores a five-character SQL state code representing the outcome of the most recent SQL operation.
• It offers additional details regarding the type of error or warning encountered during the execution of an SQL statement.

In IBM i embedded SQL programs, developers commonly utilize SQLCODE and SQLSTATE to identify and manage errors. This practice enables robust error handling and effective exception management within their applications.

Usage

• The dynamic usage of files within a program without needing F-specs.
• Data Retrieval and Manipulation
• Performance Optimization
• Data Integrity and Security
• Transaction Management
• Full SQL Capabilities
• Error Handling and Diagnostics