How can I change the default string collation of a SQL stored procedure?
On an IRIS system, we expect the default string collation for SQL columns to be SQLUPPER. This means WHERE conditions will be case-insensitive. However, when I make a WHERE condition on a SQL procedure that returns a string, it's case sensitive.For example:
Class Sample.Person Extends%Persistent
{
Property Name As%String;ClassMethod Test() As%String [ SqlProc ]
{
return"Abe Lincoln"
}
} If I query select * from Sample.Person where Name = 'abe lincoln', it will return results where name = "Abe Lincoln". If I query select * from Sample.Person where Sample.Person_Test() = 'abe lincoln', it will return no results.
Where is the default collation defined for SQL procedures? Is there a way to configure it to use the SQLUPPER collation, either with an instance-wide flag or just for this one procedure?
Comments
You can use parameters on the return type. For example:
Class DC.Demo.SqlProcCollation
{
ClassMethod Test() As %String [ SqlProc ]
{
return "Abe Lincoln"
}
ClassMethod Test2() As %String(COLLATION="SQLUPPER") [ SqlProc ]
{
return "Abe Lincoln"
}
}Given that:
select DC_Demo.SqlProcCollation_Test(),DC_Demo.SqlProcCollation_Test2() where DC_Demo.SqlProcCollation_Test() = 'ABE LINCOLN'
Returns no results
select DC_Demo.SqlProcCollation_Test(),DC_Demo.SqlProcCollation_Test2() where DC_Demo.SqlProcCollation_Test2() = 'ABE LINCOLN'
Returns 1 row
Works great, thank you!