Question
· Jul 8, 2021

Delimiter definition - CacheSQLStorage

Hi all,

We're encountering a problem using CacheSQLStorage where one of the subscripts of this mapped table contains the string "||", which causes issues as Caché uses "||" as a delimiter. Is it possible to assign a different delimiter?

I'm attaching the code of the class as an example.

Class MainCore.System.MDA.msort2x2x2 Extends %Persistent [ ProcedureBlock, StorageStrategy = MainStorage ]
{

Property Subscript1 As %String;

Property Subscript2 As %String;

Property Data As %String(MAXLEN = 32000);

Property ZREF As %String [ Calculated, SqlComputeCode = { S {*} = ##class(MainCore.System.Utils.CacheUtils).getZREF("^[""LEASING""]MSORT2",{ID})}, SqlComputed, Transient ];

Index PMKey On (Subscript1, Subscript2) [ IdKey, PrimaryKey, Unique ];

Storage MainStorage
{
<SQLMap name="MainMap">
<Data name="Data">
<Delimiter>"~"</Delimiter>
<Piece>1</Piece>
</Data>
<Global>^["LEASING"]MSORT2</Global>
<Subscript name="1">
<Expression>{Subscript1}</Expression>
</Subscript>
<Subscript name="2">
<Expression>{Subscript2}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^["LEASING"]MSORT2.S</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

}

Product version: Caché 2016.1
Discussion (3)1
Log in or sign up to continue

Ran,

Thanks to @Tom Woodfin for finding this - there is a documented limitation for using || within properties that are part of an IDKEY, see from here:

   IMPORTANT:

   There must not be a sequential pair of vertical bars (||) within the values of any property used by an IDKEY index, unless that property is a valid reference to an instance of a persistent class. This restriction is imposed by the way in which the InterSystems SQL mechanism works. The use of || in IDKey properties can result in unpredictable behavior.

And also after some internal discussion - there is no way around this limitation.