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

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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
    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.

  9. #9
    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

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

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

    Hi Raveen,

    you are correct. I've changed it back to Confirmed/Logged.

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
  •