ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Embedded SQL and host variables named 'include' error

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Embedded SQL and host variables named 'include' error

    So I was testing a program, and one of the "insert into table values()" embedded SQL statements was failing but not reporting an error. When I stepped through it in debug, the Exec SQL statement did not execute, program flow skipped form the previous statement to the next. I checked the compile listing, and the Exec SQL had been commented out as normal, but had not been replaced with the assignments and procedure call you normally get for an embedded SQL statement. It is like the compiler had silently decided to skip that statement.

    After much experimenting I found the cause was the name of one of the host variables that was among the insert values. Simplified code:

    Code:
    dcl-ds myds qualified;
      var1  char(5);
      var2  char(5);
      include  char(1);
    end-ds;
    
    
    exec sql
      insert into mytable (field1, field2, field3)
      values(:myds.var1, :myds.var2, :myds.include);
    It was "myds.include". The RDi syntax highlighter was highlighting the "include" text part of the name in blue, but I didn't give it any thought at the time. When I renamed the host variable to "includeItem" then it worked.

    It seems that a host variable named include will cause the SQL statement to not be compiled.

    I do not know if it happens when include is the whole host variable name, or just if it is the subfield name in a qualified DS. I also do not know if it happens different types of SQL statement.

    Has anyone else seen this?


    This page lists the host variable name restrictions, and makes no mention of include: https://www.ibm.com/support/knowledg...ajprpiamco.htm
    And even if include was a restricted value, I would expect the compiler to tell me that and disallow compilation, or at least provide a warning. But it was entirely silent.

  • #2
    Is it only the synatx checker or does your program really not compile due to this variable name?
    In my experience the syntax checker sometimes (especially in composition with SQL) throws errors, even though the program/module can be compiled.

    Birgitta

    Comment


    • #3
      Update - I created a few simple test cases, and I can confirm that:

      This happens if the include is the whole name, or if it is the first/last part of a qualified ds name. i.e. all of the following:
      • :include
      • :ds.include
      • :include.var1
      • Any
      This happens if the host name appears in any of the following parts of the SQL statement:
      • The into of a "select: select field1 into :include..."
      • The where of a select or update: "select * from table where field1 = :include"
      • The set of an update: "update table set field1 = :include"
      • The values of an insert: "insert into table values(:include ..."
      And in every case, the only evidence was that if I viewed the compile listing, no SQL processing code had been added after the statements. No errors or warnings were flagged in either the SQL precompile spool or in the RPGLE compile spool.

      Comment


      • Vectorspace
        Vectorspace commented
        Editing a comment
        Ignore the "any" bullet point, that was a typo. I would remove it if I could edit my posts

    • #4
      Originally posted by B.Hauser View Post
      Is it only the synatx checker or does your program really not compile due to this variable name?
      In my experience the syntax checker sometimes (especially in composition with SQL) throws errors, even though the program/module can be compiled.

      Birgitta
      Sorry, did not see your reply until I had posted my update

      It is not just the syntax checker. The program does compile: The compiler reports success, the program/module is created, but the program will not execute as you expect because any/all SQL statements containing a host variable named "include" (whether a standalone field or part of a qualified field name) will not have the SQL processing for it.

      If this is the normal compile step for an embedded SQL statement:
      1. read SQL statement
      2. comment it out
      3. add required sql processing instructions after it
      4. process next statement
      Then if a host variable named include is in it, it silently skips step 3.

      Comment


      • #5
        ... so I'd open a PMR at IBM

        Birgitta

        Comment


        • #6
          Looks like IBM intend to fix this soon:

          Comment

          Working...
          X