TOPIC:

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

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#?

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

---

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?

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

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

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

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.

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

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

...just an info out of the ADS-helpfile:
Note Collation languages are only valid with ADT and VFP tables. For all other table types, the collation language is ignored.


I use these settings since a long time and never had a problem also with mixed applications (Windows and DOS):

SetNatDLL("GERMAN.DLL")
SetAnsi( TRUE ) // after SetNatDLL()
SetExclusive( FALSE )
SetDeleted( TRUE )
SetCentury( TRUE )
SetCollation( #CLIPPER ) // after SetANSI()
SetInternational ( #CLIPPER )



AX_SetCollation( strCollation AS STRING )

Parameters
<strCollation>
Collation language for the RDD to use.

Returns
The previous collation setting.

Description
This function specifies which collation language to use when opening tables, opening cursors, or creating tables. By default, no collation language is set and the client will use the language set by the Advantage Database Server.

Note Collation languages are only valid with ADT and VFP tables. For all other table types, the collation language is ignored.

regards
Gerhard

Thanks for all the reactions.

What makes it even more strange is that I read that ADS overrides the codepage settings, and both my system and the server of the client use ADS with the same settings (Configuration/Language: ANSI character set is empty and OEM character set is USA).
@Karl-Heinz: The reason I don't reindex on the server: my program allows to select the regular databases to index and when this is all done, it auto-reindexes the language dbf. So the same effect is achieved when I copy my ADS created dbf+cdx to the ADS environments of clients. And on this server (and others) previous versions have been correct and the other way around.

One thing I still don't understand. Suppose the setcollation is the cause, as reindexing in dbMax with Clipper seems to solve it. I always though the collation determines the order in which the records appear with Skip(). Not that I simply can't find a non ANSI string like FX_VERZENDEN.

Hence I am a bit reluctant to change my program's collation to #Clipper as this may cause issues elsewhere.

I update this database max 10 x a year for max 10 clients from which only a few have had this issue. So if after copying I reindex with dbMax I can probably prevent the problem. But I mainly want to understand why it happens.

@Robert: I will try the GetUserDefaultLCID later and report back.

Dick

ic2 wrote: Thanks for all the reactions.
@Robert: I will try the GetUserDefaultLCID later and report back.

IMHO this was the most important thing to check...

Robert

Hello Robert,

I am a bit confused. On my Pc this GetUserDefaultLCID() function returns 1043. On the system of my client (all Remote Desktop so no separate client or server) it's 1033. I assume that's something different than the codepage?

So yes, they are different but I am sure nothing changed on that system and most earlier seeks in the same dbf worked earlier (most of the times0. And still the VO docs talk about compare results, not about not finding a keyvalue at all.

Should I set it in my program to 1043 with SetAppLocaleid? Or something else?

Dick

Dick,
1043 = Dutch - Netherlands
1033 = English US
There are not the codepage but these defines the sorting rules for the characters in the codepage. Both locales work with codepage 1252.
The sorting rules are used to determine the order of the keys in an index and also when seeking.Seeking a value in an index also uses these tables.
I can't say if you should use 1043 or 1033. That depends on how you want to sort. If you want to see the difference, I recommend to create a test file with character fields with values 1-255 and then create an index and list the records. You can also fill an array with strings from chr(1) until chr(255) and asort them with both locales and compare the results.
You can then see the difference and decide which sort order is best for you. Most likely there will be a difference w.r.t. accented characters and probably some special characters, such as the Euro sign.
If you use a completely different locale,for example the German then the ß is sorted as "ss" And for example in the Spanish locale the CH and LL will be sorted a single litters.

The following page gives an overview over various language codes.
www.science.co.il/language/Locale-codes.php
And this page explains some of the rules for various languages.
en.wikipedia.org/wiki/Alphabetical_order

Robert