• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Dealing With RPG Errors and Embedded SQL

    April 8, 2019 Ted Holt

    Hey, Ted! I’m having trouble using some of the new techniques I learned at the RPG and DB2 Summit. Below is a screen shot of a program I am writing. I cannot figure out why the compiler doesn’t like it. Can you see anything that would be causing the declarations to fail?

    — Mike

    I glanced over Mike’s code and noticed that he used a correlation name in the SELECT and WHERE clauses, but did not define that correlation name for any of the tables, like this:

    SELECT x.onefield, x.twofield, x.redfield, x.bluefield
    FROM MYTABLE
    WHERE x.onefield = :TestValue;
    

    He added the correlation name to FROM, but that did not completely solve the problem. Let me show you some more code and you’ll see what I should have seen.

    dcl-s TestValue dec(3);
    

    You are correct. The data type should be packed, not dec.

    The reason I didn’t see it is that dec makes sense to me. It is easy for someone who works in several programming languages to read source code without seeing such syntax errors, and I use *DEC in CL programs all the time. Fortunately, one of Mike’s coworkers saw the mistake and the story had a happy ending. The program compiled and ran correctly.

    The question I asked myself is why the system did not do a better job of pointing out such a simple error. If Mike had been using SEU to edit the source code, I could understand. SEU doesn’t understand DCL-S — never has, never will. But Mike was using RDi.

    The answer comes to this: it would be unrealistic for the SQL precompiler, and by extension the RDi syntax checker, to fully include RPG syntax checking in an RPG program with embedded SQL. They can only do so much.

    So how do you determine the cause of those less-than-ideal SQL precompiler messages, especially SQL0312 (Variable &1 not defined or not usable) and SQL5011 (Host structure array &1 not defined or not usable) when the error comes from a violation of RPG syntax?

    One easy way is to comment out the SQL statements and try to compile the program with a command that invokes the RPG compiler, such as Create Bound RPG Program (CRTBNDRPG). Rather than comment out each statement, I like to use an undefined compiler directive.

    dcl-s  RecCount   dec(3);               
                                            
    *inlr = *on;                            
    /if defined(xyz)                        
    exec sql select count(*) into :RecCount 
               from qiws.qcustcdt;          
    /endif                                  
    return;                                 
    

    The RPG compiler listing shows me the problem.

         1   dcl-s  RecCount   dec(3); 
    ======>         bbbbbbbb   aaa 
    *RNF3308 20 a 000100 Keyword name is not valid; the keyword is ignored.
    

    While I’m on the subject, let me share some good news. IBM is improving the quality of the messages coming from the SQL precompiler. Enhancements in versions 7.2 and 7.3 cause the precompiler to append a reason description to messages SQL0312 and SLQ5011. To enable these enhancements, install a PTF.

    7.2 SI67777
    7.3 SI67615

    In the next release, the reason will be incorporated into the message text with a reason code.

    See the link below for more information about these enhancements.

    Thanks to Scott Forstie and Sue Romano of IBM for this wonderful news!

    RELATED STORY

    SQL Precompiler SQL0312 and SQL5011 message enhancement

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, CL, FHG, Four Hundred Guru, IBM i, RDi, RPG, SQL

    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

    As I See It: Why The Long Face? RTPA Looking For A Few Good Software Reviewers

    One thought on “Guru: Dealing With RPG Errors and Embedded SQL”

    • Naveen kamboj says:
      April 8, 2019 at 8:59 am

      put X after the table name(mytable), if you want to use x.field1 in the sql query.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 29 Issue: 23

This Issue Sponsored By

  • Maxava
  • iTech Solutions
  • ARCAD Software
  • COMMON
  • Manta Technologies

Table of Contents

  • Yes, IBM i Shops Have AI Options, Too
  • RTPA Looking For A Few Good Software Reviewers
  • Guru: Dealing With RPG Errors and Embedded SQL
  • As I See It: Why The Long Face?
  • Let’s Try Converged Power Infrastructure One More Time

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.