RE: Date data type with embedded SQL -- RPG400-L

Matt,

So what you are saying is that it will work without date data types but not 
with date data types?  The files I have been working with in the past are 
ancient and don't use date or time data types.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tyler, Matt
Sent: Friday, January 06, 2006 2:45 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Date data type with embedded SQL


Rick, you cannot use a MODS that is not defined with LIKE() references
for dates, for some reason.  

This sample code will not compile, also.  If Change the references from
REC1 to REC then it compiles.  If I use REC1 with out ipstdt reference
in the DS, then the program compiles.

     H DATFMT(*USA)

     D REC1            DS                  OCCURS(3)

     D  ipupc                        15p 0

     D  ipstn                         3p 0

     D  ipstdt                         d   datfmt(*USA)

     D REC             DS                  OCCURS(3)

     D  upc                                like(ipupc)

     D  stn                                like(ipstn)

     D  stdt                               like(ipstdt)

 

     C/EXEC SQL

     C+ SET OPTION DATFMT = *USA

     C/END-EXEC

     C/EXEC SQL

     C+ DECLARE CSR CURSOR FOR SELECT IPUPC, IPSTN, IPSTDT FROM
TESTSQLPF
     C+ WHERE IPUPC=5100000011

     C/END-EXEC

     C     SQLSTT        DSPLY

     C/EXEC SQL

     C+ OPEN CSR

     C/END-EXEC

     C     SQLSTT        DSPLY

     C/EXEC SQL

     C/EXEC SQL

     C+ FETCH NEXT FROM CSR FOR 3 ROWS INTO :REC1

     C/END-EXEC

     C     SQLSTT        DSPLY

 

     C                   DO        3             X                 3 0

     C     X             OCCUR     REC1

     C     STDT          DSPLY

     C                   ENDDO

     C                   EVAL      *INLR = *ON

     C                   RETURN


Thank you,
Matt Tyler
WinCo Foods, LLC
mattt@xxxxxxxxxxxxxx


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Friday, January 06, 2006 12:58 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: Date data type with embedded SQL

More information.

I changed it back to a single record fetch and the precompiler liked it.
I don't know what's different between the two.  I would really like to
get the multiple record fetch working.

Here's what I have now.

     d RtnDtaSet       ds                  
     d  AWrkQ                         3a   
     d  ASysId                        1a   
     d  ATrans                        7a   
     d  AUser                        10a   
     d  ALdDate                        D   
     d  ALdTime                        T   
     d  AAppl#                       12s 0 
     d  ADlr#                         5s 0 
     d  ALName                       30a   
     d  AUpdt                         1a   
     d  AApnd                         1a   
     d  AHowR                         3a   
     d  AGfxId                        8s 0 
     d  ABrnch                        3s 0 

     c                   If        @QueType = 'MNT'

     c/EXEC SQL

     c+ DECLARE C1 CURSOR FOR

     c+ SELECT IXWRKQ, IXSYSD, IXTRAN, IXFUSR, IXTDAT, IXTTIM, IXAPPL,

     c+        IXDLR,  IXLNAM, IXUPDT, IXAPND, IXHOWR, IXGFID, IXBRCH

     c+ FROM ISFQUE06

     c+ WHERE IXAPPL >= :PosAppl

     c************+   AND IXTDAT >= :InitDate AND IXTTIM >= :InitTime

     c+ FOR READ ONLY

     c/END-EXEC

      *

     c/EXEC SQL

     c+ OPEN C1

     c/END-EXEC

     c                   Else

     ?* For Rescans & Returns

     c/EXEC SQL

     c+ DECLARE C2 CURSOR FOR

     c+ SELECT IXWRKQ, IXSYSD, IXTRAN, IXFUSR, IXTDAT, IXTTIM, IXAPPL,

     c+        IXDLR,  IXLNAM, IXUPDT, IXAPND, IXHOWR, IXGFID, IXBRCH

     c+ FROM ISFQUE06

     c+ WHERE IXWRKQ = :@QueType    AND   IXAPPL >= :PosAppl

     c*************+   AND IXTDAT >= :InitDate  AND   IXTTIM >=
:InitTime
     c+ FOR READ ONLY

     c/END-EXEC

     ?*

     c/EXEC SQL

     c+ OPEN C2

     c/END-EXEC

     c                   Endif

     ?*


     c/EXEC SQL                 
     c+ FETCH C1 into :RtnDtaSet
     c/END-EXEC                 

     c/Exec SQL         
     c+ Set Option      
     c+  Commit = *None,
     c+  DatFmt = *ISO, 
     c+  TimFmt = *ISO  
     c/End-Exec         

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Chevalier, Rick
Sent: Friday, January 06, 2006 1:27 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: Date data type with embedded SQL



Birgitta,

Sorry, I forgot to add that to the post.  I found reference to the
DATFMT set option in one of your archived posts.  It didn't seem to help
however.  My current set statement follows. 

     c/Exec SQL         
     c+ Set Option      
     c+  Commit = *None,
     c+  DatFmt = *ISO  
     c/End-Exec         

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of HauserSSS
Sent: Friday, January 06, 2006 12:38 PM
To: RPG programming on the AS400 / iSeries
Subject: AW: Date data type with embedded SQL


Hi Rick,

add a set option statement to fix the date and time format used by SQL.
The date format must have a 4 digit year, that means you specify one of
the
following formats *ISO, *EUR, *USA, *JIS.
The precompiler generates an extra field for each host variable used in
an
SQL-Statement. (SQL...)
Date and time fields get the date/time format that is either specified
in
the CRTSQLRPGI command option DATFMT and TIMEFMT or in the SET OPTION
statement. The default value in the CRTSQLRPGI is *JOB and the job date
format normally uses only a 2 digit year. In this way the field create
by
the SQL precompiler only uses 8 bytes and not 10 as expected in your
data
structure.

C/EXEC SQL  SET OPTION DATFMT = *ISO, TIMFMT = *ISO
C/END-EXEC

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)


Privileged and Confidential.  This e-mail, and any attachments there to,
is intended only for use by the addressee(s) named herein and may
contain privileged or confidential information.  If you have received
this e-mail in error, please notify me immediately by a return e-mail
and delete this e-mail.  You are hereby notified that any dissemination,
distribution or copying of this e-mail and/or any attachments thereto,
is strictly prohibited.