Question
· Jul 12, 2023

Help with global mapping with two namespaces with a static subscript

I found the thread that discusses object mapping, in particular mapping a common global among more than one namespace. The example that is given is a simple one when it's ^global(sub1, ^global(sub2, etc. However I'm having trouble getting this to compile/work when the global has a fixed subscript amongst variable ones.

I have this global in namespaces LAB and ARK in the following format:

^CB(1,sub1)=....

^CB(1,sub2)=...

^CB(1,sub3)=...

Here is what I have for this. In it's current state it throws tons of errors:

If I remove the fixed subscript (like it's not even part of the global structure), and rename {L3} to {L2}, it will compile, but of course not work correctly.

/// Namespace
Property NS As %String [ Required ];

/// Contact Row ID
Property RowID As %Library.String(SELECTIVITY = 1, TRUNCATE = 1) [ Required ];

Index RowIDBasedIDKeyIndex On (NS, RowID) [ IdKey, PrimaryKey, Unique ];

Storage SQLStorage
{
<ExtentSize>50000</ExtentSize>
<SQLMap name="MasterMap">
<Global>^CB</Global>
<RowIdSpec name="1">
<Expression>{L1}</Expression>
<Field>NS</Field>
</RowIdSpec>
<RowIdSpec name="2">
<Expression>{L3}</Expression>
<Field>RowID</Field>
</RowIdSpec>
<RowReference>^|{L1}|CB(1,{L3})</RowReference>
<Subscript name="1">
<AccessType>Other</AccessType>
<Expression>{NS}</Expression>
<Invalidcondition name="1">
<Expression>",LAB,ARK,"'[(","_{L1}_",")</Expression>
</Invalidcondition>
<NextCode> if {L1}="LAB" s {L1}="" QUIT
 if {L1}="ARK" s {L1}="LAB" QUIT
 if {L1}="" s {L1}="ARK" QUIT
</NextCode>
</Subscript>
<Subscript name="2">
<Expression>1</Expression>
</Subscript>
<Subscript name="3">
<DataAccess>^|{L1}|CB</DataAccess>
<Expression>{RowID}</Expression>
<NextCode>s {L3}=$O(^|{L1}|CB(1,{L3}))
</NextCode>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Mapping.TestS</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

Compiling class Mapping.Test
Compiling table Mapping.Test
Compiling routine Mapping.Test.1
Mapping.Test.1.INT(64) ERROR #1010: Missing right parenthesis : ''$t,$d(%mmmsqld(5)1)),%mmmsqld(4)'="",$d(^|%mmmsqld(3)|CB(%mmmsqld(4))),$d(^|%mmmsqld(3)|CB(1))' : Offset:22 [%0Ao+15^Mapping.Test.1]
 TEXT: i '$t,$d(%mmmsqld(5)1)),%mmmsqld(4)'="",$d(^|%mmmsqld(3)|CB(%mmmsqld(4))),$d(^|%mmmsqld(3)|CB(1))
Mapping.Test.1.INT(110) ERROR #1010: Missing right parenthesis : ''$t,$d(%mmmsqld(3)1)),i%RowID'="",$d(^|i%NS|CB(i%RowID)),$d(^|i%NS|CB(1))' : Offset:22 [%0Co+15^Mapping.Test.1]
 TEXT: i '$t,$d(%mmmsqld(3)1)),i%RowID'="",$d(^|i%NS|CB(i%RowID)),$d(^|i%NS|CB(1))
Mapping.Test.1.INT(172) ERROR #1010: Missing right parenthesis : ''$t,$d(%mmmsqld(8)1)),%mmmsqld(7)'="",$d(^|%mmmsqld(6)|CB(%mmmsqld(7))),$d(^|%mmmsqld(6)|CB(1))' : Offset:22 [%0Eo+17^Mapping.Test.1]
 TEXT: i '$t,$d(%mmmsqld(8)1)),%mmmsqld(7)'="",$d(^|%mmmsqld(6)|CB(%mmmsqld(7))),$d(^|%mmmsqld(6)|CB(1))
Mapping.Test.1.INT(237) ERROR #1010: Missing right parenthesis : ''$t,$d(%mmmsqld(4)1)),d(3)'="",$d(^|d(2)|CB(d(3))),$d(^|d(2)|CB(1))' : Offset:22 [%0Go+15^Mapping.Test.1]
 TEXT: i '$t,$d(%mmmsqld(4)1)),d(3)'="",$d(^|d(2)|CB(d(3))),$d(^|d(2)|CB(1))
Mapping.Test.1.INT(284) ERROR #1010: Missing right parenthesis : ''$t,$d(%mmmsqld(4)1)),d(3)'="",$d(^|d(2)|CB(d(3))),$d(^|d(2)|CB(1))' : Offset:22 [%0Io+15^Mapping.Test.1]
 TEXT: i '$t,$d(%mmmsqld(4)1)),d(3)'="",$d(^|d(2)|CB(d(3))),$d(^|d(2)|CB(1))
Mapping.Test.1.INT(370) ERROR #1027: Error in SET command : '1=$O(^|%objcsd(%qHandle,5)|CB(1,1))' : Offset:11 [%0KmBx2^Mapping.Test.1]
 TEXT: %0KmBx2 s 1=$O(^|%objcsd(%qHandle,5)|CB(1,1))
Mapping.Test.1.INT(386) ERROR #1010: Missing right parenthesis : ''$d(%objcsd(%qHandle,4)1))' : Offset:27 [%0KmBk1+2^Mapping.Test.1]
 TEXT: i '$d(%objcsd(%qHandle,4)1)) g %0KmBk1
Mapping.Test.1.INT(493) ERROR #1010: Missing right parenthesis : ''$d(%mmmsqld(8)1))' : Offset:19 [%0To+20^Mapping.Test.1]
 TEXT: i '$d(%mmmsqld(8)1)) g %0TmBdun
Detected 8 errors during compilation in 3.732s.

Update since I posted this:

If I make that "1" a variable (and set it to {L2}) and set an invalid condition {L2}'=1 then I do get this table to compile and work but I don't think that should be necessary.


However, when I carried this forward and made a child table:  ^CB(1,sub1, subb1)=...    I'm running into similar errors with the child table. What parameters should the child table's subscripts have compared to the parent above?

Product version: Caché 2014.1
$ZV: Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2014.1.5 (Build 851_7) Tue May 14 2019 08:22:20 EDT
Discussion (4)2
Log in or sign up to continue

 TEXT: %0KmBx2 s 1=$O(^|%objcsd(%qHandle,5)|CB(1,1))

this cannot be right. the solution to the $O is always a variable starting and ending with (null-"")

your comment was:

If I make that "1" a variable (and set it to {L2}) and set an invalid condition {L2}'=1 then I do get this table to compile and work but I don't think that should be necessary.

also the $O() terminates with null so {L2}'=1 looks wrong ie {L2}'=""

the ref; <Invalidcondition name=..L2

this does say NAME ie a variable

I do not do mapping, but your definitions appear to break the rules

At this point it doesn't really matter as I was able to achieve what I wanted a different way. But this all started because of a guide I found on here on table mappings, and one of the examples was using multiple namespaces which I hadn't tried with mapping before. It worked as expected when the global was simple with only variable indices, but once I added a static index (like above) I just couldn't get it to work without playing around with it.

The attached class should provide what you are looking for based on my testing.  This follows work I did while at a prior position.

Some things to note about this class

  • I'm declaring  SqlRowIdName = Id so that I don't end up with an Id1 column in the SQL view of the table
  • I've set  Parameter STORAGEDELIMITERCHECK = 1; so that when an Save/Insert/Update is done it ensures that the data fields do not contain a delimiter and then "throw off" the piecing strategy, if you aren using delimited data but instead using $list data then you wouldn't need this.
  • I've overridden %OnNew so as to establish an array of namespaces where the data lives
  • You would also want to call SetServerInitCode in %SYSTEM.SQL or the corresponding SetOption in %SYSTEM.SQL.Util  so as to build up the namespaces array.
  • My usage of the namespaces array is for demonstration purposes only, your application probably already has some array that defines where the CB global is defined.
  • If you want to only provide read access thru objects and SQL you could set the class Parameter READONLY=1;

seems like uploading a file doesn't work.. here's the class definition .. this will work on Cache or IRIS although on a Cache based system you would have to change <Type>%Storage.SQL</Type> to the proper Cache type.


Class Mapping.Test1 Extends %Persistent [ SqlRowIdName = Id, StorageStrategy = SQLStorage ]
{

/// Namespace
Property NS As %String [ Required ];

/// Contact Row ID
Property RowID As %Library.String(SELECTIVITY = 1, TRUNCATE = 1) [ Required ];

Property Field1 As %String;

Property Field2 As %String;

Index RowIDBasedIDKeyIndex On (NS, RowID) [ IdKey ];

/// <Example>
/// Set tSC=##class(Mappting.Test1).Populate()
/// </example>
ClassMethod Populate() As %Status
{
    #dim tSC 			As %Status=$$$OK
    #dim eException  	As %Exception.AbstractException
    try {
        Set tObject=..%New()
        Set tObject.NS="LAB"
        Set tObject.RowID=1
        $$$THROWONERROR(tSC,tObject.%Save())
        Write !,tObject.NS,": ",tObject.RowID
        
        Set tObject=..%New()
        Set tObject.NS="ARK"
        Set tObject.RowID=1
        $$$THROWONERROR(tSC,tObject.%Save())
        Write !,tObject.NS,": ",tObject.RowID
        
        &SQL(insert into Mapping.Test1 (NS,Rowid) values ('LAB',2))
        If SQLCODE $$$ThrowStatus(##class(%Exception.SQL).CreateFromSQLCODE(SQLCODE,%msg))
        
    }
    catch eException {
        Set tSC=eException.AsStatus()
    }
    Quit tSC
}

Storage SQLStorage
{
<SQLMap name="Map1">
<Data name="Field1">
<Delimiter>"^"</Delimiter>
<Piece>1</Piece>
</Data>
<Data name="Field2">
<Delimiter>"^"</Delimiter>
<Piece>2</Piece>
</Data>
<Global>namespaces</Global>
<Subscript name="1">
<Expression>{NS}</Expression>
</Subscript>
<Subscript name="2">
<Expression>1</Expression>
</Subscript>
<Subscript name="3">
<DataAccess>^[{L1}]CB({L2})</DataAccess>
<Expression>{RowID}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Mappting.Test1S</StreamLocation>
<Type>%Storage.SQL</Type>
}

/// This callback method is invoked by the <METHOD>%New</METHOD> method to 
/// provide notification that a new instance of an object is being created.
/// 
/// <P>If this method returns an error then the object will not be created.
/// <p>It is passed the arguments provided in the %New call.
/// When customizing this method, override the arguments with whatever variables and types you expect to receive from %New().
/// For example, if you're going to call %New, passing 2 arguments, %OnNew's signature could be:
/// <p>Method %OnNew(dob as %Date = "", name as %Name = "") as %Status
/// If instead of returning a %Status code this returns an oref and this oref is a subclass of the current
/// class then this oref will be the one returned to the caller of %New method.
Method %OnNew() As %Status [ Private, PublicList = namespaces, ServerOnly = 1 ]
{
    
    #dim tSC		As %Status=$$$OK
    #dim eException	As %Exception.AbstractException
    Try {
        Set namespaces("LAB")=""
        Set namespaces("ARK")=""
    }
    Catch eException {
        Set tSC=eException.AsStatus()
    }
    Quit tSC
}

Parameter STORAGEDELIMITERCHECK = 1;

}