All
Accepted answers
Vitaliy Serdtsev · Jun 30, 2022 go to post

This can be done much easier, for example:

Include %occUtility

Class dc.test Extends %Persistent [ ClassType = persistent, ProcedureBlock, SqlTableName = demo ]
{

Property Foo As %String [ SqlFieldName = FooBar ];

Property Bar As %Boolean;

ClassMethod Test()
{
  
  ; d ##class(dc.test).Test()
  d ..%KillExtent()
  
  &sql(insert into dc.demo(FooBar,Bar)values('f1',0))
  &sql(insert into dc.demo(FooBar,Bar)values('f2',1))
  &sql(insert into dc.demo(FooBar,Bar)values('f3',null))
  
  s tablename=$$$comClassKeyGet(..%ClassName(1),$$$cCLASSsqlqualifiednameQ)
  
  s rs=##class(%ResultSet).%New("%DynamicQuery:SQL")
  d rs.Prepare("select * from "_tablename)
  d rs.Execute()
  while rs.Next() {
    w ! f i=1:1:rs.GetColumnCount() w rs.GetColumnHeader(i)," = ",$$quote(rs.GetData(i))," "
  }
  
  d rs.%Close()
}

}

Output:

USER>d ##class(dc.test).Test()
 
ID = 1 Bar = 0 FooBar = "f1"
ID = 2 Bar = 1 FooBar = "f2"
ID = 3 Bar = "" FooBar = "f3"
Vitaliy Serdtsev · Jun 28, 2022 go to post

Caché 5.0.21:

Class DC.DemoPropertyQuery Extends %Persistent [ ClassType = persistent, ProcedureBlock ]
{

Property Foo As %String;

Property Bar As %Boolean;

ClassMethod Benchmark()
{
  set Job=##CLASS(%SYSTEM.Process).%OpenId($job)
  
  set start = $zhorolog
  set startGlobalRefs = Job.GlobalReferences
  set startLines = Job.LinesExecuted
  for i=1:1:1000 {
    kill properties
    do ..GetPropertiesAsQuickly(.properties)
  }
  set endLines = Job.LinesExecuted
  set endGlobalRefs = Job.GlobalReferences
  write "Elapsed time (1000x): ",($zhorolog-start)," seconds; ",(endGlobalRefs-startGlobalRefs)," global references; ",(endLines-startLines)," routine lines",!
  
  do ..Print(.properties)
  write !
}

ClassMethod Print(ByRef properties)
{
  set key = ""
  for {
    set key = $order(properties(key),1,data)
    quit:key=""
    set $listbuild(type,origin) = data
    write !,"property: ",key,"; type: ",type,"; origin: ",origin
  }
}

ClassMethod GetPropertiesAsQuickly(Output properties)
{
  // Getting properties via macro-wrapped direct global references is harder to read,
  // but is the fastest way to do it.
  set key = ""
  set class = ..%ClassName(1)
  for {
    set key = $$$comMemberNext(class,$$$cCLASSproperty,key)
    quit:key=""
    set type = $$$comMemberKeyGet(class,$$$cCLASSproperty,key,$$$cPROPtype)
    set origin = $$$comMemberKeyGet(class,$$$cCLASSproperty,key,$$$cPROPorigin)
    set properties(key) = $listbuild(type,origin)
  }
}

}

Result:

USER>do ##class(DC.DemoPropertyQuery).Benchmark()
Elapsed time (1000x): .018047 seconds; 25003 global references; 40000 routine lines
 
property: %Concurrency; type: %Library.CacheString; origin: %Library.Persistent
property: %IsInSave; type: %Library.CacheString; origin: %Library.Persistent
property: Bar; type: %Library.Boolean; origin: DC.DemoPropertyQuery
property: Foo; type: %Library.String; origin: DC.DemoPropertyQuery
Vitaliy Serdtsev · Jun 23, 2022 go to post

See $SYSTEM.SQL.SetServerInitCode() (there are differences for IRIS)

Simple example:

Class dc.test
{

ClassMethod Test()
{
  
  s programname=$zcvt(##class(%SYS.ProcessQuery).%OpenId($j).ClientExecutableName,"L")

  i programname="blablabla.exe" {

    ;useful work

    s $EC="ERROR"
    ;or
    s $ROLES="r1"
  }
}

}

USER>d $SYSTEM.SQL.SetServerInitCode("d ##class(dc.test).Test()")

Now, when connecting from a specific program via ODBC/JDBC to namespace "USER", an error will occur. You can configure something another.

Vitaliy Serdtsev · Jun 23, 2022 go to post

See Parent-Child Relationships and Storage

Demonstration: 

  1. Class dc.child Extends %Persistent
{
Property name;
Property parent As dc.parent;
// Relationship parent As dc.parent [ Cardinality = parent, Inverse = child ];
}

Class dc.parent Extends %Persistent
{
Property name;
// Relationship child As dc.child [ Cardinality = children, Inverse = parent ];
ClassMethod Test()
{
  d ..%KillExtent()
  d ##class(child).%KillExtent()
  
  &sql(insert into dc.parent(name) values('parent1'))
  &sql(insert into dc.parent(name) values('parent2'))

  &sql(insert into dc.child(name,parent) values('child11',1))
  &sql(insert into dc.child(name,parent) values('child12',1))
  &sql(insert into dc.child(name,parent) values('child21',2))
  &sql(insert into dc.child(name,parent) values('child22',2))
  
  zw ^dc.parentD,^dc.childD
}
}
    USER>d ##class(dc.parent).Test()
^dc.parentD=2
^dc.parentD(1)=$lb("","parent1")
^dc.parentD(2)=$lb("","parent2")
^dc.childD=4
^dc.childD(1)=$lb("","child11",1)
^dc.childD(2)=$lb("","child12",1)
^dc.childD(3)=$lb("","child21",2)
^dc.childD(4)=$lb("","child22",2)
  2. Important: do not touch the existing Storages in both classes!!!
    Class dc.child Extends %Persistent
{
Property name;
//Property parent As dc.parent;
Relationship parent As dc.parent [ Cardinality = parent, Inverse = child ];
Storage Default
{
...
}
}

Class dc.parent Extends %Persistent
{
Property name;
Relationship child As dc.child [ Cardinality = children, Inverse = parent ];
ClassMethod Test()
{
 ...
}
Storage Default
{
...
}
}
    USER>d ##class(dc.parent).Test()
^dc.parentD=2
^dc.parentD(1)=$lb("","parent1")
^dc.parentD(2)=$lb("","parent2")
^dc.childD=4
^dc.childD(1,1)=$lb("","child11",1)
^dc.childD(1,2)=$lb("","child12",1)
^dc.childD(2,3)=$lb("","child21",2)
^dc.childD(2,4)=$lb("","child22",2)
  3. Important: now remove Storage from dc.child class and recompile both classes. Note that now the Storage of the dc.child class has changed.
    USER>d ##class(dc.parent).Test()
^dc.parentD=2
^dc.parentD(1)=$lb("","parent1")
^dc.parentD(1,"child",1)=$lb("","child11")
^dc.parentD(1,"child",2)=$lb("","child12")
^dc.parentD(2)=$lb("","parent2")
^dc.parentD(2,"child",3)=$lb("","child21")
^dc.parentD(2,"child",4)=$lb("","child22")
^dc.childD=4
^dc.childD(1,1)=$lb("","child11",1)
^dc.childD(1,2)=$lb("","child12",1)
^dc.childD(2,3)=$lb("","child21",2)
^dc.childD(2,4)=$lb("","child22",2)

    Now the data in ^dc.childD from the previous test/step is hanging in the air and cannot be accessed via SQL

Based on the above, the answer to your question will depend on what and how exactly you changed in your classes.

PS: for simplicity, I would advise you to create a clone of your dc.child class (without Relationship) and already take the "disappeared" data from it. After linking the tables (possibly with subsequent copying of data from the old Storage to the new one), the clone with the data can be deleted.

Vitaliy Serdtsev · Jun 23, 2022 go to post

See MultiValue Basic | Caché Alternative Exists for SOUNDEX()

Workaround:

Class dc.test [ Abstract ]
{

ClassMethod Test()
{
  
  w ..SOUNDEX("M"),!

  ;or

  &sql(select SOUNDEX('McD') into :r)
  w r,!
}

ClassMethod SOUNDEX(s) As %String [ Language = mvbasic, SqlName = SOUNDEX, SqlProc ]
{
 RETURN SOUNDEX(s)
}

}

Result:

USER>d ##class(dc.test).Test()
M000
M230
Vitaliy Serdtsev · May 17, 2022 go to post

Now on evaluation.intersystems.com for Windows/MacOSX/Container allows to download the version of IRIS 2020.1CE, and for the rest - IRIS 2021.2CE.

What happened and when will the problem with downloading the latest version disappear once and for all?

Vitaliy Serdtsev · May 13, 2022 go to post

It gives an incorrect result for some data, for example:

Detector("abbz", "abzz")
Detector("aaz", "azz")
Detector("azz", "aaz")

Vitaliy Serdtsev · May 13, 2022 go to post

Without loops:

 

size = 82 (likely the code can be further reduced)

ClassMethod Detector(
  a As %String,
  b As %String) As %Boolean
{
 s a=$zu(28,a,6),b=$zu(28,b,6) q $l(a)=$l(b)&(a'=$tr(a,b,a_b))&(b'=$tr(b,a,b_a))
 ;or
 s a=$zu(28,a,6),b=$zu(28,b,6) q $l(a)=$l(b)&(a'=$tr(a,b,a_b))&(a'=$tr(a,a_b,b))
}
Vitaliy Serdtsev · May 12, 2022 go to post

I follow a simple rule: first I change the data, and only then I put the constraints in the code. Not the other way around.

Vitaliy Serdtsev · May 12, 2022 go to post

Excellent work.yes

You have come up with an additional optimization different from mine. Your code can be improved to 66.

 

size = 68

ClassMethod Detector(
  a As %String,
  b As %String) As %Boolean
{
 f i=65:1:91{q:$$r(a)'=$$r(b)} q i=91
r(x)q $l($$$UPPER(x),$c(i))
}
 

size = 67

ClassMethod Detector(
  a As %String,
  b As %String) As %Boolean
{
 f i=0:1:91{q:$$r(a)'=$$r(b)} q i=91
r(x)q $l($zu(28,x,6),$c(i))
}

$$$ALPHAUP(x) == $zu(28,x,6)

 

size = 66

ClassMethod Detector(
  a As %String,
  b As %String) As %Boolean
{
 f i=90:-1:0{q:$$r(a)'=$$r(b)} q 'i
r(x)q $l($zu(28,x,6),$c(i))
}
Vitaliy Serdtsev · May 11, 2022 go to post

Ok.

 

size = 190

ClassMethod Compress(s As %String) As %String
{
a s a=$p(s,",",$i(i)),d=$p(s,",",i+1)-a f c=1:1{q:d*c+a'=$p(s,",",i+c)} s q=$zabs(d),v=$s(c>2&d:"-"_(c-1*d+a)_$s(q=1:"",1:"/"_q),c>1&'d:"*"_c,1:0) s:v'=0 $p(s,",",i,i+c-1)=a_v q:a="" s g a
}
 

size = 189

ClassMethod Compress(s As %String) As %String
{
 f i=1:1:2e6{s a=$p(s,",",i),d=$p(s,",",i+1)-a f c=1:1{q:d*c+a'=$p(s,",",i+c)} s q=$zabs(d),v=$s(c>2&d:"-"_(c-1*d+a)_$s(q=1:"",1:"/"_q),c>1&'d:"*"_c,1:0) s:v'=0 $p(s,",",i,i+c-1)=a_v} q s
}
Vitaliy Serdtsev · May 11, 2022 go to post

(And I think you may be waiting a long time for the Encoder game to close)
The author seems to have forgotten or this no longer relevant. I'm not interested in opening code when there are no alternatives and there is no competitive spirit.

Vitaliy Serdtsev · May 11, 2022 go to post
 

size = 74

ClassMethod Detector(
  a As %String,
  b As %String) As %Boolean
{
 q $$r(a)=$$r(b)
r(x)f i=65:1:90{s $li(y,i)=$l($zcvt(x,"u"),$c(i))} q y
}

I'm not publishing the option with size = 73 yet, to wait, maybe someone will guess how to do it or offer an ever shorter option (by analogy with Code Golf - Encoder).

UPD: I managed to reduce size to 72.

Vitaliy Serdtsev · May 10, 2022 go to post
 

Example

Class dc.golf.Anagram
{

ClassMethod Detector(
  a As %String,
  b As %String) As %Boolean
{
  f c="a","b"{
    s @c=$zstrip($zcvt(@c,"U"),"*W")
  }
  w $$$quote(a),":",$l(a)," - ",$$$quote(b),":",$l(b),!
  q $tr(a,b)=$tr(b,a)&($l(a)=$l(b))
}

}

Output (tested on IRIS 2021.2CE):

USER>w ##class(dc.golf.Anagram).Detector("apple", "pale")
 
<UNDEFINED>zDetector+2^dc.golf.Anagram.1 *a

If I change Undefined, then there is no error, but the result is incorrect:

USER>d $system.Process.Undefined(2)
 
USER>w ##class(dc.golf.Anagram).Detector("apple", "pale")
"apple":5 - "pale":4
0

The code works correctly if change the signature of the method (ProcedureBlock/PublicList/new), but this is a violation of the conditions of the task.

Vitaliy Serdtsev · May 4, 2022 go to post

See %ZEN.Auxiliary.jsonSQLProvider:%WriteJSONStreamFromSQL()

 

Simple example

Class dc.test Extends %Persistent
{

Property int As %Integer;

Property str As %VarString;

Property bool As %Boolean;

Property Date As %Date;

ClassMethod Test()
{
  d ..%KillExtent()
  
  &sql(insert into dc.test("int",str,bool,"Date")
        select 30,'Hello Caché',0,current_date
        union
        select 303,'ăîşţâ',1,0
        union
        select null,null,null,null)
  
  
  s stream=##class(%Stream.FileBinary).%New(),
    stream.Filename="c:\temp\test.json",
  
    sql="select * from dc.test",
    format="twu",
    maxRows=0

  s status=##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONStreamFromSQL(.stream, sql, format, maxRows)  

  d:$$$ISOK(status) stream.%Save()
}

}

The contents of the file test.json (UTF8):

{
"children":[
{"ID":1,"Date":"04.05.2022","bool":0,"int":30,"str":"Hello Caché"}
,{"ID":2,"Date":"31.12.1840","bool":1,"int":303,"str":"ăîşţâ"}
,{"ID":3,"Date":"","bool":"","int":"","str":""}
]
}
Vitaliy Serdtsev · May 3, 2022 go to post

My current result: size = 94 84

 
ClassMethod Detector(
  a As %String,
  b As %String) As %Boolean
{
 s:$l(a)>$l(b) i=b,b=a,a=i f i=1:1:$l(a){s b=$replace(b,$e(a,i),"",,1,1)} q b?." "
}
Vitaliy Serdtsev · Apr 27, 2022 go to post

I already tried option 1 with Timeformat=1 for the avg and didn't work.
Have you changed the TimeFormat at the process or system level? If at the process level, then most likely you have changed this value in one process, and are executing the query in another. Therefore, there is no effect. If at the system level, then the query should work.

To avoid uncertainty, change the definition of the field 

Property PackingTimeSpent As %Time;

and execute in SMP for Display Mode 

select PackingTimeSpent from MSDS_Serenity.KitlabelAssoc

The hh:mm:ss format string should be displayed.

so eg. select %external(CAST(+avg(166.38) as TIME)) didn't work for me
Two remarks:
  • this will work as intended only if you have changed the TimeFormat=1 at the system level
  • avg(166.38) - it's pointless to write like that.

you mentioned the example above and I'm not sure to which one are youi refering to with so many replies, so can you point me to which one exactly
See Time-to-String Conversionlink

Try this (does not depend on the TimeFormat value of the current locale): 

select TO_CHAR(avg(PackingTimeSpent),'HH24:MI:SS') average from MSDS_Serenity.KitlabelAssoc where label='00007IT4'

PS: By the way, I noticed in your screenshots the differences in queries: somewhere you write 

where label='00007IT4'
somewhere you write 
where ID='00007IT4'

Is this how it should be? To avoid unnecessary questions, please do not change everytime the names of the fields in the queries. This is misleading.

Vitaliy Serdtsev · Apr 27, 2022 go to post

If the data already exists, then this is a non-trivial task, especially if inheritance or Parent/Child is present, since this will lead to a change in the storage scheme of your data.

The easiest way to do this is through an intermediate (temporary) table:
  1. create a new class with the same structure, but with a new primary key;
  2. move data from the old class into it using SQL (not the merge command);
  3. delete data/indexes in the old class, then change the primary key in it;
  4. move data from the new class to the old class, using the merge command;
  5. delete the new class with the data;
  6. rebuild the indexes if there are any.

Useful links: MERGE Persistent Objects and InterSystems IRIS SQL Introduction to Persistent Objects

If you feel insecure with Caché/IRIS, it is better to ask WRC for help.

Vitaliy Serdtsev · Apr 27, 2022 go to post

From doc:

AVG returns either NUMERIC data type values or DOUBLE data type values. If expression is data type DOUBLE, AVG returns DOUBLE; otherwise, it returns NUMERIC.

For non-DOUBLE expression values, AVG returns a double-precision floating point number.proof

The specification of the field [%Time(FORMAT=1)] plays absolutely no role in the case of an aggregate function, since AVG returns just a number (DOUBLE or NUMERIC).

Above I gave links to examples of how a number can be converted to TIME, then to STRING.

For example:

  1. if in the current locale TimeFormat = 1
    select %external(CAST(+166.38 as TIME))

    2. Important: it is necessary to convert a float number to an integer type, otherwise you will get zero.

  2. if in the current locale TimeFormat <> 1
    select TO_CHAR(166.38,'HH24:MI:SS')
Vitaliy Serdtsev · Apr 26, 2022 go to post

Try other methods/queries of this class, such as Dump*, ProcessList, ConnectionList, etc., which return the number of connections.