· Jan 31, 2020

How to unpack a property that is a List of Objects from a result Set

I am trying to get a list of all settings for all the config items of a given production using SQL . When I run the following sql as a dynamic query I am unable to access the setting names and values. Settings is a list of Ens.Config.Setting

Set query="Select Name, Production, Settings,$ListLength(Settings) "
         _"As Count from Ens_Config.Item "

Set rc=##class(%ResultSet).%New()

Set sc=rc.Prepare(query)

Set sc=rc.%Execute

While rc.Next(.sc) {

For i=1:1:Count {

  Set tSetting=$List(Setting,i)

  Set name=tSetting.Name


I get a LIST error on the last line! Is it possible to return objects in resultsets?

Discussion (4)3
Log in or sign up to continue

Hi Stella,

I dubt the code that returns "a LIST error" is the code you posted here.
There seems to be a few problems with the code posted, including:
- I don't think the query will compile with " as Count" (Count is a reserved word in SQL)
- You should get an <UNDEFINED> error when referencing the Count variable in the for loop

Please note that Settings in Ens.Config.Item is a "list Of Ens.Config.Setting", that's an instance of %Library.ListOfObjects, not a $list.

In general, when I code, today I try to avoid %ResultSet, that I consider deprecated, in favor of %SQL.Statement. But that's, maybe, my opinion.

As to a possible solution, I'm not sure the full list can be obtained with a single SQL query because the result consists of a "main" collection of all productions hosts and, for each host, a collection of settings.
Lists in Object Scrtipt Classes are not projected to SQL by default (SQLPROJECTION and/or STORAGEDEFAULT may change that default).

So, to get the list of all Settings, two loops are necessary, one for all hosts, another for all settings within the host, something like:

ClassMethod SettingsQuery()

    Set sc=$$$OK
    Try {
        Set sqlQuery="Select Production, Name, Settings from Ens_Config.Item "
        Set stSql=##class(%SQL.Statement).%New()
        Set stSql.%ObjectSelectMode=1
        Set rsSql=##class(%SQL.Statement).%ExecDirect(.stSql, sqlQuery)
        If rsSql.%SQLCODE < 0 {
            Set sc = $$$ERROR($$$GeneralError,"%SQLCODE="_rsSql.%SQLCODE_", %Message="_rsSql.%Message)
        While rsSql.%Next(.sc) {
            If $$$ISERR(sc) Quit
            Write rsSql.Production.Name," -> ",rsSql.Name," has ",rsSql.Settings.Count()," settings:",!
            For SettingCounter=1:1:rsSql.Settings.Count() {
                Write ?10,rsSql.Settings.GetAt(SettingCounter).Name," = ",rsSql.Settings.GetAt(SettingCounter).Value,!
            Write !
    } Catch CatchError {
        #dim CatchError as %Exception.SystemException
        Set sc=CatchError.AsStatus()
    Quit sc

Now, you got what you asked....but, I'm not sure you got what you want/need. ;)
There are several issues/pitfalls.
There are Business Host settings and Adapter settings, there are settings with default values....and more...

I REALLY wish ISC provided an interface to get a list of all settings, along with "actual" values (as used by the production, wherever the value comes from....) of each/all Business hosts within a production.
But...I keep wishing, any other has the same wish? :)


P.S.: Please note that the code posted works in IRIS but does not work with Ensemble/HS due to a bug on how %ListOf* are handled when %ObjectSelectMode=1

P.P.S: how can I paste properly insented and "colored" code directly from Studio?

It depends what you're trying to do. Lists are exposed through SQL as comma delimited strings (default). You can change how lists are projected to mimic how arrays are projected which is as a "child" table (default). Keep in mind, this all depends on how Settings is defined/stored. e.g. if it's a separate table with its own ID, then the settings list on the config.item table will only contain the IDs and that's all your getting.