Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Feb 2009
    Location
    Perth, Western Australia
    Posts
    1,404

    Default MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

    From prior posts it would appear that the desired collating sequence for DataFlex and MS-SQL is SQL_Latin1_General_CP1_CI_AS.

    However, MS-SQL out of the box (for us here at least) defaults to Latin1_General_CI_AS. This unfortunately does not match the collate sequence that the DataFlex runtime uses when comparing strings.

    Below is typical of how the problem manifest itself for a column that is index.

    MS-SQL Latin1_General_CI_AS
    1248B750
    1248B-900
    1253/D

    Embedded
    1248B-900
    1248B750
    1253/D

    If you jump to record '1248B750' and compare the current record with the initial value i.e. ‘1248B750’ >= '1248B750' the comparison is true. But on the next find this is either '1248B-900' in MS-SQL, or '1253/D' in embedded. However, with MS-SQL the comparison fails. This has the potential to exit processing loops prematurely (not to mention the consequences to the database’s integrity).

    There is one obvious way this can be solve and that is to switch the MS-SQL collate sequence to SQL_Latin1_General_CP1_CI_AS but this involve a reasonable amount of SQL scripting for a successful outcome.

    Therefore, is it possible to change the df_collate.cfg? And if this is possible what are the changes required so the string comparison behaves similar to MS-SQL collate sequence?
    Kind regards,

    Clive Richmond
    Triumph Business Systems Pty Ltd
    www.triumph.com.au

  2. #2
    Join Date
    Feb 2009
    Posts
    4,770

    Default Re: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

    Perhaps it is version dependent but this

    https://docs.microsoft.com/en-us/sql...l-server-ver15

    Seems to suggest the default is SQL_Latin1_General_CP1_CI_AS.

    It does also say ....
    The server collation is specified during SQL Server installation.
    Success consists of going from failure to failure without loss of enthusiasm - Winston Churchill

  3. #3
    Join Date
    Feb 2009
    Location
    Perth, Western Australia
    Posts
    1,404

    Default Re: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

    Quote Originally Posted by Focus View Post
    Perhaps it is version dependent but this

    https://docs.microsoft.com/en-us/sql...l-server-ver15

    Seems to suggest the default is SQL_Latin1_General_CP1_CI_AS.

    It does also say ....
    The server collation is specified during SQL Server installation.
    Andrew, I am sure this is region dependent. We would not have changed the default collation during the installation. This is an extract from the link Chuck posted.

    Because the name start with SQL_, this is a SQL Server collation, not a Windows collation. These are definitely obsolete, even if not officially deprecated, and are mainly for pre-SQL Server 2000 compatibility. Although, quite unfortunately SQL_Latin1_General_CP1_CI_AS is very common due to it being the default when installing on an OS using US English as its language. These collations should be avoided if at all possible.

    Windows collations (those with names not starting with SQL_) are newer, more functional, have consistent sorting between VARCHAR and NVARCHAR for the same values, and are being updated with additional / corrected sort weights and uppercase/lowercase mappings
    And this isn't the first time I've read that the SQL_ versions are considered obsolete.
    Kind regards,

    Clive Richmond
    Triumph Business Systems Pty Ltd
    www.triumph.com.au

  4. #4
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    9,017

    Default Re: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

    Clive,

    Not sure if df_collate.cfg - which you can change - will help you as comparing will fail on the CI part as well. The CI part says "AB" and "ab" are the same. For DataFlex this is not the same.
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  5. #5
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    3,706

    Default Re: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison



    CI part says "AB" and "ab" are the same. For DataFlex this is not the same


    Agreed. That's why I use SQL_Latin1_General_CP1_CS_AS.

  6. #6
    Join Date
    Feb 2009
    Location
    Perth Australia
    Posts
    97

    Default Re: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

    Quote Originally Posted by Mike Cooper View Post



    Agreed. That's why I use SQL_Latin1_General_CP1_CS_AS.
    All our indexed columns are uppercase, so this should not matter, but will keep in mind for the future.
    Kind regards,

    Michael Macliver
    Triumph Business Systems Pty Ltd
    www.triumph.com.au

  7. #7
    Join Date
    Feb 2009
    Location
    Perth, Western Australia
    Posts
    1,404

    Default Re: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

    Hi Vincent,

    Quote Originally Posted by Vincent Oorsprong View Post
    Not sure if df_collate.cfg - which you can change - will help you as comparing will fail on the CI part as well. The CI part says "AB" and "ab" are the same. For DataFlex this is not the same.
    Could you please explain what changes we would need to make to the df_collate.cfg file so the dash ('-') is either included, or excluded, and therefore the DataFlex string comparison rules match the SQL database? Or where we may find the documentation?
    Kind regards,

    Clive Richmond
    Triumph Business Systems Pty Ltd
    www.triumph.com.au

  8. #8
    Join Date
    Feb 2009
    Location
    Hengelo (NL)
    Posts
    1,726

    Default Re: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

    Quote Originally Posted by Clive Richmond View Post
    There is one obvious way this can be solve and that is to switch the MS-SQL collate sequence to SQL_Latin1_General_CP1_CI_AS but this involve a reasonable amount of SQL scripting for a successful outcome.
    You can go to SQL-manager and change the SQL-collate from just this one field. No scripting needed. I have a situation like this in one of my projects. It certainly has his drawbacks but it has been working for years.

    You could also create a (calculated) field with the same content but a different collate.

  9. #9
    Join Date
    Feb 2009
    Location
    Perth, Western Australia
    Posts
    1,404

    Default Re: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

    Quote Originally Posted by Evertjan Dondergoor View Post
    You can go to SQL-manager and change the SQL-collate from just this one field. No scripting needed. I have a situation like this in one of my projects. It certainly has his drawbacks but it has been working for years.

    You could also create a (calculated) field with the same content but a different collate.
    Evertjan, I aware you can do this but such a piecemeal approach isn’t practical in a production environment with hundreds of tables. And after all, the issue here is the DataFlex string comparison collating rules not matching that of the SQL database.
    Kind regards,

    Clive Richmond
    Triumph Business Systems Pty Ltd
    www.triumph.com.au

  10. #10
    Join Date
    Feb 2009
    Location
    Maasland, The Netherlands
    Posts
    2,319

    Default Re: MS-SQL Collation, Latin1_General_CI_AS, DF_Collate.cfg & String Comparison

    Hi Clive,

    The collating sequences starting with "SQL_" are used for compatibility and it is a "feature" that they ignore the "-" in indexes. So probably Latin1_General_CI_AS works better (if you don't want this).

    I am going to test with SQL Server 2019 and
    Latin1_General_100_CI_AS_SC_UTF8. Until now it works, but I have done very little with Unicode values.
    Regards,

    Peter van Mil
    Appvantage b.v.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •