New post
Question
· Apr 7, 2023

How can I change the default string collation of a SQL stored procedure?

#SQL #InterSystems IRIS

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"
}

} 
ObjectScript
ObjectScript

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?

Product version: IRIS 2022.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux 7 for x86-64) 2022.1 (Build 209U) Tue May 31 2022 12:13:58 EDT
Discussion (2)2
Log in or sign up to continue
Timothy Leavitt · Apr 7, 2023

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

7 0