AllAccepted answers
Vitaliy Serdtsev · Nov 10, 2023

You can convert a list to a string (and vice versa), regardless of the number of nestings. Unfortunately, I can't test this code for Caché 5.x, but I think it should work.
Here is a small example of searching for a string in a list:

#include %systemInclude

 n
 
 s list=$lb(
   "test",
   "for",
   "searching unknown strings here is a very long piece with enough characters to get a lowercase alpha as a $list marker",
   "items",
   "in",
   "aaatArGetwaaaa",
   "lists",
     $lb(
     "/subs",
     "/values",
     "nested list",
     "did you see that ""w"" before the third piece?",
     "Stuart Strickland",
     "Yaron Munz")
 )
 
 s str=$$$UPPER(##class(%Utility).FormatString(list,,.overflow))
 i 'overflow {
   ;s @("LIST="_str) zw LIST
   
   w !,$f(str,$$$UPPER("Targetw"))
 }

Result: 162

Vitaliy Serdtsev · Aug 25, 2023

To your data storage scheme, I would like to clarify one point.
Let's say there is the following data:

Organization
ID
1
2
3
GetOrgUpdatesResponse
ID Organizations
1 2,3
2 1,2
3 1,3


If you delete ID=1 (GetOrgUpdatesResponse), then ID=2 and ID=3 (Organization) will be automatically deleted. But in this case, the rows ID=2 and ID=3 (GetOrgUpdatesResponse) will contain incorrect data and the referential integrity will be violated. Or am I wrong?

Vitaliy Serdtsev · Aug 22, 2023

I also noticed three things from your screenshots:

  1. instead of the standard program %SYS.Task.RunLegacyTask you are using a non-standard CTools.Task.Legacy. Is this how it should be?
  2. you have disabled "Reschedule task after system restart?". Is this how it should be?
  3. your task is scheduled for 4:00:00 under Task2, but judging by the history, it was launched at 11:52 and 11:56 under Administrator. Something doesn't add up here.
Vitaliy Serdtsev · Aug 22, 2023

The problem can be solved in two ways:

  1. use OPTIONS="popup,sortbox"
  2. make a correction to the %CSP.PageLookup
    Write "&nbsp;<a href=""javascript:searchSort("_..QuoteJS(value)_");"" title=""Sort Results by "_alias_""">"
|
V
Write "&nbsp;<a href=""javascript:searchSort("_i_");"" title=""Sort Results by "_alias_""">"

In both case, the query will take the form "ORDER BY <the ordinal number of the field>", instead of "ORDER BY <field name>"

Vitaliy Serdtsev · Aug 14, 2023

In this case, it will be easier to temporarily dump the data into globals. Then you can access this data at any time, including through %ScrollableResultSet.

Here is a small example:

ClassMethod tt() [ Language = tsql, ReturnResultsets, SqlName = mycls, SqlProc ]
{
  drop table if exists mytemp1,mytemp2
  select name,dob,spouse into mytemp1 from Sample.Person where name like 'A%'
  select name,age,home_city,home_state into mytemp2 from Sample.Person where home_state = 'MA'
}

ClassMethod Test()
{
  d ##class(%SQL.Statement).%ExecDirect(,"call dc.mycls()")
  
  d ##class(%SQL.Statement).%ExecDirect(,"select * from mytemp1").%Display()
  d ##class(%SQL.Statement).%ExecDirect(,"select * from mytemp2").%Display()

  ; and even so
  d ##class(%SQL.Statement).%ExecDirect(,"select * from mytemp1 cross join mytemp2").%Display()
}
Vitaliy Serdtsev · Aug 11, 2023

One of the possible options:

ClassMethod odbcTest() As %Integer [ ReturnResultsets, SqlName = PersonSets2, SqlProc ]
{
  #dim %sqlcontext As %ProcedureContext
  if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() } 

  s tReturn = 0

  s conn=##class(%SQLGatewayConnection).%New()
  s sc=conn.Connect("TEST Samples","_system","SYS") //datasource
  if $$$ISOK(sc) {
    d conn.AllocateStatement(.h1)
    d conn.Prepare(h1,"select name,dob,spouse from sample.person where name %STARTSWITH 'A'")
    d conn.Execute(h1)
    d %sqlcontext.AddResultSet(conn.getResultSet(h1))
    d conn.AllocateStatement(.h2)
    d conn.Prepare(h2,"select name,age,home_city,home_state from sample.person where home_state = 'MA'")
    d conn.Execute(h2)
    d %sqlcontext.AddResultSet(conn.getResultSet(h2))
    s tReturn = 1
  }else{
    s sqlcode=$system.Status.StatusToSQLCODE(sc,.msg)
    s %sqlcontext.%SQLCODE = sqlcode, %sqlcontext.%Message = msg
  }
  q tReturn
}

Output:

SAMPLES>d ##class(%SQL.Statement).%ExecDirect(,"call Sample.PersonSets2()").%Display()
...

Surely there is a way to make it even easier.