Re: Weird SQL Problem -- RPG400-L

Yes - that's the IBM-i-ish way of doing it.

The only thing I can think of is, that it's a SQL pre-compiler thing. And we know, that the SQL-PC isn't developed by the RPG compiler team - so I don't know, if the SQL-PC team adheres to the same principles as Barbara's team.

The introduction of BOOLEAN was a big thing - maybe the backwards compatibility just slipped through.

I hope the idea is getting some traction - and you also opened a support case for it - maybe I should too - then it might create some more pressure.

In the meantime - maybe a FIELDPROC on the CHAR(1) column can help. It could translate the 'T'/'F' values to '1'/'0' and back - and leaves '1'/'0' as it is. This would be transparent for application programs - whether they are re-compiled or not.

-> https://www.ibm.com/docs/en/i/7.6.0?topic=language-defining-field-procedures

Kind regards,
Daniel

Am 03.05.2026 um 22:28 schrieb Jon Paris <jon.paris@xxxxxxxxxxxxxx>:

If you mean IBM would not normally go to the trouble to avoid incompatibilities I have to disagree.

When I worked on the compiler teams _the_ #1 rule with updates was “If you can’t make it behave the way it used to, then stop it from compiling, and/or add a compiler option to control the new behaviour”. Look at the multitude of RPG compiler options to witness this. In the case of the COBOL compiler we once had a case where we fixed the compiler to behave correctly after someone raised an APAR. We then had to change it back after another (much bigger) customer informed us that it had broken their entire codebase. It was subsequently implemented properly with a compiler option to retain the old behaviour.

Jon Paris

On May 3, 2026, at 14:27, Marco Facchinetti <marco.facchinetti@xxxxxxxxx> wrote:

Really not in IBM style.

I think the only thing to do now is to vote en masse for Daniel's idea to
put some pressure on IBM.

Best regards
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

Il giorno dom 3 mag 2026 alle ore 19:12 Jon Paris <jon.paris@xxxxxxxxxxxxxx>
ha scritto:

I agree Marco.

I confess that I am surprised that they made an incompatible change. At
the very least the pre-compiler should be issuing an error. It should never
generate code that it knows in advance will blow up at run time.

Thanks to those who pointed to the v&.5 docs - interesting that neither
google searches or AI aided search came up with this as a possible answer
when I was researching the issue.

Jon Paris

On May 3, 2026, at 09:38, Marco Facchinetti <marco.facchinetti@xxxxxxxxx>

wrote:

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

And since the value in the field is T I can suppose the Rpg indicator is
*on but what if you try to set to *off, the message supposed to change to
"length of 5".

I'll vote your idea but I think it's a bug in the precompiler.

Best regards
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

Il giorno dom 3 mag 2026 alle ore 13:04 Daniel Gross <daniel@xxxxxxxx>

ha

scritto:

Well - the field is not to short - when BOOLEAN is converted to CHAR,
'T'/'F', 1/0, '1'/'0', 'TRUE'/'FALSE' are all valid char constants.

The BOOLEAN data type was a really good addition for Db2 for i - but

like

most really good additions, it also brings some headaches for existing
programs.

The correct way to circumvent this, would be to change the column to
BOOLEAN - recompile all programs - problem solved.

But this is not the IBM i way. So I think a solution that keeps

backwards

compatibility would be good - and that would mean, that *ON/*OFF should
convert to '1'/'0' and not 'T'/'F' for CHAR(1) fields - maybe as a
pre-compiler option.

Regards,
Daniel

Am 03.05.2026 um 12:50 schrieb Marco Facchinetti <

marco.facchinetti@xxxxxxxxx>:

Suddenly, I'm not so sleepy anymore either :-). If it's as you say,

then

it's truly misleading and deserves further investigation.

Normally I hate the fact that SQL gives an error if the field is too

long

but in this case I'd say it's useful.

Best regards
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

Il giorno dom 3 mag 2026 alle ore 12:28 Daniel Gross <daniel@xxxxxxxx

ha

scritto:

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

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