RE: The datetime format of a member of the host structure array does not match the SQL datetime format.

[Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>]

Thank you Charles!

I really wish the SQL precompile errors were a bit more intuitive.  UGGH!  

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Tuesday, May 23, 2023 10:14 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: The datetime format of a member of the host structure array does not match the SQL datetime format.

You also need to set the SQL date time format to match the RPG one..

exec sql
  set option datfmt = *ISO, timfmt = *ISO;

Charles

On Tue, May 23, 2023 at 8:06 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

> I added date and time fields to an existing table 'CRDSRVCFG' in a working
> program.  Now my program won't compile.
> I get the following error:
> SQL5011: Position 14 Host structure array REC not defined or not usable.
> Reason: The datetime format of a member of the host structure array does
> not match the SQL datetime format.
>
> My file only has 10 fields... so I have no idea what position 14 is in my
> Data Structure.
>
> I found a recent post about this, but no resolution was mentioned other
> than setting the datefmt explicitly.
>
> Code snippets:
>
> ctl-opt datfmt(*iso) timfmt(*iso);
>
>   dcl-ds cfg            extname('CRDSRVCFG') qualified dim(100) inz;
>   end-ds;
>
>   Exec Sql Set Option datfmt=*iso;
>
>   Exec Sql
>     Declare sqlcsr Cursor For
>     Select * from CRDSRVCFG where interface = :interface
>     Order by com_nbr
>     For Read Only;
>
>   Exec Sql Open sqlcsr;
>
> My Procedure to fetch rows:
>
>    dcl-proc  FetchGroup;
>
>      dcl-pi  FetchGroup      ind;
>        rec                   likeds(cfg) dim(100);
>      end-pi;
>
>      dcl-s  sqlRows          int(10);
>      dcl-s  maxrows          int(10) inz(%elem(rec));
>
>      Exec Sql
>        Fetch Next From sqlcsr For :maxrows Rows
>        Into :rec;
> [Logo]<https://www.totalbizfulfillment.com/>    Greg Wilburn
> Director of IT
> 301.895.3792 ext. 1231
> 301.895.3895 direct
> gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
> 1 Corporate Dr
> Grantsville, MD 21536
> www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
> --
> 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.