Re: Weird SQL Problem

I have voted  1

Gad


> date: Sun, 3 May 2026 13:28:30 +0200
> from: Daniel Gross <[email protected]>
> 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 <[email protected]>:
> >
> > ?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-kthxv0ud/[email protected]>:
> >>
> >> ?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 <[email protected]>
> 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 <[email protected]
> >:
> >>>>> ?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 <[email protected]> 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 <[email protected]
> >:
> >>>>>> ?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 <[email protected]> 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 <
> [email protected]
> >>>> :
> >>>>>>>> ?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: [email protected]
> >>>>>>>> To subscribe, unsubscribe, or change list options,
> >>>>>>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> >>>>>>>> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> >>>>>>>> Before posting, please take a moment to review the archives
> >>>>>>>> at https://archive.midrange.com/rpg400-l.
> >>>>>>>> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription
> >>> related questions.
> >>>>>>> --
> >>>>>>> This is the RPG programming on IBM i (RPG400-L) mailing list
> >>>>>>> To post a message email: [email protected]
> >>>>>>> To subscribe, unsubscribe, or change list options,
> >>>>>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> >>>>>>> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> >>>>>>> Before posting, please take a moment to review the archives
> >>>>>>> at https://archive.midrange.com/rpg400-l.
> >>>>>>> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription
> >>> related questions.
> >>>>>> --
> >>>>>> This is the RPG programming on IBM i (RPG400-L) mailing list
> >>>>>> To post a message email: [email protected]
> >>>>>> To subscribe, unsubscribe, or change list options,
> >>>>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> >>>>>> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> >>>>>> Before posting, please take a moment to review the archives
> >>>>>> at https://archive.midrange.com/rpg400-l.
> >>>>>> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription
> >>> related questions.
> >>>>>
> >>>>> --
> >>>>> This is the RPG programming on IBM i (RPG400-L) mailing list
> >>>>> To post a message email: [email protected]
> >>>>> To subscribe, unsubscribe, or change list options,
> >>>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> >>>>> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> >>>>> Before posting, please take a moment to review the archives
> >>>>> at https://archive.midrange.com/rpg400-l.
> >>>>>
> >>>>> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription
> >>> related questions.
> >>>>
> >>>> --
> >>>> This is the RPG programming on IBM i (RPG400-L) mailing list
> >>>> To post a message email: [email protected]
> >>>> To subscribe, unsubscribe, or change list options,
> >>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> >>>> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> >>>> Before posting, please take a moment to review the archives
> >>>> at https://archive.midrange.com/rpg400-l.
> >>>>
> >>>> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription
> >>> related questions.
> >>> --
> >>> This is the RPG programming on IBM i (RPG400-L) mailing list
> >>> To post a message email: [email protected]
> >>> To subscribe, unsubscribe, or change list options,
> >>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> >>> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> >>> Before posting, please take a moment to review the archives
> >>> at https://archive.midrange.com/rpg400-l.
> >>>
> >>> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription
> related
> >>> questions.
> >>>
> >>>
> >> --
> >> This is the RPG programming on IBM i (RPG400-L) mailing list
> >> To post a message email: [email protected]
> >> To subscribe, unsubscribe, or change list options,
> >> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> >> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> >> Before posting, please take a moment to review the archives
> >> at https://archive.midrange.com/rpg400-l.
> >>
> >> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org 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: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related
> questions.
>
>
>
> ------------------------------
>
> End of RPG400-L Digest, Vol 25, Issue 140
> *****************************************
>
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions.