All
Accepted answers
Vitaliy Serdtsev · Oct 19, 2022 go to post

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
Vitaliy Serdtsev · Oct 7, 2022 go to post

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é.

Vitaliy Serdtsev · Aug 19, 2022 go to post
Vitaliy Serdtsev · Aug 4, 2022 go to post

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()

Vitaliy Serdtsev · Aug 4, 2022 go to post

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.

Vitaliy Serdtsev · Aug 4, 2022 go to post 

..
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

Vitaliy Serdtsev · Aug 4, 2022 go to post

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)
Vitaliy Serdtsev · Aug 4, 2022 go to post

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

Vitaliy Serdtsev · Aug 4, 2022 go to post

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

Vitaliy Serdtsev · Aug 3, 2022 go to post

The very first message contains a link from where you can download the ODBC-2022.1.0.209.0 driver for different operating systems.

Vitaliy Serdtsev · Aug 3, 2022 go to post

What does the JDBC driver have to do with the question about the ODBC driver?

Unlike JDBC, the ODBC driver still yet needs to be registered in the OS (at least for Windows), which is what the installer does.

Vitaliy Serdtsev · Jul 19, 2022 go to post

Made some minor changes to your code and now everything works as expected. 

ClassMethod ActivateSQL(customerId) As %Status
{
  ...
  Quit $$$OK
}


ClassMethod ActivateOO(customerId) As %Status
{
  ...
  Quit $$$OK
}


If mode=0 {
  Do $system.OBJ.DisplayError(objCust.ActivateOO(id))
} else {
  Do $system.OBJ.DisplayError(objCust.ActivateSQL(id))
  Set objCust.Active = objCust.ActiveGetStored(id)
}


Or


If mode=0 {
 Do $system.OBJ.DisplayError(objCust.ActivateOO(id))
} else {
 Do $system.OBJ.DisplayError(objCust.ActivateSQL(id))
 Do objCust.%Reload()
}

Vitaliy Serdtsev · Jul 19, 2022 go to post

If this feature is not available in the Community Edition, then it makes no sense for me to try it. I just thought that in the preview version, all the features for testing are available. But thanks anyway for the quick response.

Vitaliy Serdtsev · Jul 19, 2022 go to post

Let's say you need to write a classmethod that updates a single property.
Update where: in memory or on disk?

Using SQL, you cannot update the value of a field in memory, but only on disk. On the other hand, OpenId() reads data from disk and knows nothing about the changes on disk that occurred after its call.

To avoid confusion, I would look to the side Version Checking (Alternative to Concurrency Argument) or/and <propertyname>GetStored()

Vitaliy Serdtsev · Jul 19, 2022 go to post

I decided to try Columnar Storage (IRIS CE):

Property p As %String(STORAGEDEFAULT = "columnar");

When compiling a class, I get the following error:

ERROR #15804: Columnar Storage (STORAGEDEFAULT=COLUMNAR) is not available with this license

I turn to the documentation to find out what's the matter:

Vitaliy Serdtsev · Jul 8, 2022 go to post
 

size = 201 (single line)

ClassMethod Convert(a As %String) As %String
{
 s x=$lfs("A,4,@,B,|3,8,C,(,<,E,3,€,G,9,6,I,|,],K,|<,|{,L,1,£,O,0,*,S,5,$,T,7,+,X,><,}{,Z,2,~/_") f i=1:1:$l(a){s c=$e(a,i),p=$lf(x,$$$UPPER(c)) s:p k(p)=3-$g(k(p),2),c=$li(x,p+k(p)) s r=$g(r)_c} q r
}
Vitaliy Serdtsev · Jun 30, 2022 go to post

parameter INDEXNULLMARKER;

Override this parameter value to specify what value should be used as a null marker when a property of the type is used in a subscript of an index map. The default null marker used is -1E14, if none is specfied for the datatype. However %Library.PosixTime and %Library.BigInt datatypes could have values that collate before -1E14, and this means null values would not sort before all non-NULL values.

For beauty, I would also use the value of this parameter, for example:

Class dc.test Extends %Persistent
{

Property idp As dc.test;

Property idpC As %Integer(INDEXNULLMARKER = "$c(0)") [ Calculated, Private, Required, SqlComputeCode = {s {*}=$s({idp}="":$c(0),1:{idp})}, SqlComputed ];

Property Name As %String [ Required ];

Index iUnq On (idpC, Name) [ Unique ];

ClassMethod Test()
{
  d ..%KillExtent()
  
  &sql(insert into dc.test(Name,idp)values('n1',1)) w SQLCODE,! ;0
  &sql(insert into dc.test(Name,idp)values('n2',1)) w SQLCODE,! ;0
  &sql(insert into dc.test(Name,idp)values('n2',1)) w SQLCODE,!! ;-119

  &sql(insert into dc.test(Name,idp)values('n1',null)) w SQLCODE,! ;0
  &sql(insert into dc.test(Name,idp)values('n2',null)) w SQLCODE,! ;0
  &sql(insert into dc.test(Name,idp)values('n2',null)) w SQLCODE,!! ;-119
  
  zw ^dc.testD,^dc.testI
}

}

Output:

USER>d ##class(dc.test).Test()
0
0
-119
 
0
0
-119
 
^dc.testD=4
^dc.testD(1)=$lb("",1,"n1")
^dc.testD(2)=$lb("",1,"n2")
^dc.testD(3)=$lb("","","n1")
^dc.testD(4)=$lb("","","n2")
^dc.testI("iUnq",1," N1",1)=""
^dc.testI("iUnq",1," N2",2)=""
^dc.testI("iUnq",$c(0)," N1",3)=""
^dc.testI("iUnq",$c(0)," N2",4)=""

Or

Property idpC As %Integer [ Calculated, Private, Required, SqlComputeCode = {s {*}=$s({idp}="":$$$NULLSubscriptMarker,1:{idp})}, SqlComputed ];

Output:

USER>d ##class(dc.test).Test()
0
0
-119
 
0
0
-119
 
^dc.testD=4
^dc.testD(1)=$lb("",1,"n1")
^dc.testD(2)=$lb("",1,"n2")
^dc.testD(3)=$lb("","","n1")
^dc.testD(4)=$lb("","","n2")
^dc.testI("iUnq",-100000000000000," N1",3)=""
^dc.testI("iUnq",-100000000000000," N2",4)=""
^dc.testI("iUnq",1," N1",1)=""
^dc.testI("iUnq",1," N2",2)=""
Vitaliy Serdtsev · Jun 30, 2022 go to post 

Class dc.test Extends (%RegisteredObject, %JSON.Adaptor)
{


Parameter %JSONENABLED = 1;


Property AppointmentID As %String(%JSONFIELDNAME = "AppointmentID", %JSONINCLUDE = "inout");
Property AppointmentType As %String(%JSONINCLUDE = "inout");
Property AppointmentTypeID As %String(%JSONINCLUDE = "inout");
Property Date As %String(%JSONINCLUDE = "inout");
Property DepartmentID As %String(%JSONINCLUDE = "inout");
Property Duration As %Integer(%JSONINCLUDE = "inout");
Property PatientAppointmentTypeName As %String(%JSONINCLUDE = "inout");
Property LocalProviderID As %String(%JSONINCLUDE = "inout");
Property ProviderID As %String(%JSONINCLUDE = "inout");
Property StartTime As %String(%JSONINCLUDE = "inout");
Property Reason As %String(%JSONINCLUDE = "inout");


XData AthenaAppointment
{
<Mapping xmlns="http://www.intersystems.com/jsonmapping">
  <Property Name="AppointmentID" FieldName="appointmentid" />
  <Property Name="AppointmentType" FieldName="appointmenttype" />
  <Property Name="AppointmentTypeID" FieldName="appointmenttypeid" />
  <Property Name="Date" FieldName="date" />
  <Property Name="DepartmentID" FieldName="departmentid" />
  <Property Name="Duration" FieldName="duration" />
  <Property Name="PatientAppointmentTypeName" FieldName="patientappointmenttypename" />
  <Property Name="LocalProviderID" FieldName="localproviderid" />
  <Property Name="ProviderID" FieldName="providerid" />
  <Property Name="StartTime" FieldName="starttime" />
  <Property Name="Reason" FieldName="reasonid" />
</Mapping>
}


/// d ##class(dc.test).Test()
ClassMethod Test()
{
  s json="{""date"":""06/27/2022"",""appointmentid"":""1214525"",""departmentid"":""195"",""localproviderid"":""187"",""appointmenttype"":""NEW PATIENT 45"",""providerid"":""187"",""starttime"":""14:00"",""duration"":45,""appointmenttypeid"":""1188"",""reasonid"":""-1"",""patientappointmenttypename"":""New Patient""}"
  
  try{


    s tmp=..%New()
    
    $$$ThrowOnError(tmp.%JSONImport(json,"AthenaAppointment"))


    $$$ThrowOnError($system.OBJ.Dump(tmp))
  
  }catch(ex){
    #dim ex As %Exception.AbstractException
    w ex.DisplayString()
  }
}


}
Output:
USER>d ##class(dc.test).Test()
+----------------- general information ---------------
|      oref value: 3
|      class name: dc.test
| reference count: 1
+----------------- attribute values ------------------
|      AppointmentID = 1214525
|    AppointmentType = "NEW PATIENT 45"
|  AppointmentTypeID = 1188
|               Date = "06/27/2022"
|       DepartmentID = 195
|           Duration = 45
|    LocalProviderID = 187
|PatientAppointmentTypeName = "New Patient"
|         ProviderID = 187
|             Reason = -1
|          StartTime = "14:00"
+-----------------------------------------------------

PS: pay special attention to the reason field: is it a string or an array of strings?

Vitaliy Serdtsev · Jun 30, 2022 go to post

The new version of IRIS 2022.2 has a new feature Columnar Storage, about which the documentation says the following:

Choosing a storage layout is not an exact science. You might need to experiment with multiple layouts and run multiple query tests to find the optimal one.
Therefore, you are unlikely to find an exact answer to your question.

Usually, the more efficient the query is and there are "correct" indexes, the smaller the GREF and, accordingly, the shorter the execution time. But this is influenced by many factors, not just the above: see InterSystems SQL Optimization Guide