Variable-length timestamps in ODBC applications
For the SQL TIMESTAMP data type, you can specify a timestamp precision of in the range 0–12 digits. You can update timestamps with up to 12 digits of precision in ODBC applications.
The TIMESTAMP data type maps to the C timestamp data types SQL_C_TYPE_TIMESTAMP and SQL_C_TYPE_TIMESTAMP_EXT. You can use SQL_C_TYPE_TIMESTAMP to describe a timestamp with up to 9 fractional digits (nanoseconds). You can use SQL_C_TYPE_TIMESTAMP_EXT to describe a timestamp with up to 12 fractional digits (picoseconds).
The C data type SQL_C_TYPE_TIMESTAMP maps to the C data structure
TIMESTAMP_STRUCT. The C data type SQL_C_TYPE_TIMESTAMP_EXT maps to
C data structure TIMESTAMP_STRUCT_EXT. Both C data structures contain
a fraction field that holds the first nine digits
of the fractional part of the timestamp, if the timestamp value has
a precision of up to nanoseconds. The TIMESTAMP_STRUCT_EXT contains
a fraction2 field that holds the tenth through twelfth
digits of the fractional part of the timestamp, if the timestamp value
has a precision of up to picoseconds.
When you assign a value to the fraction or fraction2 field,
and the length of the value is less than the length of the field,
ODBC pads the value on the left to nine digits for fraction,
and three digits for fraction2.
Suppose that a Db2 table is defined like this:
CREATE TABLE CLIT1TB1
(TS0 TIMESTAMP(0),
TS3 TIMESTAMP(3),
TS6 TIMESTAMP(9),
TS9 TIMESTAMP(12),
TS12 TIMESTAMP(12))The following code demonstrates
how to set the fraction and fraction2 fields
to insert timestamp values with fractions of seconds into the table.
/* Variables for input data */
TIMESTAMP_STRUCT_EXT H1TSTMP0; /* For timestamp with 0 digits of precision */
TIMESTAMP_STRUCT_EXT H1TSTMP3; /* For timestamp with 3 digits of precision */
TIMESTAMP_STRUCT_EXT H1TSTMP9; /* For timestamp with 9 digits of precision */
TIMESTAMP_STRUCT_EXT H1TSTMP12; /* For timestamp with 12 digits of precision */
TIMESTAMP_STRUCT_EXT H2TSTMP12; /* For timestamp with 12 digits of precision */
/* Variables for input data lengths */
SQLINTEGER LEN_H1TSTMP0;
SQLINTEGER LEN_H1TSTMP3;
SQLINTEGER LEN_H1TSTMP9;
SQLINTEGER LEN_H1TSTMP12;
SQLINTEGER LEN_H2TSTMP12;
/* Set an initial input timestamp value. For */
/* this example, year, month, day, hour, */
/* minutes, and seconds are the same for all */
/* input variables. fraction and fraction2 */
/* vary. */
TIMESTAMP_STRUCT_EXT input_tstmp =
{2011, 9, 9, 9, 9, 9, 0, 0 };
/* SQL statement buffer */
SQLCHAR sqlstmt[250];
/* Return code for ODBC calls */
SQLRETURN rc = SQL_SUCCESS;
/* Prepare an INSERT statement for inserting */
/* data into table CLIT1TB1. */
strcpy((char *)sqlstmt,
"INSERT INTO CLIT1TB1 VALUES (?, ?, ?, ?, ?)");
rc = SQLPrepare( hstmt, sqlstmt, SQL_NTS );
if( rc != SQL_SUCCESS ) goto dberror;
/* Set the first timestamp input host variable */
/* to this value: */
/* 2011-09-09-09.09.09 */
/* Set fraction and fraction2 to 0 because the */
/* first timestamp has no fractional portion. */
H1TSTMP0 = input_tstmp;
H1TSTMP0.fraction=0;
H1TSTMP0.fraction2=0;
/* Set the length of the input host variable */
LEN_H1TSTMP0 = sizeof(H1TSTMP0);
/* Bind the first timestamp value */
rc=SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP_EXT,
SQL_TYPE_TIMESTAMP,
0,
0,
&H1TSTMP0,
sizeof(H1TSTMP0),
&LEN_H1TSTMP0);
if (rc!=SQL_SUCCESS) goto dberror;
/* Set the second timestamp input host variable */
/* to this value: */
/* 2011-09-09-09.09.09.123 */
/* For the fractional part to be correctly */
/* interpreted as .123, the fourth through ninth */
/* positions of the fraction field must be set */
/* to zeros. Set fraction2 to 0 to indicate */
/* that the timestamp has no picoseconds */
/* portion. */
H1TSTMP3 = input_tstmp;
H1TSTMP3.fraction=123000000;
H1TSTMP3.fraction2=0;
/* Set the length of the input host variable */
LEN_H1TSTMP3 = sizeof(H1TSTMP3);
/* Bind the second timestamp value */
rc=SQLBindParameter(hstmt,
2,
SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP_EXT,
SQL_TYPE_TIMESTAMP,
0,
3,
&H1TSTMP3,
sizeof(H1TSTMP3),
&LEN_H1TSTMP3);
if (rc!=SQL_SUCCESS) goto dberror;
/* Set the third timestamp input host variable */
/* to this value: */
/* 2011-09-09-09.09.09.000123456 */
/* You can omit leading zeros from the */
/* fractional part of the timestamp. ODBC adds */
/* the leading zeros for you. */
/* Set fraction2 to 0 to indicate that the */
/* timestamp has no picoseconds portion. */
H1TSTMP9 = input_tstmp;
H1TSTMP9.fraction=123456;
H1TSTMP9.fraction2=0;
/* Set the length of the input host variable */
LEN_H1TSTMP9 = sizeof(H1TSTMP9);
/* Bind the third timestamp value */
rc=SQLBindParameter(hstmt,
3,
SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP_EXT,
SQL_TYPE_TIMESTAMP,
0,
9,
&H1TSTMP9,
sizeof(H1TSTMP9),
&LEN_H1TSTMP9);
if (rc!=SQL_SUCCESS) goto dberror;
/* Set the fourth timestamp input host variable */
/* to this value: */
/* 2011-09-09-09.09.09.123456789120 */
/* This value has 12 digits of precision, so you */
/* need to set fraction and fraction2. */
/* Set the first nine fractional digits in */
/* fraction, and the last three digits in */
/* fraction2. */
H1TSTMP12 = input_tstmp;
H1TSTMP12.fraction=123456789;
H1TSTMP12.fraction2=120;
/* Set the length of the input host variable */
LEN_H1TSTMP12 = sizeof(H1TSTMP12);
/* Bind the fourth timestamp value */
rc=SQLBindParameter(hstmt,
4,
SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP_EXT,
SQL_TYPE_TIMESTAMP,
0,
12,
&H1TSTMP12,
sizeof(H1TSTMP12),
&LEN_H1TSTMP12);
if (rc!=SQL_SUCCESS) goto dberror;
/* Set the fifth timestamp input host variable */
/* to this value: */
/* 2011-09-09-09.09.09.123456000012 */
/* This value has 12 digits of precision, so you */
/* need to set fraction and fraction2. */
/* Set the first nine fractional digits in */
/* fraction, and the last three digits in */
/* fraction2. You can omit the leading zero from */
/* the value that goes into fraction2. */
H2TSTMP12 = input_tstmp;
H2TSTMP12.fraction=123456000;
H2TSTMP12.fraction2=12;
/* Set the length of the input host variable */
LEN_H2TSTMP12 = sizeof(H2TSTMP12);
/* Bind the fifth timestamp value */
rc=SQLBindParameter(hstmt,
5,
SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP_EXT,
SQL_TYPE_TIMESTAMP,
0,
12,
&H2TSTMP12,
sizeof(H2TSTMP12),
&LEN_H2TSTMP12);
if (rc!=SQL_SUCCESS) goto dberror;
/* Execute the INSERT statement */
rc=SQLExecute(hstmt);