RE: Date data type with embedded SQL

Matt,

Here's the fetch statement.

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

Rick

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


Rick, could you should us the code for the FETCH statement?  Also, did
you make sure your RPG DATFMT() is the same as the SQL DATFMT.  The need
to be the same but I do not remember it causing a compile issue.

I recall an issue where the programmer was using dates in a explicitly
defined DS and MODSing it.  I think you have to LIKE() reference with a
MODS used on a multiple fetch situation. 

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 11:05 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Date data type with embedded SQL


I think this has been discussed but my search of the archives hasn't
turned up a solution yet.

I am retrieving records from a file using embedded SQL.  The code
follows.  When I compile the precompiler generates error SQL5011 that my
data structure is undefined or not usable.  In the past this usually
meant I hadn't defined a field properly.  If that's the case this time I
don't see it.  I have determined that if I remove the date field from
the DS the program will at least get past the precompiler.  A post from
the archives about using the set statement to set the SQL date data type
pointed me to an interesting Redbook but didn't provide a solution to my
situation.   I would appreciate any insight anyone may be able to
provide.

This is the data structure:
      // Data Structure for returning Application information from SQL  
     d RtnDtaSet       ds                  Occurs(1280)
Based(RtnDtaSet@)
     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                              

This is how the statement is built:
      // Select statement constants

     d SelStmt1        c                   'SELECT IXWRKQ, IXSYSD,
IXTRAN, -  
     d                                     IXFUSR, IXTDAT, IXTTIM,
IXAPPL, -  
     d                                     IXDLR,  IXLNAM, IXUPDT,
IXAPND, -  
     d                                     IXHOWR, IXGFID, IXBRCH -

     d                                     FROM ISFQUE06 WHERE IXAPPL >=
'    
     d SelStmt2        c                   ' and IXWRKQ = '

     d SelStmt3        c                   ' For read only'

 

      /Free

 

        // Retrieve maintainence images

        If @QueType = 'MNT';

          SelStmt = SelStmt1 + %Char(PosAppl) + SelStmt3;

 

        // Retrieve Rescans & Returns

        Else;

          SelStmt = SelStmt1 + %Char(PosAppl) + SelStmt2 + @QueType +
SelStmt3;
        Endif;

 

      /End-Free

 

      * Prepare SQL create statement

     c/Exec SQL

     c+  Prepare S1 from :selStmt

     c/End-Exec

 

      * Declare the cursor

     c/EXEC SQL

     c+ DECLARE C1 CURSOR FOR S1

     c/END-EXEC

 

      * Open the cursor

     c/EXEC SQL

     c+ OPEN C1

     c/END-EXEC


This is the file layout being read:
Field        From   -To-  Length  Dec  Type
IXWRKQ          1      3       3       ALPHA
IXSYSD          4      4       1       ALPHA
IXTRAN          5     11       7       ALPHA
IXFUSR         12     21      10       ALPHA
IXTDAT         22     31      10       DATE
IXTTIM         32     39       8       TIME
IXAPPL         40     51      12    0  ZONED
IXDLR          52     56       5    0  ZONED
IXLNAM         57     86      30       ALPHA
IXUPDT         87     87       1       ALPHA
IXDCOD         88     88       1       ALPHA
IXRUSR         89     98      10       ALPHA
IXBRCH         99    101       3    0  ZONED
IXGROP        102    103       2       ALPHA
IXSTAY        104    105       2       ALPHA
IXAPND        106    106       1       ALPHA
IXHOWR        107    109       3       ALPHA
IXPRVQ        110    112       3       ALPHA
IXGFID        113    120       8    0  ZONED

Rick Chevalier
AmeriCredit
Enterprise Solutions
Ext. 57178



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.