Column Size

The column (or parameter) size of numeric data types is defined as the maximum number of digits used by the data type of the column or parameter, or the precision of the data. For character types, this is the length in characters of the data; for binary data types, column size is defined as the length in bytes of the data. For the time, timestamp, and all interval data types, this is the number of characters in the character representation of this data. The column size defined for each concise SQL data type is shown in the following table.

SQL type identifier

Column size

All character types [a], [b]

The defined or maximum column size in characters of the column or parameter (as contained in the SQL_DESC_LENGTH descriptor field). For example, the column size of a single-byte character column defined as CHAR(10) is 10.

SQL_DECIMAL SQL_NUMERIC

The defined number of digits. For example, the precision of a column defined as NUMERIC(10,3) is 10.

SQL_BIT [c]

1

SQL_TINYINT [c]

3

SQL_SMALLINT [c]

5

SQL_INTEGER [c]

10

SQL_BIGINT [c]

19 (if signed) or 20 (if unsigned)

SQL_REAL [c]

7

SQL_FLOAT [c]

15

SQL_DOUBLE [c]

15

All binary types [a], [b]

The defined or maximum length in bytes of the column or parameter. For example, the length of a column defined as BINARY(10) is 10.

SQL_TYPE_DATE [c]

10 (the number of characters in the yyyy-mm-dd format).

SQL_TYPE_TIME [c]

8 (the number of characters in the hh-mm-ss format), or 9 + s (the number of characters in the hh:mm:ss[.fff…] format, where s is the seconds precision).

SQL_TYPE_TIMESTAMP

19 (the number of characters in the yyyy-mm-dd hh:mm:ss format) or 20 + s (the number of characters in the yyyy-mm-dd hh:mm:ss[.fff…] format, where s is the seconds precision).

SQL_INTERVAL_SECOND

Where p is the interval leading precision and s is the seconds precision, p (if s=0) or p+s+1 (if s>0). [d]

SQL_INTERVAL_DAY_ TO_SECOND

Where p is the interval leading precision and s is the seconds precision, 9+p (if s=0) or 10+p+s (if s>0). [d]

SQL_INTERVAL_HOUR_TO_ SECOND

Where p is the interval leading precision and s is the seconds precision, 6+p (if s=0) or 7+p+s (if s>0). [d]

SQL_INTERVAL_ MINUTE_TO_ SECOND

Where p is the interval leading precision and s is the seconds precision, 3+p (if s=0) or 4+p+s (if s>0). [d]

SQL_INTERVAL_YEAR SQL_INTERVAL_MONTH SQL_INTERVAL_DAY SQL_INTERVAL_HOUR SQL_INTERVAL_MINUTE

p, where p is the interval leading precision. [d]

SQL_INTERVAL_ YEAR_TO_MONTH SQL_INTERVAL_DAY_ TO_HOUR

3+p, where p is the interval leading precision. [d]

SQL_INTERVAL_ DAY_TO_MINUTE

6+p, where p is the interval leading precision. [d]

SQL_INTERVAL_HOUR_TO_ MINUTE

3+p, where p is the interval leading precision. [d]

[a] For an ODBC 1.0 application calling SQLSetParam in an ODBC 2.0 driver, and for an ODBC 2.0 application calling SQLBindParameter in an ODBC 1.0 driver, when *StrLen_or_IndPtr is SQL_DATA_AT_EXEC for a SQL_LONGVARCHAR or SQL_LONGVARBINARY type, ColumnSize must be set to the total length of the data to be sent, not the precision as defined in this table. [b] If the driver cannot determine the column or parameter length for a variable type, it returns SQL_NO_TOTAL. [c] The ColumnSize argument of SQLBindParameter is ignored for this data type. [d] For general rules about column length in interval data types, see “Interval Data Type Length” earlier in this appendix.

The values returned for the column (or parameter) size do not correspond to the values in any one descriptor field. The values can come from either the SQL_DESC_PRECISION or SQL_DESC_LENGTH field, depending on the type of data, as shown in the following table.

SQL type

Descriptor field corresponding to column or parameter size

All character and binary types	LENGTH
All numeric types	PRECISION
All datetime and interval types	LENGTH
SQL_BIT	LENGTH