New post
AllAccepted answers
Julius Kavay · Nov 24, 2020

After seeing several solutions I got the idea to make a comparison.
The bottom line is, it's advisable to check how an algorithm (or function or method etc.) performs over another.
So try the below program snippet... you will be surprised!

Test //
   s date="20201121090000"
   s new=""
   s t0=$zh

   f i=1:1:1E6 s new=$e(date,1,4)_"-"_$e(date,5,6)_"-"_$e(date,7,8)_" "_$e(date,9,10)_":"_$e(date,11,12)_":"_$e(date,13,14)
   s t1=$zh
   f i=1:1:1E6 s new=$tr("abcd-ef-gh ij:kl:mn","abcdefghijklmn",date)
   s t2=$zh
   f i=1:1:1E6 s new=$zd($zdh($e(date,1,8),8),3)_" "_$e(date,9,10)_":"_$e(date,11,12)_":"_$e(date,13,14)
   s t3=$zh
   f i=1:1:1E6 s new=$system.SQL.TOTIMESTAMP(date, "YYYYMMDDHHMISS") 
   s t4=$zh
   f i=1:1:1E6 &SQL(SELECT TO_TIMESTAMP(:date,'YYYYMMDDHHMISS') INTO :new)
   s t5=$zh

   w "$e() only",?12,t1-t0,!
   w "$tr()",?12,t2-t1,!
   w "$e()+$zd()",?12,t3-t2,!
   w "SQL/class",?12,t4-t3,!
   w "SQL/static",?12,t5-t4,!
   q

Of course, the results will depend on hardware,  on Cache/IRIS version and on utilisation of your system

Julius Kavay · Nov 15, 2020

Hello Kevin,

in most of the cases (but not always) the reason for this is the exhaustion of TCP ports, see

https://docs.microsoft.com/en-us/windows/client-management/troubleshoot-...

too. Grab a windows command prompt and start with

netsh int ipv4 show dynamicport tcp

this shows you how many ports you have.

netstat -ano | find "TCP"

shows you all the TCP ports in use (including the process numbers) and

netstat -an | find "TCP" | find "CLOSE"

shows you all the bad guys.

If this is your problem then the solution is:

- increase the number of ports (if it's possible)

- reduce the cases, where a new port is needed and immediate closing of unneeded ports

Julius Kavay · Nov 9, 2020

I'm not sure about what you mean with "unlimited amount of indexes"...

first, there is a limit of 255 for the parameters, this means you can have 

do ..setValue(key1,key2,...key254,value)

but you can't have 

 do ..setValue(key1,key2,...key254,key255,value)

second, if you meant to have many-many values (in sense of, we have to much data)

  do ..setValue(key1,value1)
  do ..setValue(key2,value2)
  // etc.

then, at some point you will reach the and of the available RAM (incl. swap/paging space.  Usually swap/paging space is much less then the space for the real data).

thirdly, if you meant variable number of indexes (in sense of variable number of parameters) then I can just refine Robert's solution to

ClassMethod setValue(params...) As %Status
{
   set base=$name(%session.Data)
   for i=1:1:params-1 { set base=$name(@base@(params(i)))
   set @base=params(params)
   quit 1
}

In case, you have both, variable number of indices and too much data then you could use a mix of the above with data placed in a temp file.

Somewhere, at the program start, let you point %sessionData to a temp file

   set %sessionData=$nam(^||SessionTemp)

and then consider this in the setValue method

ClassMethod setValue(params...) As %Status
{
   set bas=%sessionData
   ...
}

Of course, in your application, you have to change only those %session.Data(...) to  @%sessionData@(...)  which uses the "variable-big-data" part of %session.Data(). All other (the standard ones) stay unchanged.

Julius Kavay · Oct 21, 2020

I'm pretty sure, you intended to write

select COUNT (Arrival Time) FROM dbo.table where Arrival Time < DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

i.e. do not place propertynames under quote

By the way, I don't know, how many records you have in your table, but if you have thousands or millions of records, consider to compute constant things just one time!

DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

is a constant value, it's not neccessary to compute it for each record! Also, in this particular case, CONVERT() and LEFT() are also not needless, 1603173432000 is the value, and it is an integer.

Julius Kavay · Oct 19, 2020

As Robert said, XSLT... but as he indicated too, in this case XSLT would be an overkill.

But there is also a "forgotten" function, $locate() too for such cases! This function is almost never used in DC examples. Two or three lines of code, and you have the perfect solution... but for "simple" cases only. Beware of nested tags!

SetFixedValue(str,tag,value) Public
{
   set i=0, t="(?i)<"_tag_">[^<]+", s=$l(tag)+2
   while $locate(str,t,i,j,v) { set $e(str,j+s-$l(v),j-1)=value, i=j+s-$l(v)+$l(value) }
   quit str
}

It works like a charm...

set a="<Name>..."
write $$SetFixedValue^test(a,"rollno","***")  -->  <Name>ABC</Name><RollNo>***</RollNo><Name>XYZ</Name><RollNo>***</RollNo><Name>xyz</Name><RollNo>***</RollNo>
Julius Kavay · Oct 16, 2020

To say it simply, you did this:

set x=123
set x=456

and just  see 456 but not 123!

What you (probably) want to do is:

set x(1)=123
set x(2)=456

I added one more "patient item" to your JSON and a simple test class

Class DC.PR1 Extends %RegisteredObject
{ 
ClassMethod GetDataFromTestStream() [ PublicList = (myGuid, myId, myForename, mySurname, myDOB, nokName, nokRelation, nokTel, nokEmail) ]
{
   set xData=##class(%Dictionary.CompiledXData).%OpenId(..%ClassName(1)_"||Test")
   try { set obj={}.%FromJSON(xData.Data) }
   catch e { set obj="",err=e.Name }
   if 'obj { 
      write "Error: ",err,!
      quit ""
   }  

   // Put all properties in the corresponding SUBSCRIPTED variable
   //
   for i=0:1:obj.%Size()-1 { // loop over patients (top array)
      set patient=obj.%Get(i).patient

      set myGuid(i)=patient.guid
      set myId(i)=patient.id
      set myForename(i)=patient.forename
      set mySurname(i)=patient.surname
      set myDOB(i)=patient.dateOfBirth

      for j=0:1:patient.NOK.%Size()-1 { // loop over NOKs of the i-th patient
         set nok=patient.NOK.%Get(j)

         set nokName(i,j)=nok.NOKname
         set nokRelation(i,j)=nok.NOKrelationship
         set nokTel(i,j)=nok.telephone
         set nokEmail(i,j)=nok.email

         // instead of storing each property, you could
         // process them hier...
      }
   }
} 

XData Test [ MimeType = application/json ]
{
[
  {
    "patient":
      {
        "guid":"12345",
        "id":12345,
        "forename":"Joe",
        "surname":"Bloggs",
        "dateOfBirth":"2002-12-10T00:00:00Z",
        "NOK":[
                {
                   "NOKname":"Alison Bloggs",
                   "NOKrelationship":"Wife",
                   "telephone":"02081234567",
                   "email":"alison@bloggs.com"
                },
                {
                   "NOKname":"Peter Bloggs",
                   "NOKrelationship":"Father",
                   "telephone":"02081234567",
                   "email":"Peter@bloggs.com"
                }
              ]
      }
  },
  
  {
    "patient":
      {
        "guid":"212345",
        "id":212345,
        "forename":"John",
        "surname":"Blue",
        "dateOfBirth":"2003-12-10T00:00:00Z",
        "NOK":[
                {
                   "NOKname":"Aline Blue",
                   "NOKrelationship":"Wife",
                   "telephone":"032081234567",
                   "email":"aline@blue.com"
                },
                {
                   "NOKname":"Peter Blue",
                   "NOKrelationship":"Father",
                   "telephone":"032081234567",
                   "email":"Peter@blue.com"
                }
              ]
      }
  }

]
} 

}

Try it in a terminal session:

do ##class(DC.PR1).GetDataFromTestStream()

zwrite

The output  should be:

myDOB(0)="2002-12-10T00:00:00Z"
myDOB(1)="2003-12-10T00:00:00Z"
myForename(0)="Joe"
myForename(1)="John"
myGuid(0)=12345
myGuid(1)=212345
myId(0)=12345
myId(1)=212345
mySurname(0)="Bloggs"
mySurname(1)="Blue"
nokEmail(0,0)="alison@bloggs.com"
nokEmail(0,1)="Peter@bloggs.com"
nokEmail(1,0)="aline@blue.com"
nokEmail(1,1)="Peter@blue.com"
nokName(0,0)="Alison Bloggs"
nokName(0,1)="Peter Bloggs"
nokName(1,0)="Aline Blue"
nokName(1,1)="Peter Blue"
nokRelation(0,0)="Wife"
nokRelation(0,1)="Father"
nokRelation(1,0)="Wife"
nokRelation(1,1)="Father"
nokTel(0,0)="02081234567"
nokTel(0,1)="02081234567"
nokTel(1,0)="032081234567"
nokTel(1,1)="032081234567"

And don't forget, you may have more then one "patient" item in your JSON, so either consume the data in the inner loop oder place them in a two-dimensional array.

HTH