-
Oct 6th, 2004, 11:22 PM
#1
Thread Starter
Lively Member
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 ????
-
Oct 7th, 2004, 04:34 AM
#2
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).
-
Oct 7th, 2004, 04:54 AM
#3
Thread Starter
Lively Member
:( 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
-
Oct 7th, 2004, 05:53 AM
#4
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
-
Oct 7th, 2004, 06:15 AM
#5
Thread Starter
Lively Member
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 ?
-
Oct 7th, 2004, 10:15 AM
#6
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.
-
Oct 7th, 2004, 10:33 PM
#7
Thread Starter
Lively Member
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 !!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|