LocateEx and localization problem

[logodan]

Hi,

We seems to have an observation, that the outcome of an TMSQuery.LocateEx search with the lxNearest parameter is depending on the regional settings of the PC.

E.g. a search for AA finds a different (wrong) record if the regional setting is Danish. Seems to work if it is English.

Collation on the table is SQL_Latin1_General_Cp850_CS_AS.

Is that the expected behaviour of this function? An example can be provided, if needed.


Thanks in advance,
C. Have

[Jackson]

Probably Danish language has its own code page that doesn't match code page 850 (Multilingual (MS-DOS Latin1)).
Use Unicode data types nchar, nvarchar, and ntext. These data types use
the Unicode character representation. Code pages does not applied to these data types.

[logodan]

Hi,

Thanks for your answer. Not ruling out its me doing something stupid, it doesn't do what I would expect.

I have a test table with a nvarchar column, with the above mentioned collation. It contains the rows:

22
BB
DAA
DAB
DAC

Doing a SELECT ORDER BY returns the rows in this expected sort order.

But when searching for AA on this dataset using LocateEx it returns False. When searching for BAA it returns true but locates DAA (not BB).

It looks like it translates AA to the danish Å, which is last in the danish alphabet.

By going to Control Panel/Regional and Language options and changing the setting to English, it works like a charm, and returns BB in both scenarios.

Changing to a serverside cursor doesn't seems to affect this behaviour. Neither does collations like SQL_Latin1_General_Cp437_BIN or Latin1_General_CS_AS

I hope you can help with this small problem.


Thanks in advance,
C. Have

[Jackson]

We couldn't reproduce the problem.
Please send us (sdac*crlab*com) a complete small test project to reproduce the problem; it is desirable to use Northwind or Master schema objects, otherwise include definition of your own database objects; don't use third party components

Also supply us following information
- Exact version of Delphi or C++ Builder
- Exact version of SDAC. You can see it in About sheet of TMSConnection Editor
- Exact version of Microsoft SQL Server and OLE DB provider that you use. You can see it in Info sheet of TMSConnection Editor

[Jackson]

Please specify the way you pass parameters to TMemDataSet.LocateEx method.

[logodan]

Please specify the way you pass parameters to TMemDataSet.LocateEx method.

The following code shows how it's done in the test project (which by now is mailed to you as requested). Our application is using variant arrays with the same outcome.

Code: Select all

var
  keyvalue: string;
begin
  with fDataset do
  begin

    keyvalue := edSearchText.Text;

    if LocateEx('FIELD1', keyvalue, [lxNearest]) then memResult.Lines.Add('Found!')
    else memResult.Lines.Add('Not found!');

  end;
end;

[Jackson]

We have made a conclusion that this behaviour is by design in Microsoft Windows.
Please run this example using English and Danish localization.
You will see that results are different.

Code: Select all

  procedure CompareUnicodeText(const s1, s2: WideString);
  var
    Res: integer;
  begin
    Res := CompareStringW(LOCALE_USER_DEFAULT, SORT_STRINGSORT, PWideChar(S1), -1,
      PWideChar(S2), -1);
    case Res of
      CSTR_LESS_THAN:
        ShowMessage(QuotedStr(s1) + ' is less than ' + QuotedStr(s2));
      CSTR_EQUAL:
        ShowMessage('Strings are equal');
      CSTR_GREATER_THAN:
        ShowMessage(QuotedStr(s1) + ' is greater than ' + QuotedStr(s2));
      else
        ShowMessage('Errors occurred');
    end;
  end;

var
  s1, s2: WideString;
begin
  s1 := 'AA';
  s2 := '22';
  CompareUnicodeText(s1, s2);
  s2 := 'BB';
  CompareUnicodeText(s1, s2);
end.