Allow use of RPG procedures in embedded SQL as host "variable"

IBM does not intend to provide a solution to this request at this time, so it is being closed.

This would be a costly enhancement to implement. Embedding RPG language constructs within SQL would require the SQL precompiler to recognize and handle a new type of replacement variable. Today, the ILE RPG precompiler has limited awareness of RPG procedures.

Additionally, before we could consider implementing this enhancement, the IBM SQL Language Council would need to agree that allowing host language syntax to be used in an SQL statement is an appropriate extension of the SQL language. Since the interface between SQL and host languages is intended to be limited, approval is not guaranteed. The new SQL syntax would need to be appropriate for multiple embedded languages as well.

For these reasons, IBM has decided not to invest in this enhancement.

As an alternative, we recommend defining scalar functions and using those functions in the SQL statement.
For example, the RPG code could look something like this:

CTL-OPT Nomain;
DCL-PR RPGFUNC1 VARCHAR(128);
P1 VARCHAR(128);
END-PR;

DCL-PROC RPGFUNC1 EXPORT;
DCL-PI *N VARCHAR(128);
P1 VARCHAR(128);
END-PI;
dcl-s nbr VARCHAR(128);
nbr = P1;
return nbr;
END-PROC;

The function definition would look like this:

CREATE OR REPLACE FUNCTION QGPL.RFUNC1(
P1 VARCHAR(128)
)
RETURNS VARCHAR(128)
LANGUAGE RPGLE
PARAMETER STYLE GENERAL
EXTERNAL NAME 'QGPL/TEST(RPGFUNC1)';

The example update statement would look like this:

Exec SQL update mytable set field = qgpl.rfunc1(:x);

The CAAC has reviewed this requirement and recommends that IBM view this as a high priority requirement that is important to be addressed. This will provide benefit to an overwhelming majority of application developers.

Background: The COMMON Americas Advisory Council (CAAC) members have a broad range of experience in working with small and medium-sized IBM i customers. CAAC has a key role in working with IBM i development to help assess the value and impact of individual RFEs on the broader IBM i community, and has therefore reviewed your RFE.

For more information about CAAC, see www.common.org/caac

For more details about CAAC's role with RFEs, see http://www.ibmsystemsmag.com/Blogs/i-Can/May-2017/COMMON-Americas-Advisory-Council-%28CAAC%29-and-RFEs/

Nancy Uthke-Schmucki - CAAC Program Manager

The COMMON Europe Advisory Council (CEAC) has reviewed this requirement and recommends that IBM view this as a medium priority requirement that should be addressed.

Background: The CEAC members have a broad range of experience in working with small and medium-sized IBM i customers. CEAC has a crucial role in working with IBM i development to help assess the value and impact of individual RFEs on the broader IBM i community and has therefore reviewed your RFE.

To find out how CEAC help to shape the future of IBM i, see CEAC @ ibm.biz/BdYSYj and the article "The Five Hottest IBM i RFEs Of The Quarter" at ibm.biz/BdYSZT

Therese Eaton – CEAC Program Manager, IBM

Due to processing by IBM, this request was reassigned to have the following updated attributes:
Brand - Servers and Systems Software
Product family - Power Systems
Product - IBM i
Component - Db2 for i
Operating system - IBM i
Source - Client

For recording keeping, the previous attributes were:
Brand - Servers and Systems Software
Product family - Programming Languages
Product - Developer for Power Systems
Component - RPG/COBOL Development Tools
Operating system - IBM i
Source - Client