Programming Languages on Power

Power Programming Languages

IBM Power, including the AIX, IBM i, and Linux operating systems, support a wide range of programming languages, catering to both traditional enterprise applications and modern development needs.


#Power

 View Only
Back to discussions
Expand all | Collapse all

SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

  • 1.  SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Wed February 07, 2024 07:14 AM

    Hi,

    I already posted this in another group, and got advice to try it here and in SQL.
    I have a strange case in an SQLRPGLE program. Here is my example code, to eventually reproduce this:
    **free
    ctl-opt actgrp(*new);

    dcl-s bBool ind inz(*on);
    dcl-s sVar varchar(100) inz('Hello');
    dcl-s nCount int(10) inz;

    prTest();
    prTest();

    *inlr = *on;
    return;

    dcl-proc prTest;
     exec sql select count(*)
              into :nCount
              from sysibm.sysdummy1
              where '   ' = :sVar and :bBool ;
     return;
    end-proc;
     
    When running/debugging the program, the SQLCODE is *ZERO after the first procedure call. 
     
    But after the second procedure call, the SQLCODE is -311, which means "Length in varying-length, LOB, or XML host variable not valid". 
     
    If I remove either the Boolean or the Varchar variable from the statement, it runs fine. Changing ":bBool" to ":bBool = '1'" or anything else doesn't help. 
     
    If I add the line
     
    sql_00004 = *blank;
     
    just before the EXEC SQL it runs also fine. This is the buffer before the VARCHAR field in the SQL host variables DS, that the Pre-Compiler defines. And the content of this "buffer" is the only thing that differs from call 1 to call 2. 
     
    But this shouldn't happen? Or should it? What am I doing wrong?
     
    From what I can see, it happens only, if the program was compiled on 7.5. If it was compiled on an earlier release, it seems to run fine on 7.5.
    TIA for every hint. 
    Daniel

    #RPG


  • 2.  RE: SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Thu February 08, 2024 12:51 PM
    1) You say "since 7.5" - but did this work on earlier releases?

    2) Have you checked for possible PTFs.

    3) Have you checked the Memo to Users to see if any changes were noted there (unlikely but ...)

    4) Is this just a subset of a bigger task? Becuase using SQL for this seems insane.


    Jon Paris


    Original Message


  • 3.  RE: SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Thu February 08, 2024 01:12 PM
    Edited by Daniel Gross Thu February 08, 2024 01:15 PM

    Hi Jon,

    to answer your questions:

    1. Yes - this worked in 7.3 without problems. But it was coded with :bBool = '1' back then - but this coding doesn't work either on 7.5. 

    2. We're applying the latest group PTF on our development system today - I will have to check tomorrow, if this helped. 

    3. Well - the biggest addition was, that the BOOLEAN type was introduced with 7.5 - until then all IND host variables were CHAR(1) as far as I know. But I can't read anything from the M2U that would indicate such problems. 

    4. This is the smallest possible example - of course the production code is larger, so I have reduced the code as far as I could, while still resulting in error - I also removed /COPY and anything that wasn't needed to reproduce the error. I also changed the table to something that everyone has on the machine - also in preparation to open a ticket at IBM. 

    Well - we're still kinda puzzled - even casting the CHAR literal or the VARCHAR host variable doesn't help - and the error code -311 is focused on VARCHAR, LOB and XML - not on BOOLEAN.

    What does help is, using a CHAR(1) host variable instead of the IND - and then compare with '1' - but this seems strange, as BOOLEAN was explicitly introduced with 7.5 and the type conversion rules seem to be very clear. 

    I will post an update after the PTF application today - maybe tomorrow or Monday. 

    Kind regards,

    Daniel



    ------------------------------
    Daniel Gross
    ------------------------------

    Original Message


  • 4.  RE: SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Fri February 09, 2024 11:19 AM
    If you still have the problem after the PTF application Daniel report it to IBM. It has to be a bug.

    By the way, have you looked at the content of the varchar it is complaining about to see what the length and content are?


    Jon


    Original Message


  • 5.  RE: SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Fri February 09, 2024 11:53 AM

    The content of the VARCHAR is OK - also the length prefix is OK. 

    The only problem seems to be the Filler field of the DS - it gets populated with the first call - if I set it back to *BLANK before the second call, it works fine. 

    It seems, that the SQLROUTE_CALL is buffering some information, and this might be corrupt(ed). 

    So thanks for your support. I will post if we make some progress. 

    Kind regards,

    Daniel



    ------------------------------
    Daniel Gross
    ------------------------------

    Original Message


  • 6.  RE: SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Fri February 09, 2024 04:40 AM

    Hi Daniel,

    I tested your program on my IBM i 7.5 system with all latest PTF groups installed, and I get the same error!

    When I recreate your program in a SQL compound statement, it runs without errors:

    begin

  declare sVar   varchar( 100 ) default 'Hello';
  declare bBool  boolean        default true;
  declare nCount integer        default 0;
    
  select count(*)
    into nCount
    from sysibm.sysdummy1
   where '   ' = sVar and bBool;
    
  call SYSTOOLS.LPRINTF( 'nCount = ' concat nCount );

end

    The above code writes a joblog entry with the text 'nCount = 0' - as expected.

    This is an error in the SQL precompiler or runtime, as I see it, and you should report this to IBM.

    Best regards,

    Christian



    ------------------------------
    Christian Jorgensen
    IT System Administrator
    Network of Music Partners A/S
    ------------------------------

    Original Message


  • 7.  RE: SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Fri February 09, 2024 09:26 AM
    Edited by Daniel Gross Fri February 09, 2024 09:26 AM

    Thanks Christian.

    We already testet this in every thinkable configuration, but always the same problem.

    Strange thing is - if you clear the generated field "SQL_00004" before executing the SQL in the procedure, everything works find. But "SQL_00004" is only a buffer in the generated unnamed static data structure - no host-variable.

    Also strange is - it works outside of a procedure (in the cycle main procedure) without problems. But in this case, the generated data structure is not marked as "STATIC" - which might be the problem, but we can't do very much about that.

    Long story short - we're already in the process of sending this defect to IBM ...

    Our admin team is applying latest PTFs today - but after your tests, I have no high-hopes that this will help.

    Anyway - thanks for your effort.

    Kind regards,

    Daniel



    ------------------------------
    Daniel Gross
    ------------------------------

    Original Message


  • 8.  RE: SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Fri February 09, 2024 10:27 AM

    I would not recommend ever changing any of the SQL_nnnnn fields - they are for SQL internal use only, and you don't know how they are used by SQL.

    I'm looking forward to hear more about the resolution of this problem. Please report back here any development, good or bad, in the case. :-)

    Have a nice weekend!



    ------------------------------
    Christian Jorgensen
    IT System Administrator
    Network of Music Partners A/S
    ------------------------------

    Original Message


  • 9.  RE: SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Sat February 24, 2024 02:19 PM

    Just a note: And RPG "indicator" variable is a character variable to SQL. Not a true "bool" I remember reading an RFE to add true BOOLEAN variables to SQL a while ago but don't know if they're implemented as yet.

    Try changing the boolean variable from IND to INT(10) and and use 1 to turn it on/true. 



    ------------------------------
    Robert Cozzi
    ------------------------------

    Original Message


  • 10.  RE: SQLCODE -311 with VARCHAR and BOOLEAN in WHERE clause (since 7.5)

    Posted Sat February 24, 2024 02:34 PM
    It was with one of the last TRs AFAIK. 


    And the type conversation rules are pretty straightforward - '1' means true and '0' means false - that seems fully compatible to RPG type IND. 

    But I also tried to compare like ":bool = '1'" and other ideas. To make it work, I have to use a CHAR(1) variable - IND doesn't work in any combination. 

    We're already opening a support case with IBM - hopefully we will get some results. 

    We also have some other problems with SQL right now, like with OVER() and LEFT JOIN in the same query - so we're preparing more than one case at the moment. 

    Thanks for your thoughts. 

    Kind regards,
    Daniel



    Original Message


Related Content