Our SqlAnywhere database is currently version 11.0.1.

When our database was originally created, an option was
selected for it to mimic ASE behavior in the sense that the
queries were case sensitive. For instance, if you did a
query searching for last name SMITH, you'd only get the last
names for SMITH that were all capitalized. If there were
people with the last name Smith, it wouldn't be returned in
the result set.

Is there a way/setting to turn this off in the database?
We'd like to go back to having our queries NOT be case
sensitive.

Thanks in Advance.
Shawn

Case sensitivity is a database property.

To go to a case-insensitive database, you'll have to unload/reload your
database into a new case-insensitive .db file.

Glenn

Shawn wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

If you don't have too many queries, you can modify them like this:

WHERE UPPER ( employee.name ) = 'SMITH'

or

WHERE UPPER ( employee.name ) = UPPER ( @parameter )

It's better, easier, safer to unload/reload like Glenn says, though.

Breck



On 8 Oct 2009 18:24:39 -0700, Shawn wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com

Glenn,

So if I understand you correctly, I just need to go through
the process of creating a new database (.db) file with the
case sensitivity turned off. Then, once that's complete, I
can take an unload result from my case-sensitive .db file
and load it into the new case unsensitive .db. Is there
anything special I need to pay close attention to during
this process?

Shawn

That's right. Assuming you're not switching collations, the
unload/reload should be straightforward. However, because you are
switching from case-sensitive to case-insensitive values, you may
discover primary key violations during the reload process where two rows
have the same case-insensitive string values, but have different
case-sensitive values: for example the strings "foo" and "Foo".

Glenn

Shawn wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

....and unique index errors...

On 10 Oct 2009 20:08:30 -0700, "Glenn Paulley [Sybase iAnywhere]"
<paulley (AT) ianywhere (DOT) com> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com