bobaol
Posts: 8
|
| Posted: 08/23/2004, 2:22 AM |
|
Hi Group,
I've searched the threads and archives for an answer to this but haven't found a good answer.
In an ASP & Oracle environment I am trying to setup case insensitive searching. I basically want to convert the user entered keywords and the data returned from the database to either upper or lowercase before the "% like %" comparison is made.
I can do this by writing my own code and replacing codecharge generated code, but I would like to do this the codecharge way (ie: using an event such as "Before build select")
Any help would be appreciated.
|
 |
 |
peterr
Posts: 5971
|
| Posted: 08/23/2004, 2:41 AM |
|
Based on http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&thre...sensitive%2Bsql
try entering such SQL query in CCS:
"Select * from customers where UPPER(some_value)=Upper({parameter_name});"
If something cannot be done via SQL then using events is recommended. Here are some examples of modifying the SQL in events:
http://docs.codecharge.com/studio/html/ProgrammingTechn.../ModifySQL.html
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Robert Rodgers
|
| Posted: 08/23/2004, 4:25 AM |
|
Upper() or UCase() on the column usually forces the DB to perform a table scan. We just ran into this with DB2. If you have
some control over the backend it is better (usually) to make changes there if possible. We found a way to make case
insensitive searches a option by manipulating properties on the server.
http://www.google.com/search?hl=en&ie=UTF-8&q=case+insensitive+where+oracle
I searched google and came up with this. I don't know much about oracle but maybe it will help.
http://asktom.oracle.com/pls/ask/f?p=4950:8:26376298502...:16370675423662,
"peterr" <peterr@forum.codecharge> wrote in messagenews:64129bbae6ab7c@news.codecharge.com...
> Based on
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&thre...sensitive%2Bsql
>
> try entering such SQL query in CCS:
> "Select * from customers where UPPER(some_value)
> =Upper({parameter_name});"
>
> If something cannot be done via SQL then using events is recommended. Here are
> some examples of modifying the SQL in events:
> http://docs.codecharge.com/studio/html/ProgrammingTechn.../ModifySQL.html
>
> _________________
> Peter R.
> YesSoftware Support Representative
> http://support.codecharge.com
> Warning: I use Google
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
bobaol
Posts: 8
|
| Posted: 08/23/2004, 5:01 AM |
|
Thanks Peter and Robert for the tips. So far I have not been able to make this work.
Here is where I am. In Oracle I have created a test table named THINGS. I have three fields in the table THINGS_ID, THINGS_NM, THINGS_DESC. I have filled the table with sample imported data. In Codecharge on a blank page I have used the grid builder to create a grid and a search form based on the field THINGS_NM.
In datasource for the grid I have change the WHERE component from PARAMETER:THINGS_NM like %{s_THINGS_NM}% to an EXPRESSION: Select * from THINGS where UPPER(THING_NM) like UPPER(%{s_THING_NM}%)
I have unfortunately not been able to get this to work, even though if I submit it to oracle with s_THING_NM replaced with a value I get a good return. I feel like I am making a syntax error somewhere, but I cannot find my mistake.
Any ideas? It seems like such a simple thing to solve, but it's causing me problems. I'd like to solve it in a Codecharge way. I can write a block of code for this function, but I am not sure where to put it so that it interacts with the search form and grid correctly. We could also solve it on the database side, but thats not the way we'd like to solve this.
Any help is appreciated.
|
|
|
|
bobaol
Posts: 8
|
| Posted: 08/23/2004, 6:41 AM |
|
Hi Group,
This problem has been solved with the help of re-reading the tip from Peter. In my test page I changed the datasource for the grid from TABLE to SQL and entered the Sql string Select * from THINGS where UPPER(THING_NM) like UPPER(%{s_THING_NM}%)
Worked like a charm after that. Thanks for all of your help.
|
|
|
|
|
