Jochem, Dave,
My disbelief ('shock' is really too harsh a word for me to have used) comes
from the fact that I'm a brain-washed Windoze guy - one might even say that
I have a Microca$h property patch on me - who has never (to my recollection)
in 9+ years worked with a SQL Server collation that was anything other than
case INsensitive. I'm trying to get out more these days, and venture into
the real world, the one in which you guys have long since been thriving.
:-)
Mind you, I am absolutely NOT a DBA. My database experience is 100% in
using them for web-based applications.
Jochem, I am responding inline to your other comments...
On 1/13/07, Jochem van Dieten <[EMAIL PROTECTED]> wrote:
>
> Matt Quackenbush wrote:
> >
> > Now, I know that the table names and columns are case sensitive, and I
> > already took care of all of those issues. But data? Case sensitive
> data??
> > I'm actually in shock over this one.
>
> Really? Have you ever looked close at CF? Try the following code:
> <cfscript>
> qryTest = QueryNew("str");
> queryAddRow(qryTest, 3);
> querySetCell(qryTest, "str", 'a', 1);
> querySetCell(qryTest, "str", 'B', 2);
> querySetCell(qryTest, "str", 'c', 3);
> </cfscript>
> <cfquery name="qryDemo" dbtype="query">
> SELECT *
> FROM qryTest
> ORDER BY str
> </cfquery>
> <cfdump var="#qryTest#">
> <cfdump var="#qryDemo#">
In regards to QoQ being case sensitive, yes, I am well aware of that
behavior. And it has always been something of an annoyance to me. I have
always used lower(myfield) and #lCase(myval)# to "get around" that. These
discussions here though are making me reconsider my position. Again, my
disbelief is more because of inexperience in such an environment than it is
refusal to open my mind and think that there's any other way.
> While this is an 'okay' solution for a search form, I think it's insane to
> > have to resort to the following for a login query:
> >
> > SELECT userid FROM tbl_user
> > WHERE user_name ILIKE '#form.user_name#'
> > AND user_psswd LIKE '#form.user_psswd#';
>
> I think it is insane to second-guess the user on his imput. There are few
> things that
> annoy me more then stupid systems that will fully automatically 'correct'
> my last name from 'van Dieten' to 'Van Dieten' or even 'VanDieten'.
Call me spoiled, but in the user name example, I want my user name to appear
on-screen in mixed case, but I thoroughly enjoy being able to be lazy and
enter it in all lower case when logging in. As far as passwords go,
absolutely, they should *always* be case sensitive. But I've always
believed in having case rules be managed by the app, not the database
server. I am trying hard to re-think that, and to understand the benefits
of it. You guys are helping me with that. Thank you!
> Again, my question is, and I'm praying hard that the answer is a
> resounding
> > 'YES!', but is there a way to make the data case INsensitive?
>
> You are asking the wrong question :)
>
> Data is case sensitive. Always. In every database. If you put data in in
> some case, you don't get it back in some random other case, you get it back
> in the same case you put it in. And that is a good thing because case has
> meaning, a MB is very different from a mb.
I thoroughly agree that data should be returned in the same case in which it
was entered, in regards to display. But for search strings, when you are
searching on something that you did NOT enter, or hell, maybe you entered it
6 or 8 months ago (e.g. a blog post), a case sensitive search does not seem
to be appropriate. On the contrary, it seems like that would be a source of
frustration for the end user who gets no results, but they know that the
info which they seek is there somewhere. Being forced to figure out the
case it was entered in would, I think, drive many (most?) users away.
Some operations on data can be case insensitive. So your solution is not
> case insensitive data, your solution is a case insensitve operator. Luckily
> it is very easy to define your own operators in PostgreSQL. For instance,
> this defines case-insensitive equality and inequality operators for text
> datatypes:
>
> CREATE FUNCTION case_insensitive_equality(text, text) RETURNS boolean
> AS 'SELECT LOWER($1) = LOWER($2)'
> LANGUAGE SQL
> IMMUTABLE
> RETURNS NULL ON NULL INPUT;
> CREATE FUNCTION case_insensitive_inequality(text, text) RETURNS boolean
> AS 'SELECT LOWER($1) <> LOWER($2)'
> LANGUAGE SQL
> IMMUTABLE
> RETURNS NULL ON NULL INPUT;
> CREATE OPERATOR === (
> leftarg = text,
> rightarg = text,
> procedure = case_insensitive_equality,
> commutator = ===,
> negator= <=>
> );
> CREATE OPERATOR <=> (
> leftarg = text,
> rightarg = text,
> procedure = case_insensitive_inequality,
> commutator = <=>,
> negator= ===
> );
Wow! That's very cool. I haven't gotten to that part of the manual yet. I
have so very much yet to learn about the real world. :-)
I would recommend against overwriting the current = and <> operators with
> these operators because I have no idea what the side-effects may be and I
> don't think they are indexable.
I think that would be a 'Duh!', but thank you for pointing it out
nonetheless. :-)
Jochem
>
I really am trying to learn and accept change. Hopefully you guys can
appreciate that. :-)
Matt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266511
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
