PRB: SQLColumns Return Incorrect BUFFER_LENGTH and CHAR_OCTET_LENGTH

Article translations Article translations
Article ID: 236871 - View products that this article applies to.
This article was previously published under Q236871
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SYMPTOMS

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.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior


Perform the following steps in the ODBC Test utility:
  1. Connect to a data source using the Microsoft Jet 4.0 ODBC driver.
  2. Call SQLColumns on a table that contain a text field.
  3. 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":
       "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/>
    						
    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.

REFERENCES

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:
http://www.msdn.microsoft.com

Properties

Article ID: 236871 - Last Review: February 28, 2014 - Revision: 2.1
APPLIES TO
  • Microsoft Open Database Connectivity Driver for Access 4.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
Keywords: 
kbnosurvey kbarchive kbdatabase kbdriver kbjet kbpending kbprb KB236871

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com