asked on

Timestamp retrieval

Need your help.

How to retrieve SQLT_TIMESTAMP binary representation (including fraction) in Oracle9 using dynamic embedded SQL ("Method 4": declare->open->describe->fetch->close), and what is the struct format (SQLDA::V[i] for timestamp [when SQLDA::T[i]=SQLT_TIMESTAMP])?

Thanks in advance!

seazodiac

Can you elaborate ? Is it just me having hard time understanding your question?

are you trying to retrieve the timestamp and display them?

Demiurg

ASKER

I write a program (MSVC 6.0) that communicates with Oracle 9 using dynamic embedded SQL.
I want to fetch value(s) (using cursor) of a column of type SQLT_TIMESTAMP. After "prepare" statement sqlda items contain: T[i]=187 (SQLT_TIMESTAMP), L[i]=11 (seems like timestamp struct is 11 bytes long). I assign V[i] to a pointer to 128-byte array (array is zeroed). When "fetch" statement is executed, program fails with exception (memory access violation). Then i tried to put a pointer to another memory block at the beginning of 128-byte array (rest of it is still zeroed). In this case that block is filled with struct like
{
short year;
char month;
char day;
char hour;
char minute;
char second;
}, and than 13 zero bytes except for 15th, which equal to 0x03.
The question now is where is fraction (the column has 6 digits of fraction).
In general, i need to retrieve timestamp not just to display it, but to use it in calculations, thus using string representation is risky due to possible different time formats, so i need some fixed binary one. The workaround i know is to use "EXEC SQL ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD-HH24.MI.SSXFF'" and then fetch it as a string (overriding T[i]=5, for example), but i'd prefer not to use it.

ASKER CERTIFIED SOLUTION

earth man2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Demiurg

ASKER

Thanks!

earth man2

http://download-east.oracle.com/otn_hosted_doc/jdeveloper/904preview/jdbc-javadoc/oracle/sql/TIMESTAMPTZ.html

Byte Represents
0 Century (119 for 1990)
1 Decade (190 for 1990)
2 Month
3 Day
4 Hour
5 Minute
6 Seconds
7 Nanoseconds (Most Significant bit)
8 Nanoseconds
9 Nanoseconds
10 Nanoseconds (Least Significant Bit)
11,12 Region id or Timezone Hour/Minute