Data Type Usage

ODBC and SQL Server

ODBC and SQL Server

Data Type Usage

The Microsoft® SQL Server™ ODBC driver and SQL Server impose the following use of data types.

Data type Limitation
Date literals Date literals, when stored in a SQL_TYPE_TIMESTAMP column (SQL Server data types of datetime or smalldatetime), have a time value of 12:00:00.000 A.M.
money and smallmoney Only the integer parts of the money and smallmoney data types are significant. If the decimal part of SQL money data is truncated during data type conversion, the SQL Server ODBC driver returns a warning, not an error.
SQL_BINARY (nullable) When connected to an instance of SQL Server version 6.0 and earlier, if a SQL_BINARY column is nullable, the data that is stored in the data source is not padded with zeroes. When data from such a column is retrieved, the SQL Server ODBC driver pads it with zeroes on the right. However, data that is created in operations performed by SQL Server, such as concatenation, does not have such padding.

Also, when data is placed in such a column in an instance of SQL Server 6.0 or earlier, SQL Server truncates the data on the right if it is too long to fit into the column.

SQL_CHAR (truncation) When connected to an instance of SQL Server 6.0 and earlier, and data is placed into a SQL_CHAR column, SQL Server truncates it on the right without warning if the data is too long to fit into the column.
SQL_CHAR (nullable) When connected to an instance of SQL Server 6.0 and earlier, if a SQL_CHAR column is nullable, the data that is stored in the data source is not padded with blanks. When data from such a column is retrieved, the SQL Server ODBC driver pads it with blanks on the right. However, data that is created in operations performed by SQL Server, such as concatenation, does not have such padding.
SQL_LONGVARBINARY, SQL_LONGVARCHAR, SQL_WLONGVARCHAR Updates of columns with SQL_LONGVARBINARY, SQL_LONGVARCHAR, or SQL_WLONGVARCHAR data types (using a WHERE clause) that affect multiple rows are fully supported when connected to an instance of SQL Server 6.x and later. When connected to an instance of SQL Server 4.2x, an S1000 error "Partial insert/update. The insert/update of a text or image column(s) did not succeed" is returned if the update affects more than one row.
String function parameters string_exp parameters to the string functions must be of data type SQL_CHAR or SQL_VARCHAR. SQL_LONG_VARCHAR data types are not supported in the string functions. The count parameter must be less than or equal to 8,000 because the SQL_CHAR and SQL_VARCHAR data types are limited to a maximum length of 8,000 characters. When connected to an instance of SQL Server 6.5 or earlier, the limit is 255 instead of 8000.
Time literals Time literals, when stored in a SQL_TIMESTAMP column (SQL Server data types of datetime or smalldatetime), have a date value of January 1, 1900.
timestamp Only a NULL value can be manually inserted into a timestamp column. However, because timestamp columns are automatically updated by SQL Server, a NULL value is overwritten.
tinyint The SQL Server tinyint data type is unsigned. A tinyint column is bound to a variable of data type SQL_C_UTINYINT by default.
User-defined data types When connected to an instance of SQL Server 4.2x, the SQL Server ODBC driver adds NULL to a column definition that does not explicitly declare a column's nullability. Therefore, the nullability that is stored in the definition of a user-defined data type is ignored.

When connected to an instance of SQL Server 4.2x, columns with a user-defined data type that has a base data type of char or binary and for which no nullability is declared are created as data type varchar or varbinary. SQLColAttribute, SQLColumns, and SQLDescribeCol return SQL_VARCHAR or SQL_VARBINARY as the data type for these columns. Data that is retrieved from these columns is not padded.

LONG data types data-at-execution parameters are restricted for both the SQL_LONGVARBINARY and the SQL_LONGVARCHAR data types.