New post
Question
· Dec 21, 2023

Insert ListOfObject via SQL

#Relational Tables #SQL #InterSystems IRIS #InterSystems IRIS for Health

Hello Community,

Generally we could insert the stream object through SQL for stream properties and insert the list values for list of %String property. So, Is there any way to achieve this insert for the list of objects property( ex: Property CodeTable As list of Sample.CodeTable) via SQL? 

Product version: IRIS 2023.3
$ZV: IRIS for Windows (x86-64) 2023.3
Discussion (9)2
Log in or sign up to continue
Ashok Kumar · Dec 21, 2023

Hello @David Hockenbroch

No, I've a object property as a list like Property CodeTable As list of Sample.CodeTable in my class definition. and inserting values through object refer the code below. Now I expect to insert list of object via SQL instead of object.

Class Samples.NewClass Extends %Persistent
{

Property Name As %String;

Property codetable As list Of Sample.CodeTable;

Property mycList As list Of %String;

Property Notes As %Stream.GlobalCharacter;

ClassMethod c1()
{
	set obj = ..%New()
	set codetable = ##class(Sample.CodeTable).%New()
	set codetable.Code="V"
	do obj.codetable.Insert(codetable) ;insert my Code table object as a list of 
	set codetable = ##class(Sample.CodeTable).%New()
	set codetable.Code="X"
	do obj.codetable.Insert(codetable)
	set tSC = obj.%Save()
}
}
0 0
Warlin Garcia · Dec 21, 2023

$LISTBUILD (IDs of referenced objects) should do the trick. The "parent" table here is only storing the corresponding IDs not the entire objects. You need to save each object individually and then link them.

While the object counterpart takes care of saving/inserting both objects in memory, the same is not true for SQL. You need to treat each object as individual rows on different tables.

0 0
Ashok Kumar · Dec 22, 2023

Yes Of course, We can store the object id's directly to the list of object property by using ( ex: $lb($lb("1"),$lb("2")) )  it. However we stored the values through objects. Basically the basic behaviour of storing the primary object automatically stores it's reference objects by default(DeepSave).So, I don't need to save multiple objects manually. Eventually it revokes both primary and reference object in failure state. No transactions involved in the code logic. If I save the secondary objects before storing the primary creates discrepancy in my data, Incase of failure. I thought to implement the same flow via SQL if possible.

0 0