Issues with timestamps and interval types and FieldsAsString.

[MarkF]

Timestamps and Intervals as strings currently don't work as the buffer size isn't being set properly due to a couple of bugs in function TOCIRecordSet.GetFieldDesc8. Here's the fixed code (only the lines marked "mjf" have been changed.):

Code: Select all

        dtTimeStamp, dtTimeStampTZ, dtTimeStampLTZ,
        dtIntervalYM, dtIntervalDS:
          // if (Field.DataType <> dtString) then begin  // mjf: original line: doesn't check for dtWideString
          if ((Field.DataType <> dtString) and (Field.DataType <> dtWideString)) then begin  // mjf: fixed line
            Field.Length := Field.DBLength;
            if Field.DataType = dtIntervalDS then
              Field.Scale := Field.DBScale;
          end
          else
            // case OraType of  // mjf: original line: incorrectly checking the Oracle type SQLT_ constants instead of the odac dt* types
            case Field.SubDataType of  // mjf: fixed line
              dtTimeStamp, dtTimeStampLTZ:
                Field.Length := GetNlsFieldDesc(nlsTimeStampFormat);
              dtTimeStampTZ:
                Field.Length := GetNlsFieldDesc(nlsTimeStampTZFormat);
              dtIntervalYM:
                  Field.Length := Field.DBLength + 4; // interval year to month form is +Prec-MM
              dtIntervalDS:
                  Field.Length := Field.DBLength + Field.DBScale + 11; // interval day to second form is +Prec HH:MM:SS.Scale
            end;

It would be nice to get this fix into the next release. Note also that the function GetNlsFieldDesc doesn't return large enough values for timestamps (they get cut by a couple of characters depending on the format.) Note also that this function also has the issue that the nls formats are cached and never update (which is a sticky issue, I just go ahead and replace the function to return 50... maybe not the best solution, but better than how it works now.)

Thanks,

Mark Ford
Benthic Software

[bork]

Hello

To add this fix in the next release, we need to reproduce the issue, that will be resolved by this fix. Can you provide the SQL script to create the table, that cannot be fetched. Or have you any issue with timestamps and interval parameters? In addition, we need this information to add this issue in our auto-tests.

[MarkF]

Hi Bork,

I'm actually quite busy today, so I can't supply a full script. Here are the instructions to reproduce the error:

1. Create a query object.
2. Set FieldsAsString to true.
3. Run "select systimestamp from dual"
4. You'll see that the timestamp gets cut to 6 characters or so.

However, even if you can't reproduce it (which seems unlikely) take a look at the comments I added to the code I posted. You'll notice that you are comparing the SQLT_ typecode of the datatype to your internal dtXXXX typecodes (which is obviously a bug.)

I've already fixed it here of course, I'm now just trying to save myself time with future releases and perhaps help some of your other customers who may hit the bug.

-Mark

[AlexP]

Hello,

Thank you for the information. We will try to fix this behaviour in the nearest future

[bork]

We commit this fix and it will be added to the next ODAC release.