All
Accepted answers
Vitaliy Serdtsev · Sep 28, 2021 go to post
and second, I would like to read the doc everywhere! For example, I have a 10 hour flight, and want to work. And in case, a server only has a local LAN access, then you have no docu!).
I fully support it. But I'm afraid now, apart from reading the documentation in PDF format, you can forget about local documentation ("DOCBOOK" database) with normal search and navigation. It's a pity..
Vitaliy Serdtsev · Sep 27, 2021 go to post

No. I couldn't find how to do this in the documentation (probably I searched badly), so I don't publish here undocumented features anymore. But you can easily determine this from the source code.

Vitaliy Serdtsev · Sep 27, 2021 go to post

Yes. By default, a strictly defined list of packages/classes is displayed in IRIS for security purposes, but you can change this behavior locally.

Vitaliy Serdtsev · Sep 27, 2021 go to post

Or so:

<FONT COLOR="#0000ff">set </FONT><FONT COLOR="#800000">tmp </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%IO.StringStream</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
</FONT><FONT COLOR="#0000ff">do </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Write</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"This is my text"</FONT><FONT COLOR="#000000">)

</FONT><FONT COLOR="#0000ff">do </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Seek</FONT><FONT COLOR="#000000">(11)
</FONT><FONT COLOR="#0000ff">do </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Write</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">" NEW"</FONT><FONT COLOR="#000000">)
</FONT><FONT COLOR="#0000ff">do </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Rewind</FONT><FONT COLOR="#000000">()
</FONT><FONT COLOR="#0000ff">write </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Read</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Size</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#008000">;This is my NEW text</FONT>
Vitaliy Serdtsev · Sep 23, 2021 go to post

See %SQL.StatementResult:%DisplayFormatted()

Simple sample (for namespace "SAMPLES"):

<FONT COLOR="#0000ff">set </FONT><FONT COLOR="#800000">st </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">(2,</FONT><FONT COLOR="#008000">"Sample"</FONT><FONT COLOR="#000000">)
</FONT><FONT COLOR="#0000ff">set </FONT><FONT COLOR="#800000">sql </FONT><FONT COLOR="#000000">= 3
</FONT><FONT COLOR="#0000ff">set </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">(1) = </FONT><FONT COLOR="#008000">"select TOP 5 %ID as id, Name, DOB, Home_State"
</FONT><FONT COLOR="#0000ff">set </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">(2) = </FONT><FONT COLOR="#008000">"from Person where Age > 40"
</FONT><FONT COLOR="#0000ff">set </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">(3) = </FONT><FONT COLOR="#008000">"order by 2"
</FONT><FONT COLOR="#0000ff">do </FONT><FONT COLOR="#800000">st</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Prepare</FONT><FONT COLOR="#000000">(.</FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">)
</FONT><FONT COLOR="#0000ff">for </FONT><FONT COLOR="#800000">type</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"txt"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"pdf"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"csv"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"html"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"xml" </FONT><FONT COLOR="#800080">{
  </FONT><FONT COLOR="#0000ff">set </FONT><FONT COLOR="#800000">rs </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800000">st</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Execute</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">do </FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%DisplayFormatted</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">type</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"C:\Temp\report"</FONT><FONT COLOR="#000000">)
</FONT><FONT COLOR="#800080">}</FONT>

As a result, the following files are generated:

report.csv
report.html
report.pdf
report.txt
report.xml
Vitaliy Serdtsev · Sep 17, 2021 go to post

Instead of

set str=$e(str,1,*-1)

, will be a little faster

set $e(str,*)=""

But this is already saving on matches.

PS: for the sake of interest, I checked on a special version of Caché (very old) with server-side JavaScript (not to be confused with Node.js)

 

Source code

Include %occInclude

Class dc.test [ Abstract ]
{

ClassMethod MakeStringOnJS(N) [ Language = javascript]
{
  var myval="a", arr=[], stop, str, start=(new Date()).getTime();
  
  for (var i=0;i<N;i++) arr[i]=myval;
  
  str=arr.join();
  
  stop=(new Date()).getTime();
  
  Cache.Console.WriteLine("[JavaScript] execution: ",stop-start,"(ms) len(str): ",str.length);
  
  // Force a Garbage Collection
  Cache.Class.$JavaScript_Runtime.$GarbageCollect();
  
}

ClassMethod MakeStringOnCOS(N)
{
  s myval="a",
    str="",
    
    time=$zh
  
  f i=1:1:N s str=str_myval_","
  
  s $e(str,$l(str))="" ; not supported $e(str,*)

  w $$$FormatText("[COS] execution: %1(ms) len(str): %2",$zh-time*1000,$l(str)),!
}

/// d ##class(dc.test).Test()
ClassMethod Test(N = 10)
{
  d ..MakeStringOnJS(N),
    ..MakeStringOnCOS(N)
}

}
USER>d ##class(dc.test).Test(1800000)
[JavaScript] execution: 99(ms) len(str): 3599999
[COS] execution: 126.683(ms) len(str): 3599999

By the way, the JS code has almost no limit on the string size.

Vitaliy Serdtsev · Sep 17, 2021 go to post
 

An example without complex formulas and very fast

Include %DeepSee

Class dc.test [ Abstract ]
{

ClassMethod FirstLastDayMonth(
  year = 2015,
  month = 2)
{
  s mm=$$$iscPadZero(month,2),
  
    firstDay=$zdh(year_mm_"01",8),
    lastDay=$zdh(year_mm_$$$iscDaysInMonth(year,month),8),
  
    firstDayName=$zd(firstDay,12,,,,,,,,1),
    lastDayName=$zd(lastDay,12,,,,,,,,1)
    
  w $$$FormatText("firstDay = %1 (%2), lastDay = %3 (%4)",$zd(firstDay),firstDayName,$zd(lastDay),lastDayName),!
}

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  d ..FirstLastDayMonth(2015,2),
    ..FirstLastDayMonth(2021,9)
}

}

USER>d ##class(dc.test).Test()

firstDay = 01.02.2015 (Sunday), lastDay = 28.02.2015 (Saturday)

firstDay = 01.09.2021 (Wednesday), lastDay = 30.09.2021 (Thursday)

Vitaliy Serdtsev · Sep 15, 2021 go to post

Simple sample:

<FONT COLOR="#000080">Class dc.test Extends %ZEN.Component.page
</FONT><FONT COLOR="#000000">{

</FONT><FONT COLOR="#000080">XData </FONT><FONT COLOR="#000000">Contents [ </FONT><FONT COLOR="#000080">XMLNamespace </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"http://www.intersystems.com/zen" </FONT><FONT COLOR="#000000">]
{
<</FONT><FONT COLOR="#000080">page </FONT><FONT COLOR="#800000">xmlns</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"http://www.intersystems.com/zen"</FONT><FONT COLOR="#000000">>
  <</FONT><FONT COLOR="#000080">tablePane
    </FONT><FONT COLOR="#800000">id</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"tp"
    </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"<FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">1 </FONT><FONT COLOR="#008000">ID</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'Western branch' </FONT><FONT COLOR="#008000">Branch</FONT><FONT COLOR="#000000">,{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2021-03-15'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#008000">&quot;Date&quot;</FONT><FONT COLOR="#000000">,35 </FONT><FONT COLOR="#008000">Suma
    </FONT><FONT COLOR="#000080">union
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">2,</FONT><FONT COLOR="#008080">'Eastern branch'</FONT><FONT COLOR="#000000">,{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2020-12-11'</FONT><FONT COLOR="#000000">},37</FONT>"
  </FONT><FONT COLOR="#000000">/>
  <</FONT><FONT COLOR="#000080">button </FONT><FONT COLOR="#800000">caption</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Row unselect" </FONT><FONT COLOR="#800000">onclick</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"zenPage.rowUnselect()"</FONT><FONT COLOR="#000000">/>
</</FONT><FONT COLOR="#000080">page</FONT><FONT COLOR="#000000">>
}
</FONT><FONT COLOR="#000080">ClientMethod </FONT><FONT COLOR="#000000">rowUnselect() [ </FONT><FONT COLOR="#000080">Language </FONT><FONT COLOR="#000000">= javascript ]
{
  </FONT><FONT COLOR="#008080">var </FONT><FONT COLOR="#000000">tp</FONT><FONT COLOR="#000080">=</FONT><FONT COLOR="#000000">zen(</FONT><FONT COLOR="#800000">'tp'</FONT><FONT COLOR="#000000">);
  row</FONT><FONT COLOR="#000080">=</FONT><FONT COLOR="#000000">tp.selectedIndex;
  </FONT><FONT COLOR="#008000">if </FONT><FONT COLOR="#000000">(tp.rowSelect </FONT><FONT COLOR="#000080">&& </FONT><FONT COLOR="#000000">row</FONT><FONT COLOR="#000080">>=0</FONT><FONT COLOR="#000000">) {
    </FONT><FONT COLOR="#008080">var </FONT><FONT COLOR="#000000">old</FONT><FONT COLOR="#000080">=</FONT><FONT COLOR="#000000">tp.enableToggleSelect;
    tp.enableToggleSelect</FONT><FONT COLOR="#000080">=</FONT><FONT COLOR="#000000">true;
    tp.selectRow(row);
    tp.enableToggleSelect</FONT><FONT COLOR="#000080">=</FONT><FONT COLOR="#000000">old;
  }
}
}</FONT>
Vitaliy Serdtsev · Sep 15, 2021 go to post

I also will insert my five kopecks.

  • The %Library package also includes stream classes, but those are deprecated. The class library includes additional stream classes, but those are not intended for general use.
Working with Streams

    I have %Stream.FileCharacter was an order of magnitude faster than %[Library.]File

  • If you rewrite the line-by-line reading to read blocks with further parsing of lines, the speed will more increase by an order of magnitude.
     

    Sample

    Class dc.test [ Abstract ]
{

ClassMethod ReadCSVStream(fCSV As %String) As %String
{
  s stream = ##class(%Stream.FileCharacter).%New()
  d stream.LinkToFile(fCSV)

  s time1=$zh
  While 'stream.AtEnd {
    s line=stream.ReadLine($$$MaxLocalLength)
  }
  s diff=$zh-time1
  
  q diff
}

ClassMethod ReadCSVStreamBlock(fCSV As %String) As %String
{
  s stream = ##class(%Stream.FileCharacter).%New()
  d stream.LinkToFile(fCSV)

  s i=0,time1=$zh
  While 'stream.AtEnd {
    s chunks($i(i))=stream.Read($$$MaxLocalLength)
    // do parsing chunk to lines
  }
  s diff=$zh-time1
  
  q diff
}

ClassMethod ReadCSVOURC(fCSV As %String) As %String
{
  o fCSV::1 
  e  q "Missing File"

  s eof=$zu(68,40,1)
  u fCSV

  s time1=$zh 
  f  {
    r line
    q:$zeof
    // do something with line

  }
  s diff=$zh-time1

  c fCSV
  d $zu(68,40,eof)

  q diff
}

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  s ptr=0,
    clnm=$classname()

  &sql(select %dlist(Name) into :list
       from %Dictionary.CompiledMethod
       where Parent=:clnm and Name %startswith 'ReadCSV'
       group by Parent
       order by SequenceNumber)
  
  while $listnext(list,ptr,m) {
    w !,"[",m,"]",?20,"execution: ",$classmethod(clnm,m,"data.csv"),!
  }
}

}
  • On the Internet, you can find a lot of materials about comparing the speed of reading files (in particular CSV) for different programming languages (Python, C/C++, R, C#, Java, etc.), for example (this is machine translation). Often, those who make such comparisons do not always know all these languages equally well, so sometimes casus happen.
     

    Who do you think in the article above was faster when reading 1e7+ lines: Fortran or C++ ?

    Fortran :)
  • If we approach the issue formally, then the advantage will be given to compiled languages, not interpreted ones, as well as the implementation that uses all the capabilities of the operating system and hardware.
Vitaliy Serdtsev · Aug 20, 2021 go to post

Why not use numeric codes?

$ascii("á") = 225set s1=$zconvert("Fl&aacute;vio","I","HTML"),
    s2=$zconvert("Fl&#225;vio","I","HTML")
   
write s1,$select(s1=s2:" = ",1:" <> "),s2
Vitaliy Serdtsev · Apr 30, 2021 go to post

How do you count "Count"? Why is "Northwest" 1 instead of 2 for 2021?

For now so:
select v.Branch, nvl(sum(c %FOREACH(v.Branch)),0) "Count" from 
  (
  select 'Northwest' Branch,$listbuild('Northern','Western') Branches union
  select 'Oriental',$listbuild('Eastern') union
  select 'Southern',$listbuild('Southern')
  ) v
left join
  (select replace(%exact(Branch),' branch','') Branch,count(* %FOREACH(Branch)) c from yourtable where year("Date")=2021 group by Branch) m
on m.Branch %inlist v.Branches
group by v.Branch
Vitaliy Serdtsev · Apr 29, 2021 go to post

You are implicitly using %Library.SqlQuery:Func() method, in which, as @Robert Cemper rightly pointed out, $get() is used.

You can do it differently:

Query GetInfo(pObject AS Kurro.MyClass) As %SQLQuery(CONTAINID = 1, ROWSPEC = "IdList:%String,IdProcess:%String,Duration:%String") [ SqlProc ]
{
    SELECT IdList, IdProcess, Duration
    FROM Kurro.MyClass
    WHERE KeyProcess = :pObject.KeyProcess
    AND CodeSpecialist = :pObject.CodeSpecialist
    AND CodeProvider = :pObject.CodeProvider
    AND CodeCenter = :pObject.CodeCenter
    AND "Date" = :pObject.Date
}set obj=##class(Kurro.MyClass).%New()
set obj.KeyProcess="1033004-1#"
set obj.CodeSpecialist = "surgery"
set obj.CodeProvider = "PR002"
set obj.CodeCenter = "CENTER-01"
set obj.Date = $ZDATETIME($ZDATETIMEH("2021-04-30 15:45:00",3,1),3,1)

set st=##class(%SQL.Statement).%New()
set sc=st.%PrepareClassQuery("Kurro.MyClass","GetInfo")
  if $$$ISERR(sc) {write "%PrepareClassQuery failed:" do $System.Status.DisplayError(sc) quit}
set result=st.%Execute(obj)
do result.%Display()
Vitaliy Serdtsev · Apr 29, 2021 go to post

I don't quite understand what you want to get.

If in the forehead, then so:

Select v.valueId, m.name 
 From (Select 1 valueId Union Select 2 Union Select 3 Union Select 4 Union Select 5) v
     left Join otherTable m
        on m.id = v.valueId

But you can achieve the same thing more easily through IN or %INLIST.

Vitaliy Serdtsev · Apr 16, 2021 go to post

You can easily do this by replacing two values in the registry. Just checked on Caché & IRIS: SMP and Terminal (<FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SYS.System</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">GetInstanceName</FONT><FONT COLOR="#000000">()</FONT>) displays the new instance name. Upgrade/Deinstall work fine too.

If desired, you can also replace the service names (private Apache, etc.).