Re: Weird SQL Problem -- RPG400-L

Hi Jon,

so - that problem didn't let me sleep real good - so I tested and bit and searched - this is what I found:

-> https://www.ibm.com/docs/en/i/7.5.0?topic=users-whats-new
-> https://www.ibm.com/docs/en/i/7.5.0?topic=changes-sql-ile-rpg-precompiler-change-boolean-support

With the introduction of the SQL data type BOOLEAN, the pre-compiler changed, and now interprets RPG variables of type IND as BOOLEAN. Existing programs that were not re-compiled, didn't change.

Now what happens is simple - if you write an IND variable into a CHAR(1) field in SQL, the value of the field is "T" or "F" - for "TRUE" and "FALSE". This code shows it quite good:

dcl-s indic ind inz(*on);
dcl-s char1 char(1) inz;
dcl-s vchar varchar(10) inz;
exec sql set (:char1, :vchar) = (:indic, :indic);

char1 will be "T" and char will be "TRUE" after the EXEC SQL.

And the same will happen with a CHAR(1) column in a table. But in a newly compiled SQLRPGLE program, this wouldn't be a problem, as char values "T" and "F" are correctly interpreted - but only in newly compiled programs.

I hope I could clarify this a bit.

Regards,
Daniel

Am 02.05.2026 um 23:04 schrieb Jon Paris <jon.paris@xxxxxxxxxxxxxx>:
Well the “fix” was this Daniel

Dcl-DS Remap; // Used to remap indicator as char field for logging (SQL bug)
result Ind;
resultC Char(1) samepos(result);
End-DS;

Then using “resultC" in place of “result" as the host variable and that fixed it.

Remember - as I said before - this _exact_ code has been running without errors for a couple of years. Because of the nature of the data (a log of errors) we’re not sure exactly when it stopped working as we hadn’t needed to look at the log until another issue arose a few days ago.

I’ll be reporting the error to IBM.

Jon Paris

On May 2, 2026, at 15:11, Daniel Gross <daniel@xxxxxxxx> wrote:

Well - yes - historically an indicator is a CHAR(1) field, that only can be '0' or '1' - I think that was our way back on punched cards ;-) - today BOOLEAN is the safe way to go. And in an ideal world, the pre-compiler should be helpful - but we live with an imperfect pre-compiler since it exists.

And now that you say it - the phenomenon with the "T" as a value has occurred to me too some time ago - but I can't remember what I have done to cure it. Probably I also CASTed or used a CASE expression.

The explanation for that phenomenon seems to this: https://www.ibm.com/docs/en/i/7.6.0?topic=statement-boolean-data-type - I quote from the page:

String values representing true are 't' , 'true' , 'y', 'yes' , 'on', and '1'. False can be represented by 'f', 'false', 'n', 'no', 'off', and '0'. Any combination of uppercase and lowercase characters are recognized.

So - probably the indicator value *ON is converted to 'T' when written to the table with SQL - because since BOOLEAN exists as a data type, indicators are treated as boolean values by the SQL engine. But this is pure speculation on my side.

Can you look into the table data? Maybe look at the rows, where the SQLRPGLE fails?

Regards,
Daniel

Am 02.05.2026 um 20:44 schrieb Jon Paris <jon.paris@xxxxxxxxxxxxxx>:
I understand all that Daniel but the underlying data type for an RPG indicator has always been char(1).
Perhaps more to the point:
1) if this was a coding error on my part, the pre-compiler should spit it out. Not accept it and screw up at run time.
2) This has been working since 2024
3) If I manually cast the indicator I no longer get an error but the value returned is not zero or 1. It is currently a “T” for the *On condition.
4) The table has been around for some time and a bunch of other stuff would have to change if I switch it to boolean. I’d rather avoid that.
Looks like I’m going to have to “manually” interpret the indicator and pass the resulting char field to the SQL.
Jon Paris

On May 2, 2026, at 14:27, Daniel Gross <daniel@xxxxxxxx> wrote:
Hi Jon,
if you use an indicator for the RESULT column, the natural SQL data type would be BOOLEAN. An indicator can only be *ON or *OFF - a Boolean column can only be TRUE (*ON) or FALSE (*OFF) - an embedded SQL is correctly casting/converting between those 2 data types.
HTH
Daniel

Am 02.05.2026 um 20:15 schrieb Jon Paris <jon.paris@xxxxxxxxxxxxxx>:

Apologies if this is a dup but my original was rejected (I think) and I can’t see anything for May in the archives.
I have a table defined as:
REGISTERTIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
WEBINARID DECIMAL(11, 0) NOT NULL DEFAULT 0 ,
RESULT CHAR(1) NOT NULL DEFAULT '0' ,
REGISTERDATA VARCHAR(1000) ALLOCATE(300) CCSID 37 NOT NULL
The SQL statement to insert the data is:
Insert into table ( webinarId, result, registerData )
values( :webinarId, :result, :request );
Where the columns are all defined as per the table definition - except that “result” in the RPG code is an indicator.
This code has been running for a couple of years but since our move to 7.6 has started to sometimes throw SQLCODE -404 against the “result" column claiming that its length of 4 exceeds the capacity of the column. But it isn’t 4 long it is 1. Running in debug, I can see that the temp variable for “result” created by the pre-processor is char(1).
Not sure where else to look.
I have googled for PTFs or reported errors but am not seeing anything.
Anyone got any ideas on where to look next?
I have a work around - if I face the cast of the indicator to char(1) it works. Guess there is a bug in the code generated by the pre-compiler.
Insert into partner400/sqlregtest ( result, webinarId, regData )
values( cast (:result as char(1)), :webinarId, :request );
Jon Paris
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.