go to post Vitaliy Serdtsev · Nov 11, 2022 size = 79 ClassMethod Order(a As %String) As %String { s r=a f i=1:1:5e5{s s=$p(a," ",i),w=$tr(s,1e20/17),$p(r," ",$tr(s,w))=w} q r }
go to post Vitaliy Serdtsev · Oct 24, 2022 Have you looked at the type of fields in the metadata? For me, the INTEGER type (4) is returned for ID, and the BIGINT type (-5) is returned for %ID. Try SELECT id, x, y, z FROM some_table order by %id and d rs.%GetMetadata().%Display() What are the default values for AdaptiveMode, AllowRowIDUpdate, IdKeyCPF, PKEY_IS_IDKEY ? Try disabling AdaptiveMode.
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
go to post Vitaliy Serdtsev · Oct 20, 2022 Here are two ways: s s="12162,CHAPTER I,Certain infectious and parasitic diseases (A00-B99),003 (A20-A28),Certain zoonotic bacterial diseases,A28,""Other zoonotic bacterial diseases, not elsewhere classified"",A28,""Other zoonotic bacterial diseases, not elsewhere classified"",N,N,N,N,N,,,,,,,,,,G" d ##class(%DeepSee.TermList).%ParseCSVRecord(s,.arr1) zw arr1 w ! s list=$$CSVtoList^%occLibrary(s) d ##class(%ListOfDataTypes).BuildValueArray(list,.arr2) zw arr2 Take a look at the class methods %SQL.Util.Procedures
go to post Vitaliy Serdtsev · Oct 19, 2022 If there was a built-in function for expanding the list into a table, then the solution would be even shorter. An example of such a function: Class dc.golf.Kata [ Abstract ] { Query split( s As %VarString, delim As %VarString) As %Query(ROWSPEC = "word:%String") [ SqlProc ] { } ClassMethod splitExecute( ByRef qHandle As %Binary, s As %VarString, delim As %VarString) As %Status { d ##class(%ListOfDataTypes).BuildValueArray($lfs(s,delim),.qHandle) s qHandle=0 q $$$OK } ClassMethod splitFetch( ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = splitExecute ] { s d=$o(qHandle(qHandle),1,v) i d="" { s AtEnd=1 } else { s Row=$lb(v),qHandle=d } q $$$OK } } Now you can do a variety of things with the string, for example: select min(length(word)) "min",length(word) len,word from dc_golf.Kata_split('bitcoin take over the world maybe who knows perhaps',' ') Result: min len word 3 7 bitcoin 3 4 take 3 4 over 3 3 the 3 5 world 3 5 maybe 3 3 who 3 5 knows 3 7 perhaps
go to post Vitaliy Serdtsev · Oct 7, 2022 There are no such methods, because the size of the stream (file) can exceed the maximum length of the string, which at the moment is 3641144, so the type of string (%String, %VarString, %xsd.base64Binary, etc.) will not always be able to hold all the data. But also it is not difficult to read the stream into a line: ClassMethod StreamToStr(ByRef stream As %Stream.Object) As %String { s s="" d stream.Rewind() while 'stream.AtEnd { s s=s_stream.Read($$$MaxLocalLength) } q s }PS: above I have given methods, some of which are available in older versions of Caché.
go to post Vitaliy Serdtsev · Aug 19, 2022 See documentation for $SYSTEM.SQL.QueryToTable(): THIS METHOD IS DEPRECATED. Refer to $SYSTEM.SQL.Schema.QueryToTable instead.2021.1 Am I correct in saying that a create table as select is strictly as COPY operation of an existing table. Yes, see CREATE TABLE table AS SELECT query copies column definitions and column data from an existing table (or tables) into a new table based on the specified SELECT query.2022.1 To clarify, I used the $SYSTEM command to create table from query, because I kept getting errors with create table as select (didn't like my AS statement). What could have gone wrong there? Benjamin De Boe For that purpose, the CREATE TABLE .. AS SELECT .. syntax is offered by several other databases, and will appear in IRIS SQL soon. As a matter of fact, a developer is working on that command this very week :-) Because this feature has appeared since version IRIS 2022.1 (see link above).
go to post Vitaliy Serdtsev · Aug 4, 2022 well, looks so, but there are no methods, which will return available connections. USER>d $system.License.ConnectionListFunc().%Display() or d $system.License.ProcessListFunc().%Display()
go to post Vitaliy Serdtsev · Aug 4, 2022 Try to play around with the SetConnectionLimit() & SetUserLimit() methods. PS: I do not know what about DBeaver, but DbVisualizer has the ability to configure one physical connection.
go to post Vitaliy Serdtsev · Aug 4, 2022 I think it is necessary to focus on the $System.License.InstanceConnections() PS: and is better in my opinion to use $SYSTEM.License.KeyPlatform(), not $SYSTEM.License.KeyCustomerName()
go to post Vitaliy Serdtsev · Aug 4, 2022 .. s sql=0 s sql($i(sql))="select *" s sql($i(sql))=",%external(DOB)||' '||%external(DOBTime) DOBString" s sql($i(sql))=",TO_CHAR(DOB||','||DOBTime,'MM/DD/YYYY HH24:MI:SS') DOBString2" s sql($i(sql))="from dc.test" d st.%ExecDirect(.st,.sql).%Display() .. Result: USER>d ##class(dc.test).Test() ID(INTEGER) DOB(DATE) DOBTime(TIME) DOBString(VARCHAR) DOBString2(VARCHAR) 1 01.01.2020 23:59:59 01.01.2020 23:59:59 01/01/2020 23:59:59 2 31.12.2022 10:10:10 31.12.2022 10:10:10 12/31/2022 10:10:10
go to post Vitaliy Serdtsev · Aug 4, 2022 I like @Yaron Munz version better Class dc.test [ Abstract ] { ClassMethod Test() { s v=0, v($i(v))=$lb("2021-11-27","2022-08-04"), v($i(v))=$lb("2020-02-28","2023-02-27"), v($i(v))=$lb("2020-02-28","2023-03-01"), v($i(v))=$lb("2017-03-01","2020-02-28") f i=1:1:$o(v(""),-1) { s d1 = $li(v(i),1), d2 = $li(v(i),2), age1 = $e(d2,1,4)-$e(d1,1,4)-($e(d1,6,10)]$e(d2,6,10)), age2 = $number($zabs($zdh(d2,3)-$zdh(d1,3))/365.25,0), age3 = $zdh(d2,3)-$zdh(d1,3)\365.25 w d1," ",d2," (age1 = ",age1,", age2 = ",age2,", age3 = ",age3,")",! } } }Result: USER>d ##class(dc.test).Test() 2021-11-27 2022-08-04 (age1 = 0, age2 = 1, age3 = 0) 2020-02-28 2023-02-27 (age1 = 2, age2 = 3, age3 = 2) 2020-02-28 2023-03-01 (age1 = 3, age2 = 3, age3 = 3) 2017-03-01 2020-02-28 (age1 = 2, age2 = 3, age3 = 2)
go to post Vitaliy Serdtsev · Aug 4, 2022 I am aware that we have 5 License Units on Community Edition. I think this is where the confusion lies, because the documentation says about 5 connections, not license units InterSystems IRIS Community Edition Limitations Resource usage is limited to the following: Total data: 10 GB Connections: 5 Cores: 8
go to post Vitaliy Serdtsev · Aug 4, 2022 See my old article Indexing of non-atomic attributes, chapter "Date (time, etc.)"
go to post Vitaliy Serdtsev · Aug 4, 2022 A simple example of combining: Class dc.test Extends %Persistent { Property DOB As %Date; Property DOBTime As %Time; ClassMethod Test() { d ..%KillExtent() &sql(insert into dc.test(DOB,DOBTime) select {d '2020-01-01'},{t '23:59:59'} union all select {d '2022-12-31'},{t '10:10:10'}) s st=##class(%SQL.Statement).%New() s st.%SelectMode=2 s sql=5 s sql(1)="select *" s sql(2)=",%odbcout(DOB)||' '||%odbcout(DOBTime) DOBString" s sql(3)=",CAST(%odbcout(DOB)||' '||%odbcout(DOBTime) as DATETIME) DOBDateTime" s sql(4)=",UNIX_TIMESTAMP(DOB||','||DOBTime) DOBUnixTimeStamp" s sql(5)="from dc.test" d st.%ExecDirect(.st,.sql).%Display() } }Result: USER>d ##class(dc.test).Test() ID(INTEGER) DOB(DATE) DOBTime(TIME) DOBString(VARCHAR) DOBDateTime(TIMESTAMP) DOBUnixTimeStamp(NUMERIC) 1 01.01.2020 23:59:59 2020-01-01 23:59:59 2020-01-01 23:59:59 1577923199.00 2 31.12.2022 10:10:10 2022-12-31 10:10:10 2022-12-31 10:10:10 1672481410.00 2 Rows(s) Affected
go to post Vitaliy Serdtsev · Aug 4, 2022 Instead of two separate fields, it would be easier to use the %TimeStamp (or %PosixTime) type, where the date and time are paired at once. For this type of data, you can make your own indexes for different parts and/or combinations of them: a separate date, a separate time, a separate year, a separate year and month, etc. You can also use these separate parts in the query (in SELECT and WHERE). select datediff('yy',DOB,current_date) Age from yourtable
go to post Vitaliy Serdtsev · Aug 3, 2022 I have already written, but I will repeat again: not everyone can download from WRC, but only registered customers with paid technical support. There is an exception only for downloading the Community Edition version from WRC through the service evaluation.intersystems.com
