Re: Weird SQL Problem

I have voted 1

Gad

date: Sun, 3 May 2026 13:28:30 +0200
from: Daniel Gross <daniel@xxxxxxxx>
subject: Re: Weird SQL Problem

I have created an IBM idea to change the behavior of the pre-compiler,
because I think, that is not the IBM-i way of handling things:

-> https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-4904

If you like it, please vote.

Thanks
Daniel

Am 03.05.2026 um 13:04 schrieb Daniel Gross <daniel@xxxxxxxx>:

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

------------------------------

Subject: Digest Footer

--
This is the RPG programming on IBM i (RPG400-L) digest 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 http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.



------------------------------

End of RPG400-L Digest, Vol 25, Issue 140
*****************************************