SetCollation issue

ic2 · Post by **ic2** » Wed Jun 03, 2020 5:23 am

In our program we use a database with "constants" which we search to get a text in the user's language. We used to do that with constants and a DLL. From time to time a very small number of these "constants" can not be found, only for a few clients.

E.g. in a menu we have FX_VERZENDEN which should be found in the database in the record with "Send" as English translation. But sometimes it can't be found. What helps is to reindex with dbMax where Collation Clipper (Dos) is set while our program uses Windows. I actually can see that in dbMax FX_VERZENDEN and more FX_ keywords can not be found until reindexed.

What I do not understand is:

1 It often works without problems. I regularly distribute an updated translation DBFCDX to customers, and the very same latest dbf+cdx works everywhere (including at 2 clients where I have seen the issue earlier as well). Only now for 1 client that specific key can not be found (until dbMax reindexed). DBF is ANSI.
2 I would say collation rules would determine the order in which specific keyvalues are presented, especially accented ANSI characters. But how could a reindex with collation #Windows result in not finding at all a non ANSI accented keyvalue like FX_VERZENDEN?

Dick

Chris · Post by **Chris** » Wed Jun 03, 2020 12:23 pm

Hi Dick,

We really need a lot more information than that, to be able to give some suggestion. Like what your fields look like, what your index looks like, how you are doing the search etc. I also just noticed you posted this in the VFP area (obviously by accident) so I am not sure, is this a problem you face in VO, or in X#?

ic2 · Post by **ic2** » Wed Jun 03, 2020 1:15 pm

Hello Chris,

Ahh.. no, it's VO, sorry. Can you easily move it from Foxpro to VO?

Below the structure and the indexes. The order is set to CONSTNAME = UPPER(CONSTNAME)
Searching on FX_VERZENDEN in field #constname dbMax (or our VO program) will normally find the right record but sometimes the FX_ searches fail (and dbMax' softseek places the recordpointer below all FX_ fields). When I reindex in dbMax it will find again. Only difference is the collation Clipper but as written, I can't possibly explain why FX_ can not be found with collation #Windows and most of the times/installation the very same VO (#Windows) created CDX works fine. That's what makes it so strange.

1 VOLGNR C 9 0
2 CONSTNAME C 37 0
3 CONSTNR N 10 0
4 NL C 160 0
5 UK C 160 0
6 DU C 160 0
7 FR C 160 0
8 SP C 160 0
9 SW C 160 0
10 PR C 160 0
11 FI C 160 0
12 DK C 160 0
13 NO C 160 0
14 RU C 160 0
15 IT C 160 0
16 NLAUTO L 1 0
17 UKAUTO L 1 0
18 DUAUTO L 1 0
19 FRAUTO L 1 0
20 SPAUTO L 1 0
21 SWAUTO L 1 0
22 PRAUTO L 1 0
23 FIAUTO L 1 0
24 DKAUTO L 1 0
25 NOAUTO L 1 0
26 RUAUTO L 1 0
27 PROJECTCOD C 10 0
28 DATEIN D 8 0
29 DATECHANGE D 8 0
--------------------------------------------------------------------------------------------------------------------------------

Indexes:
--------------------------------------------------------------------------------------------------------------------------------
Ordername Key expression
Unique Filename
--------------------------------------------------------------------------------------------------------------------------------
NL UPPER(NL)
No TRANSLATE.CDX

CONSTNR STRZERO(CONSTNR,10)
No TRANSLATE.CDX

CONSTNAME UPPER(CONSTNAME)
No TRANSLATE.CDX
--------------------------------------------------------------------------------------------------------------------------------

Chris · Post by **Chris** » Wed Jun 03, 2020 2:21 pm

Hi Dick,

I do not know the exact inner workings of the indexing algorithm, but I wouldn't be surprised if the different collation is exactly what causes the problem. Searching through an index assumes that all records are in order and if there are two records in the wrong order due to different collation settings, they can cause searching to fail, even if those records are not the ones that you search for.

Imagine for example you are searching for number "4"in the (supposedly) sorted list 1,2,5,4,3,6, where "5" and "3" have switched places. The algorithm sees that the 3rd item is "5", which is already larger than the one you are searching for, so it concludes what you are searching for does not exist and does not look further. Not saying that the cdx drivers works like that, but there could be something happening along those lines.

Btw, I assume you are searching with a simple Seek(), is that correct?

robert · Post by **robert** » Wed Jun 03, 2020 2:28 pm

Dick,

Can you send me an example of the file where the seek does not work and an example of the search key ?
And do all the users have the same windows settings w.r.t the active Ansi codepage ?

Robert

ic2 · Post by **ic2** » Thu Jun 04, 2020 6:49 pm

Hello Robert,

I copied the faulty translate.dbf+cdx to a subdirectory. When I set the correct order in either dbMax or our own tool dbv and search for FX_VERZENDEN it skips all the FX_ and even FXxx (without _) and softseeks the first record with a G. I do this on Windows Server 2016 1607. build 14393.3564. Now I transfer these 2 files using tsclient etc to my own W10/1903 Pc....and I can find the FX_VERZENDEN key without problems. So you won't find anything incorrect when I send the file I guess?

This is logical as it's the same file which works on my PC and also worked on several other WIndows 2016 servers of clients I distributed it too. Note that I have seen the same issue (with only a few records) on the locations where it works now! But it's far from logical it fails sometimes on one location.

And as said, it, reindexing in dbMax with collation Clipper on that server solves the issue completely (as it did when i saw it earlier).

We do not have any other search issues on this servers.

I am very curious if it makes sense to you (or anyone else)..

Dick

Chris · Post by **Chris** » Thu Jun 04, 2020 11:27 pm

Robert, please note that as Dick said, this is a problem with a VO app, not an X# one.

robert · Post by **robert** » Fri Jun 05, 2020 6:50 am

Dick,
The windows collation uses the windows string comparison routines to compare keys. My guess is that the codepages on the server are set differently than on the clients.That is why I asked about the codepage.
Normally VO picks up the codepage from the machine to sort.
You can override this by calling the SetAppLocaleid() function in your code.
This function is documented in the VO Help file.

Robert

PS The locale is initialized by calling the Windows API function GetUserDefaultLCID().
As a test you could write a small program to check what this function returns on the server and on the workstations.

Karl-Heinz · Post by **Karl-Heinz** » Fri Jun 05, 2020 6:51 am

Hi Dick,

What i don´t understand: Why do *you* create the CDX and send it to your customers ? Why is the CDX not created within the customers environment ?

If your app is designed to use SetCollation (#Windows) a Clipper CDX doesn't make sense. Which nation dll are you using when DBMax creates the Clipper CDX and in which country is the customer located that such a "fix" works ?

regards
Karl-Heinz

wriedmann · Post by **wriedmann** » Fri Jun 05, 2020 6:57 am

Hi Dick,
since most of my applications work in mixed language environments (German and Italian language machines on the same data) I always use SetCollation( #Clipper ) and never had such problems because the collation does not depends on the local PCs settings, but on what I (as programmer) are defining.
I can only strongly recommend to centrally set the collation table for all machines accessing the same data.
Wolfgang
P.S. for Advantage Database Server this is not an issue because there the collation is defined at the server level

Main menu