Results 1 to 7 of 7

Thread: Q about eVB & DB2e

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2003
    Posts
    110

    Q about eVB & DB2e

    We are using eVB & DB2e for developing a DB appln for Pocket PC. Dunno why but LCase if used in query in eVB code doesnt work and gives SQL error.

    Basically we are making a search utility. But are not able to provide CASE INSENSITIVE search. SQL function "LCase" if worked could have solved the problem. But it gives error when used in eVB.

    Is there any solution for providing CASE INSENSITIVE search. Some kind of WORK AROUND ????
    Anis Bombaywala

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929
    First off, you posted in the wrong forum - CodeBank is for completed routines, not questions (I'll get it moved to the appropriate forum for you).


    Lately I've been playing around in eVB and have used ADOCE databases, which seems to use case insensitve matching by default.

    I'm guessing that DB2e behaves differently, in which case there may be an alternative way which I have used in the past, and you might be able to use here. ADOCE doesnt support a proper "Like", but if DB2e does then you should be fine.

    Two variations, depending on how the Like functionality works:

    if it allows insensitivity by default - field1 Like 'value'

    if it allows character lists - field1 Like '[Vv][Aa][Ll][Uu][Ee]'
    (which you can easily build in a loop if it works).

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2003
    Posts
    110

    :( not working

    sorry for starting the thread in the wrong forum.

    but would you mind if we finish off with this thread here itself. or you want me to to start fresh in the proper forum.

    this is my code:
    i have commented my original LOC and put your idea. but it is not working. I think either of us have misunderstood each other about the thing.

    the string "lstrPartDesc" is what comes from the user which can be in any case or even mixed case. but search results should be irrespective of the case.

    lstrPartDesc = Trim(txtDesc.Text)
    If lstrPartDesc <> "" Then
    ' lstrPartDesc = " AND PARTS_DESC LIKE '%" & lstrPartDesc & "%' "
    lstrPartDesc = " AND PARTS_DESC LIKE '%[Vv][Aa][Ll][Vv][Ee]%' "
    Else
    lstrPartDesc = ""
    End If

    lstrQuery = "SELECT PART_ID, PART_NBR, PARTS_DESC, AFFECTS_QUAL, AFFECTS_COST FROM PARTS_MSTR WHERE SPCODE='" & gstrSPCode & "' " & lstrPartDesc
    Anis Bombaywala

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929
    Dont worry, I've asked another moderator to move this thread (I havent got permission myself)

    The code you've got should work if the dbms supports it, so I'm guessing that if you've got the text 'valve' somewhere in that field and it isnt being returned, character lists arent supported (or use a different syntax, you'll have to check your documentation on the Like function).

    I have found an alternative, I'm not sure if it is appropriate for embedded development though:
    http://www-106.ibm.com/developerwork...301stolze.html

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2003
    Posts
    110

    no luck yet...

    thanks for your prompt replies.

    but the solution you suggested is good for DB2.
    mine is DB2e a very restricted one.

    if we just leave the DBMS as it is and work around in the eVB code then is something possible.

    a couple of solutions have come to my mind but they are not feasible.

    any with you ?
    Anis Bombaywala

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929
    I thought that might be the case

    Does the documentation give any useful info for Like?

    Another variation of Like that you could try (some DBMS's allow it) is Matches (which I think is case insensitive), eg:
    field1 Matches '*a?b*'

    which is basically the same as:
    field1 Like '%a_b%'


    Other than that, I guess you would have to "ignore" any non matching rows when they are returned to eVB.

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Feb 2003
    Posts
    110

    nope....

    Thanks for the reply

    "Like" works the same way as it does with other DBMS

    "Matches" is not supported.

    the version of DB2e that i am using "7.2.1" is case sensitive.
    the later versions are insensitive. But we have to use the 7.2.1 due to some reasons.

    i think we need to adjust with the way it is. in case something +ve clicks to you, pls get back.

    thanks again !!
    Anis Bombaywala

Posting Permissions

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



Click Here to Expand Forum to Full Width