• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Read a Data Area As a One-Row Table with SQL

    May 19, 2004 Hey, Ted

    Is it possible to treat a data area as if it were a one-record physical file in an SQL query? We currently copy the data area to a one-record file, which we use in our queries. However, occasionally the data area gets updated but the refresh lags behind.

    –W.G.

    I’ve never thought about it, but that’s an interesting idea. In my S/36 days, I frequently wished it were possible to treat the local data area as a one-record disk file in Query/36.

    To answer your question: yes, it’s possible if you’re at V5R2. You will need to create a table function, which is a program that returns data through parameters, one virtual row at a time.

    I cannibalized Mike Sansoterra’s program from the March 24 issue of Four Hundred Guru to throw together an RPG IV program to read a 24-character data area I call PLANT. The first four bytes of PLANT are a plant number. The next 20 bytes are a plant location.

    Here’s the RPG code.

          // GetPlantInfo -- Return one record from data area PLANT
          //
          // To create:
          //
          // 1) CRTRPGMOD MODULE(xxx/GETPLANT)
          //              SRCFILE(xxx/QRPGLESRC)
          //              SRCMBR(GETPLANT)
          // 2) CRTSRVPGM SRVPGM(xxxxx/GETPLANT)
          //              EXPORT(*ALL)
          //
          //  To create an SQL function:
          //
          //  create function xxx/GetPlantInfo ()
          //  returns table(Number   Char(4),
          //                Location Char(20))
          //  External Name 'THOLTS/GETPLANT(GETPLANTINFO)'
          //  Language RPGLE
          //  Disallow Parallel
          //  No SQL
          //  Parameter Style DB2SQL
          //  Deterministic
          //
          //  To use the function in SQL:
          //
          //  select *
          //     from table(GetPlantInfo()) as plant
    
         H NoMain
    
         DGetPlantInfo     PR
          // Table Function Input Parameters
          // Table Function Output Parameters (columns)
         D pPlantNbr                      4A
         D pPlantLoc                     20A
          // Null Indicator Input Parameters
          // Null Indicator Output Parameters
         D pPlantNbr_NI                   5I 0
         D pPlantLoc_NI                   5I 0
          // DB2SQL Style Parameters
         D pSQLState                      5
         D pFunctionName                517
         D pSpecificName                128
         D pSQLMsgText                   70    Varying
          // UDTF Call Type
         D pTFCallType                   10I 0
    
          //  UDTF call parameter constants
    
         D UDTF_FirstCall  S             10I 0 Inz(-2)
         D UDTF_Open       S             10I 0 Inz(-1)
         D UDTF_Fetch      S             10I 0 Inz(0)
         D UDTF_Close      S             10I 0 Inz(1)
         D UDTF_LastCall   S             10I 0 Inz(2)
    
          //  SQL States
    
         D SQLSTATEOK      C                   '00000'
         D ENDOFTABLE      C                   '02000'
         D UDTF_ERROR      C                   'US001'
    
          //  NULL Constants
    
         D ISNULL          C                   -1
         D NOTNULL         C                    0
    
         D Plant           ds                  DtaAra(Plant)
         D  PlantNbr                      4A
         D  PlantLoc                     20a
    
         D FirstFetch      s               n
         D NullData        s               n
    
         PGetPlantInfo     B                   Export
         DGetPlantInfo     PI
          // Table Function Input Parameters
          // Table Function Output Parameters (columns)
         D pPlantNbr                      4A
         D pPlantLoc                     20A
          // Null Indicator Input Parameters
          // Null Indicator Output Parameters
         D pPlantNbr_NI                   5I 0
         D pPlantLoc_NI                   5I 0
          // DB2SQL Style Parameters
         D pSQLState                      5
         D pFunctionName                517
         D pSpecificName                128
         D pSQLMsgText                   70    Varying
          // UDTF Call Type
         D pTFCallType                   10I 0
    
         C/Free
             pSQLState=SQLStateOK;
    
             Monitor;
                 Select;
                 // Function Open
                 When pTFCallType=UDTF_Open;
                     In(e) Plant;
                     NullData = %Error;
                     FirstFetch = *on;
    
                 // Function Fetch -> Return data
                 When pTFCallType=UDTF_Fetch;
                     if FirstFetch;
                         FirstFetch = *off;
                         if not NullData;
                             pPlantNbr=PlantNbr;
                             pPlantLoc=PlantLoc;
                             pPlantNbr_NI=NOTNULL;
                             pPlantLoc_NI=NOTNULL;
                         Else;
                             pPlantNbr_NI=ISNULL;
                             pPlantLoc_NI=ISNULL;
                         endif;
                     else;
                         pSQLState=ENDOFTABLE;
                     endif;
    
                 // Function Close -> Cleanup Work
                 When pTFCallType=UDTF_Close;
                     *InLR=*On;
                 EndSl;
             On-Error;
                    *InLR=*On;
                    pSQLState=UDTF_ERROR;
             Endmon;
    
             Return;
          /End-Free
    
         PGetPlantInfo     E
    

    The instructions to create the function are in the comments, but let me briefly explain. The first step is to compile the RPG source member into a module. Next, create a service program from the module. Last, create a function using your SQL interface of choice.

    Here’s how the program works. When you run an SQL command that references the table function, the GetPlantInfo subprocedure will be called four times. The first time, parameter pTFCALLTYPE will have a value of -1. This is a cue to do initial processing. The subprocedure reads the data area.

    The second time, pTFCALLTYPE has a value of zero, which means that it wants a row of data. GetPlantInfo returns the plant number and location, if it has them, or nulls if it doesn’t.

    The third time, pTFCALLTYPE again has a value of zero. The subprocedure returns a SQL status of 02000, which indicates the end of a data set.

    Because the program returned status 02000, pTFCALLTYPE has a value of 1 on the fourth call. This is a signal to shut down.

    Here are some SQL commands I was able to run successfully. The first one displays the plant number and location as a table. The second two use a transaction file that has a plant number in it. Both queries use the data from the data area to select records from the transaction file.

    select * from table(GetPlantInfo()) as plant
    
    select pxact.PlantID, pxact.name, plant.location 
      from pxact                                   
      join table(GeTPlantInfo()) as plant          
        on plant.number = pxact.PlantID
    
    select * from pxact where PlantID in
      (select plant.number
         from table(GeTPlantInfo()) as plant)
    

    For more information about table functions, see Michael Sansoterra’s article and the IBM iSeries Information Center.

    –Ted

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    TL Ashford

    TL Ashford writes software to generate Barcode Labels and Forms for the IBM i.

    Our software products are the most feature-rich, cost-effective solutions available!

    TLAForms converts traditional IBM i spool files into high quality, professional forms that are output as PDF documents. A form is associated with a spool file and the form is designed. TLAForms then ‘watches’ the IBM i output queue for a new spool file to be generated. When a new spool file is generated, TLAForms reads the data from the spool file, places the data on the form, and outputs a PDF document. The PDF documents can be archived to the IFS, automatically emailed, and printed.

    Features:

    • Select Data directly from an IBM i Spool File
    • Burst IBM i Spool Files based on page or Spool File data
    • Add spool file data to form as Text using a wide variety of fonts and colors (the MICR font for printing checks is included in the software)
    • Add spool file data to form as bar code – including 2-dimensional bar codes PDF-417 and QRCode
    • Configure SQL statements to retrieve and use IBM i File data on forms
    • Utilize Actions to show or hide objects on the form based on data within the spool file
    • Import Color Graphics
    • Use Color Overlays
    • Create Tables
    • Forms can be archived to the IFS
    • Forms can be emailed automatically
    • Forms can be printed to any IBM i, Network or Windows printer
    • Forms are automatically generated when a new spool file is generated in the IBM i output queue
    • NO PROGRAMMING required
    • On-Line Video Training Library

    Learn more about TLAForms at www.tlashford.com/TLA2/pages/tlaforms/overview.html

    Barcode400 is native IBM i software to design and print labels directly from your IBM i in minutes! Compliance and RFID labeling is easy using Barcode400’s tools and templates.

    Features:

    • Software resides on the IBM i
    • IBM i security and Backup
    • Labels are centrally located on the IBM i
    • Label formats are available to all users the instant they are saved – even in remote facilities
    • GUI designer (Unlimited Users)
    • Generate Labels as PDF documents!
    • Print to 100’s of thermal transfer printers
    • Print to HP and compatible printers
    • Print labels interactively – No Programming Necessary!

    OR Integrate into existing application programs to automatically print labels – Barcode400 has been integrated with nearly every ERP and WMS software available on the IBM i, including thousands of in-house written applications.

    • On-Line Video Training Library
    • Free Compliance Labels
    • Generate Checks using the MICR font
    • RFID Support (optional)
    • History / Reprint Utility
    • Integration Assistant
    • Low Cost (no tiered pricing)

    Learn more about Barcode400 at www.tlashford.com/TLA2/pages/bc400labels/overview.html

     

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Robot/SAVE Picks Up Where Bad Backups Leave Off IBM Chases Vintage OS/400, HP-UX Servers with the i5

    Leave a Reply Cancel reply

Content archive

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

Recent Posts

  • Doing The Texas Two Step From Power9 To Power10
  • PHP’s Legacy Problem
  • Guru: For IBM i Newcomers, An Access Client Solutions Primer
  • IBM i 7.1 Extended Out To 2024 And Up To The IBM Cloud
  • Some Practical Advice On That HMC-Power9 Impedance Mismatch
  • IBM Extends Dynamic Capacity Pricing Scheme To Its Cloud
  • Here’s What You Should Do About The IBM i Skills Shortage
  • Matillion Founder Recounts Midrange Roots
  • Four Hundred Monitor, February 24
  • IBM i PTF Guide, Volume 23, Number 8

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 © 2021 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.