29
Fri, Nov
0 New Articles

Using RPG in SQL

RPG
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

In this article, I will look at using a simple RPG subprocedure that converts a numeric field containing a date to an actual date field.

The RPG Side

Figure 1 shows the DDS for a simple table containing Employee ID, Name, Date Joined, and Date of Birth. The Date Joined and Date of Birth fields are defined as packed numeric data types and contain dates in ISO (YYYYMMDD) format. If there is a need to perform any sort of "date math" (e.g., calculating a person's current age or the age they were when they joined the company), there is no problem converting the dates to proper date fields in an RPG program.

A          R EMPLOYEER                                            
A            EMPID          5  0       COLHDG('Employee' '  Id.') 
A            NAME          25          COLHDG('Name')             
A            BIRTH          8  0       COLHDG('Date of' ' Birth') 
A                                      EDTWRD('   0-  -  ')       
A            JOINED         8  0       COLHDG(' Date' 'Joined')   
A                                      EDTWRD('   0-  -  ')      

Figure 1: This is the DDS of a simple table.

To make the date conversion process even easier in RPG programs, I have written a ConvertToDate subprocedure, shown in Figure 2, which converts numeric fields to date fields. The ConvertToDate subprocedure accepts a single parameter (which should be a numeric field containing a date in ISO format) and returns either the ISO date equivalent of the passed parameter (if it contains a valid date) or today's date (if the passed parameter is not a valid date). The module containing the ConvertToDate subprocedure is in a service program named DATES.

     P ConvertToDate   B                   Export           
                                                       
     D                 PI              d   DatFmt(*ISO)     
     D  Date8                         8p 0 Const 
                                                
      /Free   
        Test(DE) *ISO Date8;                             
        If Not %Error;                                     
           Return %Date(Date8:*ISO);             
        Else;                                              
           Return %Date();                          
        EndIf;                           
                                     
      /End-Free                           
     P                 E    

Figure 2: The ConvertToDate subprocedure converts numeric fields to date fields.

You can use the ConvertToDate subprocedure anywhere you need to treat a numeric date field (with a date in ISO format) as a proper date field. For example, using the database described in Figure 1, you would use the following code to calculate the age of an employee when he or she joined the company:

     Age = %Diff( ConvertToDate(Joined) : ConvertToDate(Birth) : *Y);

 

The use of ConvertToDate in programs ensures that a proper date is always used and takes care of the unlikely instance that an invalid date might exist in the database. Of course, you could add extra processing to log any errors, halt processing, or return a different default date instead of today's date.

But how can you use the ConvertToDate subprocedure in SQL?

Define an SQL Function in iSeries Navigator

Just as RPG has BIFs, SQL has scalar functions. And just as RPG allows you to define your own BIFs by writing subprocedures, SQL allows you to define your own scalar functions, referred to as user-defined functions (UDFs).

If you have an urge to jump into the deep end, you can attempt to define an SQL function using a CREATE FUNCTION command in SQL, but, for the first attempt at least, it is a lot easier to use the wizard provided in iSeries Navigator. I will return to the CREATE FUNCTION command later in this article.

To define a function using iSeries Navigator, you must add the schema (library) that will contain the function to the list of schemas. Expand Databases -> DatabaseName -> Schemas. If the required schema is not in the list, right-click on Schemas and select the option to Select Schemas to Display and add the schema to the list.

To define a function, expand the required schema, right-click on Functions, and select New -> External from the pop-up menu, as shown in Figure 3. An external SQL function indicates that the SQL function is a "wrapper" to call an external program or subprocedure.

http://www.mcpressonline.com/articles/images/2002/Using%20RPG%20in%20SQLV4--09190700.png

Figure 3: An "external" SQL function calls an external program or subprocedure. (Click images to enlarge.)

 

The wizard to define an external SQL function consists of three tabs: General, Parameters, and External Program. Let's look at the contents of the three tabs in detail, starting with the General tab, shown in Figure 4, which identifies the function and the value (or values) it returns and provides details relating to the SQL environment for the function.

http://www.mcpressonline.com/articles/images/2002/Using%20RPG%20in%20SQLV4--09190701.png

Figure 4: The General tab defines a function.
 

  • Function identifies the name of the function. This is the name you will use when you want to call the function in an SQL statement. In this instance, I've given the SQL function the same name as the subprocedure: ConvertToDate.
  • Description is simply a description of the function.
  • Data returned to invoking statement defines the parameter returned by the called program or subprocedure. This should correspond to the value returned by the ConvertToDate subprocedure, so you define it as a date. The returned data may be a single value (as in this case), or it could be a table returned by embedded SQL; you signify which by selecting either Single Value or Table.
  • Can run in parallel is selected if the function does not change data used elsewhere in the calling SQL program or procedure.
  • Program does not call outside of itself (No External Action) is selected if the called procedure does not call other functions or procedures outside of itself.
  • Same result returned from successive calls with identical input (Deterministic) is selected if the same result is to be returned every time the procedure is called with the same values for the parameters. This will save the values that the procedure returns. Those values are automatically returned if the procedure is called with the same parameters, thereby saving system resources.
  • Attempt to run in same thread as invoking statement (Not Fenced) is selected if the called function is to share resources (such as locks) with the invoking statement. As a general rule, called functions that modify data should be Fenced (i.e., this option is not selected); otherwise, Not Fenced is preferred.
  • Data Access specifies the type of SQL actions that the function performs. The available options are Contains SQL (indicates that the function changes SQL data), No SQL (indicates that the function does not read, write, or change SQL data, as is the case with ConvertToDate), Reads SQL Data (indicates that the function reads SQL data but does not write or change it), and Modify SQL Data (indicates that the function modifies SQL data but does not read it).
  • Specific Name specifies a unique name for the function or procedure within the schema. This is required only if other functions or procedures with the same name happen to be within the schema.

The Parameters tab, shown in Figure 5, identifies the parameters that are passed to the function.

http://www.mcpressonline.com/articles/images/2002/Using%20RPG%20in%20SQLV4--09190702.png

Figure 5: The Parameters tab identifies the parameters passed to the function.
 

  • Use the Insert button to insert a parameter, and then select (by clicking on them) and change the inserted values. Since the ConvertToDate subprocedure expects a numeric field of 8,0 as input, you define the parameter accordingly.
  • Parameter style indicates the format in which data is passed to the called program or subprocedure. As well as the parameters you defined above, SQL also passes a number of "fixed" parameters to your function (such as the name of the function and an SQL state code). The format of these "fixed" parameters will differ, depending on whether you select SQL, DB2 SQL or Java, and each is described in detail in the Information Center (Database -> Programming -> SQL Programming -> Routines -> Using User Defined Function -> Writing UDFs as External Functions -> Passing Arguments from DB2 to External Functions—real easy to find <g>). But the use of these "fixed" parameters is required only if you need to communicate back to the calling statement (e.g., by setting the State code) or if you need to identify which SQL function issued the call (if you defined multiple functions that called the same program or subprocedure).
  • Return null on null input indicates that if the parameter is null, then the program or subprocedure is not called and a null value is returned.

The External Program tab, shown in Figure 6, identifies the program or subprocedure that is called by the function. The function may be a program or a Java method. For the purposes of this article, I'll discuss only the program.

http://www.mcpressonline.com/articles/images/2002/Using%20RPG%20in%20SQLV4--09190703.png

Figure 6: The External Program tab identifies the program or subprocedure called by the function.
 

  • Program identifies the program or subprocedure to be called. Do not be misled by the online help, which tells you to enter a 10-character program name; you may specify a subprocedure by entering a service program name and the name of the subprocedure in parentheses. The name of the subprocedure is case-sensitive, so ensure that it corresponds to the exported name from the service program.
  • Schema identifies the library containing the service program (or program).
  • Language identifies the language in which the called program or subprocedure is written. Select RPGLE for ConvertToDate.
  • Type returned by program is specified if the value returned by the program or subprocedure does not correspond to the return value specified on the General tab.

Now click on the OK button to create the ConvertToDate Function.

Create Function

Earlier in the article, I mentioned that you could define a function using the CREATE FUNCTION command in SQL.

To get an idea of the format of the SQL CREATE FUNCTION command, right-click on an existing SQL function in iSeries Navigator and select Generate SQL from the pop-up menu. Figure 7 shows the SQL CREATE FUNCTION command that would be used to create the ConvertToDate function defined earlier.

CREATE FUNCTION SQLUDF/CONVERTTODATE (  DATEIN DECIMAL(8, 0) )  RETURNS DATE    LANGUAGE RPGLE  SPECIFIC SQLUDF/CONVERTTODATE  DETERMINISTIC  NO SQL  RETURNS NULL ON NULL INPUT  NO EXTERNAL ACTION  NOT FENCED  EXTERNAL NAME 'SQLUDF/DATES(CONVERTTODATE)'  PARAMETER STYLE SQL ;   COMMENT ON SPECIFIC FUNCTION SQLUDF/CONVERTTODATE  IS 'Convert Numeric Date Field to Proper Date' ;

 

Figure 7: Define a function with CREATE FUNCTION.

When defining multiple functions that are somewhat similar, it is often easier to define the first function using iSeries Navigator and then define the remaining functions using the first function as a template; simply generate the SQL, change the relevnt definitions, and run the commands.

Now let's look at a couple of examples of using the newly defined function.

Using the Function with SELECT

You can use your own user-defined functions in almost any place you can use an SQL scalar function. For example, Figure 8 shows an SQL SELECT statement that uses the ConvertToDate function and the corresponding result of running the statement. The ConvertToDate function is used to represent the Birth and Joined columns as proper dates and used again in the calculation of the Age When Joined column.

select empid, name, converttodate(birth) as BirthDate,
       converttodate(joined) as JoinedDate,
       year( converttodate(joined)- converttodate(birth)) as AgeJoined 
       from employee;
Employee  Name                 BIRTHDATE   JOINEDDATE      AGEJOINED
  Id.                                      
      1   Paul Tuohy           1956-05-14  2005-10-10             49
      2   Joe Bloggs           1979-10-12  2007-01-20             27

 

Figure 8: Use the function in a SELECT statement.

(Note to self: Stop using your date of birth in these examples).

Using the Function in a View

You can make life even easier by defining a view that already does all the hard work. Figure 9 shows the definition of the view EMPLOYEEV1, which contains the definition of columns corresponding to those defined in the SELECT statement shown in Figure 8.

CREATE VIEW EMPLOYEEV1 (EMPID, NAME, BIRTHDATE, JOINEDDATE, AGE )
      AS 
      SELECT EMPID, NAME, CONVERTTODATE(BIRTH), CONVERTTODATE(JOINED),
      YEAR(CONVERTTODATE(JOINED) - CONVERTTODATE(BIRTH)) 
      FROM EMPLOYEE

 

Figure 9: Use the function in the definition of a view.

Now simply selecting all columns from the view EMPLOYEEV1 generates the same result set shown back in Figure 8. Do you think your users might find this useful when they are using QUERY?

A Last Word...

The ability to use existing RPG programs and subprocedures as functions within SQL can offer some intriguing possibilities in that they can provide a means of using existing application logic to provide column values.

Yet another tool to add to your ever-expanding utility belt!

Paul Tuohy

Paul Tuohy has worked in the development of IBM midrange applications since the 1970s. He has been IT manager for Kodak Ireland, Ltd., and technical director of Precision Software, Ltd., and is currently CEO of ComCon, a midrange consultancy company based in Dublin, Ireland. He has been teaching and lecturing since the mid-1980s. 

Paul is the author of Re-engineering RPG Legacy Applications, The Programmer's Guide to iSeries Navigator, and the self-teach course "iSeries Navigator for Programmers." He is one of the partners of System i Developer and, in addition to speaking at the renowned RPG & DB2 Summit, he is an award-winning speaker at COMMON and other conferences throughout the world.


MC Press books written by Paul Tuohy available now on the MC Press Bookstore.

The Programmer’s Guide to iSeries Navigator The Programmer’s Guide to iSeries Navigator
Get to know iSeries Navigator and all the powerful tools and interfaces that will expand your programming horizons.
List Price $74.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: