New post
AllAccepted answers
Vitaliy Serdtsev · Apr 11, 2020
 
Example

Result:

USER>d ##class(dc.test).Test("test"_$c(68))
gTNg0UMkvQ3o+ehJkvr6lA==

USER>d ##class(dc.test).Test("test"_$c(768))
R8UuZkjDVZidYckYMTpnVg==

USER>d ##class(dc.test).Test("测试")
lsYxFAQgNtiXHyaeGTWJ0A==
Vitaliy Serdtsev · Apr 8, 2020 
/// Encode a stream as BASE64
ClassMethod Base64EncodeStream(
  pStream As %Stream,
  Output pEncoded As %Stream) As %Status
{
  s tSC=$$$OK
  try {
    s tSC=pStream.Rewind()
    q:$$$ISERR(tSC)

    s pEncoded=##class(%Stream.TmpCharacter).%New()
    while 'pStream.AtEnd {
      s tLen=5700 
      s tSC=pEncoded.Write($system.Encryption.Base64Encode(pStream.Read(.tLen)))
      q:$$$ISERR(tSC)
    }
    q:$$$ISERR(tSC)
        
    s tSC=pEncoded.Rewind()
  } catch (e) {
    s tSC=e.AsStatus()
  }
  q tSC
}
Vitaliy Serdtsev · Apr 2, 2020

Or something like that:

Class dc.test Extends %Persistent
{

Property Name As %String;

Property Age As %Numeric;

Property City As %String;

Property Phone As %String;

/// d ##class(dc.test).Test()
ClassMethod Test()
{
    do ..%KillExtent()
    
    // This is your Data-Object...

    ;set data="",data=[],data=..%New()

    set data=[
      {"Name":"Joe", "Age":44, "City":"Boston", "Phone":"1-234-4567"},
      {"Name":"Ron", "Age":48, "City":"Dallas", "Phone":"1-234-5678"},
      {"Name":"Eve", "Age":40, "City":"Miami",  "Phone":"1-234-4567"},
      ($listbuild("Tommy", 50, "New York", "1-345-6789")),
      ($listbuild("Alexa", 35, "Portland", "1-567-8901"))
    ]

    // Now insert all the above data into your table...
    &sql(
      INSERT INTO dc.test(Name,Age,City,Phone)
      SELECT $list(row,1),$list(row,2),$list(row,3),$list(row,4) 
      FROM dc.DataProvider(:data)
    )
    
    zwrite ^dc.testD

    // Now update your table...
    set data=[($listbuild("Vitaliy", 35, "Moscow", "1-999-1234"))]

    &sql(
      UPDATE dc.test t
      SET t.Name=obj.Name
      FROM (SELECT $list(row,1) Name,$list(row,2) Age  FROM dc.DataProvider(:data)) obj
      WHERE t.Age=obj.Age
    )
    
    zwrite ^dc.testD
}

Query NewQuery1(data) As %Query(ROWSPEC = "row:%List") [ SqlName = DataProvider, SqlProc ]
{
}

ClassMethod NewQuery1Execute(
  ByRef qHandle As %Binary,
  data) As %Status
{
  if $IsObject(data),data.%IsA("%Library.DynamicArray"),data.%Size() {

    set qHandle=data.%Size()+1

    for i=1:1:data.%Size() {
      set r=data.%Get(i-1)

      if $IsObject(r) {
        set it=r.%GetIterator(),row=""
        while it.%GetNext(,.val) { set row=row_$listbuild(val) } 

      } else {
        set row=r
      }
      set qHandle(qHandle-i)=$listbuild(row)
    }

  }

  quit $$$OK
}

ClassMethod NewQuery1Fetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = NewQuery1Execute ]
{
  if qHandle {
    set Row=qHandle($increment(qHandle,-1))
  } else {
    set AtEnd=1
  }

  quit $$$OK
}

ClassMethod NewQuery1Close(ByRef qHandle As %Binary) As %Status [ PlaceAfter = NewQuery1Execute ]
{
  quit $$$OK
}

Storage Default
{
<Data name="testDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>Name</Value>
</Value>
<Value name="3">
<Value>Age</Value>
</Value>
<Value name="4">
<Value>City</Value>
</Value>
<Value name="5">
<Value>Phone</Value>
</Value>
</Data>
<DataLocation>^dc.testD</DataLocation>
<DefaultData>testDefaultData</DefaultData>
<IdLocation>^dc.testD</IdLocation>
<IndexLocation>^dc.testI</IndexLocation>
<StreamLocation>^dc.testS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

Result:

USER>d ##class(dc.test).Test()
^dc.testD=5
^dc.testD(1)=$lb("","Joe",44,"Boston","1-234-4567")
^dc.testD(2)=$lb("","Ron",48,"Dallas","1-234-5678")
^dc.testD(3)=$lb("","Eve",40,"Miami","1-234-4567")
^dc.testD(4)=$lb("","Tommy",50,"New York","1-345-6789")
^dc.testD(5)=$lb("","Alexa",35,"Portland","1-567-8901")
^dc.testD=5
^dc.testD(1)=$lb("","Joe",44,"Boston","1-234-4567")
^dc.testD(2)=$lb("","Ron",48,"Dallas","1-234-5678")
^dc.testD(3)=$lb("","Eve",40,"Miami","1-234-4567")
^dc.testD(4)=$lb("","Tommy",50,"New York","1-345-6789")
^dc.testD(5)=$lb("","Vitaliy",35,"Portland","1-567-8901")
Vitaliy Serdtsev · Mar 28, 2020

With your permission I will add some useful links on the topic:

Vitaliy Serdtsev · Mar 27, 2020

Using my simple example, when the method receive a name param or a age param to build the where clause. I really don't know how to solve it using query class.

Query FilterBy(
  Name As %String = "",
  Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM Sample.Person
WHERE 
(nvl(:Name,'')='' or Name %STARTSWITH :Name)
AND
(nvl(:Age,'')='' or Age >= :Age)
}

Run Examples:
select * from Sample.SP_Sample_Filter_By(,47)
select * from Sample.SP_Sample_Filter_By('',47)

select * from Sample.SP_Sample_Filter_By('s',47)

select * from Sample.SP_Sample_Filter_By('s')
select * from Sample.SP_Sample_Filter_By('s','')
Vitaliy Serdtsev · Mar 19, 2020 
SELECT 

NVL(order_description,'') ||

CASE WHEN NVL(dosage,'')='' OR NVL(dosage_unit,'')='' THEN ''
     ELSE ', ' || dosage || ' ' || dosage_unit || CASE WHEN NVL(dosage_form,'')='' THEN ''
                                                       ELSE ' ' || dosage_form
                                                  END || ''
END ||

CASE WHEN NVL(administration_route,'')='' THEN ''
     ELSE ', ' || administration_route
END ||

CASE WHEN NVL(frequency_description,'')='' THEN ''
     ELSE ', ' || CASE WHEN NVL(quantity,'')='' OR NVL(quantity_unit,'')='' THEN ''
                       ELSE quantity || ' ' || quantity_unit || ' '
                  END || frequency_description
END ||

IFNULL(start_date,'',', ' || 'Start Date: ' || CONVERT(SQL_DATE,start_date,101)) As MedicationOrder

FROM CWSOrderEntry.history_client_order
WHERE PATID='100068'
--AND FACILITY=?FACILITY
AND order_status_code='A'
AND order_type_category_code='P'
Vitaliy Serdtsev · Mar 19, 2020

Symbols Used in Caché SQL

SELECT 
(CASE WHEN (order_description IS NULL OR (order_description='')) THEN '' ELSE (order_description) END) ||
(CASE WHEN (dosage IS NULL OR dosage_unit IS NULL OR (dosage='') OR (dosage_unit='')) THEN ''
ELSE (', ' || dosage || ' ' || dosage_unit || (CASE WHEN (dosage_form IS NULL OR (dosage_form='')) THEN '' ELSE (' ' || dosage_form) END) || '') END) ||
(CASE WHEN (administration_route IS NULL OR (administration_route='')) THEN '' ELSE (', ' || administration_route) END) ||
(CASE WHEN (frequency_description IS NULL OR (frequency_description='')) THEN '' ELSE (', ' || (CASE WHEN (quantity IS NULL OR quantity_unit IS NULL OR (quantity='') OR (quantity_unit='')) THEN '' ELSE (quantity || ' ' || quantity_unit || ' ') END) || frequency_description) END) ||
(CASE WHEN start_date IS NULL THEN '' ELSE (', ' || 'Start Date: ' || CONVERT(SQL_DATE,start_date,101)) END) As MedicationOrder
FROM CWSOrderEntry.history_client_order
WHERE PATID='100068'
--AND FACILITY=?FACILITY
AND order_status_code='A'
AND order_type_category_code='P'

Try changing the font, it's possible that you just have "||" displayed as "!!".

Vitaliy Serdtsev · Mar 18, 2020

Using the JSON Adaptor.

Simple example:

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

Property list As list Of %String;

/// d ##class(dc.test).test()
ClassMethod test()
{
  s json={"list":["green","yellow,red","blue"]}
  
  s t=..%New()
  d t.%JSONImport(json)
  w t.list.Count(),!,t.list.GetAt(2)
}

}

USER>d ##class(dc.test).test()
3
yellow,red

Also look at Using Document Database (DocDB).

Another option without %ZEN.Auxiliary:

Class dc.mylist Extends %ListOfDataTypes
{

Method SizeSet(newvalue As %Integer) As %Status
{
  s i%Size=newvalue
  q $$$OK
}

}


Class dc.test [ Abstract ]
{

/// d ##class(dc.test).test()
ClassMethod test()
{
  s json=["green","yellow,red","blue"]
  s t=##class(%Document.Object).CSON(json.%ToJSON())
  
  s l=##class(dc.mylist).%New()
  
  m l.Data=t."_data"
  zk l.Data
  s l.Size=t.Count()
  
  w l.Count(),!,l.GetAt(2)
}

}
Vitaliy Serdtsev · Mar 3, 2020

Defining and Using Stored Procedures

 
Source code
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

Defining Stored Procedures

 
Source code
Result: 
select * from dc.numbers(107)
n
1
2
...
105
106
107
Vitaliy Serdtsev · Feb 27, 2020

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

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.