Foxdeli530

SQLRPGLE Select Statement for db2 in iSeries (AS400) - Fetch using Cursor 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 'Create SQL ILE RPG Object' (CRTSQLRPGI) command. Figure 1: Use EXECUTE IMMEDIATE in an RPG program. The only parameter EXECUTE IMMEDIATE expects is a character variable containing an SQL statement (limited to those shown in the list above). In the program snippet in Figure 1, the SQL statement is built dynamically and then processed by EXECUTE IMMEDIATE.

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 'Create SQL ILE RPG Object' (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.

1. Embedded SQL Example in AS400 using EXECUTE IMMEDIATE is given below- EVAL SQLSTMT1= 'SELECT COUNT(.) ARR(7) FROM ARR(1)'; C/EXEC SQL; C+ EXECUTE IMMEDIATE:SQLSTMT1; C/END-EXEC. Www.go4as400.com - A programming guide to learn AS400. Toggle navigation AS400 Tutorial AS400 chapters AS400 FAQ AS400 E-BOOKS AS400 COURSE.
2. Using Execute Immediate we can parse and execute any SQL statement or a PL/SQL block dynamically in Oracle Database. And by dynamically I mean at runtime. Execute immediate takes only one argument. It can either be a SQL statement or a PL/SQL block. Compiler treats the arguments of the Execute Immediate statement as the string of VARCHAR2 data.

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.
Its better to use the SET OPTION SQL statement. 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:

The EXECUTEIMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance. For more information, see 'Using the EXECUTE IMMEDIATE Statement in PL/SQL'.

Syntax

Description of the illustration execute_immediate_statement.gif

Keyword and Parameter Description

As400 Sql Update

bind_argument

An expression whose value is passed to the dynamic SQL statement, or a variable that stores a value returned by the dynamic SQL statement.

BULK COLLECT

Stores result values in one or more collections, for faster queries than loops with FETCH statements. For more information, see 'Reducing Loop Overhead for DML Statements and Queries with Bulk SQL'.

collection_name

A declared collection into which select_item values are fetched. For each select_item, there must be a corresponding, type-compatible collection in the list.

host_array_name

An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which select_item values are fetched. For each select_item, there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.

define_variable

A variable that stores a selected column value.

dynamic_string

A string literal, variable, or expression that represents a single SQL statement or a PL/SQL block. It must be of type CHAR or VARCHAR2, not NCHAR or NVARCHAR2.

INTO ...

Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.

record_name

A user-defined or %ROWTYPE record that stores a selected row.

returning_clause

Returns values from inserted rows, eliminating the need to SELECT the rows afterward. You can retrieve the column values into variables or into collections. You cannot use the RETURNING clause for remote or parallel inserts. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined. For the syntax of returning_clause, see the 'RETURNING INTO Clause'.

USING ...

Specifies a list of input and/or output bind arguments. The parameter mode defaults to IN.

Usage Notes

Except for multi-row queries, the dynamic string can contain any SQL statement (without the final semicolon) or any PL/SQL block (with the final semicolon). The string can also contain placeholders for bind arguments. You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

You can place all bind arguments in the USING clause. The default parameter mode is IN. For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNINGINTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNINGINTO clause, the USING clause can contain only IN arguments.

At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNINGINTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE, FALSE, and NULL). To pass nulls to the dynamic string, you must use a workaround. See 'Passing Nulls to Dynamic SQL'.

Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections, LOBs, instances of an object type, and refs. Dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be BOOLEANs or index-by tables. The only exception is that a PL/SQL record can appear in the INTO clause.

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. You still incur some overhead, because EXECUTEIMMEDIATE re-prepares the dynamic string before every execution.

The string argument to the EXECUTE IMMEDIATE command cannot be one of the national character types, such as NCHAR or NVARCHAR2.

Note: When using dynamic SQL with PL/SQL, be aware of the risks of SQL injection, which is a possible security issue. For more information on SQL injection and possible problems, see Oracle Database Application Developer's Guide - Fundamentals. You can also search for 'SQL injection' on the Oracle Technology Network at http://www.oracle.com/technology/

As400 Sql Update Examples