Written by

Question Ran Kahane · 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

Comments

Tani Frankel · Jul 15, 2021

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.

0
Tani Frankel · Jul 15, 2021

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.

0
Ran Kahane · Jul 18, 2021

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.

0