All Forums Database
Angellore 19 posts Joined 06/10
02 Feb 2014
CLIv2 program and TimeStamp

Hi all,
I am writing CLIv2 program (acutally this will be external procedure later) and have following question. When issuing following query "SELECT current_timestamp(0)" in the program to Teradata the result row is represented in the CHAR format YYYY-MM-DD HH:MI:SS. Is it possible to make CLIv2 return TimeStamp data type, I mean:
 
typedef struct TimeStamp
{
    DECIMAL4 seconds;   /* represented as DECIMAL(8,6) */
    SMALLINT year;
    BYTEINT  month;
    BYTEINT  day;
    BYTEINT  hour;
    BYTEINT  minute;
} TimeStamp
 
The other question is:
If the answer to the previous question is no, then how can I get actual data type of the column? In some way ODBC and .NetProvider know the data type of each column when resultset is returned to the client.
I use following DBCAREA settings:
    dbcarea_pt->resp_mode = 'I';
    dbcarea_pt->change_opts = 'Y';
    dbcarea_pt->use_presence_bits = 'Y';
    dbcarea_pt->keep_resp = 'N';
    dbcarea_pt->loc_mode = 'Y';
    dbcarea_pt->var_len_req = 'N';
    dbcarea_pt->save_resp_buf = 'N';
    dbcarea_pt->two_resp_bufs = 'N';
    dbcarea_pt->ret_time = 'N';
    dbcarea_pt->wait_for_resp = 'Y';
    dbcarea_pt->req_proc_opt = 'B';
    dbcarea_pt->req_buf_len = 1024;
    dbcarea_pt->resp_buf_len = 1024;
    dbcarea_pt->data_encryption = 'N';
 
Thanks for help,
Dima

Angellore 19 posts Joined 06/10
02 Feb 2014

I found one way to determine whether column is CHAR or TimeStamp/Time. One need to use Format:
tmp = (PrepInfo_t *)(dbcarea_pt->fet_data_ptr);
for (i = 0; i < 100; i++)
                printf("%c", ((char *)&tmp->ColInfo[0])[i]);
 
This prints out all ColInfo_t structure one of the fields of each is format.
It appears that Timestamp has ALWAYS 'YYYY-MM-DD HH:MI:SS*' as format and Time 'HH:MI:SS*'.
Any other data type can't have such format.
This is really ugly, but I remember some severe problems while working with Teradata Timestamps in Visual Studio with .NetProvider and ODBC.
Hope this helps anyone.

Adeel Chaudhry 773 posts Joined 04/08
02 Feb 2014

What exactly are you trying to code?

-- If you are stuck at something .... consider it an opportunity to think anew.

Angellore 19 posts Joined 06/10
03 Feb 2014

I am writing external stored procedure which can execute dynamic call statement. Call statement can have out parameters. The only problem I see is with Timestamp and Time, when DBCAREA specifies that data type of the Time* fields is CHAR.

Adeel Chaudhry 773 posts Joined 04/08
03 Feb 2014

Am sure you will take care .... but just beware of the out-of-memory exceptions. They can occur in this sort of implementation.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.