Question
Ran Kahane · Jul 8

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
00
2 0 3 122
Log in or sign up to continue

By the way though this question was posed specifically for SQL Storage class (i.e. mapping a class to an existing global), this issue (while using '||' within the value of a multi-keyed key-property) would happen also with standard class storage.

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.

Hi Tani,

Thank you for replying. We feared this might be the case, but had to make sure nonetheless that there's no way around it.