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.
Original Message:
Sent: Mon March 04, 2024 07:52 AM
From: Bjarne Laumann
Subject: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause
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:
Sent: Wed February 07, 2024 06:49 AM
From: Satid Singkorapoom
Subject: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause
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:
Sent: Wed February 07, 2024 12:58 AM
From: Daniel Gross
Subject: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause
Hi Satid,
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:
Sent: Tue February 06, 2024 10:13 PM
From: Satid Singkorapoom
Subject: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause
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.
Original Message:
Sent: Tue February 06, 2024 09:19 AM
From: Daniel Gross
Subject: SQLCODE -311 for "boolean" and "varchar" host variable in WHERE clause
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