An SQL INSERT statement adds one or more records to any single table in a relational database. In order to create an RPGLE source member that can use embedded SQL, you must use a source type of SQLRPGLE. To compile an SQLRPGLE source member, use the

• As400 Sql Update
• As400 Sql Date Function

'Create SQL ILE RPG Object'

• Use of EXECUTE IMMEDIATE for SQL Queries in Oracle Dynamic SQL EXECUTE IMMEDIATE Dynamic SQL Query is used directly in PL/SQL Block to use DDL Queries and certain operation which is not normally handle in PL/SQL blocks. EXECUTE IMMEDIATE is directly used in PL/SQL blocks.
• EXECUTE IMMEDIATE EXECUTE IMMEDIATE combines the basic functions of the PREPARE and EXECUTE statements. It can be used to prepare and execute an SQL statement that contains neither host variables nor parameter markers.
• EXECUTE IMMEDIATE is the most popular path to dynamic SQL in PL/SQL. With it, you can execute data definition language (DDL) statements (for example, drop a table), queries, nonquery data manipulation language (DML) statements such as inserts and deletes, and even dynamically constructed PL/SQL blocks.
• Using EXECUTE IMMEDIATE incurs the unnecessary cost of re-preparing the SQL statement each time PREPARE is like a mini-compile of its own. If required info to PREPARE a statement is not available on COMPILE TIME than the statement will be PREPARED at run time.

As400 Sql Update

(CRTSQLRPGI) command. If you are using PDM, option 14 will create a bound program, and option 15 will create a *MODULE object. If you are issuing the command manually, use the OBJTYPE parameter to indicate *PGM or *MODULE. Also, if your program updates a file that is not journaled, you must change the command option COMMIT to *NONE, otherwise the updates will not occur.

EXECUTE IMMEDIATE is simple option to execute a Dynamic SQL. Here is an example TableName = 'CUSTOMER'; SQLString = 'DELETE FROM '+%TRIM(TableName); EXECUTE IMMEDIATE: SQLString. This is a very simple example. Program gets a TableName as input parameter and program logic delete all records from that table.

Its better to use the SET OPTION SQL statement.

As400 Sql Date Function

It allows you to enforce certain program options in the code itself rather than on the compile command which you might forget to do.

Sample SQLRPGLE Program to Insert data in db2 on iSeries(AS400)

Host Variables

Host variables are always preceded in SQL by a colon. Since your RPGLE program is the 'host' of these SQL statements, the term host variable refers to any variable declared in the RPGLE program. We can use these variables when we execute SQL as parameters or as return values.

INSERT Statement

Insert statements have the following form:
INSERT INTO table (column1, [column2, ... ]) VALUES (value1, [value2, ... ])
The number of columns and values must be the same. If a column is not specified, the default value for the column is used. Shorthand may also be used, taking advantage of the order of the columns when the table was created. It is not required to specify all columns in the table since any other columns will take their default value or remain null:
INSERT INTO table VALUES (value1, [value2, ... '])
Multirow inserts
A SQL feature (since SQL-92) is the use of row value constructors to insert multiple rows at a time in a single SQL statement: