precision information of timestamp and intarval

[kubo]

Could you add lfprec and fsprec to dpiQueryInfo? If they are added, users can know precisions of timestamp and interval data types. There is no merit for driver developers. Drivers can handle these Oracle data types without precisions. This is just information for users.

ruby-oci8 can print such information as follows:

conn = OCI8.new('username/password')
conn.exec(<<EOS)
  create table test_types (
    col1 timestamp not null,
    col2 timestamp(9),
    col3 interval day to second,
    col4 interval day(2) to second(3)
  )
EOS
cursor = conn.exec('select * from test_types where 1 = 2')
# string representation of column types which could be put in create table statements
cursor.column_metadata[0].data_type_string # => TIMESTAMP NOT NULL
cursor.column_metadata[1].data_type_string # => TIMESTAMP(9)
cursor.column_metadata[2].data_type_string # => INTERVAL DAY TO SECOND
cursor.column_metadata[3].data_type_string # => INTERVAL DAY(2) TO SECOND(3)

[anthony-tuininga]

I'll take a look and will implement this suggestion if there aren't any difficulties in doing so!

[kubo]

Thank you! I'm looking forward to it.

[anthony-tuininga]

I just checked. This information is already available! For timestamps, the value is found in the "scale" attribute. For intervals, the first value is found in the "precision" attribute and the second value is found in the "scale" attribute. I'll update the documentation accordingly.

[anthony-tuininga]

On the other hand, that behavior of OCI is confusing and looking at the other metadata, those fields are separated....so I'll likely separate them here, too!

[kubo]

I had checked scale and precision attributes values for timestamp, interval year to month and interval day to second.

• scale values are same with fsprec values for timestamp.
• precision values are same with lfprec values for interval year to month.

These results are same with yours.

However, for interval day to second: See: https://github.com/kubo/ruby-oci8/blob/ruby-oci8-2.2.4/test/test_metadata.rb#L2393-L2405

• precision and scale values are same with lfprec and fsprec values respectively when the metadata are retrieved from select statements.
• precision and scale values are same with fsprec and lfprec values respectively when the metadata are retrieved from OCIDescribeAny().

ODPI-C currently exposes precision and scale values only from select statements. But if ODPI-C retrieves metadata from OCIDescribeAny() in future, precision and scale values are exchanged.

[anthony-tuininga]

I added the dpiDataTypeInfo structure suggested in #23. In that structure I have a column called fsPrecision which is used for fractional seconds precision on timestamps and interval day to second values. From what I can tell the precision values are accurate but I'd appreciate it if you took a look and confirmed that for me! Thanks.

[kubo]

Sorry, what I wrote in https://github.com/oracle/odpi/issues/22#issuecomment-321405406 is the result of my tests just for interest. lfprec and fsprec should be got by OCI_ATTR_LFPRECISION and OCI_ATTR_FSPRECISION respectively as documented in type attribute attributes. These are available for columns but not documented in column attributes. IMO, it is a documentation bug.

[anthony-tuininga]

Thanks, Kubo. I am not using those particular attributes but the standard precision and scale attributes. I'll find out internally which attributes are the right ones to use and modify accordingly. But in the meantime the code appears to work just fine!

[kubo]

Okay, thanks! It woks fine.