7.110 SELECT_SQL Free Format

Note: Usage options

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:





|         ADD_ENTRY(#ALIST)



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.  DO NOT break SELECT_SQL loops with GOTO commands as this may leave the SQL cursor open. You should use the LEAVE RDML command to exit SELECT_SQL loops instead.

6.  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:

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


Select_Sql Fields(#STD_NUM) Using(#ANYSQL)


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


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

7.110.1 SELECT_SQL Free Format Parameters

7.110.2 SELECT_SQL Free Format Examples

7.110.3 SELECT_SQL Free Format References

7.110.4 SELECT_SQL Free Format Coercions



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


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





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

                                |                              |

                                 ------------ 20 max-----------


             >-- IO_STATUS ---- field name --------------------->



             >-- IO_ERROR ----- *ABORT -------------------------|