Microsoft KB Archive/266681

= PRB: RTRIM Function in SQL Select Does Not Remove Trailing Spaces =

Article ID: 266681

Article Last Modified on 10/22/2002

-

APPLIES TO


 * Microsoft Open Database Connectivity Driver for Visual FoxPro 5.0

-



This article was previously published under Q266681



SYMPTOMS
Trailing spaces are not removed from character fields when you use the RTRIM function in a SQL Select statement passed through the Microsoft Visual FoxPro ODBC Driver.



CAUSE
The Visual FoxPro ODBC Driver maps character fields to an SQL_CHAR type with the same length as the source column or columns.



STATUS
This behavior is by design.



MORE INFORMATION

 * The FoxPro Installable ISAM ODBC Driver (Microsoft FoxPro Driver (*.dbf)) that is included in ODBCJT32.DLL (version 3.51.171300), maps FoxPro character fields as an SQL_VARCHAR type. The column that is returned is actually 254 bytes in length, regardless of the length of the source column or columns.


 * The Visual FoxPro ODBC Driver maps character fields to an SQL_CHAR type. The length of the column that is returned is equal to the length of the source column.


 * The RTRIM and LTRIM string functions are supported and are used frequently to eliminate trailing spaces when concatenating fields. When string functions are used to concatenate fields, the length of the resulting column is equal to the combined length of the source columns.

Steps to Reproduce Behavior
  From Visual FoxPro, run the following code: CREATE TABLE DEMO (CHARFLD C(10)) && CREATE A TABLE WITH A CHARACTER FIELD INSERT INTO DEMO VALUES (&quot;AAA&quot;) CLOSE ALL  Launch the ODBC Administration Applet from the Windows Control Panel and create a new Data Source Name (DSN) by using the Microsoft Visual FoxPro ODBC Driver. Set the Database Type to Free Table Directory, and then set the path to the Visual FoxPro table created in step 1. On the Start menu, select Programs, select Microsoft Platform SDK, click Data Access, and then click ODBC Test ANSI. On the ODBC test menu, initiate a Full Connect to the DSN created in step 2 by selecting Conn and then Full Connect .  Enter the following query in the top pane of the ODBC test window: select {fn rtrim(charfld)} as testval from demo  On the ODBC test menu, select Stmt, and then SQLExecDirect to execute the query.</li> On the ODBC test menu, select Results, and then SQLDescribeCol to return field attributes. Note that the FoxPro character field is mapped to an SQL_CHAR type (*DataTypePTR = SQL_CHAR = 1). Note also that the length of the returned column (*ColumnSizePTR = 10) is equal to the length of the source field.</li></ol>

<div class="references_section">