Intersystems caché - sqlStorage

Question

I need to map my global to sql. I have global structure like this:

^myGlob("mltab","main","Dta",idRow,idCol,"Index")

concretely:

^myGlob("mltab","main","Dta",3,1)   =   20
^myGlob("mltab","main","Dta",3,2)   =   30
^myGlob("mltab","main","Dta",3,2,"Index")   =   "t|1320|30|62913"
^myGlob("mltab","main","Dta",3,3)   =   "01.04.2013"
^myGlob("mltab","main","Dta",3,4)   =   36
^myGlob("mltab","main","Dta",3,5)   =   166.8
^myGlob("mltab","main","Dta",4,1)   =   20
^myGlob("mltab","main","Dta",4,2)   =   30
^myGlob("mltab","main","Dta",4,3)   =   "01.04.2013"
^myGlob("mltab","main","Dta",4,4)   =   36
^myGlob("mltab","main","Dta",4,4,"Index")   =   "nextIndexVal"
^myGlob("mltab","main","Dta",4,5)   =   166.8
                             .
                             .
                             .

"mltab","main","Dta" and "Index" are constant. idRow is my primary key and idCol isn't important for me, but causes me problem, because it can change.

I need to get something like colId=2^index="t|1320|30|62913" value to some table column(map class property). Other values I have mapped.

my current code looks like this:

Class Kza.NewClass1 Extends %Persistent [ Not ProcedureBlock, StorageStrategy = MySqlStorageStrategy ]
{

Property hyperlink As %String;

Property colId As %String;

Property id As %Integer;

Property prop1 As %String;

Property prop2 As %String;

Property prop3 As %String;

Property prop4 As %String;

Property prop5 As %String;

Index MyIndex1 On id [ IdKey, PrimaryKey, Unique ];

<Storage name="MySqlStorageStrategy">
<ExtentSize>100000</ExtentSize>
<SQLMap name="Map1">
<Global>^myGlob</Global>
<RowIdSpec name="1">
<Expression>{L2}</Expression>
<Field>id</Field>
</RowIdSpec>
<Subscript name="1">
<Expression>{id}</Expression>
</Subscript>
<Subscript name="2">
<Expression>{colId}</Expression>
</Subscript>
<Type>index</Type>
</SQLMap>
<SQLMap name="MyMasterMap">
<ConditionalWithHostVars></ConditionalWithHostVars>
<Data name="colId">
<Node>6</Node>
</Data>
<Data name="hyperlink">
<Node>"Index"</Node>
</Data>
<Data name="prop1">
<Node>1</Node>
</Data>
<Data name="prop2">
<Node>2</Node>
</Data>
<Data name="prop3">
<Node>3</Node>
</Data>
<Data name="prop4">
<Node>4</Node>
</Data>
<Data name="prop5">
<Node>5</Node>
</Data>
<Global>^myGlob</Global>
<RowIdSpec name="1">
<Expression>{L1}</Expression>
<Field>id</Field>
</RowIdSpec>
<Subscript name="1">
<Expression>"mltab"</Expression>
</Subscript>
<Subscript name="2">
<Expression>"main"</Expression>
</Subscript>
<Subscript name="3">
<Expression>"Dta"</Expression>
</Subscript>
<Subscript name="4">
<Expression>{id}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Kza.testToTable1S</StreamLocation>
<Type>%CacheSQLStorage</Type>
</Storage>
}

I think my problem may be in data node definition:

<Data name="colId">
    <Node>6</Node>
</Data>
<Data name="hyperlink">
    <Node>"Index"</Node>
</Data>

but sadly, I am not familiar with this issue enough... So can someone help me to get column hyperlink in my sql table with value colId=2^index=t|1320|30|62913 on row with id 3 and colId=4^index=nextIndexVal on row id 4?

what you mean about idCol, i see that you use it for different properties, but one of them have "Index". And you defined colId as node 6, why ? — DAiMor, Mar 04 '15 at 17:06
@DAiMor becaus I don't know how to do it. I need tu have "t|1320|30|62913" to property hyperlink — mrfazolka, Mar 04 '15 at 17:55
@DAiMor colId can be 1,2,3,3.6,3.8,4,5.... and so on. I will edit my question for clarity — mrfazolka, Mar 05 '15 at 08:22

DAiMor · Accepted Answer · 2015-03-05T12:23:42.400

2

If colId for hyperlink property fixed, you can use tag RetrievalCode where possible to write some COS code, if not fixed, you can write something for retreive colId and then like here

<Data name="hyperlink">
  <RetrievalCode> s {hyperlink}=$g(^myGlob("mltab","main","Dta",{L4},2,"Index"))
  </RetrievalCode>
</Data>

and your RowIdSpec maybe wrong, because, property id on 4 level of subscripts, so you have use L4 for expression.

<RowIdSpec name="1">
  <Expression>{L4}</Expression>
  <Field>id</Field>
</RowIdSpec>

and latest variant

<Data name="colId">
  <RetrievalCode>s {*}="" f { s {*}=$o(^myGlob({L1},{L2},{L3},{L4},{*})) quit:{*}=""  quit:$d(^myGlob({L1},{L2},{L3},{L4},{*},"Index"))}
  </RetrievalCode>
</Data>
<Data name="hyperlink">
  <RetrievalCode>s colId="" f { s colId=$o(^myGlob({L1},{L2},{L3},{L4},colId)) quit:colId=""  quit:$d(^myGlob({L1},{L2},{L3},{L4},colId,"Index"))}
s {*}=$g(^myGlob({L1},{L2},{L3},{L4},colId,"Index"))
  </RetrievalCode>
</Data>
<Data name="hyperlink2">
  <RetrievalCode>s colId="" f { s colId=$o(^myGlob({L1},{L2},{L3},{L4},colId)) quit:colId=""  quit:$d(^myGlob({L1},{L2},{L3},{L4},colId,"Index"))}
s {*}="colId="_colId_"^index="""_$g(^myGlob({L1},{L2},{L3},{L4},colId,"Index"))_""""
  </RetrievalCode>
</Data>

edited Mar 05 '15 at 12:23

answered Mar 04 '15 at 21:26

DAiMor

3,185
16
24

It looks like one of solutions I tried. Thanks, I will implement it and let know if it works for me :) – mrfazolka Mar 05 '15 at 08:00
I edited my question ..... could you edit the code for my purpose now please? Thanks a lot – mrfazolka Mar 05 '15 at 08:25
colId for Index looks randomized, and you should guess which colId have Index subscript for hyperlink ? – DAiMor Mar 05 '15 at 08:30
In fact, I have colId description. I can get all possible values of colId. It is stored like ......... i will edit question :) – mrfazolka Mar 05 '15 at 08:33
I gone through my question again, and infer that my question could be confused. So I edited it and hope now its clear – mrfazolka Mar 05 '15 at 09:01
if it needed only for reading data, maybe it will be easier to do it with Cache Query. I still don't understand how to know correct colId for hyperlink. because global ^ohtest("mltab","main","SL") looks like a scheme of data for another global ^myGlob("mltab","main","Dta"), and so colId seems to be fixed for concrete global. – DAiMor Mar 05 '15 at 09:02
and you say that you have different properties in the same colId like here ^myGlob("mltab","main","Dta",3,2) = 30 ^myGlob("mltab","main","Dta",3,2,"Index") = "t|1320|30|62913" – DAiMor Mar 05 '15 at 09:08
Sorry I forgot to rename global name ... all data are in one global. ColId is fixed for concrete global, but I dont know on witch colId is "Index" node. My problem is changing subscript ..... I have defined subscripts "mltab", "main", "Dta" and {id}, but don't know how to deal witch colId – mrfazolka Mar 05 '15 at 09:10
I need to have column hyperlink in my sql table with value colId=2^index=t|1320|30|62913 on row with id 3 and colId=4^index=nextIndexVal on row id 4 – mrfazolka Mar 05 '15 at 09:19
Your solution is totally stunned, I see really great usability of caché database. Thank you a lot again – mrfazolka Mar 05 '15 at 12:28

score 1 · Answer 2 · answered Mar 05 '15 at 12:13

1

This code in SQLMap storage do it:

<Data name="hyperlink">
<RetrievalCode>
<![CDATA[set res=""
    set colId=""
    for
    {
        set colId = $order( ^myGlob("mltab","main","Dta",{L4},colId) )
        quit:(colId = "")

        set indexTab = $g(^myGlob("mltab","main","Dta",{L4},colId,"Index"))
        if indexTab'="" {
            set res = res_"colId="_colId_"index="_indexTab
        }
    }
    s {hyperlink}=res]]></RetrievalCode>
</Data>

{L4} is row id, and no more than one SQLmap is required

answered Mar 05 '15 at 12:13

mrfazolka

780
1
7
24

1

when i've been ready to edit my answer, just seen yours. I also did it. but in my example you can see that possible to use {L*} for define sibscripts, and {*} for current field. – DAiMor Mar 05 '15 at 12:26
@DAiMor could you show me, how to do this via %Dictionary? http://stackoverflow.com/questions/28880384/how-to-set-dictionary-storagesqlmapdatadefinition-retrievalcode – mrfazolka Mar 05 '15 at 16:16

Intersystems caché - sqlStorage

2 Answers2