7 109 SELECT

You are here:

7.109 SELECT_SQL

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"')

| ADD_ENTRY(#ALIST)

---- ENDSELECT

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.

Error Handling

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:

When you compile the RDML function. The SQL command preprocessor will indicate an error in the command and LANSA will interpret this as a failure to compile the resulting RPG. In this situation the SQL commands embedded in the resulting RPG will have to be examined for error message details.

At execution time. Even if the application compiles SQL may cause it to fail when the SELECT_SQL command is actually executed. In this case examine all the resulting error messages for the exact cause.
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.
When dealing with an execution time error, the use of debug on the function will cause SQL to present useful error analysis information. Note that this feature is provided by SQL/400, not by LANSA, but it will work in conjunction with normal LANSA debug mode (IBM i only).

C Executables

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

When you build the function/component warning messages are displayed. Some of these messages are described in a table below.

Compiling the function/component will not provide any further information as the SQL is evaluated at execution time. That is, the SQL is dynamic.

At execution time. Even if the application compiles SQL may cause it to fail when the SELECT_SQL command is actually executed. In this case examine all the resulting error messages for the exact cause.

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.

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.

When reporting issues with SELECT_SQL to support you must provide the trace file and the generated C source code.

IBM i RPG Functions Only

Cross-reference information is only taken from the FIELDS and FROM_FILES parameters. References to fields and files embedded in other parameters of this command are not reflected into the LANSA cross-reference facility in the current release.

The database's column name must be used when accessing through SQL. C executables can use either the database's column name or the field name that LANSA knows the column by. This can be different when using Naming Level 0 files. If the field name is used (without the #), LANSA converts it to a column name at runtime. This allows the name used at execution time to be portable between all platforms. All of the parameters that accept a column name exhibit this behaviour. For example, this RDML using Naming Level 0 file #MYFILE:

SELECT_SQL FIELDS(#A$ #B) FROM_FILES((#MYFILE)) WHERE('A_ = ''A VALUE''')

DISPLAY FIELDS(#A$ #B)

ENDSELECT

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)

ENDSELECT

Visual LANSA C Functions Only

The maximum number of SELECT_SQL commands that a single field can appear in is 50

A LANSA field used in SELECT_SQL only has one rename in the whole of a Function or Component. Renames are used by VL Other Files and IBM i Other Files. The loading of other files should ensure that a different field is created when a column matches an existing field, and thus the situation should not occur.

* SELECT/OMIT Criteria in a Logical File specifed in the FROM_FILE parameter will be ignored since CLI does not use the Logical File when retrieving data.

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 the 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.

Messages issued at build time by Visual LANSA

LII0898W

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.

PRC1064

** 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).

PRC1065

** 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.

PRC1067

** 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.

Portability Considerations

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).

Also See

7.109.1 SELECT_SQL Parameters

7.109.2 SELECT_SQL Column Names versus Column Values

7.109.3 SELECT_SQL Examples

7.109.4 SELECT_SQL References

7.109.5 SELECT_SQL Coercions

Required

SELECT_SQL --- FIELDS ------- field name --- *SAME ----------->

| SQL field source |

------ 1000 max --------------

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

| |