New post
AllAccepted answers
Vitaliy Serdtsev · Jan 14, 2020

You can solve the issue "head-on", namely, to re-convert character stream using %IO.StringStream:

Class dc.test [ Abstract ]
{

ClassMethod test()
{
  s char="тест",
    bin=$zcvt(char,"O","UTF8")

  w "dump char:" zzdump char
  w !!,"dump binary:" zzdump bin

  w !!,"Char:",?21,$system.Encryption.Base64Encode($system.Encryption.SHAHash(256,char)),!,
     "Binary:",?21,$system.Encryption.Base64Encode($system.Encryption.SHAHash(256,bin)),!!
    
  s cs=##class(%Stream.TmpCharacter).%New()
  d cs.Write(char)
  
  s stream=##class(%IO.StringStream).%New()
  s stream.CharEncoding="UTF8" ; here should be the encoding of your CSV file
  d stream.CopyFrom(cs) ; or d stream.CopyFrom(source.Stream)
  s stream.CharEncoding="Binary"

  /*

  Attention! The following code is needed to work around an error in method the SHAHashStream,
  since it expects "Rewind() As %Status", but class the %IO.StringStream uses "Rewind(Output pSC As %Status)"

  */
  
  s bs=##class(%Stream.TmpBinary).%New()
  d bs.CopyFrom(stream)

  w "Char (stream):",?21,$system.Encryption.Base64Encode($system.Encryption.SHAHashStream(256,cs)),!,
  "Binary (stream):",?21,$system.Encryption.Base64Encode($system.Encryption.SHAHashStream(256,bs)),!!
}

}

Result:

dump char:
0000: 0442 0435 0441 0442                                     тест
 
dump binary:
0000: D1 82 D0 B5 D1 81 D1 82                                 Ñ.еÑ.Ñ.
 
Char:                2eEII+27ZRfvbZvK4XNsx7WPDb+82DymPPOAdJ0p1SQ=
Binary:              409t7BLE9FmeugePMa6BOUINIbG9LXztfSKwnCB0+0g=
 
Char (stream):       2eEII+27ZRfvbZvK4XNsx7WPDb+82DymPPOAdJ0p1SQ=
Binary (stream):     409t7BLE9FmeugePMa6BOUINIbG9LXztfSKwnCB0+0g=
Vitaliy Serdtsev · Jan 13, 2020 
COS:

  #include %systemInclude
main()
  s PLIST=2
  s PLIST(1)="line 1"
  s PLIST(2)="multiline 1"_$$$NL_"multiline 2"
  q 1
DELPHI:

procedure TForm1.Button1Click(Sender: TObject);
begin
  VisM.Server:='cn_iptcp:127.0.0.1[1972]:_system:@SYS';
  VisM.NameSpace:='USER';
  VisM.Execute('$$main^dc');
  ListBox.Clear;
  ListBox.Items.Text:=VisM.PLIST;
end;
Result:
line 1
multiline 1
multiline 2
Vitaliy Serdtsev · Jan 11, 2020

Try change TranslateTable:

Class dc.test [ Abstract ]
{

ClassMethod runtests()
{
  ;d ##class(dc.test).runtests()

  s data="тест"
  w "data: ",?15,data,!

  s cs=##class(%Stream.FileCharacter).%New(),
    cs.Filename="C:\Temp\test.txt",
    cs.TranslateTable="UTF8"
  d cs.Write(data),
    cs.%Save()

  s bstream=##class(%Stream.FileBinary).%New()
  d bstream.LinkToFile(cs.Filename)
  w "Binary:",?15,$system.Encryption.Base64Encode($system.Encryption.SHAHashStream(256,bstream)),!

  s cstream=##class(%Stream.FileCharacter).%New()
  d cstream.LinkToFile(cs.Filename)
  w "Char:",?15,$system.Encryption.Base64Encode($system.Encryption.SHAHashStream(256,cstream)),!!
  s cstream.TranslateTable="" ;or RAW, SAME
  w "Char->Binary:",?15,$system.Encryption.Base64Encode($system.Encryption.SHAHashStream(256,cstream))
}

}

USER>d ##class(dc.test).runtests()
data:          тест
Binary:        409t7BLE9FmeugePMa6BOUINIbG9LXztfSKwnCB0+0g=
Char:          2eEII+27ZRfvbZvK4XNsx7WPDb+82DymPPOAdJ0p1SQ=
 
Char->Binary:  409t7BLE9FmeugePMa6BOUINIbG9LXztfSKwnCB0+0g=
Vitaliy Serdtsev · Nov 6, 2019

$EXTRACTdoc

Keep in mind that

Characters are counted from 1.

, so instead

$EXTRACT({LabName},0,3)

should be

$EXTRACT({LabName},1,3)

In this case, it is uncritical, because

If the from value is 0 or a negative number, $EXTRACT returns a null string; however, if from is used with to, a from value of 0 or a negative number is treated as a value of 1.

But in other cases it can lead to the wrong result.

Vitaliy Serdtsev · Nov 4, 2019

That would work storagetime but not at runtime.

It was just a matter of ease of accessing data using SQL. No more than.

Who knows, maybe the author just decided to use SQL to identify duplicates?

I think runtime hook disallowing duplicate inserts would be better.

I agree. But this is a completely different task and is solved in a different way.
Like this:

Property Actor As list Of packet.Actor(POPSPEC = ".ActorFilter():10") [ Required ];

Method ActorFilter() As %Integer
{
  set actorID=$random(10)+1

  for i=1:1:$order(i%Actor(""),-1) return:actorID=$list(i%Actor(i),1) ""

  quit actorID
}

The author after all asked about SQL therefore I above and asked to specify that it is necessary actually.

Vitaliy Serdtsev · Nov 4, 2019

There are a couple of comments:

  1. Instead of 
    Class packet.Actor Extends (%Persistent, %Populate)
{
  Property Name As %String [ Required ];

  Index NameIndex On Name [ Unique ];

  Property Age As %Integer (MAXVAL=100, MINVAL=10) [ Required ];

  Index AgeIndex On Age [ Unique ];

  Property Sex As %String (DISPLAYLIST=",Woman,Man") [ Required ];
}

    better

    Class packet.Actor Extends (%Persistent, %Populate)
{

Index NameIndex On (Name, Age) [ Unique ];

Property Name As %Name [ Required ];

Property Age As %Integer(MAXVAL = 100, MINVAL = 10) [ Required ];

Property Sex As %String(VALUELIST = ",Woman,Man") [ Required ];

}

    Otherwise it will not be possible to insert two or more persons with the same age. Or do you specifically want it?

  2. Property Actor As list of packet.Actor (POPSPEC=".ActorFilter()") [Required];

    This code contradicts the documentation (Specifying the POPSPEC Parameter for List Properties):

    Leave basicspec empty if the property is a list of objects.

    However, in this case, ActorFilter() should return strictly one ID, not a collection.

  3. The data in the Actor property is stored as $lb($lb(ID1),$lb(ID2),..,$lb(IDN)), therefore, to select all records that have ID=6 in this field, you need to perform

    select * from packet.Movie where $listbuild('6') %inlist Actor

It seems to me that in your case it would be easier to do so:

Property Actor As list Of packet.Actor(STORAGEDEFAULT = "array") [ Required ];

Then the query will be simplified:

select * from packet.Movie_Actor where Actor=6

Or even so

select * from packet.Movie where Movie_Actor->Actor=6

Vitaliy Serdtsev · Oct 30, 2019

If we have a lot of votes will consider to add it.

Do we still have to vote for this?

On-my enough one moreover, that moderators (you and Dmitry) and engineers InterSystems (Eduard, Alexander, Anton, Sergey, Anastasia, Timur, etc.) - Russian-speaking.

I have one problem with it - if we introduce Russian Community, will you stop answering questions in English?)

  1. I here like writing code more than words.
  2. It will depend on who is asking.
Vitaliy Serdtsev · Oct 30, 2019
  1. Instead of
    Property Product As %String [ Required ];
Property Item As %String [ Calculated, SqlComputeCode = { s {*}=$e({Product},1,5)}, SqlComputed ];

    it is better to use

    Property Product As %String [ Required ];
Property Item As %String(MAXLEN = 5) [ Required, SqlComputeCode = { s {*}=$e({Product},1,5)}, SqlComputed, SqlComputeOnChange = Product ];
  2. Check on the version 2017.2 can not, but checked on 2018.1 
    SELECT * FROM Portal.ProductStats ps left JOIN  Portal.ProductCacheUpdates pcu ON (pcu.Item=ps.Item) WHERE ps.Item=?
    • ExtentSize=1 (Portal.ProductStats)
      ExtentSize=1000 (Portal.ProductCacheUpdates) 
      
Relative cost  = 1338
◾Read master map Portal.ProductStats.IDKEY, looping on ID.

◾For each row:

 Read index map Portal.ProductCacheUpdates.pcacheUpdsProd, using the given %SQLUPPER(Item), and looping on ID.
 For each row:
 Read master map Portal.ProductCacheUpdates.IDKEY, using the given idkey value.
 Generate a row padded with NULL for table Portal.ProductCacheUpdates if no row qualified.
 Output the row.
    • ExtentSize=1000 (Portal.ProductStats)
      ExtentSize=1 (Portal.ProductCacheUpdates) 
      Relative cost  = 1219.2
◾Read index map Portal.ProductStats.pcacheUpds, using the given %SQLUPPER(Item), and looping on ID.

◾For each row:

 Read master map Portal.ProductStats.IDKEY, using the given idkey value.
 Read index map Portal.ProductCacheUpdates.pcacheUpdsProd, using the given %SQLUPPER(Item), and looping on ID.
 For each row:
 Read master map Portal.ProductCacheUpdates.IDKEY, using the given idkey value.
 Generate a row padded with NULL for table Portal.ProductCacheUpdates if no row qualified.
 Output the row.

      As you can see in both cases the index pcacheUpdsProd is used.

Have you really set up the tables and cleared the cached queries so that the optimizer can start using the new statistics?

Try to do it manually in the terminal:

blablabla>d $system.SQL.TuneSchema("Portal",1), $SYSTEM.SQL.Purge(), $system.Status.DisplayError($system.OBJ.CompilePackage("Portal","cu-d"))
Vitaliy Serdtsev · Sep 23, 2019 
Class dc.test Extends %RegisteredObject
{

ClassMethod Run(
  a,
  b)
{
  w $$$FormatText("a=%1, b=%2",$g(a,"<null>"),$g(b,"<null>")),!
}

ClassMethod Test()
{
  s cName="dc.test",
    mName="Run",

    args=2,
    args(1)="2019-01-01",
    args(2)="1,2,3,4"
    
  d $classmethod(cName,mName,args...)
  
  k args
  s args=1,
    args(1)=77
  d $classmethod(cName,mName,args...)

  k args
  s args=2,
    args(2)=33
  d $classmethod(cName,mName,args...)

  k args
  s args=""
  d $classmethod(cName,mName,args...)
}

}

Result:

USER>d ##class(dc.test).Test()
a=2019-01-01, b=1,2,3,4
a=77, b=<null>
a=<null>, b=33
a=<null>, b=<null>