get/set TIMESTAMP values using OCI

0
votes

I'm using C and oci.h to set and get VARCHAR, NUMBER, ... DATE values but I also need the TIMESTAMP type. I can read size, scale and precision from OCIAttrGet's on each column

Get

In my OCIDefineByPos, how do I know how much space to allocate for the TIMESTAMP type? When I OCIStmtFetch2(), how can I interpret the value retrieved?

Set

In OCIBindByPos() and OCIStmtExecute(), how do I convert my timestamp format into oracle's? I'll need to know the space required, too.

1
oracleoracle-call-interface

1 Answers

1
votes

To help with getting a TIMESTAMP (from https://docs.oracle.com/html/E49886_05/oci12oty.htm)

Example 12-2 Manipulating an Attribute of Type OCIDateTime

...

/* allocate the program variable for storing the data */
OCIDateTime *tstmpltz = (OCIDateTime *)NULL;

/* Col1 is a time stamp with local time zone column */
OraText *sqlstmt = (OraText *)"SELECT col1 FROM foo";

/* Allocate the descriptor (storage) for the data type */
status = OCIDescriptorAlloc(envhp,(void  **)&tstmpltz, OCI_DTYPE_TIMESTAMP_LTZ,
         0, (void  **)0);
....

status = OCIStmtPrepare (stmthp, errhp, sqlstmt, (ub4)strlen ((char *)sqlstmt),
         (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);

/* specify the define buffer for col1 */
status = OCIDefineByPos(stmthp, &defnp, errhp, 1, &tstmpltz, sizeof(tstmpltz),
         SQLT_TIMESTAMP_LTZ, 0, 0, 0, OCI_DEFAULT);

/* Execute and Fetch */
OCIStmtExecute(svchp, stmthp, errhp, 1, 0,(OCISnapshot *) NULL,
         (OCISnapshot *)NULL, OCI_DEFAULT)

At this point tstmpltz contains a valid time stamp with local time zone data. You
can get the time zone name of the datetime data using:

status = OCIDateTimeGetTimeZoneName(envhp, errhp, tstmpltz, (ub1 *)buf,
         (ub4 *)&buflen);
...