8 19 SELECT_SQL Free Format

LANSA Technical

8.19 SELECT_SQL Free Format

There are two forms of the SELECT_SQL command. This section describes the free format version which allows any SQL that is valid for the particular database engine. No parsing is performed of the SQL either at compile time or runtime. The entered SQL command is passed exactly as it is to the database engine. It is the responsibility of the RDML programmer to ensure that the data returned by the database engine matches the list of fields in the FIELDS parameter. See SELECT_SQL for the other form of SELECT_SQL.

This form of the SELECT_SQL command can only be used in RDMLX functions and components.

The SELECT_SQL command is used in conjunction with the ENDSELECT command to form a "loop" to process one or more rows (records) from one or more tables (files).

For example, the following SELECT_SQL / ENDSELECT loop selects all values of product and quantity from the table ORDLIN and places them, one by one, in a list:

----> DEF_LIST NAME(#ALIST) FIELDS(#PRODUCT #QUANTITY)
--> SELECT_SQL FIELDS(#PRODUCT #QUANTITY)
| USING('SELECT "PRODUCT", "QUANTITY" FROM "MYDTALIB"."ORDLIN"')
|
| ADD_ENTRY(#ALIST)
|
---- ENDSELECT

Before attempting to use free format SELECT_SQL you must be aware of the following:

1. Information accessed via SELECT_SQL is for read only. If you use INSERT or UPDATE statements in your USING parameter you do so at your own risk.

2. SELECT_SQL does not use the IO Modules/OAMs so it bypasses the repository validation and triggers.

3. The SELECT_SQL command is primarily intended for performing complex extract/join/summary extractions from one or more SQL database tables (files) for output to reports, screens or other tables. It is not intended for use in high volume or heavy use interactive applications.

With that intention in mind, it must be balanced by the fact that SELECT_SQL is a very powerful and useful command that can vastly simplify and speed up most join/extract/summary applications, no matter whether the results are to be directed to a screen, a printer, or into another file (table).

4. The SELECT_SQL command provides very powerful database extract/join/summarize capabilities that are directly supported by the SQL database facilities. However, the current IBM i implementation of SQL may require and use significant resource in some situations. It is entirely the responsibility of the user to compare the large benefits of this command, with its resource utilization, and to decide whether it is being correctly used. One of the factors to consider is whether the USING parameter uses any non-key fields. If it does, then SELECT_SQL will probably be quicker than SELECT. Otherwise SELECT will probably be quicker. This is especially important when developing the program on Visual LANSA first with the intention of also running it on IBM i. This is because Visual LANSA has much fewer performance differences between SELECT and SELECT_SQL.

5. This section assumes that the user is familiar with the SQL 'SELECT' command. This section is about how the SQL 'SELECT' command is accessed directly from RDML functions, not about the syntax, format and uses of the SQL 'SELECT' command.

If your command is incorrect then the following diagnosis is possible:

A useful technique when working with SQL is to use interactive SQL to "test case" your command (and its syntax) before compiling it into a SELECT_SQL command.
At execution time. Compiling the SELECT_SQL free format command proves very little. Almost all the parsing is performed by the SQL database engine. In this case examine all the resulting error messages for the exact cause.

When dealing with an execution time error, the use of trace on the function will allow the capture of the exact SQL that the SELECT_SQL command has generated. Open the latest trace file and search for "***ERROR". This will be the same text as in the error messages. Go back 8 lines or so to the "Preparing" message and you will find the SELECT statement that caused the error. You can copy and paste this into interactive SQL to further diagnose the problem.
One of the most common execution errors, apart from a syntax errors is that the list of fields does not match the data returned by the SQL statement in the USING parameter.
When reporting issues with SELECT_SQL to support you must provide the trace file and the generated C source code.

The extensive use of the SELECT_SQL command is not recommended for the following reasons:

The SQL access commands are imbedded directly into the RDML function. DBMS access is direct and not done via IOM/OAM access routines. This approach may compromise the use of before and after read triggers and the use of the "thin client" designs implemented via LANSA/SuperServer.
If the contents of SELECT_SQL is sourced from a field on a screen then it is possible for an end user to perform more than a select. It is especially easy in this Free Format version where this code is possible:

REQUEST FIELD(#ANYSQL)

Select_Sql Fields(#STD_NUM) Using(#ANYSQL)

endselect.

and the end user could enter this on the screen: "delete from mylib.afile;select count(*) from mylib.afile"

The use of imbedded SQL features and facilities may introduce platform dependencies into your applications. Not all SQL facilities are supported by all DBMSs. By bypassing the IOM/OAM associated with the table, you are bypassing the feature isolation it provides. Using SQL features and facilities that are DBMS defined, platform dependent extensions, is solely at the discretion of, and the responsibility of, the application designer.
Where SELECT_SQL is to be used, you should isolate the use within a specific function, separate from any user interface operations. This will allow the function to be invoked as an "RPC" (Remote Procedure Call) in the client design models.

Portability Considerations

Do NOT use this command to connect from Visual LANSA to a database on the IBM i. If you use the SELECT_SQL command to connect from Visual LANSA to an IBM i Database, it will access the Database on the PC and not on the IBM i. For this type of connection, you should use a remote procedure call (i.e call_server_function).

Also See

8.19.1 SELECT_SQL Free Format Parameters

8.19.2 SELECT_SQL Free Format Examples

8.19.3 SELECT_SQL Free Format References

8.19.4 SELECT_SQL Free Format Coercions

Required

SELECT_SQL --- FIELDS ------- field name --------------------->

>-- USING -------- SQL select command ------------->

-----------------------------------------------------------------

Optional

>-- FROM_FILES --- file name ---------------------->

| |