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.
go to post Vitaliy Serdtsev · Feb 26, 2020 Quote from the documentation: For performance reasons, the predicate %STARTSWITH 'abc' is preferable to the equivalent predicate LIKE 'abc%'. proof
go to post Vitaliy Serdtsev · Feb 26, 2020 I think that the author meant that the simplest queries of the form like 'text%' automatically worked as/converted to %startswith 'text'
go to post Vitaliy Serdtsev · Feb 25, 2020 I would look towards modern frameworks like React, Vue, etc., especially considering that CSP/ZEN/Mojo no longer develops.
go to post Vitaliy Serdtsev · Feb 25, 2020 The CSPBIND value is required at the compile-time to generate code on the client and server side. You try to change this value at runtime. See: CSP Page Lifecycle CSP Elements: Expressions
