1
592

Written by

phillip jack
Follow
Question phillip jack · Feb 2, 2022

Saving a serial object using Result set

#Caché #Databases #InterSystems Business Solutions and Architectures #ObjectScript

Hi Folks,

I created a persistent class as below 

Class myclass.DataBase Extends %Persistent
{

Property ID As %String;

Property Skill As list Of myclass.SerialTablelist;
}

and Created another Serial class as 

Class myclass.SerialTablelist Extends %SerialObject
{

Property PSkill As %String;

Property OSkill As %String;
}

Now I will save the id as below

do rs.Prepare("Insert into myclass.DataBase(ID)VALUES(?)")
do rs.Execute(ID)

 

I am facing issue with the AS LIST OF 

Could any one guide me how to save the Skill (PSkill ,OSkill ) using resultset (I am able to )

Discussion (12)1
Log in or sign up to continue

Comments

Cristiano Silva · Feb 2, 2022

Hi Philip,

The SQL projection of the property Skill of class myclass.DataBase is compounded by 3 $List.

To Insert via SQL use the following statement:

INSERT INTO myclass.DataBase VALUES ('My ID', $LISTBUILD($LISTBUILD($LISTBUILD('PSkill Value','OSkill Value'))))

Regards

Cristiano José da Silva

0
phillip jack · Feb 2, 2022

@Cristiano Silva  Thanks for your Response.

Since I am refering the property skill "as list of" another serial class ( Property Skill As list Of myclass.SerialTablelist;)

How could we retrive the skill.oskill and skill.pskill value ?

for example through objects we will fetch the value as below 

write obj.Skill.GetAt(1).PSkill,!
write obj.Skill.GetAt(1).OSkill,!

0
Cristiano Silva  Feb 2, 2022 to phillip jack

HI @phillip jack,

Like @Eduard Lebedyuk told, the better way is using stored procedures written in COS, but if you want to use only SQL you can try this:

SELECT ID, $LISTGET($LISTGET($LISTGET(Skill)),1) PSKILL, $LISTGET($LISTGET($LISTGET(Skill)),2) OSKILL FROM myclass.DataBase
 

If you change the serial object and add more properties increment the outer $LISTGET last parameter

Regards,

Cristiano José da Silva.

0
Benjamin De Boe · Feb 3, 2022

A little more documentation on the SQL projection of collection properties here.

Currently the SQLPROJECTION = table option is only available for "array of" properties. In 2022.1 we intend to make this option also available for "list of" properties.

If you're interested, I can dig up a script I wrote a while ago to create a class that projects a read-only view of your "list of" property. No warranty!

0
Benjamin De Boe  Feb 4, 2022 to Eduard Lebedyuk

what do you mean with "for object properties too"? If you're asking whether 

Property Addresses as array of Sample.Address;

projects as a child table; yes it does. (and "list of" will support this too in 2022.1)

0
Eduard Lebedyuk  Feb 5, 2022 to Benjamin De Boe

"list of" will support this too in 2022.1

If you define Addresses list property like this:

Property Addresses As list Of Sample.Address(SQLPROJECTION = "table/column", STORAGEDEFAULT = "array");

Child table would be created.

0
Benjamin De Boe  Feb 7, 2022 to Eduard Lebedyuk

you're right. I didn't properly say that with "array of" I actually meant the underlying STORAGEDEFUALT = "array" behaviour. So the combination that'll be newly supported in IRIS 2022.1 is STORAGEDEFAULT = "list" with SQLPROJECTION = "table" (and table/column)

0