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

Thread: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

  1. #1
    Join Date
    Mar 2009
    Location
    New Zealand
    Posts
    1,443

    Exclamation BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    Hi DAW,

    This bug still exist in DF18.1 (A3) - See original post

    Issue exist in both dbSuggestionForm & cWebSuggestionForm

    Basically, the SQLStrLike function needs to check whether DF_DRIVER_IGNORE_UCASE_SUPPORT is supported or not.

    If ((IGNORE_UCASE_SUPPORT=False) and (pbCaseSensitive=False)) then it should produce a Like statement as per:

    Code:
    UPPER(File.Field) LIKE '%searchtext%'
    where searchtext is uppercased

    OR:
    Code:
    UPPER(File.Field) LIKE UPPER('%searchtext%')
    Rather then the standard:

    Code:
    File.Field LIKE '%searchtext%'
    Thanks
    Regards,
    Raveen Sundram

    Software Development Manager
    Excellent Software Ltd
    Auckland, New Zealand
    www.xls.co.nz

  2. #2
    Join Date
    Nov 2008
    Location
    Round Rock, TX
    Posts
    7,602

    Default Re: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    Hi Raveen,

    logged as bug # 6814.

  3. #3
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    1,963

    Default Re: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    Hold on on this one!!

    This is not 100% the best case!

    I and probably you, don't want an expression like 'FIELD like UPPERCASE(%somevalue%)' to be inserted on my WHERE clause just becuase the pbCaseSensitive is true or not.

    This is only true if the back-end collate is CASE-SENSITIVE. but on DBMS like SQL-Server, DB2, Oracle (and others) you can define a whatever collating sequence you wish:
    with CASE-SENSITVE on or OFF
    NORMALIZATION on or OFF, (where á = â = ã = á = a)
    and several other options besides it.

    So If I am using a collate like this, I don't need an UPPERCASE function on my query. This will just introduce an unnecessary overhead to my dbms server and reduce the performance.

    This need more precaution !
    Samuel Pizarro

  4. #4
    Join Date
    Nov 2008
    Location
    Round Rock, TX
    Posts
    7,602

    Default Re: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    Thanks, I added your notes to the bug report Samuel.

  5. #5
    Join Date
    Mar 2009
    Location
    New Zealand
    Posts
    1,443

    Default Re: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    Hi Samuel,

    That's true - that is why in my initial post I mentioned that this needs to be done only when IGNORE_UCASE_SUPPORT=False

    The SQLStrLike function needs to check whether DF_DRIVER_IGNORE_UCASE_SUPPORT is supported or not. Most DBMS has the ability, as you mentioned, to control collation and case-sensitivity - hence why the default setting for DF_DRIVER_IGNORE_UCASE_SUPPORT is True.

    But in some instances, the DBMS is unable to use case-insensitivity due to design restrictions, then the DF_DRIVER_IGNORE_UCASE_SUPPORT needs to be False, so that the CK can create the U_(Fields) and use those fields for case-insensitivity index searching.

    Therefore, the same "rule" needs to be applied to full-text searching:

    Code:
    If ((IGNORE_UCASE_SUPPORT=False) and (pbCaseSensitive=False)) Then
      // where searchtext is uppercased
      UPPER(File.Field) LIKE '%searchtext%'  
    End If
    I hope this makes it clearer.

    P/s: In DB2, case-insensitivity is only supported on Unicode (UTF-8) codeset databases but we use OEM codeset databases.
    Regards,
    Raveen Sundram

    Software Development Manager
    Excellent Software Ltd
    Auckland, New Zealand
    www.xls.co.nz

  6. #6
    Join Date
    Mar 2009
    Location
    New Zealand
    Posts
    1,443

    Default Re: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    Hi,

    On more testing, there is another criteria to check for before using the explicit UPPER function - the field's DD_CAPSLOCK state.

    Therefore,

    Code:
    If ((IGNORE_UCASE_SUPPORT=False) and (pbCaseSensitive=False)  and (DD_CAPSLOCK=False)) Then
      // where searchtext is uppercased
      UPPER(File.Field) LIKE '%searchtext%'  
    End If
    Just dotting all i s
    Regards,
    Raveen Sundram

    Software Development Manager
    Excellent Software Ltd
    Auckland, New Zealand
    www.xls.co.nz

  7. #7
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    1,963

    Default Re: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    Sure Raveen.

    Take care!
    Samuel Pizarro

  8. #8
    Join Date
    Feb 2009
    Location
    The Netherlands
    Posts
    3,933

    Default Re: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    I have looked into this and it is a bit more complicated as it seems. On SQL the UPPER solution will indeed make a find on a field with a case sensitive collating sequence case insensitive. The other way around is more difficult. How to make a search on a non-case sensitive collating sequence case sensitive? The only method for doing that is to pass a custom collating sequence to be used with the SQL query but then you have to find out what the matching collating sequence is and for a start DataFlex doesn't even know the collating sequence used right now.

    It also turns out that on the embedded database if pbFullText is false it will also fail if the fields main index doesn't have the ignore case attribute set to the right value.

    So maybe trying to override the case sensitivity setting at database level is the wrong way to go here. That however does not mean that the suggestionform doesn't need to know if it is working in a case sensitive fashion as the highlighting of the results and the comparisons on the embedded database should be done in the proper fashion. That would make this more of a documentation issue (advising to make sure to make it match the database setting).
    Harm Wibier
    Data Access Europe B.V.
    http://www.dataaccess.eu/

  9. #9
    Join Date
    Nov 2008
    Location
    Round Rock, TX
    Posts
    7,602

    Default Re: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    Hi Raveen,

    the fix for this bug is in the DataFlex 18.1 Release Candidate.

  10. #10
    Join Date
    Mar 2009
    Location
    New Zealand
    Posts
    1,443

    Default Re: BUG: SuggestionForm::pbCaseSensitive does NOT work with SQL Tables

    Hi Dennis,

    Just tested this in 18.1 RC1 - sadly, the bug still exist.

    Environment:
    DB2_DRV (6.1.0.18)

    DB2_DRV.INT:
    IGNORE_UCASE_SUPPORT 0
    JIT_TRESHOLD 1
    FIND_CACHE_TIMEOUT 1000
    Regards,
    Raveen Sundram

    Software Development Manager
    Excellent Software Ltd
    Auckland, New Zealand
    www.xls.co.nz

Page 1 of 2 12 LastLast

Posting Permissions

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