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 · 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 · Nov 10, 2023 You can convert a list to a string (and vice versa), regardless of the number of nestings. Unfortunately, I can't test this code for Caché 5.x, but I think it should work. Here is a small example of searching for a string in a list: #include %systemInclude n s list=$lb( "test", "for", "searching unknown strings here is a very long piece with enough characters to get a lowercase alpha as a $list marker", "items", "in", "aaatArGetwaaaa", "lists", $lb( "/subs", "/values", "nested list", "did you see that ""w"" before the third piece?", "Stuart Strickland", "Yaron Munz") ) s str=$$$UPPER(##class(%Utility).FormatString(list,,.overflow)) i 'overflow { ;s @("LIST="_str) zw LIST w !,$f(str,$$$UPPER("Targetw")) }Result: 162
go to post Vitaliy Serdtsev · Aug 24, 2023 Alternatively, you can create a custom task that runs every 15 minutes, in which to enable disabled users. Since there are other ways to connect besides CSP, this will be more universal. PS: why not disable "Disable account if login limit reached" and reduce Invalid login limit?
go to post Vitaliy Serdtsev · Aug 23, 2023 Look at the class %SYSTEM.CSP: CSS>d $system.CSP.DisplayConfig() ... CSS>w $system.CSP.GetConfig("DefaultPasswordChangePage") %CSP.PasswordChange.cls CSS>d $system.CSP.SetConfig("DefaultPasswordChangePage","CSS.CSP.ChangePassword.cls") CSS>w $system.CSP.GetConfig("DefaultPasswordChangePage") CSS.CSP.ChangePassword.cls
go to post Vitaliy Serdtsev · Aug 22, 2023 The problem can be solved in two ways: use OPTIONS="popup,sortbox" make a correction to the %CSP.PageLookup Write " <a href=""javascript:searchSort("_..QuoteJS(value)_");"" title=""Sort Results by "_alias_""">" | V Write " <a href=""javascript:searchSort("_i_");"" title=""Sort Results by "_alias_""">" In both case, the query will take the form "ORDER BY <the ordinal number of the field>", instead of "ORDER BY <field name>"
go to post Vitaliy Serdtsev · Aug 11, 2023 One of the possible options: ClassMethod odbcTest() As %Integer [ ReturnResultsets, SqlName = PersonSets2, SqlProc ] { #dim %sqlcontext As %ProcedureContext if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() } s tReturn = 0 s conn=##class(%SQLGatewayConnection).%New() s sc=conn.Connect("TEST Samples","_system","SYS") //datasource if $$$ISOK(sc) { d conn.AllocateStatement(.h1) d conn.Prepare(h1,"select name,dob,spouse from sample.person where name %STARTSWITH 'A'") d conn.Execute(h1) d %sqlcontext.AddResultSet(conn.getResultSet(h1)) d conn.AllocateStatement(.h2) d conn.Prepare(h2,"select name,age,home_city,home_state from sample.person where home_state = 'MA'") d conn.Execute(h2) d %sqlcontext.AddResultSet(conn.getResultSet(h2)) s tReturn = 1 }else{ s sqlcode=$system.Status.StatusToSQLCODE(sc,.msg) s %sqlcontext.%SQLCODE = sqlcode, %sqlcontext.%Message = msg } q tReturn }Output: SAMPLES>d ##class(%SQL.Statement).%ExecDirect(,"call Sample.PersonSets2()").%Display() ...Surely there is a way to make it even easier.
go to post Vitaliy Serdtsev · Aug 10, 2023 Try this: Set objcontato=##class(Contatos.Amiguinho).%New() Do objcontato.MoradiaSetObjectId(3) Do objcontato.TrabalhoSetObjectId(2) Set ret=objcontato.%Save()See Fastest Way to Connect Objects
go to post Vitaliy Serdtsev · Jul 4, 2023 Your question has already been answered here: Dynamic SQL and Setting SQLCODE
go to post Vitaliy Serdtsev · Mar 7, 2023 CREATE PROCEDURE DeleteSpecimenTwo (IN Specno VARCHAR(50)) LANGUAGE OBJECTSCRIPT { NEW SQLCODE,%ROWID,%ROWCOUNT #SQLCOMPILE SELECT=LOGICAL &sql(DELETE FROM PCONT WHERE PSPECNO = :Specno) If $Get(%sqlcontext)'=$$$NULLOREF Set %sqlcontext.%SQLCODE=SQLCODE,%sqlcontext.%ROWCOUNT=%ROWCOUNT Quit:SQLCODE<0 &sql(DELETE FROM PSPEC WHERE PSPECNO = :Specno) If $Get(%sqlcontext)'=$$$NULLOREF Set %sqlcontext.%SQLCODE=SQLCODE,%sqlcontext.%ROWCOUNT=%ROWCOUNT }
go to post Vitaliy Serdtsev · Feb 20, 2023 #include %occInclude n alg,algInfo f alg="RSA-OAEP","A256KW","A256CBC-HS512","A256GCM" { s algInfo=##class(%Net.JSON.JWA).GetAlgInfo(alg) w alg," = ",$s(algInfo'="":algInfo.%ToJSON(),1:$system.Status.GetErrorText($$$ERROR($$$UnsupportedJOSEAlg,alg))),! }Output: RSA-OAEP = {"alg":"RSA-OAEP","kty":"RSA","use":"enc","key_ops":["wrapKey","unwrapKey"],"keysize":2048,"hashsize":0} A256KW = {"alg":"A256KW","kty":"oct","use":"enc","key_ops":["wrapKey","unwrapKey"],"keysize":256,"hashsize":0} A256CBC-HS512 = {"alg":"A256CBC-HS512","kty":"oct","use":"enc","key_ops":["encrypt","decrypt"],"keysize":256,"hashsize":512} A256GCM = Error #9723: Unsupported JOSE algorithm: A256GCMSince this algorithm is not supported, it remains either to try to modify the GetAlgInfo method or to work directly with the OpenSSL library or similar.
go to post Vitaliy Serdtsev · Feb 17, 2023 Try working directly with the private key file, for example: #include %msql s f=##class(%Stream.FileBinary).%New() s f.Filename="С:\your_private_key.pem" s privateKey=f.Read($$$MaxLocalLength) s myString = "text to sign" s signedTxt = ##class(%SYSTEM.Encryption).RSASHASign(256, $zcvt(myString,"O","UTF8"), privateKey) zw signedTxtThis code works for me.
go to post Vitaliy Serdtsev · Feb 1, 2023 For Caché: $$GroupJob^%SYS.WorkQueueMgr($system.Context.WorkMgr().MasterGroup)
go to post Vitaliy Serdtsev · Jan 31, 2023 Is there any harm in putting in a massive number into the len parameter of the Read() method? You cannot read more than 3641144 characters from a stream to a string, as this is the limit for long strings: String Length Limit From the documentation for the Read() method: If no len is passed in, ie. 'Read()' then it is up to the Read implementation as to how much data to return. Some stream classes use this to optimize the amount of data returned to align this with the underlying storage of the stream. So you can write like this: Set inMsg = %request.Content.Read($$$MaxLocalLength) Macros are defined in %msql.inc: #define MaxStringLength 3641144 #define MaxLocalLength $zutil(96,39)
go to post Vitaliy Serdtsev · Jan 31, 2023 Instead of $ZPARENT, try using $system.Context.WorkMgr().MasterJob See your local documentation: MasterJob2022.1 ParentJob2022.2 PS: This is done differently for Caché.
go to post Vitaliy Serdtsev · Jan 23, 2023 It is necessary to take into account the following points: according to the documentation should be // add the columns to export Do mgr.ColumnNames.Insert("Closed") Do mgr.ColumnTypes.Insert("N") Do mgr.ColumnNames.Insert("DocumentType") Do mgr.ColumnTypes.Insert("S") Do mgr.ColumnNames.Insert("StatusCode") Do mgr.ColumnTypes.Insert("N") Do mgr.ColumnNames.Insert("StatusLastUpdated") Do mgr.ColumnTypes.Insert("TS") StringQuote only affects when escaping the corresponding characters, for example: Set mgr.StringQuote = $c(34) // double quotes a"b -> "a""b" Set mgr.StringQuote = $c(39) // ' a'b -> 'a''b' DateFormat and TimeFormat are applicable only for fields of type D and T, respectively, but you have a field of type TS TimeStampFormat is applicable only for import, but not for export
go to post Vitaliy Serdtsev · Dec 1, 2022 It is not entirely clear that you are using Caché or IRIS 2021.1: #Caché I have two tables: one a local table using cache and the other is via an SQL gateway.. Product version: IRIS 2021.1 The presence/absence of restrictions on the execution of heterogeneous queries depends on this: Caché 2018.1.7: All the tables listed in the FROM clause of an SQL query must come from the same data source. Queries that join data from heterogeneous data sources are not allowed. IRIS 2021.1: Queries that join data from heterogeneous data sources are only supported for ODBC connections. When using JDBC, all tables listed in the FROM clause of an SQL query must come from the same data source.
go to post Vitaliy Serdtsev · Oct 24, 2022 There is a faster way without opening the object: Class dc.test Extends %Persistent { ClassMethod Test() { s classname="dc.test", storagename="Default" w $$$defMemberKeyGet(classname,$$$cCLASSstorage,storagename,$$$cSDEFdatalocation) } Storage Default { <Data name="testDefaultData"> <Value name="1"> <Value>%%CLASSNAME</Value> </Value> </Data> <DataLocation>^abc.1</DataLocation> <DefaultData>testDefaultData</DefaultData> <IdLocation>^dc.testD</IdLocation> <IndexLocation>^dc.testI</IndexLocation> <StreamLocation>^dc.testS</StreamLocation> <Type>%Library.CacheStorage</Type> } }Result: ^abc.1