All
Accepted answers
Vitaliy Serdtsev · Mar 3, 2020 go to post

Defining and Using Stored Procedures

 

Source code

Class dc.test [ Abstract ]
{

Query daterange(
  d1 As %String,
  d2 As %String) As %Query(ROWSPEC = "dBegin:%String,dEnd:%String") [ SqlName = daterange, SqlProc ]
{
}

ClassMethod daterangeExecute(
  ByRef qHandle As %Binary,
  d1 As %String,
  d2 As %String) As %Status
{
  s qHandle("d1")=$system.SQL.TODATE(d1,"MM-DD-YYYY"),
    qHandle("d2")=$system.SQL.TODATE(d2,"MM-DD-YYYY")
  q $$$OK
}

ClassMethod daterangeFetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = daterangeExecute ]
{
  s d2=qHandle("d2"),
    dBegin=qHandle("d1"),
    dEnd=$system.SQL.LASTDAY(dBegin)
  
  s:dEnd>d2 dEnd=d2
  
  i dBegin>d2 {
    s AtEnd=1
  } else {
    s Row=$lb($system.SQL.TOCHAR(dBegin,"MM-DD-YYYY"),$system.SQL.TOCHAR(dEnd,"MM-DD-YYYY")),
      qHandle("d1")=dEnd+1
  }

  q $$$OK
}

ClassMethod daterangeClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = daterangeExecute ]
{
  q $$$OK
}

}
Result: 
select * from dc.daterange('1-5-2019','5-25-2019')
dBegin dEnd
01-05-2019 01-31-2019
02-01-2019 02-28-2019
03-01-2019 03-31-2019
04-01-2019 04-30-2019
05-01-2019 05-25-2019
Vitaliy Serdtsev · Mar 3, 2020 go to post

Defining Stored Procedures

 

Source code

Class dc.test [ Abstract ]
{

Query numbers(count As %Integer = 4) As %Query(ROWSPEC = "n:%Integer") [ SqlName = numbers, SqlProc ]
{
}

ClassMethod numbersExecute(
  ByRef qHandle As %Binary,
  count As %Integer = 4) As %Status
{
  s qHandle=$lb(0,count)
  q $$$OK
}

ClassMethod numbersFetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = numbersExecute ]
{
  s n=$li(qHandle,1)+1
  
  i n>$li(qHandle,2) {
    s AtEnd=1
  }
  else {
    s Row=$lb(n)
    s $li(qHandle,1)=n
  }
  q $$$OK
}

ClassMethod numbersClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = numbersExecute ]
{
  q $$$OK
}

}
Result: 
select * from dc.numbers(107)
n
1
2
...
105
106
107
Vitaliy Serdtsev · Feb 28, 2020 go to post

Forget. Now %STARTSWITH all other things being equal is slightly faster than LIKE. This point is deeply hidden in the documentation, and it seems that this applies only to FOR SOME %ELEMENT. If manage to speed up the special case for LIKE, then still need to correct/supplement the documentation.

Vitaliy Serdtsev · Feb 27, 2020 go to post

Through %ZEN.proxyObject is unlikely to work, since the q parameter cannot be disabled in this case

q - output numeric values unquoted even when they come from a non-numeric property

Use your own class, for example:

Class dc.test Extends %RegisteredObject
{

Property articlenumber As %String;

}
s object = ##class(dc.test).%New()
s object.articlenumber = "15049950"

s x = ##class(%ZEN.Auxiliary.jsonProvider).%WriteJSONStreamFromObject(.json,object,,,,"aelotw")

Output:

{
  "articlenumber":"15049950"
}
Vitaliy Serdtsev · Feb 27, 2020 go to post

Conclusion: the quote will be removed from the documentation since it's no longer true.

Then besides this, in the documentation for %STARTSWITH need to add the note DEPRECATED and the recommendation "use LIKE 'XXX%'"

I also did an analysis for Caché 2018.1

Class del.t Extends %Persistent
{

Index ip On p;

Property p As %VarString;

/// d ##class(del.t).Fill()
ClassMethod Fill(N = 1000000)
{
  d DISABLE^%NOJRN
  k ^del.tD,^del.tI

  f i=1:1:N s ^del.tD(i)=$lb("","test"_i)
  s ^del.tD=N
  d ENABLE^%NOJRN

  d ..%BuildIndices(,,,$$$NO)
  d $system.SQL.TuneTable($classname(),$$$YES)
  d $system.OBJ.Compile($classname(),"cu-d")
}
}

Although the plans are exactly the same in SMP the results of the metrics differ:

select count(*) from del.t where p like 'test7%'
Row count: 1 Performance: 0.291 seconds 333340 global references 2000537 lines executed

select count(*) from del.t where p %startswith 'test7'
Row count: 1 Performance: 0.215 seconds 333340 global references 1889349 lines executed

For the next two queries, the INT code matches:

&sql(select * from del.t where p like 'test7%')
&sql(select * from del.t where p %startswith 'test7')

But for these - is already different, so the metrics in SMP are different:

&sql(select * from del.t where p like :a)
&sql(select * from del.t where p %startswith :a)

In IRIS 2020.1, the work with embedded queries was changed, but I can't check it.

Vitaliy Serdtsev · Feb 26, 2020 go to post

Quote from the documentation:

For performance reasons, the predicate %STARTSWITH 'abc' is preferable to the equivalent predicate LIKE 'abc%'. proof

Vitaliy Serdtsev · Feb 26, 2020 go to post

I think that the author meant that the simplest queries of the form
like 'text%'
automatically worked as/converted to
%startswith 'text'

Vitaliy Serdtsev · Feb 25, 2020 go to post

I would look towards modern frameworks like React, Vue, etc., especially considering that CSP/ZEN/Mojo no longer develops.

Vitaliy Serdtsev · Feb 24, 2020 go to post

If you have old version of Caché, you can use %ZEN.Auxiliary.jsonProvider or %ZEN.Auxiliary.altJSONProvider, which have a bunch of useful methods, for example:
%ArrayToJSON
%WriteJSONFromArray
%WriteJSONStreamFromArray
etc.

Here are two small examples:

set array=##class(%ListOfDataTypes).%New()
for i=1:1:4000000 d array.Insert("item"_i)
   
write "count = ",array.Count(),!
   
do ##class(%ZEN.Auxiliary.altJSONProvider).%WriteJSONStreamFromObject(.stream,array,,,1,"aeloq")
   
; here you can save stream to a file or send it to the client
set meta=$lb("nameA","nameB","nameC")
   
for i=1:1:4000000 set data(i)=$lb("itemA"_i,"itemB"_i,"itemC"_i)
   
do ##class(%ZEN.Auxiliary.altJSONProvider).%ArrayToJSON(.meta,.data)
Vitaliy Serdtsev · Feb 24, 2020 go to post

To Dmitry's words I will add a few links from the documentation:

In your case, be the $LISTTOSTRING function is useful, for example: 

USER>set first=$lb("words","more","words")
USER>write first
words-morewords
USER>write $listtostring(first)
words,more,words
USER>write $listtostring(first,"^")
words^more^words
USER>write $listtostring(first,"")
wordsmorewords

I think that the result of "dogcatfish" in the book was due to copying/pasting, which caused the service characters to be lost.

By the way, here on the forum it’s also not so easy to insert this gibberish ;)

Vitaliy Serdtsev · Jan 30, 2020 go to post

Are you sure that the "cdate" type is a string ? Maybe %Date or %PosixTime?

Even if leave a string, the result is highly dependent on ExtentSize. For example, if ExtentSize=10, the plans match.

Vitaliy Serdtsev · Jan 24, 2020 go to post

It should be noted that this applies not only to the try/catch block, but also to other blocks, for example:

f i=1:1:2 {
  q 1 ; runtime error, but the line is highlighted
}

do {
  q 2 ; compilation error
}while(0)

while 0 {
  q 3 ; compilation error
}
Vitaliy Serdtsev · Jan 23, 2020 go to post
 

And more food for thought:

Class dc.test [ Abstract ]
{

/// d ##class(dc.test).test()
ClassMethod test(N As %Integer = 10)
{
  d ..Run1(N),..Run2(N),..Run3(N),..Run4(N)
}

ClassMethod Run1(N As %Integer)
{
  f i=1:1:4 {
    s time(i,"start")=$zh
    f j=1:1:N {
        i i=2 {
          s a=20
        } ElseIf i=3 {
          s a=30
        } ElseIf i=1 {
          s a=10
        } Else {
          s a=-4
        }
    }
    s time(i,"end")=$zh
  }

  w "If i=2: ",?12,time(2,"end")-time(2,"start")," seconds",!,
    "ElseIf i=3: ",?12,time(3,"end")-time(3,"start")," seconds",!,
    "ElseIf i=1: ",?12,time(1,"end")-time(1,"start")," seconds",!,
    "Else: ",?12,time(4,"end")-time(4,"start")," seconds",!!
}

ClassMethod Run2(N As %Integer)
{
  f i=2,3,1,4 {
    s time(i,"start")=$zh

    f j=1:1:N s a=$case(i,1:10,2:20,3:30,:-4)

    s time(i,"end")=$zh
  }

  w "i=1: ",time(1,"end")-time(1,"start")," seconds",!,
    "i=2: ",time(2,"end")-time(2,"start")," seconds",!,
    "i=3: ",time(3,"end")-time(3,"start")," seconds",!,
    "i=4: ",time(4,"end")-time(4,"start")," seconds",!!
}

ClassMethod Run3(N As %Integer)
{
  f i=1,2,3,4 {
    s time(i,"start")=$zh

    f j=1:1:N s a=$case(i,2:20,3:30,1:10,:-4)

    s time(i,"end")=$zh
  }

  w "i=1: ",time(1,"end")-time(1,"start")," seconds",!,
    "i=2: ",time(2,"end")-time(2,"start")," seconds",!,
    "i=3: ",time(3,"end")-time(3,"start")," seconds",!,
    "i=4: ",time(4,"end")-time(4,"start")," seconds",!!
}

ClassMethod Run4(N As %Integer) [ ProcedureBlock = 0 ]
{
  n i,time,j,a
  
  f i=2,3,1,4 {
    s time(i,"start")=$zh

    f j=1:1:N d $case(i,1:a1,2:a2,3:a3,:a4)

    s time(i,"end")=$zh
  }

  w "i=1: ",time(1,"end")-time(1,"start")," seconds",!,
    "i=2: ",time(2,"end")-time(2,"start")," seconds",!,
    "i=3: ",time(3,"end")-time(3,"start")," seconds",!,
    "i=4: ",time(4,"end")-time(4,"start")," seconds",!!
  q
a1 s a=10 q
a2 s a=20 q
a3 s a=30 q
a4 s a=-4 q
}

}

USER>d ##class(dc.test).test(1000000)
If i=2:     .027962 seconds
ElseIf i=3: .043612 seconds
ElseIf i=1: .073138 seconds
Else:       .068023 seconds
 
i=1: .035705 seconds
i=2: .035941 seconds
i=3: .03498 seconds
i=4: .033288 seconds
 
i=1: .078231 seconds
i=2: .052477 seconds
i=3: .066045 seconds
i=4: .07372 seconds
 
i=1: .051371 seconds
i=2: .052017 seconds
i=3: .050972 seconds
i=4: .052397 seconds
 
 
USER>d ##class(dc.test).test(1e6)
If i=2:     .060933 seconds
ElseIf i=3: .071999 seconds
ElseIf i=1: .094509 seconds
Else:       .096684 seconds
 
i=1: .058554 seconds
i=2: .058182 seconds
i=3: .059299 seconds
i=4: .054863 seconds
 
i=1: .09973 seconds
i=2: .074734 seconds
i=3: .087673 seconds
i=4: .097123 seconds
 
i=1: .082372 seconds
i=2: .084538 seconds
i=3: .081647 seconds
i=4: .079353 seconds
Vitaliy Serdtsev · Jan 23, 2020 go to post

Hi Peter.

Ok.

w "Time for If (i=2): ",time(2,"end")-time(2,"start")," seconds",!,
  "Time for ElseIf #1 (i=3): ",time(3,"end")-time(3,"start")," seconds",!,
  "Time for ElseIf #2 (i=1): ",time(1,"end")-time(1,"start")," seconds",!,
  "Time for Else (i=4): ",time(4,"end")-time(4,"start")," seconds",!!

1e6
Time for If (i=2): .030974 seconds
Time for ElseIf #1 (i=3): .045126 seconds
Time for ElseIf #2 (i=1): .07144 seconds
Time for Else (i=4): .087353 seconds

1e9
Time for If (i=2): 28.59286 seconds
Time for ElseIf #1 (i=3): 43.044261 seconds
Time for ElseIf #2 (i=1): 82.277535 seconds
Time for Else (i=4): 69.212718 seconds
Vitaliy Serdtsev · Jan 23, 2020 go to post

I have (2019.1.1CE) this is not confirmed:

f i=1:1:4 {
  s time(i,"start")=$zh
  f j=1:1:1e6 {
      i i=2 {
        s a=2
      } ElseIf i=3 {
        s a=3
      } ElseIf i=1 {
        s a=1
      } Else {
        s a=0
      }
  }
  s time(i,"end")=$zh
}

w "Time for If (i=2): ",time(1,"end")-time(1,"start")," seconds",!,
  "Time for ElseIf #1 (i=3): ",time(2,"end")-time(2,"start")," seconds",!,
  "Time for ElseIf #2 (i=1): ",time(3,"end")-time(3,"start")," seconds",!,
  "Time for Else (i=4): ",time(4,"end")-time(4,"start")," seconds",!

Time for If (i=2): .109283 seconds
Time for ElseIf #1 (i=3): .060785 seconds
Time for ElseIf #2 (i=1): .08026 seconds
Time for Else (i=4): .109974 seconds
Vitaliy Serdtsev · Jan 23, 2020 go to post

Hi Nigel.

I hasten to inform you that new classes have been added to IRIS for writing/reading streams in JSON format: %Stream.DynamicBinary, %Stream.DynamicCharacter.

 

Here is a sample code:

Class dc.test Extends (%RegisteredObject, %JSON.Adaptor)
{

Property string As %VarString(%JSONFIELDNAME = "longstring");

Property cs As %Stream.TmpCharacter(%JSONFIELDNAME = "cstream");

/// d ##class(dc.test).test()
ClassMethod test()
{
  #define fill(%len,%val) $tr($j("",%len)," ",%val)

  try{
    
    s tmp=..%New()
    
    ; the maximum string length when exporting to JSON depends on the length of the property name
    s tmp.string=$$$fill($$$MaxLocalLength-($l("longstring")+5),$c(351))
    
    $$$ThrowOnError(tmp.cs.Write($$$fill($$$MaxLocalLength,$c(355))))
    $$$ThrowOnError(tmp.cs.Write($$$fill($$$MaxLocalLength,$c(354))))

    w "[Export] len(string): ",$l(tmp.string),", len(cs): ",tmp.cs.Size,!!

    s file=##class(%Stream.FileCharacter).%New()
    s file.Filename="VeryBigObject.json"
    
    $$$ThrowOnError(tmp.%JSONExportToStream(file))
    $$$ThrowOnError(file.%Save())
    
    ;------------------
    
    s dobj={}.%FromJSON(file)
    w "1. [Import    %DynamicObject] len(longstring): ",$l(dobj.longstring),", len(cstream): ",dobj.%Get("cstream",,"stream").Size,!

    s obj1=..%JSONNew(dobj)
    w "2. [Import %RegisteredObject] len(string): ",$l(obj1.string),", len(cs): ",obj1.cs.Size,!

    /*
    
    The following command will generate an error (tested on IRIS 2019.1.1 CE, Unicode):
    
    ERROR: <MAXSTRING>  zWrite^%Stream.TmpCharacter.1
    
    To avoid errors, you need to fix the code in the GenImportCharacterStream method (see the patch below)
    
    */
    
    s obj2=..%New()
    $$$ThrowOnError(obj2.%JSONImport(file))
    w "3. [Import %RegisteredObject] len(string): ",$l(obj2.string),", len(cs): ",obj2.cs.Size,!

  }catch(ex) {
    w "ERROR: ",ex.DisplayString(),!
  }
}

/// Get an instance of an JSON enabled class.<br><br>
/// 
/// You may override this method to do custom processing (such as initializing
/// the object instance) before returning an instance of this class.
/// However, this method should not be called directly from user code.<br>
/// Arguments:<br>
///     dynamicObject is the dynamic object with thee values to be assigned to the new object.<br>
///     containerOref is the containing object instance when called from JSONImport.
ClassMethod %JSONNew(
  dynamicObject As %DynamicObject,
  containerOref As %RegisteredObject = "") As %RegisteredObject [ GenerateAfter = %JSONGenerate, ServerOnly = 1 ]
{
  #dim r As dc.test=$s($IsObject(containerOref):containerOref,1:..%New())
  s r.string=dynamicObject.longstring
  d r.cs.CopyFrom(dynamicObject.%Get("cstream",,"stream"))
  q r
}

}

Result:

USER>d ##class(dc.test).test()
[Export] len(string): 3641129, len(cs): 7282288
 
1. [Import    %DynamicObject] len(longstring): 3641129, len(cstream): 7282288
2. [Import %RegisteredObject] len(string): 3641129, len(cs): 7282288
3. [Import %RegisteredObject] len(string): 3641129, len(cs): 7282288

Patch for 2019.1.1CE:

Instead of

		$$$GENERATE(indent_"  Set sc=stream.Write(%JSONObject."_$$$QN($$$jsonfieldname(propertyMap))_") If $$$ISERR(sc) Goto %JSONImportExit")

need

		$$$GENERATE(indent_"  Set testInvalidField=0, sc=stream.CopyFrom(%JSONObject.%Get(field,,""stream"")) If $$$ISERR(sc) Goto %JSONImportExit")

It is possible that in version 2019.4.x has already fixed everything.

Vitaliy Serdtsev · Jan 22, 2020 go to post

Can I safely read any size %GlobalBinaryStream into a %Binary?
No. Any string (%Binary, %String, %VarString, etc.) has size limit of 3,6MB

PS: if it's not a secret, why do you need to read from a stream to a string?

Vitaliy Serdtsev · Jan 22, 2020 go to post

The example below works even in Caché:

#include %systemInclude
n

try{

  $$$AddAllRoleTemporaryInTry
  n $namespace
  s $namespace="SAMPLES"
  
  s person=##class(Sample.Person).%OpenId(1)
  
  d ##class(%ZEN.Auxiliary.altJSONProvider).%WriteJSONStreamFromObject(.stream,person,,,$$$YES,"aeloq")
  w "json (string): ",stream.Read($$$MaxLocalLength),!!
  
  d ##class(%ZEN.Auxiliary.altJSONProvider).%ConvertJSONToObject(stream,"Sample.Person",.obj)
  w "obj.Home.Street: ",obj.Home.Street ; or d $system.OBJ.Dump(obj)
  
}catch(ex){
  w "Error ", ex.DisplayString(),!
}
Result:

USER>d ^test
json (string): {"Name":"Pascal,Martin F.","SSN":"502-68-5767","DOB":43307,"Home":{"Street":"9347 Franklin Drive","City":"Denver","State":"VA","Zip":66346},"Office":{"Street":"4897 Main Blvd","City":"Miami","State":"MO","Zip":60084},"Spouse":"","FavoriteColors":[],"Age":"60"}
 
obj.Home.Street: 9347 Franklin Drive

Or use %JSON.Adaptor: Exporting and Importing

Vitaliy Serdtsev · Jan 17, 2020 go to post

See answer by @Brendan Bannon about TuneTable.

Documentation:

“Relative cost” is an integer value which is computed from many factors as an abstract number for comparing the efficiency of different execution plans for the same query. This calculation takes into account (among other factors) the complexity of the query, the presence of indices, and the size of the table(s). Relative cost is not useful for comparing two different queries. proof

Vitaliy Serdtsev · Jan 17, 2020 go to post

Could you cite the test code for $$select^LVBEPVIS and run it from a VBScript program from Windows Explorer.
Also give your $zv (interested primarily in 8-bit or Unicode).

My test for the Caché Unicode:

LVBEPVIS.MAC:
select(n)
  s PLIST=4
  s PLIST(1)="018625110"
  s PLIST(2)="01862511"_$c(9,233,769)
  s PLIST(3)="F"_$c(1)_"Female"_$c(1)_"Y"
  s PLIST(4)=35633
  q 1
test.vbs:
Set AxVisM1 = CreateObject("VISM.VisMCtrl.1")
AxVisM1.Server="CN_IPTCP:localhost[1972]:_system:@SYS"
AxVisM1.NameSpace="USER"
AxVisM1.Execute("s err="""",err=$$select^LVBEPVIS(""018625110"")")
WScript.Echo AxVisM1.PLIST
Result:
Vitaliy Serdtsev · Jan 16, 2020 go to post

Example with one file for Windows:

Class dc.test [ Abstract ]
{

/// d ##class(dc.test).test()
ClassMethod test()
{
  s ts=##class(%PosixTime).LogicalToUnixTime(##class(%PosixTime).CurrentUTCTimeStamp()),
    oldName="C:\Tmp\test",
    newName=oldName_$$$FormatText("a%1.txt",ts)
  
  s f=##class(%Stream.FileBinary).%New()
  d f.LinkToFile(oldName)
  
  s gz=##class(%Stream.FileBinaryGzip).%New()
  s gz.Filename=newName
  d gz.CopyFromAndSave(f)

  d ##class(%File).Delete(oldName)
}

}