RE: Date data type with embedded SQL

[<Rick.Chevalier@xxxxxxxxxxxxxxx>]

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.