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

    Simple.  Reliable.  Powerful.

    IBM i software to help you navigate your universe!

    Use these tools to get where you are going:

    DBU to easily, yet securely, access the data on your IBM i or remote system.

    RDB Connect for powerful record level access to remote data using RPG, CL or COBOL.

    SQL/Pro and Audit for reliable SQL data processing, tracking and reporting.

    And more!

    Join our DBUniverse of loyal, happy customers and experience your destination…

    Do IT with DBU!

    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

  • Big Blue Unveils Spring 2021 IBM i Technology Refreshes
  • Thoroughly Modern: Innovative And Realistic Approaches To IBM i Modernization
  • Guru: Web Services, DATA-INTO and DATA-GEN, Part 2
  • Back To The Future With A New IBM i Logo
  • IBM i PTF Guide, Volume 23, Number 14
  • When Cloud Meets DevOps on IBM i
  • JD Edwards Roadmap Reveals Decisions To Be Made
  • IBM Completes Migration of Knowledge Center to IBM Documentation
  • Four Hundred Monitor, April 7
  • Crazy Idea Number 615: Variable Priced Power Systems Partitions

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.