Microsoft KB Archive/241820

= FIX: Visual FoxPro ODBC Driver Does Not Support Some Collating Sequences =

Article ID: 241820

Article Last Modified on 9/22/2005

-

APPLIES TO


 * Microsoft Open Database Connectivity Driver for Visual FoxPro 5.0
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2

-



This article was previously published under Q241820



SYMPTOMS
You are using the Visual FoxPro ODBC driver on a non-U.S. English version of Microsoft Windows 95 or Windows 98, or on Microsoft Windows NT with the regional settings set to something other than English (United States). Attempting to issue a SQLEXEC call that performs a SELECT against a table that uses index tags created with certain collating sequences returns -1. Under these circumstances, the second element of an array populated with the AERROR function contains the following text:

Connectivity error: [Microsoft][ODBC Visual FoxPro Driver]Collating sequence ' ' is not found.

Some collating sequences may also be unavailable when configuring a data source name (DSN) that uses the Visual FoxPro ODBC driver. For instance, on Polish Windows 95 or 98, or Windows NT with Polish as the default locale, General and Polish do not appear in the Collating sequence drop-down text box in the ODBC Visual FoxPro Setup dialog box.



RESOLUTION
This problem is corrected in the Microsoft Visual FoxPro ODBC driver version 6.0.8616.0 and later. You can obtain updated versions of the driver by installing Microsoft Data Access Components (MDAC) version 2.5. This package is available for download from the following Microsoft Web site:

http://msdn.microsoft.com/dataaccess



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
These steps demonstrate the behavior from Visual FoxPro, but this problem also occurs from other applications that use the Visual FoxPro ODBC driver.  On Windows NT, from the Start menu, point to Settings, click Control Panel, and then double-click Regional Settings. From the drop-down list box, select Polish, and click to select the Set as system default check box. Click Apply. You will then be prompted for your Windows NT 4.0 CD. After the updated files have been copied, you will be prompted to restart your computer. After the restart, start Visual FoxPro.  Run the following code from a program (.prg) file: * Do some preparation first. =SQLDISCONNECT(0) CLEAR ALL CLOSE DATABASE ALL CLEAR SET SAFETY OFF CREATE DATA testdb CREATE TABLE colltest (char1 c(7)) SET COLLATE TO "Polish" INDEX ON char1 TAG char1 FOR lni = 1 TO 2 cval = PADL(lni,7) INSERT INTO colltest (char1) VALUES (cval) ENDFOR CLOS DATA ALL CREATE DATABASE conndbc SET SAFETY ON lcdir = SET("default") + CURDIR cConstr = "Driver={Microsoft Visual FoxPro Driver};"+ ; "UID=;PWD=;SourceDB="+lcdir+ ; "testdb.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=No;"+ ; "Collate=Machine;Null=Yes;Deleted=Yes;" CREATE CONNECTION conn1 CONNSTRING (cConstr) lnh = SQLCONNECT("conn1") ?'Handle is ', lnh ?SQLEXEC(lnh ,'select * from colltest','cCursor') =AERROR(atest) LIST MEMO LIKE atest =SQLDISCONNECT(lnh) =SQLDISCONNECT(0)  The SQLEXEC call returns -1, indicating that it has failed.</li> The contents of the error array created by the AERROR function contain the following message:
 * Make database and table.
 * Make database with Polish collating sequence.
 * Create connection database and connection.
 * Connect.
 * Try select.

Connectivity error: [Microsoft][ODBC Visual FoxPro Driver]Collating sequence 'POLISH' is not found.

The contents of the array are printed to the desktop.</li></ol>

The same behavior occurs if you select the General collating sequence instead of Polish.

If you use the above computer configuration and create a DSN using the Visual FoxPro ODBC driver, you will see the following items in the Collating sequence drop-down text box in the ODBC Visual FoxPro Setup dialog box:
 * Czech
 * Hungarian
 * Machine
 * Slovak

The correct list should be:


 * Czech
 * General
 * Hungarian
 * Machine
 * Polish
 * Slovak

You can create a cursor which contains the return the current collate sequence and codepage setting from the Visual FoxPro ODBC Driver by issuing a SQLEXEC call as follows: ?SQLEXEC(lnh ,'SELECT SET("collate"),CPCURRENT,VERSION '+ ;  'FROM WHERE RECNO = 1') The table name must be substituted for  . Note that the table must have supported collating sequences for the SQLEXEC call to succeed. You can use this code in the program above by changing the SET COLLATE command from "Polish" to "Machine", by using colltest for  , and by substituting the SQLEXEC call above for the one in the program in step 5.

Possible affected collation sequences under various codepages include the following:
 * General
 * Iceland
 * Nordan
 * Polish
 * Spanish
 * Swefin
 * Uniqwt

Others sequences may also be affected.

<div class="references_section">