Topic
  • 5 replies
  • Latest Post - ‏2012-09-19T03:37:13Z by JackCallahan
Arthur Adams
Arthur Adams
12 Posts

Pinned topic SQLRPGLE Error with ISO dates

‏2012-09-11T15:56:54Z |
I have an SQLRPGLE program that is selecting into an externally defined data structure which gets its definition from a physical file. The record format (and therefore the data structure) contain ISO date and time fields.

When the RPG program runs, the SQL return code contains -181 after each select statement, which indicates there is bad data in a date, time or timestamp field. However, if I run the same SQL statement via Run SQL Scripts (iNavigator), the statement runs successfully.

When I looked at the generated D specs in the program listing I noticed that the record format date fields are defined as (for example) ODDT03 10D DATFMT (*ISO-), but the equivalent date field in the receiving SQL data structure is defined as SQL_00063 363 370D DATFMT(*DMY/). In other words, the SQL date field is 2 bytes too short for the data being moved to it.

Am I reading this correctly and, if so, is this a bug or is there some other explanation?

We are currently running IBMi 6.1 and the highest PTF for 5761WDS is SI47610.
Updated on 2012-09-19T03:37:13Z at 2012-09-19T03:37:13Z by JackCallahan
  • PWConner
    PWConner
    20 Posts

    Re: SQLRPGLE Error with ISO dates

    ‏2012-09-11T18:53:43Z  
    I'm guessing you can either change the DatFmt keyword value to *ISO on the CrtSQLRPGI command or use embedded SQL as set option datFmt = *iso. Play with either of those suggestions and let me know if it works.

    I think the default of datFmt = *job is causing a problem during compile or run time of your sqlrpg code.
  • Arthur Adams
    Arthur Adams
    12 Posts

    Re: SQLRPGLE Error with ISO dates

    ‏2012-09-12T09:01:54Z  
    • PWConner
    • ‏2012-09-11T18:53:43Z
    I'm guessing you can either change the DatFmt keyword value to *ISO on the CrtSQLRPGI command or use embedded SQL as set option datFmt = *iso. Play with either of those suggestions and let me know if it works.

    I think the default of datFmt = *job is causing a problem during compile or run time of your sqlrpg code.
    Well, here's an interesting thing.

    Including the date format in the H specs H DatFmt(*ISO-) makes no difference at all, but specifying the format at compile time does work and the program then runs successfully. This is not ideal, because I know that if I have to amend the program in 6 month's time I will forget to specify those parameters again even if I leave some comments in the source to help me remember.

    This problem must surely be a bug in the RPG compiler. The generated SQL fields must have the same attributes as the field they are reproducing or, as I demonstrated, the program will fail.
  • barbara_morris
    barbara_morris
    385 Posts

    Re: SQLRPGLE Error with ISO dates

    ‏2012-09-12T13:32:22Z  
    Well, here's an interesting thing.

    Including the date format in the H specs H DatFmt(*ISO-) makes no difference at all, but specifying the format at compile time does work and the program then runs successfully. This is not ideal, because I know that if I have to amend the program in 6 month's time I will forget to specify those parameters again even if I leave some comments in the source to help me remember.

    This problem must surely be a bug in the RPG compiler. The generated SQL fields must have the same attributes as the field they are reproducing or, as I demonstrated, the program will fail.
    Hi Arthur,

    You can use the SQL statement SET OPTION in your source to tell the SQL precompiler which date or time formats to use.
    
    c/exec sql set option datfmt=*iso, timfmt=*iso c/end-exec
    

    Here's the documentation that explains how the date-formats are handled by the precompiler:
    http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Frzajp%2Frzajpdeclaringirpg.htm
  • Arthur Adams
    Arthur Adams
    12 Posts

    Re: SQLRPGLE Error with ISO dates

    ‏2012-09-12T17:15:41Z  
    Hi Arthur,

    You can use the SQL statement SET OPTION in your source to tell the SQL precompiler which date or time formats to use.
    <pre class="jive-pre"> c/exec sql set option datfmt=*iso, timfmt=*iso c/end-exec </pre>
    Here's the documentation that explains how the date-formats are handled by the precompiler:
    http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Frzajp%2Frzajpdeclaringirpg.htm
    Thanks Barbara and Patrick for the tip on using the SET OPTION statement, which means I won't now forget to re-apply the appropriate parameters.

    However, while the RPG compiler is working as designed, I would suggest that perhaps it should work differently.

    Each field used in an SQL statement has a corresponding generated SQL field so why doesn't the SQL field simply take the attribute of the original field? The compiler does that with numeric and character fields so why not do the same for date and time fields? What would happen if there were two files, perhaps from different pieces of third party software, with date and time fields with different attributes? The SQL SET OPTION can only set the format one way, so a program using those files would fail in the same way that my program was failing.
  • JackCallahan
    JackCallahan
    1 Post

    Re: SQLRPGLE Error with ISO dates

    ‏2012-09-19T03:37:13Z  
    Thanks Barbara and Patrick for the tip on using the SET OPTION statement, which means I won't now forget to re-apply the appropriate parameters.

    However, while the RPG compiler is working as designed, I would suggest that perhaps it should work differently.

    Each field used in an SQL statement has a corresponding generated SQL field so why doesn't the SQL field simply take the attribute of the original field? The compiler does that with numeric and character fields so why not do the same for date and time fields? What would happen if there were two files, perhaps from different pieces of third party software, with date and time fields with different attributes? The SQL SET OPTION can only set the format one way, so a program using those files would fail in the same way that my program was failing.
    > Arthur Adams wrote:
    > Thanks Barbara and Patrick for the tip on using the SET OPTION statement, which means I won't now forget to re-apply the appropriate parameters.
    >
    > However, while the RPG compiler is working as designed, I would suggest that perhaps it should work differently.
    >
    > Each field used in an SQL statement has a corresponding generated SQL field so why doesn't the SQL field simply take the attribute of the original field? The compiler does that with numeric and character fields so why not do the same for date and time fields? What would happen if there were two files, perhaps from different pieces of third party software, with date and time fields with different attributes? The SQL SET OPTION can only set the format one way, so a program using those files would fail in the same way that my program was failing.

    Arthur:

    Internally DB2 stores the date as as integer (believe it's a Lilian date value- number of days from 14 October 1582). Using SET OPTION allows a programmer to treat all dates within a program with a consistent format regardless of the external formatting specified in file descriptions.

    If IBM implemented your suggestion, your example would present a problem- the programmer would have to convert one (or both) sets of differently formatted dates from each vendors to some common format.

    Jack