All
Accepted answers
Vitaliy Serdtsev · Jul 19, 2018 go to post

So, in order.

  1. There is no limit to perfection.

    Better then  d:(v=2)!(v=8)!(v=9) t3case289 q
  2. There are one error in the Test2 method:
      s:'$d(addr) addr(1)=1, (addr(2),addr(8),addr(9))=289, add(3)=3
  3. There are error in the Test3 method:
      d:v=1 t3case1 Q ;will work in any way
  d:v=2 t3case289 Q  d:v=8 t3case289 Q  d:v=9 t3case289 Q 
  d:v=3 t3case3 Q 
  d t3case Q 
  4. Speed will depend on many factors: RAM size, number of conditions (the more of them, the slower the lasts of them will be executed and more RAM is required).

Vitaliy Serdtsev · Jul 19, 2018 go to post

So, in order.

- The third article mentions a few options of which some are not available in Cache, such as LIMIT and OFFSET.

Caché has analogues - TOP N and %vid, which with more than replace the LIMIT/OFFSET. In the second link this is discussed in detail.

- And the 4th talks about using LIMIT and OFFSET which are, again, not available in Cache.

The essence of the article is to replace query

SELECT user_id, external_id, name, metadata, date_created
FROM users
ORDER BY user_id ASC
LIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size

10 000 rows in set (40.81 sec)

to

SELECT user_id, external_id, name, metadata, date_created
FROM users
WHERE user_id > 51 234 123 --- value of user_id for 50 000 000th record
ORDER BY user_id ASC
LIMIT 10 000

10 000 rows in set (0.03 sec)

Eventhough I am only asking for the first 100, there is still a major performance hit when executing the first rset.%Next() due to, what I assume is, the code trying to find the 100 records I am requesting out of the 1 mil records.

In this case, the following query is sufficient:

SELECT TOP 100 prop FROM table WHERE prop=?

-The second suggestion is what I am doing above. Using %VID with a subquery is just too slow when dealing with large datasets.

Is there an index for "prop"?

You tuned the table?

I have all works quickly:

Class dc.test Extends %Persistent
{

Index iprop On prop;

Property prop As %String;

ClassMethod Fill(
  Nrow = {1e6},
  Npage = {1e3})
{
  d DISABLE^%NOJRN
    ,..%KillExtent()
  
  s time=$zh
    ,^dc.testD=Nrow
  
  f id=1:1:Nrow {
    s p=id-1\Npage+1
      ,v=id-1#Npage+1
      ,val=p_":"_v
      ,^dc.testD(id)=$lb("",val)
      ,^dc.testI("iprop",$$$SQLUPPER(val),id)=""
  }
  
  w "(Fill) time = ",$zh-time," s.",!!
  
  zw:Nrow<=50 ^dc.testD,^dc.testI
  
  d ENABLE^%NOJRN
    ,$system.SQL.TuneTable($classname(),$$$YES)
    ,$system.OBJ.Compile($classname(),"cu-d")
}

ClassMethod Query(
  q As %TinyInt = 1,
  prop As %String,
  rownum1 As %Integer,
  rownum2 As %Integer)
{
  s sql(1)="select *,%vid from (select %ID,prop from dc.test where prop %startswith ?) where %vid between ? and ?"
    ,sql(2)="select *,%vid from (select %ID,prop from %ignoreindex iprop dc.test where prop like ?) where %vid between ? and ?"
    ,sql(3)="select *,%vid from (select top ? %ID,prop from %ignoreindex iprop dc.test where prop like ? order by %ID desc) order by %vid desc"

    ,time=$zh
    ,rs=$s(q=3:##class(%SQL.Statement).%ExecDirect(,sql(q),rownum1,prop),
             1:##class(%SQL.Statement).%ExecDirect(,sql(q),prop,rownum1,rownum2))
    
  i 'rs.%SQLCODE {
    while rs.%Next() {
      /*
      s id=rs.%Get("ID")
        ,prop=rs.%Get("prop")
      w id," ",prop,!
      */
      d rs.%Print()
    }
  }
  w "(",$lts($lb(q,prop,rownum1,rownum2)),") time = ",$zh-time," s.",!!
}

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  d ..Fill()
    ,..Query(1,"1000:",111,111+16)
    ,..Query(2,"%12%",111,111+16)
    ,..Query(1,"1",111984,111984+16)

    ,..Query(2,"%12%",39584,39584+16) ;# slow (last 17)
    ,..Query(3,"%12%",17,"")          ;# fast (last 17)
}
}

Result:

USER>d ##class(dc.test).Test()
(Fill) time = 1.277645 s.
 
999111 1000:111 111
999112 1000:112 112
999113 1000:113 113
999114 1000:114 114
999115 1000:115 115
999116 1000:116 116
999117 1000:117 117
999118 1000:118 118
999119 1000:119 119
999120 1000:120 120
999121 1000:121 121
999122 1000:122 122
999123 1000:123 123
999124 1000:124 124
999125 1000:125 125
999126 1000:126 126
999127 1000:127 127
(1,1000:,111,127) time = .084489 s.
 
5128 6:128 111
5129 6:129 112
5212 6:212 113
5312 6:312 114
5412 6:412 115
5512 6:512 116
5612 6:612 117
5712 6:712 118
5812 6:812 119
5912 6:912 120
6012 7:12 121
6112 7:112 122
6120 7:120 123
6121 7:121 124
6122 7:122 125
6123 7:123 126
6124 7:124 127
(2,%12%,111,127) time = .091251 s.
 
999984 1000:984 111984
999985 1000:985 111985
999986 1000:986 111986
999987 1000:987 111987
999988 1000:988 111988
999989 1000:989 111989
999990 1000:990 111990
999991 1000:991 111991
999992 1000:992 111992
999993 1000:993 111993
999994 1000:994 111994
999995 1000:995 111995
999996 1000:996 111996
999997 1000:997 111997
999998 1000:998 111998
999999 1000:999 111999
1000000 1000:1000 112000
(1,1,111984,112000) time = .66504 s.
 
999121 1000:121 39584
999122 1000:122 39585
999123 1000:123 39586
999124 1000:124 39587
999125 1000:125 39588
999126 1000:126 39589
999127 1000:127 39590
999128 1000:128 39591
999129 1000:129 39592
999212 1000:212 39593
999312 1000:312 39594
999412 1000:412 39595
999512 1000:512 39596
999612 1000:612 39597
999712 1000:712 39598
999812 1000:812 39599
999912 1000:912 39600
(2,%12%,39584,39600) time = 1.946264 s.
 
999121 1000:121 17
999122 1000:122 16
999123 1000:123 15
999124 1000:124 14
999125 1000:125 13
999126 1000:126 12
999127 1000:127 11
999128 1000:128 10
999129 1000:129 9
999212 1000:212 8
999312 1000:312 7
999412 1000:412 6
999512 1000:512 5
999612 1000:612 4
999712 1000:712 3
999812 1000:812 2
999912 1000:912 1
(3,%12%,17,) time = .089032 s.
Vitaliy Serdtsev · Jul 17, 2018 go to post

Use %MVR

For example:

Class dc.test Extends %Persistent
{

Index ibar On bar;

Property bar As %String(COLLATION = "MVR");

ClassMethod Test()
{
  d ..%KillExtent()
  &sql(insert into dc.test(bar)
  select '00123AB' union
  select '12345AB')

  d $system.SQL.TuneTable($classname(),$$$YES), $system.OBJ.Compile($classname(),"cu-d")
  f args="00123AB","123AB" d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.test where bar=?",args).%Display() w !!
}

}

Result:

USER>d ##class(dc.test).Test()
ID      bar
1       00123AB
 
1 Rows(s) Affected
 
ID      bar
1       00123AB
 
1 Rows(s) Affected
Vitaliy Serdtsev · Jul 17, 2018 go to post

test.mac:

#include %systemInclude

  #define getServState(%serviceName) ##class(%Activate.GenericObject).GetObject("winmgmts:\\.\root\cimv2").InvokeMethod1("Get",$$$FormatText("Win32_Service.Name=%1",$$$quote(%serviceName))).GetProperty("State")

  f s="wudfsvc","WSearch" w s,": ",$$$getServState(s),!

Result:

USER>d ^test
wudfsvc: Stopped
WSearch: Running
Vitaliy Serdtsev · Jul 16, 2018 go to post

Components Installed by Setup Type: IRIS, Caché

Unfortunately, Perl/Python Binding supported only on the x86-32 bit platform, but IRIS is only supported for x86-64.

Vitaliy Serdtsev · Jul 6, 2018 go to post

Contents of a Script File:

Script files are line oriented; there is no line-continuation convention. Each line is separate from any other. Lines beginning with a semicolon are considered comments. You can use blank lines liberally to improve readability. Normally, invalid lines are ignored. Script commands may be preceded by spaces and/or tabs.

Vitaliy Serdtsev · Jul 6, 2018 go to post

Example of converting a CSV to JSON file and then reading from a JSON file without creating intermediate classes/tables:

  ; CSV -> JSON

  s rowtype = "name VARCHAR(50),year INTEGER,amount NUMERIC(9,2),date DATE",
    fileIn = "C:\Temp\import.csv",
    fileOut = "C:\Temp\export.json",
    fileExp=##class(%Stream.FileBinary).%New(),
    obj=##class(%Document.Object).%New()

  d obj.loadResultSet(##class(%SQL.Statement).%ExecDirect(,"call %SQL_Util.CSV(,?,?)",.rowtype,.fileIn),"res"),
    fileExp.LinkToFile(fileOut),
    obj.%ToJSONStream(fileExp),
    fileExp.%Save()

  ; Read JSON

  s fileImp=##class(%Stream.FileBinary).%New()
  d fileImp.LinkToFile(fileOut)
  s obj = ##class(%DynamicAbstractObject).%FromJSON(fileImp)
  d obj.%ToJSON()
  w !,"obj.res.%Size() = ",obj.res.%Size()
    ,!,obj.res."0"."""date"""

Result:

C:\Temp\import.csv:
car,2000,100.51,27.10.2016,
phone,2003,65.8,15.01.2017,

USER>d ^test
{"res":[{"\"date\"":"27.10.2016","amount":100.51,"name":"car","year":2000},{"\"date\"":"15.01.2017","amount":65.8,"name":"phone","year":2003}]}
obj.res.%Size() = 2
27.10.2016
Vitaliy Serdtsev · Jun 26, 2018 go to post 

TEST1(STATUS=1)
 N MSG
 X ("(IN,OUT) S OUT=$S(IN:""HELLO"", 1:""GOODBYE"")",STATUS,.MSG)
 
 W !,MSG
 Q
 
TEST2(STATUS=1) PUBLIC {
 X ("(IN,OUT) S OUT=$S(IN:""HELLO"", 1:""GOODBYE"")",STATUS,.MSG)


 W !,MSG
}

Vitaliy Serdtsev · Jun 25, 2018 go to post

Try the following example of uploading multiple files at once, showing the progress for each file you upload (without form, submit, iframe, jQuery, flash, java, reloading/redrawing the page): 

<!DOCTYPE html>
  <html>
  <head>
    <title>Upload multiple files using XMLHttpRequest</title>


    <style type="text/css">
    .ok {
      color:green;
    }


    #dropZone {
      width: 360px;
      height: 125px;
      border: dashed 2px #ccc;
      background-color: #fefefe;
      color: #ccc;
      text-align: center;
      padding: 125px 0 0 0;
    }
  </style>
    
  </head>
<body onload="onloadHandler()">
<div id="dropZone">Drag and drop files here or use the button below</div>
<div>
  Select files
  <input type="file" id="fileToUpload" onchange="fileSelected(document.getElementById('fileToUpload').files)" multiple="multiple" />
</div>
<button type="button" title="Clearing the queue" onclick="clearList();">Clear</button>
<button type="button" title="Upload files to the server" onclick="upFile();">Upload</button>
<div id="holder"></div>
<script language="javascript">
function clearList()
{
  fileQueue=[];
  document.getElementById('holder').innerHTML='';
  document.getElementById('fileToUpload').value='';
}


function fileSelected(files){
  var holder = document.getElementById('holder');
  for (var i = 0; i < files.length; i++) {
  
    var file = files[i];


    var fileSize = 0;
    if (file.size > 1024  1024)
      fileSize = (Math.round(file.size  100 / (1024  1024)) / 100).toString() + 'Mbyte';
    else
      fileSize = (Math.round(file.size  100 / 1024) / 100).toString() + 'Kbyte';
  
    var divInfo = document.createElement('div');
    divInfo.innerHTML=file.name+' ('+file.type+') - '+fileSize;
    holder.appendChild(divInfo);


    var divProgN = document.createElement('div');
    divProgN.id='progressNumber'+i;
    divProgN.innerHTML='%';
    holder.appendChild(divProgN);


    var prog = document.createElement('progress');
    prog.id='progressValue'+i;
    prog.max='100.0';
    prog.value='0';
    holder.appendChild(prog);


    fileQueue.push({i:i,file:file});
  }
}


function upFile()
{
  while (fileQueue.length > 0) {
    var item=fileQueue.pop();
    uploadFile(item.file,item.i);
  }
}


function onloadHandler() {
  if (typeof FileReader == "undefined") alert('Sorry, your browser does not support File API, so this demo will not work correctly');
  
  fileQueue = new Array();


  uploadFile = function (file, i) {
    var xhr = new XMLHttpRequest(), upload = xhr.upload, fd = new FormData();
    
    fd.append('fUpload', file);


    upload.addEventListener('progress',
    function (evt) {
      if (evt.lengthComputable) {
        var percentComplete = Math.round(evt.loaded * 100 / evt.total);


        document.getElementById('progressNumber'+i).innerHTML = percentComplete.toString() + '%';
        document.getElementById('progressValue'+i).value = percentComplete;
        
      }
      else {
        document.getElementById('progressNumber'+i).innerHTML = 'Cannot calculate';
      }
      
    }, false);
    upload.addEventListener('load', function (ev) {
      var c=document.getElementById('progressNumber'+i);
      c.className='ok';
      c.innerHTML='OK';
    }, false);
    upload.addEventListener('error', function (ev) {alert('An error occurred while trying to upload the file.');}, false);
    upload.addEventListener('abort', function (ev) {alert('The upload was cancelled by the user or the browser reset the connection.');}, false);
    xhr.open('POST',window.location.href);
    xhr.setRequestHeader('Cache-Control', 'no-cache');
    xhr.setRequestHeader('X-Requested-With', 'XMLHttpRequest');
    xhr.send(fd);
  }
  
  dropZone=document.getElementById('dropZone');
  dropZone.addEventListener('dragenter',  function(ev){
    ev.stopPropagation();
    ev.preventDefault();
  }, false);
  dropZone.addEventListener('dragleave',  function(ev){
    ev.stopPropagation();
    ev.preventDefault();
    this.style['backgroundColor'] = '#FEFEFE';
    this.style['borderColor'] = '#CCC';
    this.style['color'] = '#CCC';
  }, false);
  dropZone.addEventListener('dragover',  function(ev){
    ev.stopPropagation();
    ev.preventDefault();
    this.style['backgroundColor'] = '#F0FCF0';
    this.style['borderColor'] = '#3DD13F';
    this.style['color'] = '#3DD13F';
  }, false);
  dropZone.addEventListener('drop',  function(ev){
  ev.stopPropagation();
  ev.preventDefault();
  this.style['backgroundColor'] = '#FEFEFE';
  this.style['borderColor'] = '#CCC';
  this.style['color'] = '#CCC';
  fileSelected(ev.dataTransfer.files);
  }, false);
}
</script>
<script language="Cache" method="OnPreHTTP" arguments="" returntype="%Boolean">
  #dim stream As %CSP.BinaryStream=%request.GetMimeData("fUpload")
  i $IsObject(stream) {
    
    // make with the resulting file useful work
    
    ;s ^tmp($i(^tmp),"filename")=stream.FileName
    ;s ^tmp($i(^tmp),"filesize")=stream.Size
    
    q $$$NO
  }
  q $$$YES
</script>
</body>
</html>

Vitaliy Serdtsev · Jun 22, 2018 go to post

Use %UTC instead of %TimeStamp

In this case, all conversions will be performed automatically.

Simple example:

Class dc.test Extends %Persistent [ Final ]
{

Property CreationTime As %TimeStamp [ InitialExpression = {$ZDateTime($ZTimeStamp, 3, 1, 2)} ];

Property utc As %UTC [ InitialExpression = {##class(%UTC).NowUTC()} ];

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  d ..%KillExtent()
  
  &sql(insert into dc.test default values)
  zw ^dc.testD
  
  w !
  
  ; %SelectMode = DISPLAY
  d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(2),"select * from dc.test").%Display($c(9,9)) w !

  ; %SelectMode = ODBC
  d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(1),"select * from dc.test").%Display($c(9,9)) w !

  ; %SelectMode = LOGICAL
  d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(0),"select * from dc.test").%Display($c(9,9))
}
}

Result:

USER>d ##class(dc.test).Test()
^dc.testD=1
^dc.testD(1)=$lb("2018-06-22 14:58:11.61","2018-06-22 14:58:11.618") note that the database stores the same values
 
ID              CreationTime            utc
1               2018-06-22 14:58:11.61  2018-06-22 17:58:11.618
 
1 Rows(s) Affected
ID              CreationTime            utc
1               2018-06-22 14:58:11.61  2018-06-22 17:58:11.618
 
1 Rows(s) Affected
ID              CreationTime            utc
1               2018-06-22 14:58:11.61  2018-06-22 14:58:11.618
 
1 Rows(s) Affected

Similarly, you can play with the query in the Portal or any ODBC/JDBC client.

PS: don't forget about SQL Functions: %internal()/%external(), %odbcin()/%odbcout().

Vitaliy Serdtsev · Jun 22, 2018 go to post

Useful links:

  1. SQL and NLS Collations
  2. Collations in Caché

This issue dares relatively simply:

  • Create the required collation tables. Fortunately, for some languages, they are already included and can be taken as a basis. In complex cases, you can contact to WRC.
    Check their list and status: 
    USER>zn "%SYS"
 
%SYS>d ^COLLATE
 
Status       Number   Abbrev   Name
----------   ------   ------   ----------------------
Built-in        0     OANS     ISM Pre-6.2
Built-in        1     ANSI     ISM 6.2->6.4
Built-in        2     COBR     Ipsum/Cobra
Built-in        3     DTMC     DTM-compatible
Built-in        4     CBR2     Ipsum/Cobra-2
Built-in        5     UNIC     Cache standard
Not loaded     10     GER1     German1
Not loaded     11     POR1     Portuguese1
Not loaded     12     POL1     Polish1
Not loaded     13     GER2     German2
Not loaded     14     SPA1     Spanish1
Not loaded     15     DAN1     Danish1
Available      16     CYR1     Cyrillic1
Not loaded     17     GRE1     Greek1
Not loaded     18     CZE1     Czech1
Not loaded     19     CZE2     Czech2
Not loaded     20     POR2     Portuguese2
Not loaded     21     FIN1     Finnish1
Not loaded     22     JAP1     Japanese1
Not loaded     24     POL2     Polish2
Not loaded     27     FRE1     French1
Not loaded     28     FIN2     Finnish2
Available      29     HUN1     Hungarian1
Available      30     GER3     German3
Not loaded     31     POL3     Polish3
Not loaded     32     SPA2     Spanish2
Not loaded     33     DAN2     Danish2
Not loaded     34     GRE2     Greek2
Not loaded     35     FIN3     Finnish3
Not loaded     36     LIT1     Lithuanian1
Available      37     CYR3     Cyrillic3
Not loaded     38     SLO1     Slovenian1
Not loaded     39     SLO2     Slovenian2
Not loaded     40     TUR1     Turkish1
Not loaded     41     DAN3     Danish3
Available      42     UKR1     Ukrainian1
Available      43     CYR4     Cyrillic4
Not loaded     44     CZE3     Czech3
Not loaded     46     MAL1     Maltese1
Not loaded     48     MAL2     Maltese2
Not loaded     49     SPA4     Spanish4
Not loaded     50     SLO1     Slovak1
Not loaded     51     SPA5     Spanish5
Not loaded     52     FIN4     Finnish4
Not loaded     53     CZE4     Czech4
Built-in      128     OSTR     ISM Pre-6.2 string
Built-in      129     NSTR     ISM 6.2->6.4 string
Built-in      133     USTR     Cache standard string
Available     144     CYR1S    Cyrillic1 string
Available     157     HUN1S    Hungarian1 string
Available     158     GER3S    German3 string
Available     165     CYR3S    Cyrillic3 string
Available     170     UKR1S    Ukrainian1 string
Available     171     CYR4S    Cyrillic4 string
  • load the new collation tables to your current locale
  • for Storage make mapping your indexes in separate globals with own collation
  • Profit!

Here are two small examples: 

  1. Class dc.test1 Extends %Persistent
{

Index iDE On DescDE As EXACT;

Index iHU On DescHU As EXACT;

Index iRU On DescRU As EXACT;

Property DescDE As %String;

Property DescHU As %String;

Property DescRU As %String;

ClassMethod LoadColTables() [ Private ]
{
  s r=$$$NO
  
  try {
    $$$AddAllRoleTemporaryInTry
    n $namespace
  
    s $namespace="%SYS"
    
    $$$ThrowOnError(##class(Config.NLS.Tables).Load("deuw","COL","German3"))
    $$$ThrowOnError(##class(Config.NLS.Tables).Load("hunw","COL","Hungarian1"))
    $$$ThrowOnError(##class(Config.NLS.Tables).Load("rusw","COL","Cyrillic4"))
    
    ;d ^COLLATE ; for check only

    s r=$$$YES
  }catch(ex) {
    w ex.DisplayString(),!
  }

  q r
}

/// d ##class(dc.test1).Test()
ClassMethod Test()
{
  q:'..LoadColTables()
  
  d ..%KillExtent()
  
  &sql(insert into dc.test1(DescDE,DescHU,DescRU)
  select 'Straßer','csak','Прохор' union all
  select 'Straster','comb','Павел' union all
  select 'Strasser','cukor','Пётр')
  
  zw ^dc.test1D
  w !,"---- Before (for all indexes by default = 'Cache standard') --------",!!
  d print
  
  w !,"---- After (each index has its own collation) --------",!!
  d ..%PurgeIndices()
  
  d $system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test1.iDE",##class(%Collate).DisplayToLogical("German3")))
   ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test1.iHU",##class(%Collate).DisplayToLogical("Hungarian1")))
   ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test1.iRU",##class(%Collate).DisplayToLogical("Cyrillic4")))
   ,..%BuildIndices(,$$$NO,$$$YES,$$$NO)
   ,$system.SQL.TuneTable($classname(),$$$YES)
   ,$system.OBJ.Compile($classname(),"cu-d")
   ,print

  q

print  
  zw ^dc.test1.iDE,^dc.test1.iHU,^dc.test1.iRU
  w !
  f lng="DE","HU","RU" d ##class(%SQL.Statement).%ExecDirect(,$$$FormatText("select %ID,Desc%1 from dc.test1 order by %exact(Desc%1)",lng)).%Display() w !!
  q
}

Storage Default
{
<Data name="test1DefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>DescDE</Value>
</Value>
<Value name="3">
<Value>DescHU</Value>
</Value>
<Value name="4">
<Value>DescRU</Value>
</Value>
</Data>
<DataLocation>^dc.test1D</DataLocation>
<DefaultData>test1DefaultData</DefaultData>
<IdLocation>^dc.test1D</IdLocation>
<Index name="iDE">
<Location>^dc.test1.iDE</Location>
</Index>
<Index name="iHU">
<Location>^dc.test1.iHU</Location>
</Index>
<Index name="iRU">
<Location>^dc.test1.iRU</Location>
</Index>
<IndexLocation>^dc.test1I</IndexLocation>
<StreamLocation>^dc.test1S</StreamLocation>
<Type>%Library.CacheStorage</Type>
}

}

    Result:

    USER>d ##class(dc.test1).Test()
^dc.test1D=3
^dc.test1D(1)=$lb("","Straßer","csak","Прохор")
^dc.test1D(2)=$lb("","Straster","comb","Павел")
^dc.test1D(3)=$lb("","Strasser","cukor","Пётр")
 
---- Before (for all indexes by default = 'Cache standard') --------
 
^dc.test1.iDE("Strasser",3)=""
^dc.test1.iDE("Straster",2)=""
^dc.test1.iDE("Straßer",1)=""
^dc.test1.iHU("comb",2)=""
^dc.test1.iHU("csak",1)=""
^dc.test1.iHU("cukor",3)=""
^dc.test1.iRU("Павел",2)=""
^dc.test1.iRU("Прохор",1)=""
^dc.test1.iRU("Пётр",3)=""
 
ID      DescDE
3       Strasser
2       Straster
1       Straßer
 
3 Rows(s) Affected
 
ID      DescHU
2       comb
1       csak
3       cukor
 
3 Rows(s) Affected
 
ID      DescRU
2       Павел
1       Прохор
3       Пётр
 
3 Rows(s) Affected
 
 
---- After (each index has its own collation) --------
 
^dc.test1.iDE("Strasser",3)=""
^dc.test1.iDE("Straßer",1)=""
^dc.test1.iDE("Straster",2)=""
^dc.test1.iHU("comb",2)=""
^dc.test1.iHU("cukor",3)=""
^dc.test1.iHU("csak",1)=""
^dc.test1.iRU("Павел",2)=""
^dc.test1.iRU("Пётр",3)=""
^dc.test1.iRU("Прохор",1)=""
 
ID      DescDE
3       Strasser
1       Straßer
2       Straster
 
3 Rows(s) Affected
 
ID      DescHU
2       comb
3       cukor
1       csak
 
3 Rows(s) Affected
 
ID      DescRU
2       Павел
3       Пётр
1       Прохор
 
3 Rows(s) Affected
  2. Class dc.test2 Extends %Persistent
{

Index iDE On (Lng, Descr As EXACT);

Index iHU On (Lng, Descr As EXACT);

Index iRU On (Lng, Descr As EXACT);

Property Lng As %String;

Property Descr As %String;

/// d ##class(dc.test2).Test()
ClassMethod Test()
{
  d ..%KillExtent()
   ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test2.iDE",##class(%Collate).DisplayToLogical("German3")))
   ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test2.iHU",##class(%Collate).DisplayToLogical("Hungarian1")))
   ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test2.iRU",##class(%Collate).DisplayToLogical("Cyrillic4")))

  &sql(insert into dc.test2(Lng,Descr)
  select 'DE', 'Straßer' union
  select 'DE', 'Straster' union
  select 'DE', 'Strasser' union
  select 'HU', 'csak' union
  select 'HU', 'comb' union
  select 'HU', 'cukor' union
  select 'RU', 'Прохор' union
  select 'RU', 'Павел' union
  select 'RU', 'Пётр')
  
  zw ^dc.test2D,^dc.test2.iDE,^dc.test2.iHU,^dc.test2.iRU
  
  d $system.SQL.TuneTable($classname(),$$$YES)
   ,$system.OBJ.Compile($classname(),"cu-d")
   ,print

  q

print  
  w !
  f lng="DE","HU","RU" {
    s idx=$replace("iDE,iHU,iRU","i"_lng,"_i"_lng)
    d ##class(%SQL.Statement).%ExecDirect(,$$$FormatText("select * from %IGNOREINDEX %1 dc.test2 where Lng=? order by %exact(Descr)",idx),lng).%Display()
    w !!
  }
  q
}

Storage Default
{
<Data name="test2DefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>Lng</Value>
</Value>
<Value name="3">
<Value>Descr</Value>
</Value>
</Data>
<DataLocation>^dc.test2D</DataLocation>
<DefaultData>test2DefaultData</DefaultData>
<IdLocation>^dc.test2D</IdLocation>
<Index name="iDE">
<Location>^dc.test2.iDE</Location>
</Index>
<Index name="iHU">
<Location>^dc.test2.iHU</Location>
</Index>
<Index name="iRU">
<Location>^dc.test2.iRU</Location>
</Index>
<IndexLocation>^dc.test2I</IndexLocation>
<StreamLocation>^dc.test2S</StreamLocation>
<Type>%Library.CacheStorage</Type>
}

}

    Result:

    USER>d ##class(dc.test2).Test()
^dc.test2D=9
^dc.test2D(1)=$lb("","DE","Straßer")
^dc.test2D(2)=$lb("","DE","Straster")
^dc.test2D(3)=$lb("","DE","Strasser")
^dc.test2D(4)=$lb("","HU","csak")
^dc.test2D(5)=$lb("","HU","comb")
^dc.test2D(6)=$lb("","HU","cukor")
^dc.test2D(7)=$lb("","RU","Прохор")
^dc.test2D(8)=$lb("","RU","Павел")
^dc.test2D(9)=$lb("","RU","Пётр")
^dc.test2.iDE(" DE","Strasser",3)=""
^dc.test2.iDE(" DE","Straßer",1)=""
^dc.test2.iDE(" DE","Straster",2)=""
^dc.test2.iDE(" HU","comb",5)=""
^dc.test2.iDE(" HU","csak",4)=""
^dc.test2.iDE(" HU","cukor",6)=""
^dc.test2.iDE(" RU","Павел",8)=""
^dc.test2.iDE(" RU","Прохор",7)=""
^dc.test2.iDE(" RU","Пётр",9)=""
^dc.test2.iHU(" DE","Strasser",3)=""
^dc.test2.iHU(" DE","Straster",2)=""
^dc.test2.iHU(" DE","Straßer",1)=""
^dc.test2.iHU(" HU","comb",5)=""
^dc.test2.iHU(" HU","cukor",6)=""
^dc.test2.iHU(" HU","csak",4)=""
^dc.test2.iHU(" RU","Павел",8)=""
^dc.test2.iHU(" RU","Прохор",7)=""
^dc.test2.iHU(" RU","Пётр",9)=""
^dc.test2.iRU(" DE","Strasser",3)=""
^dc.test2.iRU(" DE","Straster",2)=""
^dc.test2.iRU(" DE","Straßer",1)=""
^dc.test2.iRU(" HU","comb",5)=""
^dc.test2.iRU(" HU","csak",4)=""
^dc.test2.iRU(" HU","cukor",6)=""
^dc.test2.iRU(" RU","Павел",8)=""
^dc.test2.iRU(" RU","Пётр",9)=""
^dc.test2.iRU(" RU","Прохор",7)=""
 
ID      Descr   Lng
3       Strasser        DE
1       Straßer DE
2       Straster        DE
 
3 Rows(s) Affected
 
ID      Descr   Lng
5       comb    HU
6       cukor   HU
4       csak    HU
 
3 Rows(s) Affected
 
ID      Descr   Lng
8       Павел   RU
9       Пётр    RU
7       Прохор  RU
 
3 Rows(s) Affected

Important: if you remove "As EXACT" in the index, the examples will not work correctly for the German language, because: Capital ẞ

Vitaliy Serdtsev · Jun 15, 2018 go to post

Add the RenderDirectlyOnTimeout property in the report: 

Property RenderDirectlyOnTimeout As %ZEN.Datatype.boolean(ZENURL = "$RENDERDIRECTLYONTIMEOUT") [ InitialExpression = {$$$NO} ];
Then you will get the following error:
ERROR #5001: Cannot contact server on port %1.
or
ERROR #5001: Received Control-C or there was a fatal error.

Vitaliy Serdtsev · Jun 14, 2018 go to post

Try so:

Class dc.test Extends %ZEN.Component.page
{

XData Contents [ XMLNamespace = "http://www.intersystems.com/zen" ]
{
  <page xmlns="http://www.intersystems.com/zen">
    <button id="btn1" caption="Test Server"/>
    <button id="btn2" caption="Test Client"/>
  </page>
}

ClassMethod SrvTest() [ ZenMethod ]
{
  &js<zenAlert('from Server');>
}

ClientMethod test() [ Language = javascript ]
{
  zenAlert('from Client');
}

ClientMethod onloadHandler() [ Language = javascript ]
{
  document.getElementById('btn1').addEventListener('click',new Function('evt','return zenPage.SrvTest();'),false);
  document.getElementById('btn2').addEventListener('click',new Function('evt','return zenPage.test();'),false);
}
}
Vitaliy Serdtsev · Jun 14, 2018 go to post

Suppose there is such a code:

int caseSwitch = 1;
 
switch (caseSwitch)
{
  case 1:
  Console.WriteLine("Case 1");
  //a lot of code
  break;
 
  case 2:
  case 8-9:
  Console.WriteLine("Case 2 or 8 or 9");
  //a lot of code
  break;
 
  case 3:
  Console.WriteLine("Case 3");
  //a lot of code
  break;
 
  /*...
  a lot conditions
  ...*/
 
  default:
  Console.WriteLine("Default case");
  //a lot of code
  break;
}

In this case

  1. $select;if/elseif/else is not effective because for last conditions will have to iterate through all the previous ones
  2. $case is redundant: you will have to specify the same code multiple times for different values

For this particular case, I suggest using a transition table, for example:

ClassMethod Test(caseSwitch = 3) [ ProcedureBlock = 0 ]
{
  s:'$d(^||addr) ^||addr(1)=1,
                (^(2),^(8),^(9))=289,
                 ^(3)=3
  
  d @("case"_$g(^||addr(caseSwitch),"def"))
  q  

case1
  w "Case 1"
  q
case289
  w "Case 2 or 8 or 9"
  q
case3
  w "Case 3"
  q
casedef
  w "Default case"
  q
}

Of course, you can modify the example to call procedures, functions, class methods, etc.

Vitaliy Serdtsev · Jun 14, 2018 go to post

And if so? 

Class dc.EmbedObj Extends %SerialObject
{


Property pOID As %ObjectIdentity [ Private ];


Property a As %String;


Property b As %String [ Calculated, SqlComputeCode = {s {*}=##class(dc.EmbedObj).Calcb({pOID})}, SqlComputed ];


ClassMethod Calcb(pOID) As %String
{
  s r=""
  q:pOID="" r


  s $lb(id,cls)=$lfs(pOID,"@")


  s p=$system.OBJ.OpenId(cls,id)
  q:'$IsObject(p) r


  i cls="dc.ContainerObj" {
    s r="b"p.Foobar
  }elseif cls="dc.blablablaContainerObj" {
    s r="b"p.qwe
  }
  q r
}
}


Class dc.ContainerObj Extends %Persistent
{


Property Foobar As %String;


Property InnerObj As dc.EmbedObj;


Trigger NewTrigger1 [ Event = INSERT, Foreach = row/object, Time = AFTER ]
{
  n oid
  s oid={%%ID}"@"{%%CLASSNAMEQ}
  &sql(update dc.ContainerObj set InnerObj_pOID=:oid where %ID=:{id})
}


/// d ##class(dc.ContainerObj).Test()
ClassMethod Test()
{
  d ..%KillExtent()
  
  s t=..%New()
  s t.Foobar="foobar1"
  s t.InnerObj.a="a1"
  d t.%Save()


  &sql(insert into dc.ContainerObj(Foobar,InnerObj_a) values('foobar2','a2'))
  
  d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.ContainerObj").%Display()
}
}

Vitaliy Serdtsev · Jun 13, 2018 go to post

For serial class, I'm afraid that's not possible because of its nature. $this, $classname, etc. in the context of EmbedObj don't know anything about ContainerObj.

Use bidirectional Relationship.

Vitaliy Serdtsev · Jun 13, 2018 go to post

You can change the XSLFO Stylesheet dynamically or statically. See XSLFOSTYLESHEET

So, there is a class-report, for example MyApp.ReportDemo.

Series of steps:
  1. remove option XSLFOSTYLESHEET, if any, and recompile class
  2. generate XSLFO Stylesheet: 
    SAMPLES>d $system.OBJ.DisplayError(##class(MyApp.ReportDemo).GenerateToFile($system.CSP.GetFileName($system.CSP.GetDefaultApp($zu(5))_"/MyApp.ReportDemo.xsl"),4))
  3. make changes to MyApp.ReportDemo.xsl
  4. add the parameter XSLFOSTYLESHEET and recompile the class again 
    /// If defined, this provides a reference to the external
/// stylesheet to use in generating the XSL-FO (PDF) report.
/// If it is not provided, a stylesheet will be generated 
/// from the ReportDisplay XData block.
Parameter XSLFOSTYLESHEET As String = "MyApp.ReportDemo.xsl";

Now open the report in your browser/command line/etc. Profit!

Important: if you change something in ReportDisplay, you need to repeat the steps again.

Vitaliy Serdtsev · Jun 12, 2018 go to post

Page numbering in Roman Numerals

Need add the attribute format to the element <fo:page-sequence>, i.e.

<fo:page-sequence master-reference='blablabla' format ='I' .. >

Possible value format:

  • format="1" results in 1 2 3 . .
  • format="01" results in 01 02 03
  • format="a" results in a b c . .
  • format="A" results in A B C. .
  • format="i" results in i ii iii iv . .
  • format="I" results in I II III IV . .
  • more..

Example:

  1. Do all the steps according to Zen Report Tutorial
  2. Insert the page number into <pagefooter>
    <!-- Optional Pagefooter element. Does not apply in HTML output. -->
<pagefooter>
<item special="page-number"/>
</pagefooter>
  3. Insert the following code into the class:
    ClassMethod ReplaceStream(
  ByRef stream As %Stream.TmpBinary,
  format) [ Private ]
{
  s tmp=##class(%Stream.TmpBinary).%New()
  while 'stream.AtEnd {
    d tmp.Write($replace(stream.Read($$$MaxLocalLength),"<fo:page-sequence ",$$$FormatText("<fo:page-sequence format=%1 ",$$$quote(format))))
  }
  d stream.CopyFrom(tmp)
}

/// d ##class(MyApp.ReportDemo).Test()
ClassMethod Test(format = "1")
{
  /*
    •0 = XML
    •1 = HTML
    •2 = PDF
    •3 = ToHTML Stylesheet
    •4 = ToXSLFO Stylesheet
    •5 = XSD Schema
    •6 = PrintPS
    •7 = Excel
    •8 = XSLFO
    •9 = ToEXCEL
    •10=xlsx
    •11=TIFF
    •12=pdfprint
    •13=displayxlsx
    •14=fo2pdf
    •15=foandpdf
  */
  
  s xslfo=##class(%Stream.TmpBinary).%New()

  s t=..%New()
  d t.GenerateReportToStream(.xslfo,4)
  d ..ReplaceStream(.xslfo,format)
  s t.toxslfostream=xslfo
  d $system.OBJ.DisplayError(t.GenerateReport("c:\temp\test.pdf",2))
}
  4. SAMPLES>d ##class(MyApp.ReportDemo).Test("I")
or
SAMPLES>d ##class(MyApp.ReportDemo).Test("01")
or
SAMPLES>d ##class(MyApp.ReportDemo).Test("A")
  5. open test.pdf. Profit!
Vitaliy Serdtsev · Jun 6, 2018 go to post

Choose to your taste:

Class dc.test [ Abstract ]
{

ClassMethod Public() [ Internal, Private, ProcedureBlock = 0 ]
{
  q
Choice0()
  q "Sunday"
Choice1()
  q "Monday"
Choice2()
  q "Tuesday"
Choice3()
  q "Wednesday"
Choice4()
  q "Thursday"
Choice5()
  q "Friday"
Choice6()
  q "Saturday"
Choice()
  set a="entry " _"error"
  return a
}

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  s daynum=$zd($h,10)

  w "1) ",$case(daynum,
                0:$$Choice0,
                1:$$Choice1,
                2:$$Choice2,
                3:$$Choice3,
                4:$$Choice4,
                5:$$Choice5,
                6:$$Choice6,
                :$$Choice),!

  w "2) ",@("$$Choice"_$case(daynum,
                              0:0,
                              1:1,
                              2:2,
                              3:3,
                              4:4,
                              5:5,
                              6:6,
                              :"")),!

  s daynum="-"
  
  w "3) ",$case(daynum,
                0:"Sunday",
                1:"Monday",
                2:"Tuesday",
                3:"Wednesday",
                4:"Thursday",
                5:"Friday",
                6:"Saturday",
                :$xecute("()"_
                          " set a=""entry "" _""error"""_
                          " return a")),!
}

}