• 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
    Maxava

    Migrating to New IBM i Hardware?

    Whether you are moving to POWER9, Power10, or a cloud hosted system MAXAVA HAS YOU COVERED!

    Learn More

    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

  • LANSA Developing Business Intelligence Tool
  • Blazing The Trail For VTL In The Cloud
  • Data De-Dupe Gives VTL Customers More Options
  • Four Hundred Monitor, March 29
  • The Big Spending On IT Security Is Only Going To Get Bigger
  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13

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 © 2023 IT Jungle