• 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
    Maxava

    Migrating to a new IBM Power System?

    Whether it be Power8, Power9 or Power10 – Maxava has you covered

    Our migration service moves data from the old to the new server without disruption while the business continues to operate without impacting performance. Our service avoids long periods of downtime and means businesses can reduce the risk of moving to new hardware.

    To learn more about Maxava’s migration service, call us on 888 400 1541 or VISIT maxava.com

    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

  • Power10 Entry Machines: The Power S1024 And Power L1024
  • Thoroughly Modern: Latest IT Trends – Bring Security, Speed, And Consistency To IT With Automation
  • Big Blue Unveils New Scalable VTL For IBM i
  • As I See It: Thank God It’s Thursday
  • IBM i PTF Guide, Volume 24, Number 32
  • JD Edwards Customers Face Support Decisions
  • Security, Automation, and Cloud Top Midrange IT Priorities, Study Says
  • Cleo and SrinSoft in Integration-Modernization Link Up
  • Four Hundred Monitor, August 3
  • IBM i PTF Guide, Volume 24, Number 31

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.