Oracle timestamp (6) gives SQL_UNKNOWN_TYPE (0) as DATA_TYPE

[#1]

Hi All,

I have a client who is connecting to both tables and views in Oracle 11 via the 32 bit ODBC driver MSORCL32.dll v 6.01.7601.17514 from qv 10. He is not able to acquire data from Timestamp (6) datatype columns, they are marked as SQL_UNKNOWN_TYPE (0) as DATA_TYPE in the table information box. The TYPE_NAME is TIMESTAMP (6). Any ideas please? Do you think I should pass this back formally to support?

I ahve asked him to try this in the mean time:-

Load *;

SQL SELECT TO_CHAR(DTCOL,'DD-MON-YYYY HH24:MI:SSxFF')

FROM TABLENAME;

Cheers,

Jane

[kaushiknsolanki]

HI,

   I dont know this will solve your problem or not, but you can try this,

   Load *, Date(DTCOL,'DD-MM-YYYY hh:mm:ss') as DTCOL;

    Select DTCOL

    From TableName;

Regards,

Kaushik Solanki

[#3]

Hi Kaushik,

The problem is that the SQL SELECT DTCOL gives an error. I am trying to also troubleshhot this over the phone which is hard.

Cheers,

Jane

[kaushiknsolanki]

Hi,

   Can you tell me what kind of error its giving.

Regards,

Kaushik Solanki

[#5]

Hi Kaushik,

It gives this very helpful message!!

SQL##f - SqlState: 00000,ErrorCode: 0, ErrorMsg:

SQL SELECT DTCOL as RawDate

FROM CIRCNETTRUNK.VW_DISTRIBUTION;

If I wrap the DTCOL in the Select statement thus

TO_CHAR(DTCOL,'DD-MON-YYYY HH24:MI:SSxFF') ad CharDate

it works. The ODBC SQL Engine is not parsing timestamp data types correctly so if you force them to char strings it loads and then you have to unpick thedate from it in the Load statement thus

Date#(left(CharDate, 20), 'DD-MMM-YYYY hh:mm:ss') as DateStampDate

Which is obviously tortuous for a new QV user. I think this is a bug situation.

Cheers,

Jane