IBM Power Ideas Portal


This portal is to open public enhancement requests against IBM Power Systems products, including IBM i. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas

  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

Add a new idea Subscribe
Status Not under consideration
Workspace IBM i
Categories Db2 for i
Created by Guest
Created on Aug 6, 2025

RELATED IDEAS

Allow the evaluation of RPG expressions as "pseudo host variables" in EXEC SQL statements

The idea is, to allow the evaluation of RPG expressions (including the call of %BIFs and procedures) inside an EXEC SQL statement as "pseudo-host-variables".

The syntax could be like that:

exec sql select count(*)
         into :myCounter
         from mytable
         where mytable.col1 = :(%trim(myHostVariable));

 

By using parentheses after the colon, it's indicated, that an expression follows. 

As the SQL precompiler already does create EVAL statements before and after the SQL route call, to bring the host variables into SQL working fields and back into the host variables, it shouldn't be too hard, to make complex RPG expressions usable at this point.

Expressions could even be possible as left-hand targets in evaluations - like this:

exec sql set :(%subst(myHostVariable:1:13)) = upper('Hello, World!');

 

Again - the SQL precompiler already generates EVAL statements to bring the values back into the host variables.

To make is possible to access SQL column values or variables "inside" such a RPG host-expression, a new "operator" has to be introduced - like this:

exec sql declare csrMyCursor cursor for
         select mytable.col1, :(myProcedure(@mytable.col2))
         from mytable;

 

In this case the RPG procedure "myProcedure" is called with the value of the column "mytable.col2" - of course, the procedure has to return a value here - and also the parameters have to be defined as CONST or VALUE, as the value would not be "changeable" by the procedure.

Those "rules" should be enforced by the SQL precompiler during compilation.

 

 

 

 

 

 

 

 

Idea priority Medium
  • Guest
    Sep 14, 2025
    We agree that this Idea is interesting, but it is much more complex than described by the request. For one example, the SQL precompiler would need to understand the evaluation of every supported RPG construct to determine its result data type, length, etc. This information is needed by the precompiler to confirm that the SQL statement conforms to SQL rules and is dealing with a supported data type.

    Due to the complexity and cost, we do not intend to implement this Idea. The COMMON Americas Advisory Council came to the same conclusion.

    You can pass values from within an SQL statement to RPG code by define an external SQL function written in RPG. We do not plan to integrate RPG any deeper into the SQL language.

    Db2 for i development team
    IBM Power Systems Development
    Reply
  • Guest
    Aug 12, 2025

    Hi Carmelita,

    I have to disagree here - the idea would add a lot of additional value to the interaction of RPG and SQL.

    First - it would be possible to declare less working variables if it's possible to directly use a returned value of an RPG procedure. This makes code clearer und it helps to reduce clutter.

    Second - it would even solve the problem that is described in idea IBMI-I-1942 - "multi-level" data structures or arrays are not usable as host variables - example:

    exec sql fetch xyzCursor into :ds1.subds.field; // is impossible today
    exec sql fetch xyzCursor into :(ds1.subds.field); // would be possible
    exec sql fetch xyzCursor into :array(x).field; // is impossible today
    exec sql fetch xyzCursor into :(array(x).field); // would be possible

    This is because the pre-compiler should only insert the expression inside the parentheses without looking into it.

    Third - it would maybe even solve the problem which is described in idea IBMI-I-3733 - VARCHAR host variables longer than around 32000 are not usable - example:

    dcl-s longVarchar varchar(1000000) inz;
    exec sql fetch xyzCursor into :longVarchar; // is impossible today
    exec sql fetch xyzCursor into :(longVarchar); // would be possible

    Also here - the pre-compiler should only insert the expression inside the parentheses.

    Of course, if the RPG host expression is invalid, the RPG compiler has to complain - but it would do this anyway.

    So this idea solves more than one idea at a time - therefore I still think, it should be implemented.

    Kind regards,
    Daniel



    Reply
  • Admin
    Carmelita Ruvalcaba
    Aug 12, 2025

    The CAAC has reviewed this IBM Idea and recommends that IBM not implement this request.

    Adds unnecessary complexity without appreciable benefits.

    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 IBM Ideas on the broader IBM i community and has therefore reviewed your Idea.

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

    Carmelita Ruvalcaba - CAAC Program Manager

    Reply

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.