All
Accepted answers
Vitaliy Serdtsev · Jul 9, 2020 go to post

select *,
case when a is JSON then 1 else 0 end aa,
case when b is JSON then 1 else 0 end bb,
case when c is JSON then 1 else 0 end cc

from (SELECT 1 a, JSON_OBJECT('id':1) b, '{"id":1}' c)

Vitaliy Serdtsev · Jun 10, 2020 go to post

Here is a ready-made example (works even in last Caché):

Class dc.test Extends %Persistent
{

Property title As %VarString;

Property author As %VarString;

/// do ##class(dc.test).test()
ClassMethod test()
{
  &sql(truncate table dc.test)
  
  &sql(insert into dc.test(title,author)
    select 'For Whom the Bell Tolls','Hemmingway' union
    select 'The Invisible Man','Ellison' union
    select 'Tender is the Night','Fitzgerald')
  
  set provider=##class(%ZEN.Auxiliary.altJSONSQLProvider).%New(),
      provider.sql="select title,author from dc.test",
      provider.arrayName="items",
      provider.maxRows = 0,
      provider.%Format = "tw"
  
  do provider.%WriteJSONStreamFromSQL(.stream,,,,,provider)
  
  set json={}.%FromJSON(stream),
      json.results=json.items.%Size()

  write json.%ToJSON()
}

}

Result:

USER>do ##class(dc.test).test()

{"items":[{"title":"For Whom the Bell Tolls","author":"Hemmingway"},{"title":"The Invisible Man","author":"Ellison"},{"title":"Tender is the Night","author":"Fitzgerald"}],"results":3}
Vitaliy Serdtsev · May 22, 2020 go to post
 

Added several more types:

Class dc.test [ Abstract ]
{

ClassMethod ValType(ByRef var) As %String
{
  s a(-1)="undefined",a(0)="empty",a(1)="string",a(2)="integer",a(3)="float",a(4)="double",a(5)="object",a(6)="list",a(7)="array",a(8)="bitstring"
  s r=$s('$d(var):-1, $isobject(var):5, $d(var)>9:7, var="":0, $lv(var):6, $l(var)>254:1, 1:$tr($a($lb(var),2),1245678,1122334))
  i r=1 {
    s s=##class(%Utility).FormatString(var)
    s:($f(s,"$c(")=4)||($f(s,"$zwc(")=6) r=8
  }
  q a(r)
}

/// d ##class(dc.test).test()
ClassMethod test()
{
  s old=$system.Process.Undefined(2)
  try{

    w ##class(%Utility).FormatString(undef)," => ",..ValType(.undef),!

    f i="","1",1,1.1,$double(1.1),##class(%ListOfDataTypes).%New(),$lb(),$factor(1),$c(7) {
      w ##class(%Utility).FormatString(i)," => ",..ValType(.i),!
    }
    
    s (i,i(1))=""
    w ##class(%Utility).FormatString(i)," => ",..ValType(.i),!

  }catch{
  }
  
  d $system.Process.Undefined(old)
}

}

Result:

USER>d ##class(dc.test).test()
"" => undefined
"" => empty
1 => string
1 => integer
1.1 => float
$double(1.1000000000000000888) => double
"1@%Library.ListOfDataTypes" => object
$lb() => list
$zwc(128,4)_$c(1,0,0,0)/*$bit(1)*/ => bitstring
$c(7) => bitstring
"" => array
Vitaliy Serdtsev · May 20, 2020 go to post

Extending the reply of @Robert Cemper

  • The following query compiles without errors, even though Studio highlights the error 
    &sql(select :fieldname into :fieldvar from :tablename)

    Everything is fine in Caché: the error occurs at the compilation-time.

    In order for an error to occur in IRIS Studio too you need to use the flag /compileembedded=1

  • It is strange that the documentation mentions field name, but there is no mention of table name:
    A host variable cannot be used to pass a field name or keyword into an SQL statement. proof
Vitaliy Serdtsev · May 20, 2020 go to post

In MUMPS, not everything is so simple, so specify technical task ;)

Example: 

  1. USER>set v1=1,v2="1" zzdump v1,v2
 
0000: 31                                                      1
0000: 31                                                      1
  2. what to do with dates:
    30000 is a number or date 20.02.1923?
    or
    "20.02.1923" - is it a date or a string?
  3. what about numbers: 0 is %TinyInt, %SmallInt, or %Integer?
  4. what about boolean: 1 is %Boolean or %Integer?

In addition, you mix MUMPS (variable) and InterSystems ObjectScript (%Library.XXX): see Variable Types

Vitaliy Serdtsev · May 20, 2020 go to post

It can be simpler:

Property HowManyRecords As %Integer [ Calculated, ReadOnly, Required, SqlComputeCode = {n r,SQLCODE &sql(select count(*) into :r from schema_package.table) s {*}=$s(SQLCODE:-1,1:r)}, SqlComputed ];
Vitaliy Serdtsev · May 18, 2020 go to post

Absolutely true.

InterSystems SQL uses this index to improve performance of COUNT(*), which returns the number of records (rows) in the table. proof

Vitaliy Serdtsev · Apr 30, 2020 go to post

Unfortunately, there are errors in the methods GenExportBinaryStream/GenImportBinaryStream in the class %JSON.Generator.

Vitaliy Serdtsev · Apr 30, 2020 go to post

Thank you for asking.
It seems that no one has tried my code in all this time, or it's just that no one is interested in it.

There are two ways to solve the issue:

  1. configure Undefined=2. I have historically set this value. This can be done programmatically or via SMP
  2. fix the code:
    ClassMethod NewQuery1Fetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = NewQuery1Execute ]
{
  if qHandle>1 {
    set Row=qHandle($increment(qHandle,-1))
  } else {
    set AtEnd=1
  }

  quit $$$OK
}
Vitaliy Serdtsev · Apr 11, 2020 go to post
 

Example

Class dc.test [ Abstract ]
{

/// Encode a stream as BASE64
ClassMethod Base64EncodeStream(
  pStream As %Stream.Object,
  Output pEncoded As %Stream.Object) As %Status
{
  try{
     $$$ThrowOnError(pStream.Rewind())

    s pEncoded=##class(%Stream.TmpCharacter).%New()
    while 'pStream.AtEnd {
      s tLen=5700 
      s sc=pEncoded.Write($system.Encryption.Base64Encode(pStream.Read(.tLen)))
      return:$$$ISERR(sc) sc
    }
        
    s sc=pEncoded.Rewind()
  }catch(ex) {
    s sc=ex.AsStatus()
  }
  q sc
}

ClassMethod AESCBCBase64EncryptStream(
  Plaintext As %Stream.Object,
  Output Base64text As %Stream.Object,
  Key As %String,
  IV As %String) As %Status
{
  try{
    $$$ThrowOnError(Plaintext.Rewind())
    
    s length=$$$MaxLocalLength, wasWide=$$$NO
    i $System.Version.IsUnicode() {
      while 'Plaintext.AtEnd && 'wasWide {
        s wasWide=$ZISWIDE(Plaintext.Read(length,.sc)) return:$$$ISERR(sc) sc
      }
      $$$ThrowOnError(Plaintext.Rewind())
    }

    i wasWide {
      s tmp=##class(%IO.StringStream).%New()
      s tmp.CharEncoding="UTF8" 
      d tmp.CopyFrom(Plaintext,,$$$YES,.sc) return:$$$ISERR(sc) sc
      s tmp.CharEncoding="Binary"

      $$$ThrowOnError(Plaintext.Clear())
      $$$ThrowOnError(Plaintext.CopyFrom(tmp))
    }

    s chiperstream=##class(%Stream.TmpCharacter).%New()
    $$$ThrowOnError($system.Encryption.AESCBCEncryptStream(Plaintext,chiperstream,Key,IV))
    s sc=..Base64EncodeStream(chiperstream,.Base64text)
  }catch(ex) {
    s sc=ex.AsStatus()
  }
  q sc
}

/// d ##class(dc.test).Test()
ClassMethod Test(plaintext = {"test"_$c(768)})
{
  s key=$tr($j("",16)," ","0"),
    IV="",
    plainstream=##class(%Stream.TmpCharacter).%New()

  d plainstream.Write(plaintext)
    
  ;w $system.Encryption.Base64Encode($system.Encryption.AESCBCEncrypt($zcvt(plaintext,"O","UTF8"),key,IV)),!
  d $system.OBJ.DisplayError(..AESCBCBase64EncryptStream(plainstream,.base64stream,key,IV))
  w base64stream.Read(),!
}

}

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 go to post 
/// 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 go to post

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 27, 2020 go to post

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 go to post 
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 go to post

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 go to post

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 16, 2020 go to post

You can do this very simply ($get(array.Data("key"),"default")), for example:

set array=##class(%ArrayOfDataTypes).%New()

write $get(array.Data("oops"),"what?!"),!

do array.SetAt("blabla","oops")
   
write $get(array.Data("oops"),"what?!"),!
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"
}