• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Dynamically Invoke SQL UDFs From RPG Code

    September 7, 2011 Hey, Mike

    I’m wondering if it is possible to use dynamic SQL (prepare/execute or execute immediate) to call a User Defined [scalar] Function (UDF).

    I understand we can use the SET statement in our code but we would like to “soft” code some RPG business logic and we are looking at having the RPG code dynamically call certain UDFs based on certain conditions. So it would be nice to create dynamic SQL statements to call certain UDFs. Thanks!

    –Brian

    Hi, Brian:

    Today is your lucky day because there is a way to do this. Starting with V5R1, the VALUES INTO statement can be dynamically prepared by an RPG program. VALUES INTO is just like SET except that it can “set” multiple values in a single statement, and the output variables are placed on the right/bottom instead of the left/top. In the following sample statement, three SQL registers are simultaneously fetched into corresponding host variables:

    EXEC SQL VALUES (CLIENT WRKSTNNAME, CLIENT_PROGRAMID, CURRENT TIMESTAMP)
             INTO :WORKSTATION, :PROGRAM, :TIME;
    

    Incidentally, the i7.1 DB2 SQL Reference manual is the first to declare that SET can be dynamically prepared. However, in my test the dynamic SET statement didn’t work; not sure if it’s an O/S bug or I did something wrong.

    Now for an RPG embedded SQL example! Please excuse the quick and dirty code (including the use of the DSPLY op-code). To start, a VALUES INTO statement is placed in a SQL string. I’ve used the built-in POWER function for a simple illustration; but you would substitute your own user-defined function name(s) as needed.

    SQL='VALUES (POWER(3,CAST(? AS INT))) INTO ?';
    

    In this case a host variable (called Exponent) will be used as the exponent input parameter for the POWER function. A second host variable (Result) will be assigned the result. Also, notice that sometimes DB2 might need a hint as to the input parameter’s data type and so CAST is used to supply this information.

    Once the SQL string is built, the PREPARE statement is used to create an executable version of the statement. The prepared statement is identified by a name the developer assigns; in this case FunctionEval. Once a statement is prepared, it can be run by name using the EXECUTE statement. (The host variables are substituted in the statement’s parameter markers from left to right; top to bottom.)

    DSQL              S            512    Varying     
    DExponent         S             10I 0 INZ(4)
    DResult           S              8F
     /Free
         SQL='VALUES (POWER(3,CAST(? AS INT))) INTO ?';
         Exec SQL
             Prepare FunctionEval From :SQL;
         If SqlCode<>*Zero;
             Dsply 'Prep Problem' '' SqlCode;
         EndIf;
         Exec SQL
            Execute FunctionEval Using :Exponent,:Result;
         If SqlCode<>*Zero;
             Dsply SqlCode '' *InLR;
         Else;
             Dsply Result '' *InLR;
         EndIf;
         *InLR=*On;
         Return;
     /End-Free
    

    The exponent value passed to the function from the “Exponent” host variable is “4”; therefore SQL will evaluate three to the fourth power and the result “81” is placed in the “Result” host variable.

    If you’re going to do much with dynamically prepared statements, I’d recommend becoming familiar with the SQL Descriptor area and Descriptor statements in the DB2 for i SQL Reference. SET DESCRIPTOR, for example, can be used to set the input parameter’s initial value and data type attributes so that a CAST would not necessarily be required to give DB2 a data type “hint.”

    Keep in mind that dynamic prepartion of SQL is relatively slow. Once a statement has been prepared, if it needs to be evaluated several times, you should continue to issue EXECUTE … USING and substitute new variable values with each execution.

    Finally, it appears that VALUES INTO can only be prepared in a high level language program like RPG, C or COBOL. My attempts to dynamically prepare a VALUES INTO statement in VBA and .NET environments were unsuccessful.

    –Mike

    Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    PowerTech:  2011 Security Event of the Year. September 22–23 in Las Vegas. RVSP today!
    VAULT400:  IBM Power7 Upgrade and HA/DR Webinars. Sept 27, Oct 25. Get a FREE white paper!
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    SunGard Launches Recover2Cloud Appreciation Rising for Web Serving on IBM i

    Leave a Reply Cancel reply

Volume 11, Number 25 -- September 7, 2011
THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
Botz & Associates, Inc.

Table of Contents

  • Adobe Flash Builder for the iSeries Programmer, Part 2
  • Dynamically Invoke SQL UDFs From RPG Code
  • Admin Alert: Starting Multiple PC5250 Sessions in a Batch File

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle