go to post Vitaliy Serdtsev · Jan 11, 2020 Try change TranslateTable: Class dc.test [ Abstract ] { ClassMethod runtests() { ;d ##class(dc.test).runtests() s data="тест" w "data: ",?15,data,! s cs=##class(%Stream.FileCharacter).%New(), cs.Filename="C:\Temp\test.txt", cs.TranslateTable="UTF8" d cs.Write(data), cs.%Save() s bstream=##class(%Stream.FileBinary).%New() d bstream.LinkToFile(cs.Filename) w "Binary:",?15,$system.Encryption.Base64Encode($system.Encryption.SHAHashStream(256,bstream)),! s cstream=##class(%Stream.FileCharacter).%New() d cstream.LinkToFile(cs.Filename) w "Char:",?15,$system.Encryption.Base64Encode($system.Encryption.SHAHashStream(256,cstream)),!! s cstream.TranslateTable="" ;or RAW, SAME w "Char->Binary:",?15,$system.Encryption.Base64Encode($system.Encryption.SHAHashStream(256,cstream)) } } USER>d ##class(dc.test).runtests() data: тест Binary: 409t7BLE9FmeugePMa6BOUINIbG9LXztfSKwnCB0+0g= Char: 2eEII+27ZRfvbZvK4XNsx7WPDb+82DymPPOAdJ0p1SQ= Char->Binary: 409t7BLE9FmeugePMa6BOUINIbG9LXztfSKwnCB0+0g=
go to post Vitaliy Serdtsev · Nov 6, 2019 $EXTRACTdoc Keep in mind that Characters are counted from 1. , so instead $EXTRACT({LabName},0,3) should be $EXTRACT({LabName},1,3) In this case, it is uncritical, because If the from value is 0 or a negative number, $EXTRACT returns a null string; however, if from is used with to, a from value of 0 or a negative number is treated as a value of 1. But in other cases it can lead to the wrong result.
go to post Vitaliy Serdtsev · Nov 4, 2019 That would work storagetime but not at runtime. It was just a matter of ease of accessing data using SQL. No more than. Who knows, maybe the author just decided to use SQL to identify duplicates? I think runtime hook disallowing duplicate inserts would be better. I agree. But this is a completely different task and is solved in a different way. Like this: Property Actor As list Of packet.Actor(POPSPEC = ".ActorFilter():10") [ Required ]; Method ActorFilter() As %Integer { set actorID=$random(10)+1 for i=1:1:$order(i%Actor(""),-1) return:actorID=$list(i%Actor(i),1) "" quit actorID }The author after all asked about SQL therefore I above and asked to specify that it is necessary actually.
go to post Vitaliy Serdtsev · Nov 4, 2019 There are a couple of comments: Instead of Class packet.Actor Extends (%Persistent, %Populate) { Property Name As %String [ Required ]; Index NameIndex On Name [ Unique ]; Property Age As %Integer (MAXVAL=100, MINVAL=10) [ Required ]; Index AgeIndex On Age [ Unique ]; Property Sex As %String (DISPLAYLIST=",Woman,Man") [ Required ]; }better Class packet.Actor Extends (%Persistent, %Populate) { Index NameIndex On (Name, Age) [ Unique ]; Property Name As %Name [ Required ]; Property Age As %Integer(MAXVAL = 100, MINVAL = 10) [ Required ]; Property Sex As %String(VALUELIST = ",Woman,Man") [ Required ]; }Otherwise it will not be possible to insert two or more persons with the same age. Or do you specifically want it? Property Actor As list of packet.Actor (POPSPEC=".ActorFilter()") [Required]; This code contradicts the documentation (Specifying the POPSPEC Parameter for List Properties): Leave basicspec empty if the property is a list of objects. However, in this case, ActorFilter() should return strictly one ID, not a collection. The data in the Actor property is stored as $lb($lb(ID1),$lb(ID2),..,$lb(IDN)), therefore, to select all records that have ID=6 in this field, you need to perform select * from packet.Movie where $listbuild('6') %inlist Actor It seems to me that in your case it would be easier to do so: Property Actor As list Of packet.Actor(STORAGEDEFAULT = "array") [ Required ]; Then the query will be simplified: select * from packet.Movie_Actor where Actor=6 Or even so select * from packet.Movie where Movie_Actor->Actor=6
go to post Vitaliy Serdtsev · Oct 31, 2019 Hi, Yana! Give the class code, the source data and an example of what you want to get in the end.
go to post Vitaliy Serdtsev · Oct 30, 2019 If we have a lot of votes will consider to add it. Do we still have to vote for this? On-my enough one moreover, that moderators (you and Dmitry) and engineers InterSystems (Eduard, Alexander, Anton, Sergey, Anastasia, Timur, etc.) - Russian-speaking. I have one problem with it - if we introduce Russian Community, will you stop answering questions in English?) I here like writing code more than words. It will depend on who is asking.
go to post Vitaliy Serdtsev · Oct 30, 2019 Instead of Property Product As %String [ Required ]; Property Item As %String [ Calculated, SqlComputeCode = { s {*}=$e({Product},1,5)}, SqlComputed ]; it is better to use Property Product As %String [ Required ]; Property Item As %String(MAXLEN = 5) [ Required, SqlComputeCode = { s {*}=$e({Product},1,5)}, SqlComputed, SqlComputeOnChange = Product ]; Check on the version 2017.2 can not, but checked on 2018.1 SELECT * FROM Portal.ProductStats ps left JOIN Portal.ProductCacheUpdates pcu ON (pcu.Item=ps.Item) WHERE ps.Item=? ExtentSize=1 (Portal.ProductStats) ExtentSize=1000 (Portal.ProductCacheUpdates) Relative cost = 1338 ◾Read master map Portal.ProductStats.IDKEY, looping on ID. ◾For each row: Read index map Portal.ProductCacheUpdates.pcacheUpdsProd, using the given %SQLUPPER(Item), and looping on ID. For each row: Read master map Portal.ProductCacheUpdates.IDKEY, using the given idkey value. Generate a row padded with NULL for table Portal.ProductCacheUpdates if no row qualified. Output the row. ExtentSize=1000 (Portal.ProductStats) ExtentSize=1 (Portal.ProductCacheUpdates) Relative cost = 1219.2 ◾Read index map Portal.ProductStats.pcacheUpds, using the given %SQLUPPER(Item), and looping on ID. ◾For each row: Read master map Portal.ProductStats.IDKEY, using the given idkey value. Read index map Portal.ProductCacheUpdates.pcacheUpdsProd, using the given %SQLUPPER(Item), and looping on ID. For each row: Read master map Portal.ProductCacheUpdates.IDKEY, using the given idkey value. Generate a row padded with NULL for table Portal.ProductCacheUpdates if no row qualified. Output the row. As you can see in both cases the index pcacheUpdsProd is used. Have you really set up the tables and cleared the cached queries so that the optimizer can start using the new statistics? Try to do it manually in the terminal: blablabla>d $system.SQL.TuneSchema("Portal",1), $SYSTEM.SQL.Purge(), $system.Status.DisplayError($system.OBJ.CompilePackage("Portal","cu-d"))
go to post Vitaliy Serdtsev · Oct 30, 2019 The documentation describes in detail how and why. For example, sometimes the optimizer decides not to use the index if a full crawl would be more efficient. It depends on many parameters: ExtentSize, Selectivity, etc. Give here what Robert asks, and it will be possible to tell more precisely why.
go to post Vitaliy Serdtsev · Oct 30, 2019 [Caché SQL Optimization Guide > Introduction to SQL Performance Optimization] In most cases, helps "tune table". But you can try hints (%ALLINDEX, %FIRSTTABLE, etc.)
go to post Vitaliy Serdtsev · Oct 30, 2019 Try this: update RB_ResEffDateSessPayorRestr set RESTR_DATETo=to_date(DATEADD('year',1,RESTR_DATETo),'YYYY-MM-DD') where YEAR(RESTR_DATETo)=2020Or this: update RB_ResEffDateSessPayorRestr set RESTR_DATETo=%odbcin(DATEADD('year',1,RESTR_DATETo)) where YEAR(RESTR_DATETo)=2020
go to post Vitaliy Serdtsev · Oct 7, 2019 &SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC) similar to the query &SQL(SELECT TOP ALL ID FROM Cinema.Film ORDER BY ID DESC) Therefore, it is natural that the query plans &SQL(SELECT TOP ALL ID FROM Cinema.Film ORDER BY ID DESC) and &SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC) are so significantly different.
go to post Vitaliy Serdtsev · Sep 23, 2019 Class dc.test Extends %RegisteredObject { ClassMethod Run( a, b) { w $$$FormatText("a=%1, b=%2",$g(a,"<null>"),$g(b,"<null>")),! } ClassMethod Test() { s cName="dc.test", mName="Run", args=2, args(1)="2019-01-01", args(2)="1,2,3,4" d $classmethod(cName,mName,args...) k args s args=1, args(1)=77 d $classmethod(cName,mName,args...) k args s args=2, args(2)=33 d $classmethod(cName,mName,args...) k args s args="" d $classmethod(cName,mName,args...) } } Result: USER>d ##class(dc.test).Test() a=2019-01-01, b=1,2,3,4 a=77, b=<null> a=<null>, b=33 a=<null>, b=<null>
go to post Vitaliy Serdtsev · Sep 18, 2019 The field TimeCreated is of type Ens.DataType.UTC. Then so: datepart('hh', %external(TimeCreated))See Data Display Options
go to post Vitaliy Serdtsev · Sep 17, 2019 It's a challenge. Give a reproducible example for "play around". There are a bunch of hints: Query Optimization. @Kyle.Baxter will also be interested in your issue.
go to post Vitaliy Serdtsev · Sep 17, 2019 At me in SMP the following query returns 23: select datepart('hh', {ts '2019-09-10 23:01:45'})
go to post Vitaliy Serdtsev · Jul 30, 2019 By default, the mac address of the computer on which the DBMS instance is running is returned. But you can get the mac address of any other computer, see getmac /?.
go to post Vitaliy Serdtsev · Jul 29, 2019 I checked for versions 2009.1/2010.1: unfortunately, the $zu(114,0) returns nothing, therefore, remains variant with the command line. Example for Windows, provided that the system has a single network card: #include %syConfig n result w $zu(144,1,$$$DEFETHADDR),!,"------",! d $system.OBJ.DisplayError(##class(%Net.Remote.Utility).RunCommandViaCPIPE("getmac /NH /fo table",,.result)) w $p(result," ",1)PS: for other OS command line may be different.
go to post Vitaliy Serdtsev · Jul 25, 2019 I know. After all the author requested the solution on pure M and not COS.
go to post Vitaliy Serdtsev · Jul 25, 2019 Try this: w $zu(114,0) or w $$$DEFETHADDR ; from %syConfig.inc
