Page History
7.110 SELECT_SQL Free Format
| Note | ||
|---|---|---|
| title | 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.
...
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:
...
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). |
...
7.110.4 SELECT_SQL Free Format Coercions
| Panel | ||
|---|---|---|
| ||
Required SELECT_SQL ----- FIELDS -- | ||
| No Format | ||
| Required
SELECT_SQL --- FIELDS ------- field name --------------------- > >> >-- USING -------- SQL select command -----------> -- >-------------------------------------------------------------------- Optional >--Optional >-- FROM_FILES --- file name -----------------------> | |
20 max----------- >>-- IO_STATUS ---- field name ----------------------> *STATUS >--
>-- IO_ERROR ---- -*ABORT -------------------------| *NEXT *RETURN label
|