• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL Functions You Didn’t Know You Had, Part 2

    November 12, 2014 Ted Holt

    In SQL Functions You Didn’t Know You Had, Part 1, I showed you how to make an SQL function that runs a subprocedure in a service program. What I showed you is fine up to a point, but it’s not the entire story.

    The technique that I shared with you breaks down when null values enter the picture. First, the subprocedures in the ADDR service program cannot accept null arguments into parameters. Second, those subprocedures cannot return a null value to the SQL query.

    These behaviors are specified in two clauses of the CREATE FUNCTION statement:

    • PARAMETER STYLE GENERAL means that parameter list in the CREATE FUNCTION statement exactly matches the parameter list in the subprocedure of the service program.
    • RETURNS NULL ON NULL INPUT tells the system to automatically return a null value, without running the function, if any input argument is null.

    And that’s why we didn’t have to talk about nulls in Part 1.

    RETURNS NULL ON NULL INPUT is usually all the support for nulls that I need. But that may not always be the case. Assume, for example, a price quotation subprocedure. Given a customer account number, item number and quantity, the subprocedure returns a price. If any of those values is not provided, the routine cannot return a price and must therefore return a null. RETURNS NULL ON NULL INPUT handles that case.

    But what if the values are all there, and one of the values is invalid? In that case, I might want the function to return a null value. (An alternative would be to have the function raise an error, which would cause the query to cancel.)

    To return to the subject at hand, I’ve borrowed a subprocedure from Aaron Bartell. Aaron’s IFS_file2var subprocedure copies the contents of a stream file into a variable. This function is somewhat like the GET_CLOB_FROM_FILE function that IBM added to DB2 for i as part of 7.1. You can get Aaron’s code here.

    I tweaked Aaron’s code a bit, not because there was anything wrong with it, but because I needed it to behave slightly differently for this illustration.

    Aaron’s IFS_file2Var subprocedure does all the work, but it doesn’t handle nulls. I added a wrapper subprocedure, LoadStmf, in order to add null support. Here’s the revised module source, from which I built my service program.

    H nomain
    
      //----------------------
      // @Author: Aaron Bartell
      // @Descr: Read an IFS file into an RPG string variable
      // @Butchered by: Ted Holt
      //----------------------
    
     /copy prototypes,stmf
    
    P IFS_file2Var    b                   export
    D IFS_file2Var    pi         65535a   varying
    D  pFile                       256a   value
    D  pSuccess                       n
    
    D open            pr            10i 0 extproc('open')
    D  filename                       *   value options(*string)
    D  openflags                    10i 0 value
    D  mode                         10u 0 value options(*nopass)
    D  codepage                     10u 0 value options(*nopass)
    D  creatcnvid                   10u 0 value options(*nopass)
    
    D read            pr            10i 0 extproc('read')
    D  handle                       10i 0 value
    D  buffer                         *   value
    D  bytes                        10u 0 value
    
    D close           pr            10i 0 extproc('close')
    D  handle                       10i 0 value
    
    D data            s          65535a
    D bytesRead       s             10i 0
    D handle          s             10i 0
    D rc              s             10i 0
    
    D O_RDONLY        c                   1
    D O_TEXTDATA      c                   16777216
    
       handle = open(%trim(pFile): O_RDONLY+O_TEXTDATA);
       pSuccess = (handle > 0);
    
       if pSuccess;
          bytesRead = read(handle: %addr(data): %size(data));
          pSuccess = (bytesRead >= 0);
          rc = close(handle);
       endif;
    
    
       if pSuccess;
          return %trimr(data);
       endif;
    
    P                 e
    
    P LoadStmf        b                   export
    D                 pi
    D  inFile                      256a   varying
    D  ouValue                    4096a   varying
    D  inFileNull                    5i 0
    D  ouNull                        5i 0
    D  SqlState                      5a
    D  FuncName                    517a   varying
    D  SpecificName                128a   varying
    D  MsgText                      70a   varying
    
    D success         s               n
    
    D NullValue       c                   const(-1)
    D NonNullValue    c                   const(0)
    
       monitor;
          ouValue = IFS_file2Var(inFile: success);
       on-error;
          success = *off;
       endmon;
    
       if success;
          ouNull  = NonNullValue;
       else;
          ouNull = NullValue;
       endif;
    
    P                 e
    

    Here’s the prototype, in case you’re interested.

    D IFS_file2Var    pr         65535a   varying
    D  pFile                       256a   value  
    D  pSuccess                       n          
                                                 
    D LoadStmf        pr                         
    D  inFile                      256a   varying
    D  ouValue                    4096a   varying
    D  inFileNull                    5i 0        
    D  ouNull                        5i 0        
    D  SqlState                      5a          
    D  FuncName                    517a   varying
    D  SpecificName                128a   varying
    D  MsgText                      70a   varying
    

    From this source code, I created service program STMF.

    CRTRPGMOD MODULE(THOLT/STMF) SRCFILE(THOLT/QRPGLESRC) SRCMBR(STMF)
    
    CRTSRVPGM SRVPGM(STMF) MODULE(STMF) EXPORT(*ALL)
    

    Then I created an SQL function to run the LoadStmf subprocedure.

    create or replace function LoadStmf
        (inStmf varchar(256))
        returns varchar(4096)
        language rpgle
        parameter style sql
        not deterministic
        no sql
        returns null on null input
        no external action
        not fenced
        no final call
        allow parallel
        no scratchpad
        external name 'THOLT/STMF(LOADSTMF)'
    

    To make the magic happen, I used PARAMETER STYLE SQL in the CREATE FUNCTION statement. (I have written about this before.) When using the SQL style to pass parameters, you specify the input parameter(s), the output value, a null indicator for each input parameter, and a null indicator for the output value. In the prototype, InFile is the only input parameter, ouValue is the return value, inFileNull is the null indicator for inFile, and ouNull is the null indicator for the output value.

    D LoadStmf        pr                         
    D  inFile                      256a   varying
    D  ouValue                    4096a   varying
    D  inFileNull                    5i 0        
    D  ouNull                        5i 0        
    D  SqlState                      5a          
    D  FuncName                    517a   varying
    D  SpecificName                128a   varying
    D  MsgText                      70a   varying
    

    A null indicator is a two-byte integer that indicates whether a parameter is null or not. A value of negative one means the parameter is null. A value of zero means the parameter is not null. If you want to know if a parameter is null, check the null value parameter that corresponds to it. In this example, I don’t want my subprocedure to handle a null stream file name, so I never check the value of inFileNull. I let RETURNS NULL ON NULL INPUT handle the case of a null file name.

    However, I do want the function to return a null if the specified stream file does not exist. In that case, I set ouNull to -1.

    Now I can do this:

    select loadstmf('wisdom.txt') from qsys2.qsqptabl
    

    And get this:

    LOADSTMF
    ====================================================
    Time flies like an arrow. Fruit flies like a banana.
    

    Let me finish with one word of caution. Since the query engine has to run a subprocedure every time the function is called, performance may suffer. My RPG-based functions usually work well for me, but recently I put a function call in a join, and that did not work well at all.

    RELATED STORIES

    SQL Functions You Didn’t Know You Had, Part 1

    Make an SQL UDF Return Null



                         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

    NGS:  FREE, Live Webinar: Reporting & Analytics on IBM i for Manufacturers & Distributors. Nov 18
    MiNET:  Automate PDF, email, Fax, Archiving & more with ArtForm400. Try us for a FREE Redbox code!
    ASNA:  Powerful IBM i apps shouldn't require a painful learning curve. Get the ASNA Wings white paper.

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    IBM Builds Infrastructure Resource Site The Windows Of Opportunity

    Leave a Reply Cancel reply

Volume 14, Number 25 -- November 12, 2014
THIS ISSUE SPONSORED BY:

SEQUEL Software
ProData Computer Services
WorksRight Software

Table of Contents

  • TR8 DB2 For i Enhancements, Part 1
  • SQL Functions You Didn’t Know You Had, Part 2
  • Flip This Job Number: Adjusting The Job Queue Control Utility For Job Number Resets

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