Question
· Aug 18

Expose Cache Globals using SQL Storage and SQLMAP

#Globals #SQL #Caché

Trying to create a new SQL Storage map on existing cache Global in the following format - ^MYGLO("R",rec)=data where the 'data' is built using $zel. e.g. $zel(data,1)="p1", $zel(data,2)="p2" etc... and the ^MYGLO("R",123)=data.

I'm having 2 issues. First, using the SQL Storage map wizard, I cannot figure out how to convey data in $zel format in the "Delimiter" field. 

 

Second, since I couldn't figure that out,  I tried to use the "Use Retrieval Code" option and entered the following line for the P1 property:

 

but get an error when compiling saying I cannot reference another field:

 

So my 2 questions are:

1. How to convey data in $zel format

2. What to add as the main rec ID if using the "Retrieval Code" option

Thank you

Product version: IRIS 2021.1
$ZV: 2021.1.3
Discussion (9)4
Log in or sign up to continue
Mark Pousee · Aug 18

You don’t actually need to overcomplicate this Caché/IRIS doesn’t support putting $ZEL logic directly in the SQL Storage “Delimiter” field. Instead, the usual pattern is to store your delimited string in the global and then expose each element through calculated properties with custom SqlComputeCode (for inserts) and SqlComputed (for retrieval). That way you can parse $ZEL(data,n) into P1, P2, etc. without fighting the wizard. For the record ID, just use the numeric subscript (e.g. rec in ^MYGLO("R",rec)) as your %ID  that’s your primary key. In short: don’t try to shoehorn $ZEL into the delimiter option; define your properties as computed and base them on $ZEL slices of your stored string, with rec as the ID. 👍

0 0
Julius Kavay · Aug 18

Try it this way...

Class DC.OldStuff Extends %Persistent [ StorageStrategy = NewStorage1 ]
{

Property Rec As %Integer [ Identity ];
Property Name As %String;
Property City As %String;
Property Phone As %String;
Storage NewStorage1
{
<SQLMap name="Map1">
<Data name="City">
<RetrievalCode>s {*}=$zel(^myGlo("R",{L2}),2)</RetrievalCode>
</Data>
<Data name="Name">
<RetrievalCode>s {*}=$zel(^myGlo("R",{L2}),1)</RetrievalCode>
</Data>
<Data name="Phone">
<RetrievalCode>s {*}=$zel(^myGlo("R",{L2}),3)</RetrievalCode>
</Data>
<Global>^myGlo</Global>
<Subscript name="1">
<Expression>"R"</Expression>
</Subscript>
<Subscript name="2">
<Expression>{Rec}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^DC.OldS</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

}

A short test shows, it works

USER>k ^myGlo
USER>s ^myGlo("R",1)=$zlp("John,Boston,11-22-33")
USER>s ^myGlo("R",5)=$zlp("Laura,New York,333-444-555")
USER>s ^myGlo("R",7)=$zlp("Paul,Chicago,556-666-777")
USER>d $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------

The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
USER>>select * from DC.OldStuff
3.      select * from DC.OldStuff

ID      City    Name    Phone   Rec
1       Boston  John    11-22-33        1
5       New York        Laura   333-444-555     5
7       Chicago Paul    556-666-777     7
3 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0003s/5/159/0ms
          execute time(s)/globals/lines/disk: 0.0003s/13/1136/0ms
                          cached query class: %sqlcq.USER.cls43
---------------------------------------------------------------------------
USER>>quit

or as objects

USER>s obj=##class(DC.OldStuff).%OpenId(7)

USER>w obj.Name,!,obj.City,!,obj.Phone
Paul
Chicago
556-666-777
USER>
0 0