Expose Cache Globals using SQL Storage and SQLMAP
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.
.png)
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:
.png)
but get an error when compiling saying I cannot reference another field:
.png)
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
Comments
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. 👍
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-3315New York Laura 333-444-55557 Chicago Paul 556-666-77773 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>>quitor as objects
USER>s obj=##class(DC.OldStuff).%OpenId(7)
USER>w obj.Name,!,obj.City,!,obj.Phone
Paul
Chicago
556-666-777
USER>
$zel, $zlp - two new ObjectScript functions in one day after more than 10 years in InterSystems... Love this community! )
Ha ha ha 😂, that's a big mistake. Those are old functions (for even older applications, maintained for backward compatibility only) in the mean time all replaced by the $list...() functions.
In anyways, there are folks on the community that can help even with these already undocummented stuff )
Occasionally found yet another undocumented function - $zle:
USER> setx=$zlp("a,b,$c(2)") set y=$zle(x) set z=$zel(x,3) zwritex,y,z
x=$c(2)_"a"_$c(2)_"b"_$c(6)_"$c(2)"
y=3
z="$c(2)"Who knows more?
It's the length (number of elements) on the (old format) list
Enrico, it was obvious :)
I meant if anybody knows another "tricky" functions
in retrieval code use: Set {*} = ...