Call Level Interface Guide and Reference

Data Type Attributes

Information is shown for the following Data Type Attributes:

Precision
Scale
Length
Display Size

The precision of a numeric column or parameter refers to the maximum number of digits used by the data type of the column or parameter. The precision of a non-numeric column or parameter generally refers to the maximum length or the defined length of the column or parameter. The following table defines the precision for each SQL data type.

Table 194. Precision

fSqlType Precision

SQL_CHAR
SQL_VARCHAR
SQL_CLOB

The defined length of the column or parameter. For example, the precision of a column defined as CHAR(10) is 10.
SQL_LONGVARCHAR The maximum length of the column or parameter. ^a

SQL_DECIMAL
SQL_NUMERIC

The defined maximum number of digits. For example, the precision of a column defined as NUMERIC(10,3) is 10.
SQL_SMALLINT ^b 5
SQL_BIGINT 19
SQL_INTEGER ^b 10
SQL_FLOAT ^b 15

SQL_REAL ^b

7

SQL_DOUBLE ^b

15

SQL_BINARY
SQL_VARBINARY
SQL_BLOB

The defined length of the column or parameter. For example, the precision of a column defined as CHAR(10) FOR BIT DATA, is 10.

SQL_LONGVARBINARY

The maximum length of the column or parameter.

SQL_DATE ^b

10 (the number of characters in the yyyy-mm-dd format).
SQL_TIME ^b 8 (the number of characters in the hh:mm:ss format).

SQL_TIMESTAMP

The number of characters in the "yyy-mm-dd hh:mm:ss[.fff[fff]]" format used by the TIMESTAMP data type. For example, if a timestamp does not use seconds or fractional seconds, the precision is 16 (the number of characters in the "yyyy-mm-dd hh:mm" format). If a timestamp uses thousandths of a second, the precision is 23 (the number of characters in the "yyyy-mm-dd hh:mm:ss.fff" format).

SQL_GRAPHIC
SQL_VARGRAPHIC
SQL_DBCLOB

The defined length of the column or parameter. For example, the precision of a column defined as GRAPHIC(10) is 10.
SQL_LONGVARGRAPHIC The maximum length of the column or parameter.

Note:

^a
When defining the precision of a parameter of this data type with SQLBindParameter() or SQLSetParam(), cbParamDef should be set to the total length of the data, not the precision as defined in this table.
^b
The cbParamDef argument of SQLBindParameter() or SQLSetParam() is ignored for this data type.

Scale

The scale of a numeric column or parameter refers to the maximum number of digits to the right of the decimal point. Note that, for approximate floating point number columns or parameters, the scale is undefined, since the number of digits to the right of the decimal place is not fixed. The following table defines the scale for each SQL data type.

Table 195. Scale

fSqlType

Scale

SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
SQL_CLOB

Not applicable.

SQL_DECIMAL
SQL_NUMERIC

The defined number of digits to the right of the decimal place. For example, the scale of a column defined as NUMERIC(10, 3) is 3.

SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT

0

SQL_REAL
SQL_FLOAT
SQL_DOUBLE

Not applicable.

SQL_BINARY
SQL_VARBINARY
SQL_LONGVARBINARY
SQL_BLOB

Not applicable.

SQL_DATE
SQL_TIME

Not applicable.

SQL_TIMESTAMP

The number of digits to the right of the decimal point in the "yyyy-mm-dd hh:mm:ss[fff[fff]]" format. For example, if the TIMESTAMP data type uses the "yyyy-mm-dd hh:mm:ss.fff" format, the scale is 3.

SQL_GRAPHIC
SQL_VARGRAPHIC
SQL_LONGVARGRAPHIC
SQL_DBCLOB

Not applicable.

fSqlType	Scale
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_CLOB	Not applicable.
SQL_DECIMAL SQL_NUMERIC	The defined number of digits to the right of the decimal place. For example, the scale of a column defined as NUMERIC(10, 3) is 3.
SQL_SMALLINT SQL_INTEGER SQL_BIGINT	0
SQL_REAL SQL_FLOAT SQL_DOUBLE	Not applicable.
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_BLOB	Not applicable.
SQL_DATE SQL_TIME	Not applicable.
SQL_TIMESTAMP	The number of digits to the right of the decimal point in the "yyyy-mm-dd hh:mm:ss[fff[fff]]" format. For example, if the TIMESTAMP data type uses the "yyyy-mm-dd hh:mm:ss.fff" format, the scale is 3.
SQL_GRAPHIC SQL_VARGRAPHIC SQL_LONGVARGRAPHIC SQL_DBCLOB	Not applicable.

Length

The length of a column is the maximum number of bytes returned to the application when data is transferred to its default C data type. For character data, the length does not include the null termination byte. Note that the length of a column may be different than the number of bytes required to store the data on the data source. For a list of default C data types, see the "Default C Data Types" section.

The following table defines the length for each SQL data type.

Table 196. Length

fSqlType

Length

SQL_CHAR
SQL_VARCHAR
SQL_CLOB

The defined length of the column. For example, the length of a column defined as CHAR(10) is 10.

SQL_LONGVARCHAR

The maximum length of the column.

SQL_DECIMAL
SQL_NUMERIC

The maximum number of digits plus two. Since these data types are returned as character strings, characters are needed for the digits, a sign, and a decimal point. For example, the length of a column defined as NUMERIC(10,3) is 12.

SQL_SMALLINT

2 (two bytes).
SQL_INTEGER
4 (four bytes).

SQL_REAL

4 (four bytes).

SQL_FLOAT

8 (eight bytes).

SQL_DOUBLE

8 (eight bytes).

SQL_BINARY
SQL_VARBINARY
SQL_BLOB

The defined length of the column. For example, the length of a column defined as CHAR(10) FOR BIT DATA is 10.

SQL_LONGVARBINARY

The maximum length of the column.

SQL_DATE
SQL_TIME

6 (the size of the DATE_STRUCT or TIME_STRUCT structure).

SQL_TIMESTAMP

16 (the size of the TIMESTAMP_STRUCT structure).

SQL_GRAPHIC
SQL_VARGRAPHIC
SQL_DBCLOB

The defined length of the column times 2. For example, the length of a column defined as GRAPHIC(10) is 20.

SQL_LONGVARGRAPHIC

The maximum length of the column times 2.

fSqlType	Length
SQL_CHAR SQL_VARCHAR SQL_CLOB	The defined length of the column. For example, the length of a column defined as CHAR(10) is 10.
SQL_LONGVARCHAR	The maximum length of the column.
SQL_DECIMAL SQL_NUMERIC	The maximum number of digits plus two. Since these data types are returned as character strings, characters are needed for the digits, a sign, and a decimal point. For example, the length of a column defined as NUMERIC(10,3) is 12.
SQL_SMALLINT	2 (two bytes).
SQL_INTEGER	4 (four bytes).
SQL_REAL	4 (four bytes).
SQL_FLOAT	8 (eight bytes).
SQL_DOUBLE	8 (eight bytes).
SQL_BINARY SQL_VARBINARY SQL_BLOB	The defined length of the column. For example, the length of a column defined as CHAR(10) FOR BIT DATA is 10.
SQL_LONGVARBINARY	The maximum length of the column.
SQL_DATE SQL_TIME	6 (the size of the DATE_STRUCT or TIME_STRUCT structure).
SQL_TIMESTAMP	16 (the size of the TIMESTAMP_STRUCT structure).
SQL_GRAPHIC SQL_VARGRAPHIC SQL_DBCLOB	The defined length of the column times 2. For example, the length of a column defined as GRAPHIC(10) is 20.
SQL_LONGVARGRAPHIC	The maximum length of the column times 2.

Display Size

The display size of a column is the maximum number of bytes needed to display data in character form. The following table defines the display size for each SQL data type.

Table 197. Display Size

fSqlType Display Size

SQL_CHAR
SQL_VARCHAR
SQL_CLOB

The defined length of the column. For example, the display size of a column defined as CHAR(10) is 10.

SQL_LONGVARCHAR

The maximum length of the column.

SQL_DECIMAL
SQL_NUMERIC

The precision of the column plus two (a sign, precision digits, and a decimal point). For example, the display size of a column defined as NUMERIC(10,3) is 12.
SQL_SMALLINT 6 (a sign and 5 digits).

SQL_INTEGER

11 (a sign and 10 digits).

SQL_BIGINT

20 (a sign and 19 digits).

SQL_REAL

13 (a sign, 7 digits, a decimal point, the letter E, a sign, and 2 digits).

SQL_FLOAT
SQL_DOUBLE

22 (a sign, 15 digits, a decimal point, the letter E, a sign, and 3 digits).

SQL_BINARY
SQL_VARBINARY
SQL_BLOB

The defined length of the column times 2 (each binary byte is represented by a 2 digit hexadecimal number). For example, the display size of a column defined as CHAR(10) FOR BIT DATA is 20.

SQL_LONGVARBINARY

The maximum length of the column times 2.

SQL_DATE

10 (a date in the format yyyy-mm-dd).

SQL_TIME

8 (a time in the format hh:mm:ss).

SQL_TIMESTAMP

19 (if the scale of the timestamp is 0) or 20 plus the scale of the timestamp (if the scale is greater than 0). This is the number of characters in the "yyyy-mm-dd hh:mm:ss[fff[fff]]" format. For example, the display size of a column storing thousandths of a second is 23 (the number of characters in "yyyy-mm-dd hh:mm:ss.fff").

SQL_GRAPHIC
SQL_VARGRAPHIC
SQL_DBCLOB

The defined length of the column or parameter. For example, the display size of a column defined as GRAPHIC(10) is 20.
SQL_LONGVARGRAPHIC The maximum length of the column or parameter.

fSqlType	Display Size
SQL_CHAR SQL_VARCHAR SQL_CLOB	The defined length of the column. For example, the display size of a column defined as CHAR(10) is 10.
SQL_LONGVARCHAR	The maximum length of the column.
SQL_DECIMAL SQL_NUMERIC	The precision of the column plus two (a sign, precision digits, and a decimal point). For example, the display size of a column defined as NUMERIC(10,3) is 12.
SQL_SMALLINT	6 (a sign and 5 digits).
SQL_INTEGER	11 (a sign and 10 digits).
SQL_BIGINT	20 (a sign and 19 digits).
SQL_REAL	13 (a sign, 7 digits, a decimal point, the letter E, a sign, and 2 digits).
SQL_FLOAT SQL_DOUBLE	22 (a sign, 15 digits, a decimal point, the letter E, a sign, and 3 digits).
SQL_BINARY SQL_VARBINARY SQL_BLOB	The defined length of the column times 2 (each binary byte is represented by a 2 digit hexadecimal number). For example, the display size of a column defined as CHAR(10) FOR BIT DATA is 20.
SQL_LONGVARBINARY	The maximum length of the column times 2.
SQL_DATE	10 (a date in the format yyyy-mm-dd).
SQL_TIME	8 (a time in the format hh:mm:ss).
SQL_TIMESTAMP	19 (if the scale of the timestamp is 0) or 20 plus the scale of the timestamp (if the scale is greater than 0). This is the number of characters in the "yyyy-mm-dd hh:mm:ss[fff[fff]]" format. For example, the display size of a column storing thousandths of a second is 23 (the number of characters in "yyyy-mm-dd hh:mm:ss.fff").
SQL_GRAPHIC SQL_VARGRAPHIC SQL_DBCLOB	The defined length of the column or parameter. For example, the display size of a column defined as GRAPHIC(10) is 20.
SQL_LONGVARGRAPHIC	The maximum length of the column or parameter.

[ Top of Page | Previous Page | Next Page ]