go to post Vitaliy Serdtsev · Sep 14, 2021 Example of using the import/export classes SQL Export Data with %SQL.ExportMgr
go to post Vitaliy Serdtsev · Sep 6, 2021 Caché SQL Optimization Guide New Video: Optimizing SQL Queries New Video: Optimizing Your SQL Queries SQL Performance Resources I think that's enough for a start ;)
go to post Vitaliy Serdtsev · Sep 6, 2021 Here you will find a lot of useful things in particular $QLENGTH: Using Globals
go to post Vitaliy Serdtsev · Aug 20, 2021 See documentation: $ZF(-100) requires the %System_Callout:U privilege. And check "Error Handling".
go to post Vitaliy Serdtsev · Aug 20, 2021 See documentation: Frequently Asked Questions About CSP: How do I debug a CSP page?
go to post Vitaliy Serdtsev · Apr 30, 2021 How do you count "Count"? Why is "Northwest" 1 instead of 2 for 2021? For now so: select v.Branch, nvl(sum(c %FOREACH(v.Branch)),0) "Count" from ( select 'Northwest' Branch,$listbuild('Northern','Western') Branches union select 'Oriental',$listbuild('Eastern') union select 'Southern',$listbuild('Southern') ) v left join (select replace(%exact(Branch),' branch','') Branch,count(* %FOREACH(Branch)) c from yourtable where year("Date")=2021 group by Branch) m on m.Branch %inlist v.Branches group by v.Branch
go to post Vitaliy Serdtsev · Apr 29, 2021 You are implicitly using %Library.SqlQuery:Func() method, in which, as @Robert Cemper rightly pointed out, $get() is used. You can do it differently: Query GetInfo(pObject AS Kurro.MyClass) As %SQLQuery(CONTAINID = 1, ROWSPEC = "IdList:%String,IdProcess:%String,Duration:%String") [ SqlProc ] { SELECT IdList, IdProcess, Duration FROM Kurro.MyClass WHERE KeyProcess = :pObject.KeyProcess AND CodeSpecialist = :pObject.CodeSpecialist AND CodeProvider = :pObject.CodeProvider AND CodeCenter = :pObject.CodeCenter AND "Date" = :pObject.Date } set obj=##class(Kurro.MyClass).%New() set obj.KeyProcess="1033004-1#" set obj.CodeSpecialist = "surgery" set obj.CodeProvider = "PR002" set obj.CodeCenter = "CENTER-01" set obj.Date = $ZDATETIME($ZDATETIMEH("2021-04-30 15:45:00",3,1),3,1) set st=##class(%SQL.Statement).%New() set sc=st.%PrepareClassQuery("Kurro.MyClass","GetInfo") if $$$ISERR(sc) {write "%PrepareClassQuery failed:" do $System.Status.DisplayError(sc) quit} set result=st.%Execute(obj) do result.%Display()
go to post Vitaliy Serdtsev · Apr 16, 2021 You can easily do this by replacing two values in the registry. Just checked on Caché & IRIS: SMP and Terminal (##class(%SYS.System).GetInstanceName()) displays the new instance name. Upgrade/Deinstall work fine too. If desired, you can also replace the service names (private Apache, etc.).
go to post Vitaliy Serdtsev · Apr 12, 2021 Important: Jobbed Process Permissions are Platform-dependent Running Programs or System Commands with $ZF(-100) So that we speak the same language, I made a simple example using Using the Work Queue Manager Class dc.test [ Abstract ] { ClassMethod MyJob(SDIR As %String) { s FILE=##class(%File).NormalizeFilename("DIRLIST.TXT",SDIR) q:##class(%File).GetFileSize(FILE)>0 d ##class(%File).Delete(FILE) s X=$ZF(-1,$$$FormatText("DIR %1 >> %2",$$$quote(##class(%File).NormalizeDirectory(SDIR)),$$$quote(FILE))) } /// d ##class(dc.test).test() ClassMethod test() { s N=4 s queue=$system.WorkMgr.Initialize(,.sc,N) f i=1:1:N d queue.Queue("##class(dc.test).MyJob","C:\Temp\test "_i) d queue.WaitForComplete() } }I copied different files to the following directories: C:\Temp\test 1 C:\Temp\test 2 C:\Temp\test 3 C:\Temp\test 4After calling d ##class(dc.test).test() , everything worked out as expected: DIRLIST.TXT were created in the corresponding directories each with its own content.
go to post Vitaliy Serdtsev · Apr 12, 2021 If something does not suit in the bundle Backup.General:ExternalFreeze()/ExternalThaw(), then can try the bundle SYS.Database:DismountDatabase()/MountDatabase(). Or at all SYS.Database:Copy().
go to post Vitaliy Serdtsev · Apr 12, 2021 See CREATE FUNCTION Create your own counting function: CREATE FUNCTION my.GetCalcTableExtentSize(IN SchemaName SYSNAME, IN TableName SYSNAME) RETURNS BIGINT('') PROCEDURE LANGUAGE OBJECTSCRIPT { quit ##class(%SQL.Manager.Catalog).GetCalcTableExtentSize(SchemaName, TableName) } Now you can use it in queries: select table_schema "Schema", table_name TableName,my.GetCalcTableExtentSize(table_schema,table_name) RowCount from information_schema.tables where table_type in ('BASE TABLE','VIEW')
go to post Vitaliy Serdtsev · Apr 7, 2021 See %Regex.Matcher Example: s text = "This is a sample blob of text", keywords="This,blob,text" s matcher=##class(%Regex.Matcher).%New($tr(keywords,",","|")), matcher.Text=text w:matcher.Locate() "hit",! d matcher.ResetPosition() while matcher.Locate() {write "Found ",matcher.Group," at position ",matcher.Start,!} USER>d ^test hit Found This at position 1 Found blob at position 18 Found text at position 26 Or see $locate: Using Regular Expressions in Caché Example: USER>w $locate(text,$tr(keywords,",","|"),1,e,x) 1
go to post Vitaliy Serdtsev · Apr 2, 2021 I keep my promise (yes, it wasn't an April Fool's joke ;) The first way is associated with a dummy field for the sake of being able to override the final BuildValueArray method and avoid the following error ERROR #5272: Can't change final 'Method' : 'BuildValueArray')Class rcc.IC.ItemList Extends (%Persistent, %Populate) [ Final ] { Index xitmp On (ItemsP(KEYS), ItemsP(ELEMENTS)); Property Company As %String [ Required ]; Property Region As list Of %String(COLLATION = "EXACT", POPSPEC = ":4", VALUELIST = ",US,CD,MX,EU,JP,AU,ZA") [ Required ]; Property Items As list Of rcc.IC.serItem(POPSPEC = ":4") [ Required ]; Property ItemsP As %String(COLLATION = "EXACT") [ Calculated, Private, ReadOnly, Required, SqlComputeCode = {s {*} = {Items}}, SqlComputed ]; ClassMethod ItemsPBuildValueArray( value, ByRef array) As %Status { s ptr=0 while $listnext(value,ptr,val){ s v=$li(val,1), array($li(v,1))="Subject", array($li(v,2))="Change", array($li(v,3))="Color" } q $$$OK } }select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow') and %value='Color') or if need to find values in any fields select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow')) For the sake of speed, you can store in the index not all the fields of the serial class, i.e.: Index xitmp On ItemsP(KEYS); ClassMethod ItemsPBuildValueArray( value, ByRef array) As %Status { s ptr=0 while $listnext(value,ptr,val){ s v=$li(val,1), array($li(v,3))="" ; only Color } q $$$OK }select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow')) You can also add more dummy fields and accordingly indexes to cover all possible queries. The second way involves changing the storage and creating a virtual table Class rcc.IC.ItemList Extends (%Persistent, %Populate) [ Final ] { Index xitm On Items(ELEMENTS).Color; Index xitm1 On (Items(ELEMENTS).Color, Items(KEYS)); Property Company As %String [ Required ]; Property Region As list Of %String(COLLATION = "EXACT", POPSPEC = ":4", VALUELIST = ",US,CD,MX,EU,JP,AU,ZA") [ Required ]; Property Items As list Of rcc.IC.serItem(POPSPEC = ":4", STORAGEDEFAULT = "array") [ Required ]; }select count(distinct ID) from rcc_IC.ItemList where ItemList_Items->Items_Color in ('blue','yellow') -- index "xitm1" is used or even faster select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(Items) (%Value in ('blue','yellow')) -- index "xitm" is used Of course, the data can be accessed from both tables, just do not forget about SetCollectionProjection GetCollectionProjection (for more information, see my article SQL index for array property elements)
go to post Vitaliy Serdtsev · Apr 2, 2021 The %SYS namespace sources are open to study and often (but not always) serve as a coding etalon reference for application developers. This is a whole storehouse of knowledge for those who want to better understand certain mechanisms work of system classes.If some of the commands found there are not documented, but effectively do useful work, then why can't they be used by application developers?In addition, I do not rule out the fact that some of them are simply forgot to document. What really needs to be hidden or potentially dangerous is already hidden in the deployed classes.
go to post Vitaliy Serdtsev · Apr 1, 2021 Hi Robert. I also got carried away with this question and found two more ways to use indexes for a list of serial objects, and you can explicitly specify specific fields in the query, rather than $list (%Value,3). The speed may be not always the best, but I did the best I could. I tested on Caché (perhaps something has been improved in IRIS in this regard?) If you're interested, I can share it.
go to post Vitaliy Serdtsev · Apr 1, 2021 zen(id) works with the component object model, and document.getElementbyId(id) works with the document object model.
go to post Vitaliy Serdtsev · Apr 1, 2021 Try the following: s status=$$DeleteSession^%SYS.cspServer("wuuZ2Gwgxw") This is not documented or supported. Use at your own risk. But since you specified Caché 2018.1, which is no longer being developed so not may change in the future (not counting security patches).