Why so complicated?

write obj.%Size()

should do the job

It's not clear to me what you want to do.

A property like

Property MyData as %(Global-or-File)Stream;

means, the size of MyData can be something between 0 and the free space on your (hard) drive.
That's the reason, why is MyData defined as a stream and not as a %String.

On the other hand, in an excel cell you can put no more then 32767 characters, hence the plan to extract those data to an spreadsheet will work only if the MyData properties do not have more then 32767 chars, see
https://support.microsoft.com/en-us/office/excel-specifications-and-limi...

Nevertheless, you could use the following stored procedure to extrac the first 32767 chars from those stream data:

Class Your.Table Extends %Persistent
{
Property StreamData As %GlobalCharacterStream;
// other properties
ClassMethod StreamDataAsText(ID) As %String [ SqlProc ]
{
    set obj = ..%OpenId(ID,0), text = ""
    if obj { do obj.StreamData.Rewind() set text obj.StreamData.Read(32767) }
    quit text
}
}

Now you can get, beside the other data, the first 32767 chars of those stream data too

select Your.Table_StreamDataAsText(ID), * from Your.Table

 Oh yes, the idea ... I still have to think about that and get some sleep

 

 The idea is good, I just have to sleep about it...
 

I can't you provide a .Net help but there are methods in IRIS/Cache to create QR code:

##class(%SYS.QRCode).GenerateFile(...) and
##class(%SYS.QRCode).GenerateImage(...)

so your developers could have a direct use instead of messing with passing data back and fort between IRIS/Cache and .Net

The correct timestamp format is YYYY-MM-DD HH:MM:SS but according to the error message, your data does not meets this format.

104    Field validation failed in INSERT, or value failed ...MyTimeStampField' (value '2021-05-26 11:45:40 ') 

You see the space or tab character after the seconds? 

[1,2,,3] is equally arguably as [1,2,3,,] or [1,2,3,,,,,] and IRIS/Cache accepts all of them.

Nothing against a system which is input tolerant (forgiving, with your words) but then this tolerance should be obvious and in some way logical. An example, I tolerate trailing comma(s), becuse they could be leftovers of editing. So I would say, all the arrays

[1,2,3]
[1,2,3,]
[1,2,3,,,]

have a size of 3 - there are three elements, no more. But IRIS/Cache says the sizes are 3, 4 and 6. So let check  the last one

set obj=[].%FromJSON("[1,2,3,,,]")
write obj.%Size() --> 6
for i=0:1:9 write i,?3,obj.%Get(i),?7,obj.%GetTypeOf(i),!

The output of the FOR-Loop is:

0  1   number
1  2   number
2  3   number
3      unassigned
4      unassigned
5      unassigned
6      unassigned
7      unassigned
8      unassigned
9      unassigned

The elements with index 3, 4 and 5 are unassigned and in some kind, I can understand that. But if the higher indices, like 6, 7, 88 or 1000 etc. are also unassigned then I ask you, why is the size 6 and not, say 12 or 573?
For me the logical size should be 3 because there are three intendeed elements, the others are a result of tolerated delimiters! 

Finally, I don't want to start a war, how to interpret JSON strings. It was just my 2cc to a theme, which is out-of-round, according to my opinion.

OK, take a more simple case:

set obj=[1,2,,3]  // again, this is a SYNTAX
set obj=[].%FromJSON("[1,2,,3]") // this is OK

but in both cases, the problem was the bouncing comma-key on my keyboard.

The first was told by compiler the second was "forgiven" by JSON-Reader! BUT the real question is, WHAT IS the third item in the above array? The latter shows obj has a size of 4, so the, and the desired thrid element could be null, 3 or maybe something else!

I wrote my very first program somewhere in 1971 or 1972, I can't remember anymore. But one thing I have learned is, one should accept checked data only.

Imagine, you accept those incorrect (aka forgiven) data and beside processing, store the data in your database, then later, for whatever reason, you send the (original string) data to an external party.... bang! They can't read it, because it's not JSON conform.

For the sake of completness, there is one more validator: https://jsonlint.com/  (which shows the above settings.json file as incorrect).

One more problem, it seems IRIS (and Cache) speaks with a forked tongue (but has nothing to do with the above problem) :

set string = "{""Value"":123, }"    // note the extra comma!
set stream=##class(%Stream.TmpCharacter).%New()
do stream.WriteLine(string)
set obj1={"Value":123, }  --> gives a SYNTAX
set obj2={}.%FromJSON(string) --> accepts the incorrect (json)string!

I'm not sure... but I think, your settings.json does NOT conform to JSON  specification. It seems, you like extra commas but JSON does not likes them. Take a look at:

...
    "active":true , <----extra comma
  }, <----------------------- extra comma
}

This produces in IRIS/Cache an <SYNTAX... which starts with an "<"

There is a keyword %NOINDEX indexname1, indexname2, ... to prevent the SQL-Enginne to use specific indices but there is no keyword for the opposite, something like %USEINDEX indexname, sadly.

Maybe someone with more SQL experience knows what is preventing the SQL engine to use the existing index over the numDossiersMER property...

But, and this is the great thing with IRIS and Cache, if everything else fails, you can always create your custom query.

Class User.TestList.Data.Titre Extends (%Persistent, %Populate)
{
Property numTitre As %Integer;
Property millesime As %Integer;
Property codeProduit As %String;
/// Old field which will be replaced by the next one
Property numDossierMER As %Integer;
Property numDossiersMER As list Of %Integer;
Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ];
/// Old index
Index numDossierMERIdx On numDossierMER;
Index numDossiersMERIdx On numDossiersMER(ELEMENTS);

Query Select(num...) As %Query(CONTAINID = 1, ROWSPEC = "ID:%Integer,Dossier:%Integer,codeProd:%String") [ SqlProc ]
{
}

ClassMethod SelectExecute(par As %Binary, num...) As %Status
{
   kill par, ^||tmpSelectQry
   for i=1:1:$g(num) set nr=$g(num(i)) merge:nr]"" ^||tmpSelectQry(nr)=^User.TestList.Data.TitreI("numDossiersMERIdx",nr)
   set par=$na(^||tmpSelectQry)
   quit $$$OK
}

ClassMethod SelectFetch(par As %Binary, row As %List, end As %Integer) As %Status
{
   set par=$query(@par)
   if par="" { set end=1, row="" }
   else {
      set end=0, id=$qs(par,2)
      set row=$lb(id, $qs(par,1), ..codeProduitGetStored(id)) // and other fields...
   }
   quit $$$OK
}

ClassMethod SelectClose(par As %Binary) As %Status
{
   kill par, ^||tmpSelectQry
   quit $$$OK
}

ClassMethod Test()
{
   write "Using a ResultSet...",!
   set rs=##class(%ResultSet).%New("User.TestList.Data.Titre:Select")
   if rs.Execute(230,3590,40110,507550,6094,70071,820096,9380148,8,592) {
   set t=$zh
   while rs.Next() { write rs.Data("ID"),?10,rs.Data("Dossier"),?30,rs.Data("codeProd"),! }
   }
   write "Time: ",$zh-t*1E3,!!
   write "Direct usage of the query methods...",!
   do ..SelectExecute(.par,230,3590,40110,507550,6094,70071,820096,9380148,8,592)
   set t=$zh
   for  do ..SelectFetch(.par,.row,.end) quit:end  zwrite row
   write "Time: ",$zh-t*1E3,!
}

Storage Default
{
<Data name="TitreDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>numTitre</Value>
</Value>
<Value name="3">
<Value>millesime</Value>
</Value>
<Value name="4">
<Value>codeProduit</Value>
</Value>
<Value name="5">
<Value>numDossierMER</Value>
</Value>
</Data>
<Data name="numDossiersMER">
<Attribute>numDossiersMER</Attribute>
<Structure>subnode</Structure>
<Subscript>"numDossiersMER"</Subscript>
</Data>
<DataLocation>^User.TestList.Data.TitreD</DataLocation>
<DefaultData>TitreDefaultData</DefaultData>
<ExtentSize>1000000</ExtentSize>
<IdLocation>^User.TestList.Data.TitreD</IdLocation>
<IndexLocation>^User.TestList.Data.TitreI</IndexLocation>
<Property name="%%CLASSNAME">
<AverageFieldSize>1</AverageFieldSize>
<Selectivity>100.0000%</Selectivity>
</Property>
<Property name="%%ID">
<AverageFieldSize>5.88</AverageFieldSize>
<Selectivity>1</Selectivity>
</Property>
<Property name="codeProduit">
<AverageFieldSize>4.89</AverageFieldSize>
<Selectivity>0.0004%</Selectivity>
</Property>
<Property name="millesime">
<AverageFieldSize>8.89</AverageFieldSize>
<Selectivity>0.0001%</Selectivity>
</Property>
<Property name="numDossierMER">
<AverageFieldSize>8.89</AverageFieldSize>
<Selectivity>0.0001%</Selectivity>
</Property>
<Property name="numTitre">
<AverageFieldSize>8.89</AverageFieldSize>
<Selectivity>0.0001%</Selectivity>
</Property>
<SQLMap name="IDKEY">
<BlockCount>-63088</BlockCount>
</SQLMap>
<SQLMap name="numDossierMERIdx">
<BlockCount>-7912</BlockCount>
</SQLMap>
<SQLMap name="titreIdx">
<BlockCount>-19940</BlockCount>
</SQLMap>
<StreamLocation>^User.TestList.Data.TitreS</StreamLocation>
<Type>%Storage.Persistent</Type>
} }

Some examples after do ##class(..).Poulate(1E6)

USER>d ##class(User.TestList.Data.Titre).Test()
Using a ResultSet...
700556    8                   R7369
696384    230                 R6776
952257    592                 E8624
209184    3590                Q7863
239874    6094                N7969
497500    40110               W6490
188796    70071               O9708
145090    507550              S3705
803994    820096              S20
97986     9380148             W6598
Time: .787

Direct usage of the query methods...
row=$lb("700556","8","R7369")
row=$lb("696384","230","R6776")
row=$lb("952257","592","E8624")
row=$lb("209184","3590","Q7863")
row=$lb("239874","6094","N7969")
row=$lb("497500","40110","W6490")
row=$lb("188796","70071","O9708")
row=$lb("145090","507550","S3705")
row=$lb("803994","820096","S20")
row=$lb("97986","9380148","W6598")
Time: .894