MDAC 2.5 SDK - ODBC Programmer's Reference
Appendix D: Data Types


 

C to SQL: Day-Time Intervals

The identifiers for the day-time interval ODBC C data types are:

SQL_C_INTERVAL_DAY
SQL_C_INTERVAL_HOUR
SQL_C_INTERVAL_MINUTE
SQL_C_INTERVAL_SECOND
SQL_C_INTERVAL_DAY_TO_HOUR
SQL_C_INTERVAL_DAY_TO_MINUTE
SQL_C_INTERVAL_DAY_TO_SECOND
SQL_C_INTERVAL_HOUR_TO_MINUTE
SQL_C_INTERVAL_HOUR_TO_SECOND
SQL_C_INTERVAL_MINUTE_TO_SECOND

The following table shows the ODBC SQL data types to which interval C data may be converted. For an explanation of the columns and terms in the table, see "Converting Data from C to SQL Data Types."

SQL type identifier Test SQLSTATE
SQL_CHAR[a]
SQL_VARCHAR[a]
SQL_LONGVARCHAR[a]
Column byte length >= Character byte length

Column byte length < Character byte length[a]

Data value is not a valid interval literal

n/a

22001


22015

SQL_WCHAR[a]
SQL_WVARCHAR[a]
SQL_WLONGVARCHAR[a]
Column character length >= Character length of data

Column character length < Character length of data[a]

Data value is not a valid interval literal

n/a

22001


22015

SQL_TINYINT[b]
SQL_SMALLINT[b]
SQL_INTEGER[b]
SQL_BIGINT[b]
SQL_NUMERIC[b]
SQL_DECIMAL[b]
Conversion of a single-field interval did not result in truncation of whole digits

Conversion resulted in truncation of whole digits

n/a

22003

SQL_INTERVAL_DAY
SQL_INTERVAL_HOUR
SQL_INTERVAL_MINUTE
SQL_INTERVAL_SECOND
SQL_INTERVAL_DAY_TO_HOUR
SQL_INTERVAL_DAY_TO_MINUTE
SQL_INTERVAL_DAY_TO_SECOND
SQL_INTERVAL_HOUR_TO_MINUTE
SQL_INTERVAL_HOUR_TO_SECOND
SQL_INTERVAL_MINUTE_TO_SECOND
Data value was converted without truncation of any fields

One or more fields of data value were truncated during conversion

n/a

22015


[a]   All C interval data types can be converted to a character data type.

[b]   If the type field in the interval structure is such that the interval is a single field (SQL_DAY, SQL_HOUR, SQL_MINUTE, or SQL_SECOND), the interval C type can be converted to any exact numeric (SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, SQL_BIGINT, SQL_DECIMAL, or SQL_NUMERIC).

The default conversion of an interval C type is to the corresponding day-time interval SQL type.

The driver ignores the length/indicator value when converting data from the interval C data type and assumes that the size of the data buffer is the size of the interval C data type. The length/indicator value is passed in the StrLen_or_Ind argument in SQLPutData and in the buffer specified with the StrLen_or_IndPtr argument in SQLBindParameter. The data buffer is specified with the DataPtr argument in SQLPutData and the ParameterValuePtr argument in SQLBindParameter.

The following example demonstrates how to send interval C data stored in the SQL_INTERVAL_STRUCT structure into a database column. The interval structure contains a DAY_TO_SECOND interval; it will be stored in a database column of type SQL_INTERVAL_DAY_TO_MINUTE.

SQL_INTERVAL_STRUCT is;
SQLINTEGER cbValue;

// Initialize the interval struct to contain the DAY_TO_SECOND
// interval "154 days, 22 hours, 44 minutes, and 10 seconds"
is.intval.day_second.day      = 154;
is.intval.day_second.hour     = 22;
is.intval.day_second.minute   = 44;
is.intval.day_second.second   = 10;
is.interval_sign              = SQL_FALSE;

// Bind the dynamic parameter
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_INTERVAL_DAY_TO_SECOND,
                  SQL_INTERVAL_DAY_TO_MINUTE, 0, 0, &is, 
                  sizeof(SQL_INTERVAL_STRUCT), &cbValue);

// Execute an insert statement; "interval_column" is a column
// whose data type is SQL_INTERVAL_DAY_TO_MINUTE.
SQLExecDirect(hstmt,"INSERT INTO table(interval_column) VALUES (?)",SQL_NTS);