go to post Vitaliy Serdtsev · Apr 30, 2020 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: configure Undefined=2. I have historically set this value. This can be done programmatically or via SMP 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 }
go to post Vitaliy Serdtsev · Apr 11, 2020 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==
go to post 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 }
go to post 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")
go to post Vitaliy Serdtsev · Mar 28, 2020 With your permission I will add some useful links on the topic: Here can find much more discussion of this project/idea: Declarative development in Caché Another implementation of Declarative ObjectScript: Looking for the equivalent of first-class functions in the Caché DBMS Discussion of this idea/approach on a Russian-language forum (link to machine translation): A declarative approach to the development
go to post 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','')
go to post 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'
go to post 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 "!!".
go to post 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,redAlso 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) } }
go to post Vitaliy Serdtsev · Mar 16, 2020 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?!"),!
go to post Vitaliy Serdtsev · Mar 3, 2020 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
go to post Vitaliy Serdtsev · Mar 3, 2020 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
go to post Vitaliy Serdtsev · Feb 28, 2020 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.
go to post 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" }
go to post Vitaliy Serdtsev · Feb 27, 2020 Hi Evgeny! I can't check for technical reasons. Docker version does not suit me.
go to post 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.
