Re: ORACLE Date to C

In article <3lh41m$a4k_at_abacus.tis.tandy.com>, bpearc1_at_abacus.tis.tandy.com (Byron Pearce) writes:
|>Russell Dickerson (dickerso_at_gomez.stortek.com) wrote:
|>: Does anyone have words of wisdom for getting/putting a timestamp from/info
|>: my Oracle database? I am using Pro*C. The manuals are not very clear on
|>: how this works. I would love to be able to use the UNIX time_t (long)
|>: to do this.
|>
|>Use the TO_CHAR function when SELECTing a DATE from the database and into
|>a host variable. Use TO_DATE when INSERTing or UPDATEing a host variable
|>into the database. Check the SQL Reference manual for details on this.
|>

I had the same question as Russ, so I tried it. While Byron is right, in version 2 of Pro*C, the TO_DATE and TO_CHAR conversions are not needed. If the host variable is a character string, and the table column is a date, then Oracle does the conversion for you. If fact, Oracle seem to always do this implicit conversion. I tried to get the date out in the internal 7-byte format, but without success. The format will be whatever your default NLS_DATE is set to.

The reason I point this out is because with the new feature in version 2 of selecting and inserting directly into/out of a C structure, there is no need to list each field. So instead of doing

EXEC SQL Insert into people (name, address, DOB)   values (:rec.name, :rec.addr, TO_DATE(:rec.dob) );

You can do:

EXEC SQL Insert into people values :rec;

Which is much cleaner. Note that this does depend on your record structure matching the table definition, which could be considered a risk. But you only need to define the structure to match the table in one place, which is better than needing to match columm names to host variables all over the place.

But I think we are missing the point. Russ (and any normal C programmer) would much rather define the rec.dob field as a time_t rather than a char[DATE_LEN+1]. I haven't found any clever way to get Oracle to convert from a Date to a time_t for you.

I would suggest that you define a union type for time_t and char[DATE_LEN+1]. Then you could have Oracle put the Date values into a string, and you could then convert the string to a time_t in the same structure. Then all of your C code would refer to the time_t part. I said that I *would* suggest that, but the Pro*C book says that Oracle can not handle unions. So your need to figure a way out of that (two structs, one for C, one for Oracle, seems best).

If anyone out there has a better way, I welcome suggestions.

Stan

(no cool sig.) Received on Mon Apr 03 1995 - 00:00:00 CEST