IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
#Power
#Operatingsystems
#Servers
 View Only

  • 1.  SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause

    Posted 02/06/24 09:19 AM
    Edited by Daniel Gross 02/06/24 09:20 AM

    Hi,

    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



  • 2.  RE: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause

    Posted 02/06/24 10:14 PM
    Edited by Satid Singkorapoom 02/06/24 10:17 PM

    Dear Daniel 

    It would be better if you can print the job log in details after you run the SP and download it as a text file (using IBM  ACS) for us to look at as there can be useful clues in there. 

    >>>> 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. <<<<

    The use of :sVar with '     ' may be the problem here and it may have to do with Promotion of data types in DB2i (look here  https://www.ibm.com/docs/en/i/7.4?topic=elements-promotion-data-types).   :sVar is declared as VARCHAR but I think the literal '    ' is CHAR and VARCHAR cannot be demoted to CHAR and therefore the incompatibility.  I'm not totally sure on this but if you read the URL I provide above, you should see what I mean.     This info should be useful for you as well: Determining equivalent SQL and ILE RPG data types at  https://www.ibm.com/docs/en/i/7.4?topic=applications-determining-equivalent-sql-ile-rpg-data-types.

     

    As for :bBool which is declared as RPG indicator type, I do not think SQL supports this.  You need to read this :  Using indicator variables in ILE RPG applications that use SQL at  https://www.ibm.com/docs/en/i/7.4?topic=cssiira-using-indicator-variables-in-ile-rpg-applications-that-use-sql  and make proper change.



    ------------------------------
    Chance favors only the prepared mind.
    -- Louis Pasteur
    ------------------------------
    Satid S.
    ------------------------------



  • 3.  RE: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause

    Posted 02/07/24 12:59 AM

    Hi Satid,

    As for :bBool which is declared as RPG indicator type, I do not think SQL supports this.  You need to read this :  Using indicator variables in ILE RPG applications that use SQL at  https://www.ibm.com/docs/en/i/7.4?topic=cssiira-using-indicator-variables-in-ile-rpg-applications-that-use-sql  and make proper change.

    The BOOLEAN data type was introduced with 7.5 and it's correctly converted to/from RPG indicator (bool) type "IND" (or more old school "N" in position 40 of a D-spec). 

    The indicators that you mean are the SQL indicator variables - which signal NULL or other states. The example doesn't use these though, but they have to be defines as INT(5) in RPG.

    :sVar is declared as VARCHAR but I think the literal '    ' is CHAR and VARCHAR cannot be demoted to CHAR and therefore the incompatibility.

    Thanks for that info - yes that might be a part of the solution - I often forget that CHAR and VARCHAR aren't the same.

    But it puzzles me, that it works as soon as I remove the BOOLEAN from the WHERE clause. If it's a type incompatibility between CHAR and VARCHAR it should also be incompatible without the BOOL variable.

    We're waiting for the latest PTFs to be applied by our admin team, and we will try if it works then. Until then I will try around with some CAST around the CHAR/VARCHAR variables. Maybe this will give at least some hint on what happens.

    Thanks for now.

    Regards,

    Daniel


    Original Message


  • 4.  RE: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause

    Posted 02/07/24 06:50 AM

    Dear Daniel

    I suggest you repost your question into SQL group of this community as there are world renowned experts in DB2i SQL who I'm sure will provide you answer. 



    ------------------------------
    Chance favors only the prepared mind.
    -- Louis Pasteur
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 5.  RE: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause

    Posted 03/04/24 12:44 PM

    Hi Daniel,

    I came across the same error. Did you find any solution in the meantime or can you provide a link to the aforementioned post in the SQL group Satid mentioned?

    Best regards



    ------------------------------
    Bjarne Laumann
    ------------------------------

    Original Message


  • 6.  RE: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause

    Posted 03/04/24 01:39 PM

    Hi Bjarne,

    I received exactly zero answers in the SQL group - so the link isn't useful.

    We opened a case with IBM and received a "BETA" PTF that is not publicly available right now. I can send you the PTF number and you can request it from IBM. 

    The PTF works - but seems completely unrelated to the BOOL variable, according to the APAR on which the PTF is based.

    I don't know if I can share the PTF number publicly -  so please send me an e-mail to daniel (at) qpgmr (dot) de and I send you the PTF and APAR back.

    Kind regards,

    Daniel



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

    Original Message


Related Content