SQL access method for properties that define list collections

InterSystems FAQ rubric

Properties defined in list collections (Property XXX As list of %String;) can be manipulated using SQL functions: $LISTBUILD(), $LISTFROMSTRING().

For each SQL function, please refer to the document page below.

InterSystems SQL Reference - $LISTBUILD() [IRIS]
IRIS SQL Reference - $LISTFROMSTRING() [IRIS]
Caché SQL Reference - $LISTBUILD()
Caché SQL Reference - $LISTFROMSTRING()

/// Class definition example
Class ISJ.ListOfData Extends %Persistent
{
    Property listdata As list Of %String;
}

An example of SQL statement execution is as follows. 

USER>do $system.SQL.Shell()
--- Display omitted ---
USER>>insert into ISJ.ListOfData (listdata) values($LISTBUILD('A','I','U'))
10. insert into ISJ.ListOfData (listdata) values($LISTBUILD('A','I','U'))
1 Row Affected
--- Display omitted ---
↓The following is an example using $LISTFROMTOSTRING() ↓
USER >>insert into ISJ.ListOfData (listdata) values($LISTFROMSTRING('KA,KI,KU'))
12. insert into ISJ.ListOfData (listdata) values($LISTFROMSTRING('KA,KI,KU') )
1 Row Affected
statement prepare time: 0.0180s, elapsed execute time: 0.0001s.
----------------------------------- ----------------------------------------
USER>>quit
USER>set obj=# #class(ISJ.ListOfData).%OpenId(1) // Check with object
USER>write obj.listdata.Count()
3
USER>write obj.listdata.GetAt(1)
A
USER>write obj.listdata.GetAt( 2)
USER
>write obj.listdata.GetAt(3) USER>
set
obj=##class(ISJ.ListOfData).%OpenId(2)
USER>for i=1:1:obj.listdata.Count() {write obj.listdata.GetAt(i),!} write

When accessed in ODBC format, it can be handled as comma-delimited data.

*The display format change method is the same as for DATE type and TIME type columns.

Please see related topics for details. 

USER>do $system.SQL.Shell()
-- Display omitted --
USER>>set selectmode = odbc // ←Switch to ODBC mode
selectmode = odbc
USER>>select * from ISJ.ListOfData
14. select * from ISJ .ListOfData
ID listdata
1 a,i,u2
ka,ki,ku2
Rows(s) Affected
statement prepare time: 0.0003s, elapsed execute time: 0.0006s.
---------------------------------------------------------------------------
USER>>update ISJ.ListOfData set listdata='Sa,shi,su' where ID=1
15. update ISJ.ListOfData set listdata='Sa,shi,su' where ID =1
1 Row Affected
statement prepare time: 0.0007s, elapsed execute time: 0.0003s.
------------------------------------------------------------------------
USER>>select * from ISJ.ListOfData
16. select * from ISJ.ListOfData
ID listdata
1 sa,shi,su2
ka,ki,
ku2 Rows(s) Affected
statement prepare time: 0.0003s, elapsed execute time: 0.0005s.
USER>>quit
------------------------------------------