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

Hi,

if no date format is specified in the H-Specs, date format *ISO is used for
all date fields defined in the D-Specs, as long as there is no different
date format specified.

If the MODS works with date fields defined with LIKE, but not with the data
type D, it is in my opinion a bug and should be reported.

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 22:11
An: rpg400-l@xxxxxxxxxxxx
Betreff: RE: Date data type with embedded SQL


Rob,

There isn't a date format keyword on the H spec.

Rick

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


That's an idea.  What's the date format on your H spec?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Tyler, Matt" <mattt@xxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/06/2006 02:40 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

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.
--

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.


--
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.


--
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.



--
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.