Unlike the standard SELECT command, which uses native IBM i database access, the SELECT_SQL command uses the SQL/400 product to perform database access.
There are two forms of the SELECT_SQL command. The first, which is documented in this section, is heavily structured helping to ensure the SQL is correct and object names that differ between platforms are catered for but it restricts the type of SELECT statements to quite simple ones. The other form of SELECT_SQL is free-format. Any SELECT statement can be entered that the database engine accepts as valid syntax, but LANSA does not attempt to make object names compatible across platforms. These two differences make it more likely that the SQL will not execute as expected across different databases. See SELECT_SQL Free Format for further information.
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"')
The method of implementing SELECT_SQL differs between objects generated as RPG on IBM i and objects generated as C. RPG implements SELECT_SQL in static embedded SQL. C implements SELECT_SQL in a call level interface (CLI) and thus is dynamic. The effect of this distinction is described below where relevant.
Before attempting to use SELECT_SQL you must be aware of the following:
1. To compile functions containing SELECT_SQL commands these licensed products are required:
For IBM i RPG Functions:
IBM - SQL DevKit
For C executables:
No other products required
If an IBM iRPG application using SELECT_SQL is ported in compiled form from one IBM i to another, it can still be executed, even if the target machine does not have the IBM licensed product installed. However, this situation will cause problems if the need to recompile the application on the target machine ever arises.
2. Information accessed via SELECT_SQL is for read only. If you wish to update information it is often easier to use the standard SELECT command.
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. 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.
5. 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 WHERE 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.
6. These notes assume that you are 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.
7. Very limited checking is performed on the correctness of the WHERE, GROUP_BY, HAVING and ORDER_BY parameters.
8. SELECT_SQL does not use the IO Modules/OAMs so it bypasses the repository validation and triggers.
8. When a file is deployed on non-IBM i platforms, by default the table is created using the target partition's data library. But, calls to SELECT_SQL have compiled in the source partition's data library. So if the names are different, you must use the DEFINE_OVERRIDE_FILE Built-In Function to change the table owner.
If an SQL Function is incorrectly quoted by SELECT_SQL, it will cause an error. With SQL Server the error may be "SQL error code 16954…Executing SQL directly; no cursor". Other error codes may occur for the same reason. Other databases will have different error codes.
This occurs when a Function is not known by LANSA and so the word is presumed to be an identifier and is quoted. The workaround for this is to use the SELECT_SQL Free Format version of the command.
IBM i RPG Functions
If your command is incorrect then there are 2 possible points where it will fail:
If your command is incorrect then the following diagnosis is possible:
IBM i RPG Functions Only
SELECT_SQL FIELDS(#A$ #B) FROM_FILES((#MYFILE)) WHERE('A_ = ''A VALUE''')
DISPLAY FIELDS(#A$ #B)
will work correctly on non-IBM i platforms but will fail on IBM i. Visual LANSA will issue warning PRC1065 if A_ is not a physical field in one of the files in the FROM_FILES parameter. A portable way to write this so that it executes on all LANSA platforms is as follows:
SELECT_SQL FIELDS(#A$ #B) FROM_FILES((#MYFILE)) WHERE('A$ = ''A VALUE''')
DISPLAY FIELDS(#A$ #B)
Visual LANSA C Functions Only
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"
Messages issued at build time by Visual LANSA
Ambiguous. Field #A$ exists in more than one file and they use different naming algorithms.
This message is reporting about the SQL name that will be used for the field at runtime. There are two further messages which follow this message which provide more detail.The generator decides on the naming algorithm to use based on the following precedence: (1) Older Visual LANSA Files use LANSA mangled names, like #A$ becomes A_; (2) Naming Level 1 files which use LANSA-defined names, that is, the SQL name is the same as the field name; (3) VL Other File naming or IBM i Other File naming, which ever one appears first in the FROM_FILES parameter.
It is not necessary to change the RDML to eliminate the message. It depends on which file's data you need to access. If the default behaviour is not wanted, then add an SQL source parameter with the real name that is needed.
The following warnings should be eliminated to improve success at runtime and when running on IBM i.
|** WARNING: Name is not a defined field. Correct it for portability.
The field name may be a real column in one of the files and so the select will work, but to work on all LANSA supported databases a field name must be used (without the hash character).
|** WARNING: Field <afield> is not a physical field in any of the files in the FROM_FILES parameter.
LANSA checks if a name specified in SQL is known to LANSA in one of the files in the FROM_FILES parameter. It checks if the name is a LANSA name, a converted name or a column rename. It also checks if it is a reserved SQL keyword. If it is none of these, then this warning is displayed:
This can be caused either be using the column name instead of the field name in which case the SQL will still work on Visual LANSA, or because the field is not correct and so will fail at runtime.
| ** Fields A$ and A_ both resolve to A_ so A_ in SELECT_SQL will be set with Non-IBM i text A_
Two or more fields that resolve to the same name mean that the generated code cannot tell them apart and so a compile error would occur. So, for backward compatibility, SELECT_SQL uses a fixed literal value so the compile will succeed. But, this may not execute on IBM i. Change your code so that it does not use both these matching Fields in the one Function.
For example, the column name has been fixed at A_, so it will not run on IBM i. Use A$ instead.
When using multiple platforms, you must take into consideration the length of the field names used by each of the platforms. Refer to the WHERE parameter.
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).
SELECT_SQL --- FIELDS ------- field name --- *SAME ----------->
| SQL field source |
------ 1000 max --------------
>-- FROM_FILES ------- file name -- correlation --->
------------ 20 max-----------
>-- WHERE -------- 'SQL where condition' ---------->
>-- GROUP_BY ----- 'SQL group by clause' ---------->
>-- HAVING ------- 'SQL having condition' --------->
>-- ORDER_BY ----- 'SQL order by parameter' ------->
>-- DISTINCT ----- *NO ---------------------------->
>-- IO_STATUS ---- field name --------------------->
>-- IO_ERROR ----- *ABORT -------------------------|