case sensitivity

[salvatore fusto]

Hi, my need is to get a list of records that, on condition, could be
case sensitive or not.
using filters i have
{field:'name',operator:'LIKE',value:'Salvat%'}
that generates

SELECT .... WHERE name LIKE 'Salvat%' that is case sensitive.

can i write a filter corrisponding to

SELECT .... WHERE upper(name) LIKE 'SALVAT%)

that is case-insensitive?
my goal is to use a checkbox in a search form to set the case
sensitivity of the search.
Regards
salvatore

[Steve Bryant]

Salvatore,

I have to admit that I thought most databases were inherently
case-insensitive when it came to LIKE operators. In fact, I have code
in to ensure that Derby is case-insensitive.

If they all can be case sensitive, then I would think this is a really
good idea. If not, then it would introduce a feature that can't be
universally supported (an anathema to DataMgr).

What database are you using right now?

Assuming it can be universal, what would you think about another
argument/attribute for the filter to indicate that?

Thanks,

Steve

> --
> You received this message because you are subscribed to the Google Groups "DataMgr" group.
> To post to this group, send email to dat...@googlegroups.com.
> To unsubscribe from this group, send email to datamgr+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/datamgr?hl=en.
>
>

[salvatore fusto]

Steve,
i'm working on h2 that is case sensitive by default if you do not
define a column as varchar_ignorecase or do not set ignore case false,
but this is a general question.
in the app i'm working on, query has to be case sensitive or not
depending on a simple checkbox that user can set:
i've solved using the advsql structure:
<cfsaveContent variable="myAdvSql.WHERE">
<cfoutput>
<cfif caseSensitive>
upper(searchField) LIKE '#uCase(searchString)#'
<cfelse>
searchField LIKE '#searchString#'
</cfif>
</cfoutput>
</cfsaveContent>

searchField is the table field and searchString is the string to
search.
i think dataMgr has to leave casesensitivity unchanged so user can
search matching the case

another way is to add a new couple key/value to the struct used for
filters:
case sensitive:

{field:'name',operator:'LIKE',value:'Salvat%'}

case insensitive
{field:'name',operator:'LIKE',value:'ucase(Salvat)%',func:'upper'}
where func is the function to apply to the table field, upper() in h2
for capitalize a column
of course a minimal code may be required to test the type of the
database used by dataMgr and the apply the right function for it
Let ne know what do you think about.
salvatore

On 8 Set, 15:50, Steve Bryant <sebto...@gmail.com> wrote:
> Salvatore,
>
> I have to admit that I thought most databases were inherently
> case-insensitive when it came to LIKE operators. In fact, I have code
> in to ensure that Derby is case-insensitive.
>
> If they all can be case sensitive, then I would think this is a really
> good idea. If not, then it would introduce a feature that can't be
> universally supported (an anathema to DataMgr).
>
> What database are you using right now?
>
> Assuming it can be universal, what would you think about another
> argument/attribute for the filter to indicate that?
>
> Thanks,
>
> Steve
>

[Steve Bryant]

Salvatore,

That sounds like a good solution for now.

It looks like SQL Server, at least, is not case sensitive for LIKE statements.
http://www.bennadel.com/blog/723-SQL-Server-Text-Matching-Is-Case-INSENSITIVE.htm

This implies, to me, that all LIKE searches done through DataMgr (not
using custom SQL) should be case insensitive. My thinking is that the
same DataMgr statement (so long as it doesn't include custom SQL) on
the same data should return the exact same result regardless of the
database platform.

Anyone have any thoughts on this?

Thanks,

Steve

[Sean P. Ford]

As far as SQL Server goes, it all depends on how the server was set up during installation.

You can use the following command to determine the settings for your particular installation.

<Cfquery name="helpsort">
sp_helpsort
</Cfquery>
<cfdump var="#helpsort#" label="COLLATIONINFO" expand="yes">

Hope that helps.

--
Sean Ford
SPF Management, LLC
c:  305.788.3673
e:  se...@seanford.com
w: http://seanford.com
w: http://twitter.com/seanford
w: http://facebook.com/seanpford

[Kym Kovan]

On 10/09/2010 12:58, Steve Bryant wrote:
> It looks like SQL Server, at least, is not case sensitive for LIKE statements.
> http://www.bennadel.com/blog/723-SQL-Server-Text-Matching-Is-Case-INSENSITIVE.htm
>
> This implies, to me, that all LIKE searches done through DataMgr (not
> using custom SQL) should be case insensitive. My thinking is that the
> same DataMgr statement (so long as it doesn't include custom SQL) on
> the same data should return the exact same result regardless of the
> database platform.
>
> Anyone have any thoughts on this?

We have come from a MS SQL server background so case-insensitive LIKE
searches have been our norm. One of the reasons we had to moving to
using DataMgr for *everything* was to become completely database
agnostic for our OSS applications and obviously you want the DAL to be
consistent and not have to make code variations depending on database
under the DAL, it makes the whole point of DataMgr irrelevant.

So I would say be consistent above all else and in this case make LIKE
searches case-insensitive.

--

Yours,

Kym

[salvatore fusto]

Steve, many db engine are case sensitive or not, depending on creation
mode of tables and columns, or with a statement, but often sensitivity
is needed: imagine a full text search on a clob column.
this is the reason why all editors run text search either case-
sensitive or insensitive.
So i think that in many real app the feature is very important and
usefull.
regards
salvatore

On 10 Set, 04:58, Steve Bryant <sebto...@gmail.com> wrote:
> Salvatore,
>

> That sounds like a good solution for now.
>

> It looks like SQL Server, at least, is not case sensitive for LIKE statements.http://www.bennadel.com/blog/723-SQL-Server-Text-Matching-Is-Case-INS...

>
> This implies, to me, that all LIKE searches done through DataMgr (not
> using custom SQL) should be case insensitive. My thinking is that the
> same DataMgr statement (so long as it doesn't include custom SQL) on
> the same data should return the exact same result regardless of the
> database platform.
>
> Anyone have any thoughts on this?
>
> Thanks,
>
> Steve
>

[Steve Bryant]

I definitely see the advantage to the feature, but I am inclined to
agree with Kym that consistency across database platforms has
priority. After all, you can use advsql to get the results that you
want.

It wouldn't be that hard to add a feature to indicate that you want
the statement to be case sensitive if possible, but I am not sure
about adding a feature that may or may not be supported across
databases (I could indicate the feature as such, but I have worked
pretty hard to make sure that DataMgr produces the exact same results
regardless of the database).

Of course, I could follow the path of the paging feature and do some
post-query work for case sensitivity in databases that are case
sensitive for LIKE searches.

Any thoughts?

Steve

[salvatore fusto]

Steve i 100% agree with you and Kym about the consistency across
databases,.that is the most important feature, but i think that
dataMgr has to replicate the exact db case sensitivity:if i run a sql
query, i obtain a query that in terms of case sensitivity depends on
db, so i think that dataMgr' s behaviour must be the same, and just as
i write a select for case insensitive results, i have to be able to
distinguish if results are to be casesensitive or not with dataMgr
too.
Example: suppose i have a long text and have to serach all paragraphs
start with a word:i can do this searching the word with the first
letter capitalizzed; if the search is case insensitive, i'll get all
occurrence, on the other hand i can obtain qhat i want: do you agrre?
regards
Salvatore

[Steve Bryant]

Salvatore,

I think in order to provide consistent results across databases and to
allow for case-sensitive searching I would have to make it
case-insensitive by default and allow for an option for
case-sensitive.

For databases that don't support it, however, I would have to do that
in post-processing of the query. In order to do that, however, the
column(s) in question would have to be in the result set - which they
might not be. I couldn't even really automatically add them to the
result set because the SQL might have a DISTINCT statement or a Max()
function or something where that would break the query.

In short, I just can't figure a way to support case-sensitive
searching and return the exact same results across all databases. I'm
open to suggestions, of course.

Steve

[salvatore fusto]

Steve, i think we are misundestanding each other: in my opinion
dataMgr api must have the exact behaviour of an hard-coded query, in
other words if a db is case insensitive, dataMgr api have to return a
case insensitive query, and if the db is case sensitive, dataMgr api
have to return a case sensitive query; IMHO dataMgr must leave db
behaviour unchanged.
this way a dataMgr lover, such as i am, programmatically but outside
of the tool, can manage case sensitivity.
hope benn clear.
regards.
Salvatore

On 13 Set, 16:35, Steve Bryant <sebto...@gmail.com> wrote:
> Salvatore,
>
> I think in order to provide consistent results across databases and to
> allow for case-sensitive searching I would have to make it
> case-insensitive by default and allow for an option for
> case-sensitive.
>
> For databases that don't support it, however, I would have to do that
> in post-processing of the query. In order to do that, however, the
> column(s) in question would have to be in the result set - which they
> might not be. I couldn't even really automatically add them to the
> result set because the SQL might have a DISTINCT statement or a Max()
> function or something where that would break the query.
>
> In short, I just can't figure a way to support case-sensitive
> searching and return the exact same results across all databases. I'm
> open to suggestions, of course.
>
> Steve
>

[Kym Kovan]

On 14/09/2010 16:21, salvatore fusto wrote:
> Steve, i think we are misundestanding each other: in my opinion
> dataMgr api must have the exact behaviour of an hard-coded query, in
> other words if a db is case insensitive, dataMgr api have to return a
> case insensitive query, and if the db is case sensitive, dataMgr api
> have to return a case sensitive query; IMHO dataMgr must leave db
> behaviour unchanged.
> this way a dataMgr lover, such as i am, programmatically but outside
> of the tool, can manage case sensitivity.

Salvatore

Can I politely disagree?

To my mind as a coder of multi-platform OSS I want to be completely
database agnostic, I want the end user to be able to use any database
behind our CMS without any code changes needing to be made. Every
database has to look the same to the codebase. If I start coding flags
and differences for each database's behaviour then what is the point of
using DataMgr, I might as well hard code for each DB.

I also see your point so it seems we have a quandary :-)

--

Yours,

Kym

[salvatore fusto]

Kym, i love this type of posts, much more that simple coding
discussion.
a simple question: suppose you have 2 databases:
a) case -sensitive db (A)
b) case insensitive db (B)
the you develop a dataMgr- based app for these 2 db: do you thik:

a) dataMgr has to return its results always case-sensitive for all db
b) dataMgr has to return its results always case-insensitive for all
db
c) dataMgrmust be case sensitive for db A, and case insensitive for
db B, depending on what db it drives

thanks and regards
Salvatore

[Kym Kovan]

On 14/09/2010 6:16 PM, salvatore fusto wrote:
> Kym, i love this type of posts, much more that simple coding
> discussion.

:-)

> a simple question: suppose you have 2 databases:
> a) case -sensitive db (A)
> b) case insensitive db (B)
> the you develop a dataMgr- based app for these 2 db:

Do you meant that the app is running two different databases at once?

> do you thik:
>
> a) dataMgr has to return its results always case-sensitive for all db
> b) dataMgr has to return its results always case-insensitive for all
> db
> c) dataMgrmust be case sensitive for db A, and case insensitive for
> db B, depending on what db it drives

Any of the above, or all of them :-)

I believe more than anything else that I want to write code that does
not have to change depending on DB and it is also very convenient to be
able to specify whether a query is case-sensitive or not.

My ideal scenario is to have a flag/attribute to specify
case-sensitivity or not, with case-insensitive as the default. Obviously
using a database that is fixed in case and cannot change its sensitivity
is a limitation but that responsibility then moves to the developer/user
to not choose an impossible combination.

--

Yours,

Kym

[salvatore fusto]

On 14 Set, 11:38, Kym Kovan <k...@mbcomms.net.au> wrote:
>   On 14/09/2010 6:16 PM, salvatore fusto wrote:
>
> > Kym, i love this type of posts, much more that simple coding
> > discussion.
>
> :-)
>
> > a simple question: suppose you have 2 databases:
> > a) case -sensitive db (A)
> > b) case insensitive db (B)
> > the you develop a dataMgr- based app for these 2 db:
>
> Do you meant that the app is running two different databases at once?

NO: i mean an app that, in an installation, runs dbA, and in another
installation runs db B

>
> > do you thik:
>
> > a) dataMgr has to return its results always case-sensitive for all db
> > b) dataMgr has to return its results always case-insensitive for all
> > db
> > c) dataMgrmust be  case sensitive for db A, and case insensitive for
> > db B, depending on what db it drives
>
> Any of the above, or all of them :-)
>
> I believe more than anything else that I want to write code that does
> not have to change depending on DB and it is also very convenient to be
> able to specify whether a query is case-sensitive or not.
>
> My ideal scenario is to have a flag/attribute to specify
> case-sensitivity or not, with case-insensitive as the default. Obviously
> using a database that is fixed in case and cannot change its sensitivity
> is a limitation but that responsibility then moves to the developer/user
> to not choose an impossible combination.
>
> --
>
> Yours,
>
> Kym

now i've understood and agree: im my actual app i do so: i use a case-
sensitive database; in my search forms i've a checkbox, checked by
default, indicating when chechek that the search must be case
insensitive, then i code my advSql, as previously mentioned, to have
a result cas-sensitive or not.

salvatore

P.S.
For Steve: leave all as is. :-)