TOracleTimestamp problems

[mournblade]

I have a database to which I connect to via a suite of PL/SQL scripts. The only one that is giving us trouble is the one that uses TimeStamps. I was originally using licensed version of DOA 3.4.6 to connect, but trying to pass a TDateTime resulted in the millisecond field being stored as 000.

So I try using an evaluation copy of 4.0.5 which supports timestamps. However I can't get them to work. I have changed the DeclareVariable from otDate to otTimestamp, and originally tried simply passing the TDateTime which still resulted in the milliseconds being stored as 000. So I tried passing TOracleTimestamp which wouldn't compile and had to change from SetVariable to SetComplexVariable. That "works" too but now when I view it in TOAD I get the milliseconds as 0x0 where x is a number between 1 and 9. The view changes the timestamp by "to_char(clip_start, 'hh:mi:ss:ff3')" since all I get from TOAD and SQL*Plus is "19/MAY/2003 1" and can't figure out a way of seeing the rest of the column. I've tried multiplying the milliseconds by 100, 100000, 1000000, and 1000000000 to see if that has any effect but to no avail.

Any ideas to what I am doing wrong?

 

[Marco Kalter]

What happens if you execute:

select to_char(clip_start, 'DD-MM-YYYY HH24:MI:SSXFF') from ...

 

[mournblade]

I've got it fixed, but now I have another problem. An intermittent "Invalid argument to date encode". However the exact same data may encode the next few tries. I am trying to work out what it is that is causing it. He is the code snippet to encode the date and stuff it into the database:

unsigned char	tmp[2];
    unsigned short	year, month, day;
    unsigned char	dd, hour, min, sec, frame;
    unsigned long	ns;	// nanoseconds

    // decode the date - is there a better way of doing this?
    tmp[0] = szDate[0];  tmp[1] = szDate[1];  year  = atoi(tmp);
    tmp[0] = szDate[2];  tmp[1] = szDate[3];  month = atoi(tmp);
    tmp[0] = szDate[4];  tmp[1] = szDate[5];  day   = atoi(tmp);
    if (year > 90)
    {  	year += 1900;	}
    else
    {  	year += 2000;	}
    TDateTime	dtDate(year, month, day);

    // Timestamp
    TOracleTimestamp *otStartTime = new TOracleTimestamp(osSession, otTimestamp);
    TOracleTimestamp *otEndTime = new TOracleTimestamp(osSession, otTimestamp);

    // decode the start time.  Times are stored as number of frames since midnight
    frame = (ulStartTime % 25);
    ns = (frame * 4) * 10000000;
    NormalTime((ulStartTime / 25), dd, hour, min, sec); // NormalTime returns the day, hour, min and seconds
    otStartTime->SetValues(year, month, day, hour, min, sec, ns);

    // decode the end time
    frame = (ulEndTime % 25);
    ns = (frame * 4) * 10000000;
    NormalTime((ulEndTime / 25), dd, hour, min, sec);
    otEndTime->SetValues(year, month, day, hour, min, sec, ns);

    // add clip
    int		iRetVal;
    oqAddRoute->SetVariable("project_in", iProjectId);
    oqAddRoute->SetVariable("name_in", szName);
    oqAddRoute->SetVariable("day_in", dtDate);
    oqAddRoute->SetVariable("tape_in", iTape);
    oqAddRoute->SetComplexVariable("start_in", otStartTime);
    oqAddRoute->SetComplexVariable("end_in", otEndTime);
    oqAddRoute->SetVariable("mile_start_in", 0);        // no mileage since these are day
    oqAddRoute->SetVariable("mile_end_in", 0);		// no mileage since these are day
    oqAddRoute->SetVariable("comment_in", "");		// no comment
    oqAddRoute->SetVariable("sessionid_in", m_iSessionId);
    try
    {
    	oqAddRoute->Execute();
        iRetVal = oqAddRoute->GetVariable("retval");
    }
    catch (EOracleError &E)
    {
    	ShowMessage(E.Message);
    }
    return iRetVal;

 

[mournblade]

My mistake - its not to do with TOracleTimestamp.

Just a comment, but the TOracleTimestamp documentation seems rather unfinished. A lot of the help for functions just says "Creates a new" and that is it.

Also some help for us C++ coders would be nice (or even just a couple of examples).

G.