How to add the case insensitive index to a class?
Hi!
Consider I have a class Package.Data with Property UniqueStringValue as %String.
I introduced the Index for this property:
Index ValueIndex on UniqueStringValue [Unique];
It works well. But if I try to check if there is an object with the certain value in code like this:
if ##class(Package.Data).ValueIndexExists(value)
this expression fails, if value="value", even if there is an instance with instance.UniqueStingValue="Value"
How can I set the index to prevent saving case sensitive values in this class?
UseCOLLATION as above, to specify the manner in which property values are transformed for indexing.
Below is how data and index will look like,
ZW ^DataGlobal
^DataGlobal=1
^DataGlobal(1)=$lb("",,"Hello")
ZW ^IndexGlobal
^IndexGlobal("NameIDX"," HELLO",1)=""
^IndexGlobal("ValueIndex"," HELLO",1)=""
Test:
Write ##class(Package.Data).ValueIndexExists("hello") = 1
Write ##class(Package.Data).ValueIndexExists("HELLO") = 1
Write ##class(Package.Data).ValueIndexExists("heLLO") = 1
Write ##class(Package.Data).ValueIndexExists("hello") = 1
Write ##class(Package.Data).ValueIndexExists("Hello") = 1
Write ##class(Package.Data).ValueIndexExists("HelloGuys") = 0
ISC DocBook: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_basics#GSQL_basics_collation
Agreed. Default collation for String is SQLUPPER. You can't define the collation of a index if its defined as Unique as below. Below is prohibited, but you can mention any collation on property instead of specifying in Index.
Index ValueIndex On UniqueStringValue As SQLUPPER [ Unique ];
On the other hand, I mentioned another solution to the actual problem above. Please review/comment on that.
The default behavior for %String is case insensitive searching, so I would expect what Bachhar shows would be the same if he left off Collation = 'SQLUPPER".
It is possible to change the default collation for %String on a system, so maybe that is why you are seeing different results. For example this command would make the default behavior %EXACT:
USER>w $$SetEnvironment^%apiOBJ("collation","%Library.String","EXACT")
1
USER>w $$GetEnvironment^%apiOBJ("collation","%Library.String",.current)
1
USER>w current
EXACT
USER>
This is done on the namespace level
brendan
Thanks for the answers!
Actually there was a mistake in example. The original case in my app uses idkey:
Caché uses Exact collation for idkey indexes.
So that's why I had a problem with
method.
Yes, if you have idkey in index, that has to be Exact collation.
But I can tell you one way around it, lets see if you like it. Its tricky though.
Now, if you do ValueIndex2Exists("hello") or ValueIndex2Exists("Hello") or ValueIndex2Exists("hELLO") you will find match. This will also work on SQL as well if you do - 'Where CopyUniqueStringValue=?'.
This should solve your purpose though, but the space will be duplicated for same index though.
And yes: Caché creates the index with SQLUPPER collation by default for all the indexes, except idkey ones.
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue