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

Try this:

ClassMethod GetTreeInfo(
  pRoot As %String,
  Output pTree,
  ByRef pParmsAs %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.

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 ##class(%ZEN.Auxiliary.jsonProvider).%WriteJSONStreamFromObject(.json,object,,,,"aelotw")

Output:

{
  "ID":"123456"
}

I do not observe any differences in the behavior of xDBC and Portal.

Given:

Class dc.test Extends %Persistent
{

Property s1 As %String(MAXLEN 2TRUNCATE 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>]

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()
{
  ..%KillExtent(,$$$YES)

   t=..%New()
   t.s1=$tr($j("",51)," ","a")
   sc=t.%Save()
   "s1: ",$s($$$ISERR(sc):$system.Status.GetErrorText(sc),1:"OK"),!

   t=..%New()
   t.s2=$tr($j("",51)," ","a")
   sc=t.%Save()
   "s2: ",$s($$$ISERR(sc):$system.Status.GetErrorText(sc),1:"OK"),!
}

}

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

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}{
   t={stream}
   ^||tmp=t.Size
 }
}

ClassMethod Test()
{
  ..%KillExtent(,$$$YES)
  
  f=##class(%Stream.FileBinary).%New()
  f.LinkToFile("C:\test.jpg")
  
  &sql(insert into dc.test(streamvalues(:f))
  'SQLCODE {
    &sql(select CHAR_LENGTH(streaminto :len from dc.test where %id=1)
    len," ",^||tmp,!
  }
}

}

I have saved a 16 MB file without any problems:

USER>##class(dc.test).Test()
16446809 16446809

See Defining Custom Class Queries

 
Example of a stored procedure

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 ELSE ENDFROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) generatedCount,
COALESCE((SELECT SUM(CASE WHEN value_after_changing 'FINISH' THEN ELSE ENDFROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) finishedCount,
COALESCE((SELECT SUM(CASE WHEN value_after_changing 'DROPOUT' THEN ELSE ENDFROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) dropoutCount,
COALESCE((SELECT SUM(CASE WHEN value_after_changing 'CANCEL' THEN ELSE ENDFROM 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)

I think it will also be useful to mention here the links to the documentation:

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.