Question William Beetge · Feb 28, 2019

Inserting into table array of objects property via SQL

I have an existing table, and I've added an array property to it that I need to populate.
The issue is that I can only use SQL to insert into the table due to access reasons.

For example:

Class Package.Tables.Person Extends %Persistent
{
Property Name As %String(MAXLEN = "");

Property Address As Array of Package.Datatypes.Address;
}

Class Package.Datatypes.Address Extends %SerialObject
{
    Property AddressType As %Integer;
    Property StreetAddress As %String(MAXLKEN = "");
}
                
So the projected tables are:

Package_Tables.Person
Package_Tables.Person_Address

Now via ObjectScript it's as easy as:

    set tPerson = ##class(Package.Tables.Person).%OpenId(1)
    set tNewAddress = ##class(Package.Datatypes.Address).%New()
    set tNewAddress.AddressType = 1
    set tNewAddress.StreetAddress "72 Fake Street"
    do tPerson.Address.SetAt(tNewAddress,1)
    set sc = tPerson.%Save()
    
My first guess was:
    INSERT INTO Package_Tables.Person_Address
    (Person, element_key, AddressType, StreetAddress)
    VALUES
    (1,'1||1',1,"72 Fake Street")
    
    But I get "INSERT not allowed for read only table 'Package_Tables.Person_Address'".
    This makes sense as the Package_Tables.Person_Address table does not actually exist.
    
    
But this leaves the question. How do I add new Address entrioes to my Person table via SQL?

Comments

Vitaliy Serdtsev · Feb 28, 2019

SQL Projection of Object-Valued Properties

<FONT COLOR="#0000ff">INSERT </FONT><FONT COLOR="#000080">INTO </FONT><FONT COLOR="#008000">Package_Tables</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Person_Address
    </FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">Person</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">element_key</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">Address_AddressType</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">Address_StreetAddress</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#000080">VALUES
    </FONT><FONT COLOR="#000000">(1,2,1,</FONT><FONT COLOR="#008080">'72 Fake Street'</FONT><FONT COLOR="#000000">)</FONT>
0