go to post Vitaliy Serdtsev · Apr 11 I do not observe any differences in the behavior of xDBC and Portal. Given: Class dc.test Extends %Persistent { Property s1 As %String(MAXLEN = 2, TRUNCATE = 1); Property s2 As %String(MAXLEN = 2); } insert into dc.test(s1,s2)values('abc','ab') Result ID s1 s2 1 ab ab insert into dc.test(s1,s2)values('abc','abc') Result for any JDBC/ODBC tools (DBVisualizer, SQL Data Lens, etc.): [SQLCODE: <-104>:<Field validation failed in INSERT>] <Field 'dc.test.s2' Truncation (Varchar Value: 'abc ...' Length: 3) > maxlen: (2)>] Result for Management Portal: [SQLCODE: <-104>:<Field validation failed in INSERT>] [%msg: <Field 'dc.test.s2' (value 'abc') failed validation>]
go to post Vitaliy Serdtsev · Apr 10 Check your TRUNCATE parameter. Common Parameters:TRUNCATE TRUNCATE — Specifies whether to truncate the string to MAXLEN characters, where 1 is TRUE and 0 is FALSE. This parameter is used by the Normalize() and IsValid() methods but is not used by database drivers. Class dc.test Extends %Persistent { Property s1 As %String(TRUNCATE = 1); Property s2 As %String; ClassMethod Test() { d ..%KillExtent(,$$$YES) s t=..%New() s t.s1=$tr($j("",51)," ","a") s sc=t.%Save() w "s1: ",$s($$$ISERR(sc):$system.Status.GetErrorText(sc),1:"OK"),! s t=..%New() s t.s2=$tr($j("",51)," ","a") s sc=t.%Save() w "s2: ",$s($$$ISERR(sc):$system.Status.GetErrorText(sc),1:"OK"),! } } USER>d ##class(dc.test).Test() s1: OK s2: ERROR #7201: Datatype value 'aaaa..aa' length longer than MAXLEN allowed of 50 > ERROR #5802: Datatype validation failed on property 'dc.test:s2', with value equal to "aaaa..aa" The same error occurs in the Management Portal.
go to post Vitaliy Serdtsev · Apr 10 MAXLEN is not applicable for %GlobalCharacterStream (Blob). You will get compilation error #5480
go to post Vitaliy Serdtsev · Apr 4 select lpad(s\3600,2,0)||':'||lpad(s\60#60,2,0)||':'||lpad((s)#3600#60,2,0) diff from (select DATEDIFF('s','2024-04-01 09:13:46','2024-04-01 11:11:44') s)
go to post Vitaliy Serdtsev · Apr 2 I noticed that you are using the {Stream} syntax, which is typical for trigger code or calculated field. Could you check the following code at your place: Class dc.test Extends %Persistent { Property stream As %GlobalCharacterStream; Trigger NewTrigger1 [ Event = INSERT ] { i $IsObject({stream}) { s t={stream} s ^||tmp=t.Size } } ClassMethod Test() { d ..%KillExtent(,$$$YES) s f=##class(%Stream.FileBinary).%New() d f.LinkToFile("C:\test.jpg") &sql(insert into dc.test(stream) values(:f)) i 'SQLCODE { &sql(select CHAR_LENGTH(stream) into :len from dc.test where %id=1) w len," ",^||tmp,! } } }I have saved a 16 MB file without any problems: USER>d ##class(dc.test).Test() 16446809 16446809
go to post Vitaliy Serdtsev · Apr 1 The resulting archive is easily recognized/unpacked in WinRAR, 7z: s f=##class(%Stream.FileCharacterGzip).%New() s f.TranslateTable="UTF8" s f.Filename="C:\test.gz" d f.WriteLine("test") d f.Write("Привет Caché") d f.%Save()
go to post Vitaliy Serdtsev · Mar 29 See Defining Custom Class Queries Example of a stored procedure Class dc.test [ Abstract ] { Query Intervals( start As %TimeStamp, end As %TimeStamp, minute As %TinyInt) As %Query(ROWSPEC = "intStart:%PosixTime,intEnd:%PosixTime") [ SqlName = Intervals, SqlProc ] { } ClassMethod IntervalsExecute( ByRef qHandle As %Binary, start As %TimeStamp, end As %TimeStamp, minute As %TinyInt) As %Status { s qHandle(0)=##class(%PosixTime).OdbcToLogical(start), qHandle(1)=##class(%PosixTime).OdbcToLogical(end), qHandle=minute q $$$OK } ClassMethod IntervalsFetch( ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = IntervalsExecute ] { i qHandle(0)<=qHandle(1) { s j=$system.SQL.Functions.DATEADD("minute",qHandle,qHandle(0)), Row=$lb(qHandle(0),$s(j>=qHandle(1):qHandle(1),1:j-1)), qHandle(0)=j=qHandle(1)+j }else{ s Row="", AtEnd=$$$YES } q $$$OK } ClassMethod IntervalsClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = IntervalsExecute ] { q $$$OK } } The result of calling a stored procedure in the Management Portal: SELECT * FROM dc.Intervals({ts '2024-01-01 10:00:00'},{ts '2024-01-01 11:00:00'},15) Display/ODBC Mode intStart intEnd 2024-01-01 10:00:00 2024-01-01 10:14:59.999999 2024-01-01 10:15:00 2024-01-01 10:29:59.999999 2024-01-01 10:30:00 2024-01-01 10:44:59.999999 2024-01-01 10:45:00 2024-01-01 11:00:00.000000 Logical Mode intStart intEnd 1154625607806846976 1154625608706846975 1154625608706846976 1154625609606846975 1154625609606846976 1154625610506846975 1154625610506846976 1154625611406846976 Accordingly, your query needs to be rewritten, for example: SELECT intStart, intEnd, COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'GENERATE' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) generatedCount, COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'FINISH' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) finishedCount, COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'DROPOUT' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) dropoutCount, COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'CANCEL' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) cancelledCount FROM dc.Intervals({ts '2024-01-01 10:00:00'},{ts '2024-01-01 11:00:00'},15)
go to post Vitaliy Serdtsev · Mar 26 I think it will also be useful to mention here the links to the documentation: Optimizing SQLIRIS (Best Practices for Improving SQL Performance, Configure SQL Performance Options, etc.) Performance and Programming ConsiderationsCaché (for ECP) Performance tips for Business IntelligenceIRIS (DeepSee) Performance&Security Tips and InformationIRIS (for Persistent Classes)
go to post Vitaliy Serdtsev · Mar 25 I would like to supplement the above with such parameters as STORAGEDEFAULT, SQLTABLENAME, and SQLPROJECTION: Storage and SQL Projection of Collection Properties However a collection property is actually stored, the property can be projected as a column in the parent table, as a child table, or in both ways (as of release 2022.1, this is true for both list and array properties). To control this, you specify the SQLPROJECTION parameter of the property.
go to post Vitaliy Serdtsev · Mar 15 Thanks for the hint, I fixed it. By the way, your code can be reduced to 173: f i=$i(r):1:a
go to post Vitaliy Serdtsev · Mar 15 There is no limit to perfection. By the way, your code shows 175 characters, where did the number 174 come from?
go to post Vitaliy Serdtsev · Mar 13 size = 207 195 179 ClassMethod Type(a...) As %String { f j=$i(r):1:a{s w=$tr(a(j)," "),p=$f(w,",")-2 f i=2:1:$l(w,",") s c=$l($p(w,",",i)),r=$s(p=c:r,r+p-c<2:2,c<p*2#r:3,1:4),p=c} q $p("Constant7Increasing7Decreasing7Unsorted",7,r) }
go to post Vitaliy Serdtsev · Mar 1 Try this: ClassMethod test2() { set tKP = ..%New() set tIdentifierSerial = ##class(HS.Message.AddUpdateHubRequest).%OpenId(21986071).Identifiers zw tIdentifierSerial set tKP.IsSerial = 1 set tKP.IsList = 1 set tSerial = tIdentifierSerial.GetObjectNext(.tKey) for { if tKey="" {quit} do tKP.SerialList.InsertObject(tSerial) set tSerial = tIdentifierSerial.GetObjectNext(.tKey) } w tKP.%Save() }
go to post Vitaliy Serdtsev · Feb 20 As far as I understand, the author wants to calculate the value of a field based on some list of fields whose names he takes from some table. Right?
go to post Vitaliy Serdtsev · Feb 20 Try inserting an apostrophe through concatenation, something like INSERT INTO MyText (text) VALUES ('I visited O' || CHAR(39) || 'Brien before heading out of town.')
