Page History
7.109 SELECT_SQL
| Note |
|---|
| Note: Usage options Go to SELECT_SQL Statement Wizard |
...
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
| Info |
|---|
| 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. |
...
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 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"
...
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). |
...
| Panel | ||
|---|---|---|
| ||
Required | ||
| No Format | ||
| Required
SELECT_SQL ---- FIELDS --------- field name ----- *SAME -----------> | SQL field source |
1000 max -------------- >>-- FROM_FILES ------- file name ---- correlation ----- -> | |
20 max------------ ----------------------------------------------------------------------- Optional >-- WHEREOptional >-- WHERE --------- 'SQL where condition' ------------- > >> >-- GROUP_BY ----- 'SQL group by clause' -------------> >>-- HAVING ------- 'SQL having condition' ------------ > >> >-- ORDER_BY ----- 'SQL order by parameter' --------- > >> >-- DISTINCT ------ *NO ------------------------------> *YES >--
>-- IO_STATUS ----- field name ------------------------ > *STATUS >--> >-- IO_ERROR ----- *ABORT ----------------------------| *NEXT *RETURN label
|