Can't use the "Timestamp" data type with ODBC in Oracle...

[Jason D.]

I'm having a few odd errors I was hoping to get some advice on.

I'm using an ODBC connection with the Microsoft ODBC Driver for Oracle
to an Oracle 10g Express Edition database (the same errors occur on a
9i standard with the MS Driver).

When the connection is formed; I'm using SQLExecDirect to fire off a
simple Select * From to the table. But if the table contains the
"timestamp" datatype....the program crashes, SQLExecDirect returns -1
(SQL_ERROR). ODBC isn't talking either...I can't get any error
records.

So any advice on why it's dying? It works using the oracle driver
(that is, the oracle 9i client driver) connected to 9i, but I can't
connect to my 10g express with the oracle driver (it works
occasionally, but usually I get a "[Oracle][ODBC][Ora]ORA-12154:
TNS:could not resolve the connect identifier specified" error message,
though the connection test works fine in Control Panel. The driver
that came with the express edition just dies altogether.
Using the 9i DB/Driver, I can only query from a table with timestamp
IF it has no tuples; it crashes once their are tuples. Specifically,
it crashes at "SQLFetch" not "SQLExecDirect".

A few other odd things. For a table with the "date" data type, the
driver brings it in as SQL Type #11 (which is SQL_TIMESTAMP) instead
of #9 (SQL_DATE), using SQLColumns. However, trying to get the results
from a query using SQLDescribeCols, it returns 93. I've also tried a
MySQL and PostgreSQL database, both always return 91 for date and 93
for Timestamp)

Thanks!

[Pak-Ming Cheung [MSFT]]

The "Microsoft ODBC Driver for Oracle" is only for Oracle 7 and 8 (both of
which are no longer supported by Oracle). The driver has never been tested
against Oracle 9 or Oracle 10, or later. Therefore we actually recommend you
to use Oracle's drivers if possible.

Also, please refer to following KB for more information:

"Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider"
http://support.microsoft.com/default.aspx?scid=kb;EN-US;244661

Note that multiple Oracle homes are not supported by any Microsoft driver.

Oracle has published OLE DB Providers and ODBC Drivers for later versions
of their database server. When I last checked, they were available at:
http://www.oracle.com/technology/software/tech/windows/ole_db/htdocs/utilsof
t.htm

Ming.
MDAC Team, Microsoft.

[Peter Nolan]

Hi Jason,
yes, you have to use a third party driver for oracle...on windows
oracle provides one...on linux, unix I use easysoft odbc drivers.....

>From memory, I think Oracle always returns 93 for dates and
timestamps......

Best Regards
Peter
www.peternolan.com