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)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von
Rick.Chevalier@xxxxxxxxxxxxxxx
Gesendet: Freitag, 6. Januar 2006 19:05
An: rpg400-l@xxxxxxxxxxxx
Betreff: 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.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.