• 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
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    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

  • Security Still Top Concern, IBM i Marketplace Study Says
  • Bob Langieri Shares IBM i Career Trends Outlook for 2023
  • Kisco Brings Native SMS Messaging to IBM i
  • Four Hundred Monitor, February 1
  • 2023 IBM i Predictions, Part 4
  • Power Systems Did Indeed Grow Revenues Last Year
  • The IBM Power Trap: Three Mistakes That Leave You Stuck
  • Big Blue Decrees Its 2023 IBM Champions
  • As I See It: The Good, the Bad, And The Mistaken
  • IBM i PTF Guide, Volume 25, Number 5

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

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.