Join with CharType GERMAN_VFP_CI_AS_437
I've got a problem with my dotnet application reading data from different tables of my advantage database server through a sql "join" statement. The tables are from an old clipper application, so the table type is "ntx". One *.dbf and several *.ntx files for each table.
I use the dotnet data provider to connect to the database. Connection String: Data Source=\PathToDatabase\database.add;Password=*;User ID=adssys;CharType=GENERAL_VFP_CI_AS_437;ServerType=REMOTE;LockMode=COMPATIBLE;Shared=True;TableType=NTX;UnicodeCollation=de_DE"
Everything works fine until applying a "join" in the sql statement. The server always returns an empty table.
Without setting the charType in the connection string, the server returns the correct result.
How is it possible to define a "join" statement with CharType set to GENERAL_VFP_CI_AS_437?
The VFP-style collations are only supported for ADT and VFP file formats. They are not supported with the NTX style tables. Some information is here.
Unfortunately, it appears that an error check is either being bypassed or suppressed somehow with that usage when the NTX table is in the dictionary (as opposed to a free table). The query (whether or not it includes a join) should be returning an error (error code 7200 with a 5025 native error).
In order to use that collation with a DBF table, it will be necessary to use the VFP table type.