Could the Aware IM support team please clarify the issues of case sensitive searches and databases.
Other posts seem to suggest that if you use Derby all searches will be case sensitive by default but if you use MySQL, searches will be case insensitive by default.
This is an important issue, I think.
Could you please indicate what will be the case if I use Derby, MySQL, MSDE, or MS SQL server?
thanks,
Terry
Databases and case sensitive searches
-
- Posts: 7526
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Terry,
The following applies to database searches:
1) Derby searches are case sensitive. This is buit into Derby and unfortunately cannot be changed for now.
2) MySQL is case insensitive by default. There are ways to force MySQL to be case sensitive at the database level but Aware IM does not support these. So in Aware IM MySQL is always case insensitive.
3) MS SQL Server (and MSDE as far as I know) is case insensitive by default. You can also install MS SQL Server to be case sensitive. Aware IM will support whatever current installation setting is.
Best Regards,
The following applies to database searches:
1) Derby searches are case sensitive. This is buit into Derby and unfortunately cannot be changed for now.
2) MySQL is case insensitive by default. There are ways to force MySQL to be case sensitive at the database level but Aware IM does not support these. So in Aware IM MySQL is always case insensitive.
3) MS SQL Server (and MSDE as far as I know) is case insensitive by default. You can also install MS SQL Server to be case sensitive. Aware IM will support whatever current installation setting is.
Best Regards,
Aware IM Support Team
Derby > case sensitive searches > problem & work a
Hello Aware Support.
Since this issue was last replied to in February regarding Derby being case sensitive for searches, has there been any changes to this?
If not, would the following be a possible work around: The Aware IM configurator would need to add an addtional rule or expand the query to account for case sentitivity.
For example; If a user searches for 'autonomy2', Aware IM could also include 'Autonomy2' in the query.
This issue needs to be addressed before our host sets up our dedicated server and if we should go with Mysql.
I like the fact that Derby is built in and apparently requires no administration.
Since this issue was last replied to in February regarding Derby being case sensitive for searches, has there been any changes to this?
If not, would the following be a possible work around: The Aware IM configurator would need to add an addtional rule or expand the query to account for case sentitivity.
For example; If a user searches for 'autonomy2', Aware IM could also include 'Autonomy2' in the query.
This issue needs to be addressed before our host sets up our dedicated server and if we should go with Mysql.
I like the fact that Derby is built in and apparently requires no administration.
-
- Posts: 7526
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Hello autonomy2,
no there has been no changes to this since February. Derby is case sensitive and this cannot be changed at the database level, unfortunately.
You are right, the work around is to modify your queries - for example, use TO_LOWER_CASE function on both sides of the expression:
FIND Account WHERE (TO_LOWER_CASE (Account.Name) = 'autonomy2')
This query will ignore the case of the Account.Name.
Best Regards
no there has been no changes to this since February. Derby is case sensitive and this cannot be changed at the database level, unfortunately.
You are right, the work around is to modify your queries - for example, use TO_LOWER_CASE function on both sides of the expression:
FIND Account WHERE (TO_LOWER_CASE (Account.Name) = 'autonomy2')
This query will ignore the case of the Account.Name.
Best Regards
Aware IM Support Team
Title Case
Hello Support.
How would one configure a query rule to account for and include title case searches?
For example;
AUTONOMY2 (TO_UPPER_CASE)
autonomy2(TO_LOWER_CASE)
Autonomy2 ?
How would one configure a query rule to account for and include title case searches?
For example;
AUTONOMY2 (TO_UPPER_CASE)
autonomy2(TO_LOWER_CASE)
Autonomy2 ?
-
- Posts: 595
- Joined: Sun Apr 24, 2005 2:22 am
- Contact:
If you want to ignore case in the search parameter, as well as the database values, you can do it like this:
FIND Account WHERE TO_LOWER_CASE (Account.Name) = TO_LOWER_CASE ('Autonomy2')
or a more useful version where the user can enter the desired search parameter at run time:
FIND Account WHERE TO_LOWER_CASE (Account.Name) = TO_LOWER_CASE (?'Account Name')
This will ensure that both the search parameter and the database value are converted to lower case before comparison is done.
FIND Account WHERE TO_LOWER_CASE (Account.Name) = TO_LOWER_CASE ('Autonomy2')
or a more useful version where the user can enter the desired search parameter at run time:
FIND Account WHERE TO_LOWER_CASE (Account.Name) = TO_LOWER_CASE (?'Account Name')
This will ensure that both the search parameter and the database value are converted to lower case before comparison is done.
Aware IM Support Team
another search option
I tried to use this approach with the REPLACE_PATTERN function:
FIND Product WHERE Product.CatNoShort CONTAINS REPLACE_PATTERN(?'Catalog No.','[ .-]','')
Unfortunately it did not work. REPLACE_PATTERN did not modify an entered search string but transferred it directly to FIND. Am I doing something wrong or is it a limitation of the system? Thank you.
FIND Product WHERE Product.CatNoShort CONTAINS REPLACE_PATTERN(?'Catalog No.','[ .-]','')
Unfortunately it did not work. REPLACE_PATTERN did not modify an entered search string but transferred it directly to FIND. Am I doing something wrong or is it a limitation of the system? Thank you.