Skip to Main Content
  • Questions
  • ODBC does not support interval data type

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, zhang.

Asked: April 19, 2017 - 12:32 am UTC

Last updated: April 24, 2017 - 1:58 am UTC

Version: org 11g

Viewed 1000+ times

You Asked

Hi, In Oracle through the ODBC API query a type of data for the Interval day to second, call SQLBindCol interface returned that the data does not support the type; I binding the column with varchar, call SQLFetch interface directly crash. Check some information, Interval type in Oracle 9i has been supported, is how to set up, or ODBC version is too low, how to upgrade? Thank you.

and Connor said...

I think you're out of luck.

From MOS Note Doc ID 730225.1

"There is a enhancement request already filed for implementing TIMESTAMP and INTERVAL with Oracle ODBC Driver: Oracle Development is considering these requests and are planning to implement such functionalities. No projected/estimated date are available at this stage. Oracle's ODBC driver support basic TIMESTAMP datatype. Just not with TZ, LTZ, interval."




Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

zhang CJay, April 19, 2017 - 2:31 am UTC

Thanks for your reply. it means that I can not directly query the interval type of data? but like this query text: "SELECT INTERVAL_DAY_TO_SECOND_COLUMN FROM TABLE", how can I get the result? Convert it to "varchar"? Thanks.
Connor McDonald
April 24, 2017 - 1:58 am UTC

Unfortunately that is the case. Options would be:

1) query it back as char, and convert back for further use, eg

SQL> create table t ( t1 interval day to second);

Table created.

SQL> insert into t values ( systimestamp - cast(systimestamp-2.34 as timestamp) );

1 row created.

SQL>
SQL> select t1 from t;

T1
---------------------------------------------------------------------------
+02 08:09:36.575000

SQL> select to_char(t1) from t;

TO_CHAR(T1)
--------------------
+02 08:09:36.575000

SQL> insert into t values ( to_dsinterval('+02 08:09:36.374000'));

1 row created.



2) Use the components of the interval individually, eg

SQL> select
  2    extract(day from t1) d,
  3    extract(hour from t1) h,
  4    extract(minute from t1) m,
  5    extract(second from t1) s
  6  from t;

         D          H          M          S
---------- ---------- ---------- ----------
         2          8          9     36.575
         2          8          9     36.374