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#">
> 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'.
> 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.
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= ===
);
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.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:266491
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
