o–This
is the simplest way of implementing SQL. In this the SQL statement is hard
coded in the program.

·Dynamic
SQL

o–The
SQL statement is dynamic in nature and can be changed at runtime based on the
parameter input

o–It
requires more resource at run-time for preparing the statement

ÜSource member type

·SQLRPG

·SQLRPGLE

ÜCompilation
command

·Create
SQL RPG Program (CRTSQLRPG)

·Create
SQL ILE RPG Object (CRTSQLRPGI)

ÜCompilation process

·The compilation of
embedded SQL is different than the normal RPG program.

·The compilation is
divided into 2 parts:

(1)SQL precompilation: To validate the embedded
SQL in the program and convert those into dynamic program calls. If there is
any error in host variable or in SQL statement selection fields or any other
sql statement related error, then the compilation stops and SQL precompilation
report is generated.

We
can check the converted source by seeing the spool file of the program. The
converted source includes SQL communication area and many sql-call commands to
open, process and close sql cursors.

·Some other steps can also be involved in case of dynamic
embedded SQL:

EXECUTE IMMEDIATE
Prepares and executes a statement that does not use any host variables.
PREPARE
Turns the character string form of the SQL statement into an executable
form of the statement, which when executed gives the result set.
EXECUTE
Executes a previously prepared SQL statement.

·An SQLCA is a data
structure whose subfields get updated after the execution of any embedded SQL.

·The subfields most used
in SQLCA are SQLCOD and SQLSTT.

SQLCOD
It Contains SQL return code which can be tested for successful or
unsuccessful execution of an sql statement.
If SQLCOD=0, represents successful execution.
If SQLCOD>0, represents successful execution with some warnings.
If SQLCOD<0, represents unsuccessful execution with errors.
e.g.
SQLCOD=100; Row not found
SQLCOD=-552; Not authorized to an object

SQLSTATE
SQLSTATE is similar to SQLCODE but it returns more specific status code.
It consists of five characters in which the first two characters comprise
of a code that defines the class as depicted below:
Class '00': It represents successful execution.
Class '01': It also represents successful execution but with warnings.
Class '02' It represents no data found.
All other classes represents unsuccessful executions.

In SENSETIVE cursor the
current updated data is fetched from the database. Hence if any kind of
updating or insertion happens in the database the cursor is modified
accordingly to provide the latest data.

In INSENSITIVE cursor,
the data is copied to a temporary memory and then the cursor fetches the data
from there. Therefore, modifications made to the database tables are not
reflected in the data returned by fetches made to this cursor.