Insert ListOfObject via SQL
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?
Comments
Is $LISTBUILD what you're looking for?
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()
}
}
$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.
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.
The "only" way to do this via SQL would be using stored procedures. You can pass your object tree using either XML or JSON string and process them (using object logic) inside your SP. Standard SQL as intended won't support what you want to achieve.
If there's no data yet, project it as a table:
Property CodeTable As list Of Sample.CodeTable(SQLPROJECTION = "table/column", STORAGEDEFAULT = "array");Hello @Eduard Lebedyuk
Table have data. Incase If I make it as array then it will be no longer a list and its totally different for my case.
You can try just SQLPROJECTION = "table/column" but I highly recommend testing it first.
Sure will try it.