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
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>