Re: Weird SQL Problem -- RPG400-L

1. does the compiler listing (and pre-compiler listing if you select that
option) offer any clues, showing length ?
2. does it matter that "result" is a reserved word in DB2 (although wiki
not specific about which version of DB2)

https://en.wikipedia.org/wiki/List_of_SQL_reserved_words#:~:text=This%20list%20includes%20SQL%20reserved,and%20some%20RDBMSs%20have%20added
.

Jim Franz

On Sat, May 2, 2026 at 3:12 PM 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.