PRB: SQLColumns Return Incorrect BUFFER_LENGTH and CHAR_OCTET_LENGTH
This article was previously published under Q236871
This article has been archived. It is offered "as is" and will no longer be updated.
When SQLColumns is called on a table in an Access database, the BUFFER_LENGTH and CHAR_OCTET_LENGTH values that appear to be returned are incorrect for non-UNICODE text fields. Fields of data type CHAR and VARCHAR will show these lengths as twice the value of COLUMN_SIZE. This is because Jet now stores text data in the Unicode format, which requires two bytes for every character.
This behavior is by design.
Steps to Reproduce Behavior
Perform the following steps in the ODBC Test utility:
- Connect to a data source using the Microsoft Jet 4.0 ODBC driver.
- Call SQLColumns on a table that contain a text field.
- Select Get Data All from the Results menu to display the returned recordset. The following is an example of how to open the sample Access database Northwind.mdb with table "Categories":You can see that the CategoryName column is a VARCHAR of size 15, but BUFFER_LENGTH and CHAR_OCTET_LENGTH are 30 when they should also be 15.
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE","SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE", "ORDINAL" "C:\testmdbs\NWIND", <Null>, "Categories", "CategoryName", 12, "VARCHAR", 15, 30, <Null>, <Null>, 1, "Name of food category.",<Null>, 12, <Null>, 30, 2, "YES", 2<BR/>
See the documentation for SQLColumns for more information on the data that this function returns.
The ODBC Test utility can be obtained from the Microsoft Data Access SDK, which comes with Visual Studio or can be downloaded from the following Web site:
odbcjt32.dll mdac 2.1 buffer
Article ID: 236871 - Last Review: 02/28/2014 00:27:48 - Revision: 2.1
Microsoft Open Database Connectivity Driver for Access 4.0, Microsoft Data Access Components 2.1, Microsoft Data Access Components 2.5
- kbnosurvey kbarchive kbdatabase kbdriver kbjet kbpending kbprb KB236871