All
Accepted answers
Vitaliy Serdtsev · Jun 26, 2024 go to post

Hi!

You need first convert the formatted date and time string to a standard timestamp. Unfortunately, the built-in TO_TIMESTAMP function is not suitable for your case.
Next, you need determine whether the time zone will be taken into account or not.

For tests, you can use https://www.timestamp-converter.com / (ISO 8601 section).

Here is a small example:

Class dc.test Extends %RegisteredObject
{

ClassMethod ISO2TS(
  strISO As %String,
  withTZ As %Boolean = 0) As %TimeStamp [ SqlProc ]
{
  s tsUTC=##class(%TimeStamp).XSDToLogical(strISO)
  q $s(withTZ:##class(%UTC).ConvertUTCtoLocal(tsUTC),1:tsUTC)
}

ClassMethod Test()
{
   f s="2024-06-23T06:03:00Z","2024-06-23T06:03:00-02:45" {
     s iso=s,
       utc=##class(%TimeStamp).XSDToLogical(iso),
       tz=##class(%UTC).ConvertUTCtoLocal(utc)

     w $$$FormatText("ISO 8601 = %2%1Date Time (UTC) = %3%1Date Time (your time zone) = %4%1",$$$NL,iso,utc,tz),!
   }
}

}

Result (for me):

USER>d ##class(dc.test).Test()
ISO 8601 = 2024-06-23T06:03:00Z
Date Time (UTC) = 2024-06-23 06:03:00
Date Time (your time zone) = 2024-06-23 09:03:00.000
 
ISO 8601 = 2024-06-23T06:03:00-02:45
Date Time (UTC) = 2024-06-23 08:48:00
Date Time (your time zone) = 2024-06-23 11:48:00.000

Using via SQL:

SELECT
  YEAR(ts) "YEAR",
  MONTH(ts) "MONTH",
  DAY(ts) "DAY",
  {fn HOUR(ts)} "HOUR",
  {fn MINUTE(ts)} "MINUTE",
  {fn SECOND(ts)} "SECOND"
FROM (SELECT dc.test_ISO2TS('2024-06-23T06:03:00Z',0) ts)
Vitaliy Serdtsev · Jun 24, 2024 go to post
 

Try this example

Class User.myclass Extends %Persistent
{

Property myVECTOR As %Vector(CAPTION = "Vector", DATATYPE = "INTEGER");

Property myProperty As %String(MAXLEN = 40) [ Required ];

ClassMethod GetEmbedding1(sentences As %String) As %String
{
  q "2,4,6,8"
}

ClassMethod GetEmbedding2(sentences As %String) As %DynamicObject
{
  q [1,3,5,7,9]
}

ClassMethod Test()
{
   d ..%KillExtent()
   
  set data=##class(User.myclass).%New()
  set data.myProperty ="anything 1"
  set data.myVECTOR=##class(User.myclass).myVECTORDisplayToLogical(##class(User.myclass).GetEmbedding1("this is my text"))
  d $system.OBJ.DisplayError(data.%Save())

   ; OR
   
  set data=..%New()
  set data.myProperty ="anything 2"
  set data.myVECTOR=data.myVECTORDisplayToLogical(..GetEmbedding2("this is my text"))
  d $system.OBJ.DisplayError(data.%Save())

  zw ^User.myclassD
}

}


USER>d ##class(User.myclass).Test()
^User.myclassD=2
^User.myclassD(1)=$lb("",{"type":"integer", "count":4, "length":4, "vector":[2,4,6,8]}  ; <VECTOR>,"anything 1")
^User.myclassD(2)=$lb("",{"type":"integer", "count":5, "length":5, "vector":[1,3,5,7,9]}  ; <VECTOR>,"anything 2")
Vitaliy Serdtsev · Jun 6, 2024 go to post 

SELECT LIST(Diagnose) FROM
(SELECT TOP ALL MRDIA_ICDCode_DR->MRCID_Desc Diagnose
FROM SQLUser.MR_Diagnos
WHERE MRDIA_MRADM_ParRef=123456789
ORDER BY MRDIA_DiagnosisType_DR ASC)

Vitaliy Serdtsev · May 28, 2024 go to post

Regional preferences have nothing to do with it.

From the conditions of the task it is unclear how many mouths, noses and eyes are allowed in one emoticon (based on physiology one at a time (we do not count pathologies)): ")", "))", ")D", ")))", "xD", "xDD"

Vitaliy Serdtsev · May 27, 2024 go to post

Additional explanations are required.

It is not entirely clear from the description whether one smiling mouth is acceptable, for example ")", "D", etc., and also why "))" is a valid smiley face.

Vitaliy Serdtsev · May 15, 2024 go to post

How would you like to solve it, using ObjectScript?

 

For example, like this:

ClassMethod Test()
{
  s C = 2500,
    r = 0.5,
    S = 3000000,
    T = 35,
    W = 5
  w $$maxWeddingCost(C, r, S, T, W),! ; => 93278.3281405256664

  s S = 2000000
  w $$maxWeddingCost(C, r, S, T, W),! ; => 174425.0762746580325
    
maxWeddingCost(C,r,S,T,W) s r=r/100,a=1+r**12,b=a**(T-W) q a**W-1/r*C+$$LEAST^%qarfunc(0,b-1/r*C-S/b)
}
Vitaliy Serdtsev · May 15, 2024 go to post

We have our own challenge with very specific conditions and goals: #Code Golf
And what is the purpose of your challenge, I do not quite understand: speed, code size, etc? Rewriting the code in ObjectScript is too simple and not interesting.

 

esthetic()

ClassMethod esthetic(num = 441) As %String
{
  set estheticBases=""
  for base=2:1:10{
    set digits = $$toBase(num, base)
    if $$isEstheticInBase(digits) set estheticBases=estheticBases_$listbuild(base)
  }
  quit $listtostring(estheticBases)
  
toBase(n,base)
  ; Convert number n to a given base and return the digits as a list.
  set digits=""
  while n {
    set digits=(n#base)_digits,
        n=n\base
  }
  quit digits
isEstheticInBase(digits)
  ; Check if the given list of digits is esthetic.
  for i=2:1:$length(digits){
      if $zabs($extract(digits,i)-$extract(digits,i-1))'=1 {
          return 0
      }
  }
  return 1
}
Vitaliy Serdtsev · May 15, 2024 go to post

The calculateTotalSavingsAtTime method can be shortened because initialPrincipal is always 0: 

	public static double calculateTotalSavingsAtTime(double initialPrincipal, double r, double monthlyDeposit, int timeMonths) {
return monthlyDeposit * ((Math.pow(1 + r, timeMonths) - 1) / r) + initialPrincipal * Math.pow(1 + r, timeMonths);
}

Vitaliy Serdtsev · May 14, 2024 go to post

Try this:

ClassMethod GetTreeInfo(
  pRoot As %String,
  Output pTree,
  ByRef pParms) As %Status
{
  Set MyId=..GetId,pos=1
  &Sql(Select Name,ID into :name,:id from MSDS_COM.Loc where Parent=:MyId)
  Set:SQLCODE (name,id)=""
  if pRoot=""
  {
    Set pTree(0,"ch",1) = ""
    Set pTree(1) = $LB(name,id,1,"","")
  }
  Quit $$$OK
}

For further examples, see the class ZENTest.DynaTreeTest in the SAMPLES namespace.

Vitaliy Serdtsev · May 14, 2024 go to post

Through %ZEN.proxyObject is unlikely to work, since the q parameter cannot be disabled in this case

q - output numeric values unquoted even when they come from a non-numeric property

Use your own class, for example:

Class dc.proxyObject Extends %RegisteredObject
{

Property ID As %VarString;

}
set object = ##class(dc.proxyObject).%New()
set object.ID = 123456

set x = ##class(%ZEN.Auxiliary.jsonProvider).%WriteJSONStreamFromObject(.json,object,,,,"aelotw")

Output:

{
  "ID":"123456"
}
Vitaliy Serdtsev · Apr 11, 2024 go to post

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

Vitaliy Serdtsev · Apr 10, 2024 go to post

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.

Vitaliy Serdtsev · Apr 10, 2024 go to post

MAXLEN is not applicable for %GlobalCharacterStream (Blob). You will get compilation error #5480

Vitaliy Serdtsev · Apr 4, 2024 go to post 

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)

Vitaliy Serdtsev · Apr 2, 2024 go to post

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
Vitaliy Serdtsev · Apr 1, 2024 go to post

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()
Vitaliy Serdtsev · Mar 29, 2024 go to post

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)
Vitaliy Serdtsev · Mar 25, 2024 go to post

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.

Vitaliy Serdtsev · Mar 15, 2024 go to post

Thanks for the hint, I fixed it.
By the way, your code can be reduced to 173:  f i=$i(r):1:a

Vitaliy Serdtsev · Mar 15, 2024 go to post

There is no limit to perfection.

By the way, your code shows 175 characters, where did the number 174 come from?

Vitaliy Serdtsev · Mar 13, 2024 go to post
 

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