All
Accepted answers
Robert Cemper · Aug 1, 2020 go to post

Thanks for your clarification.  I have now 1 rcc.PKG and several rcc.anything.PKG

Robert Cemper · Aug 1, 2020 go to post

  @Evgeny Shvarov 
     Are you looking for something like this ?

this is the related query:

select %vid AbsRef
    , TO_CHAR(Day,'YYYY-MM-DD')  Day
    , Seq 
    , $PIECE($PIECE(p,'$ZE=',2),'>',1)||'>' Type
    , $PIECE($PIECE(p,'>',2),',$ZV',1)||'>' Line
    , $PIECE(p,'$ZV=',2)||'>' Version
 from (
    SELECTTOP ALL
       day, seq, LIST(item||'='||value) p
       FROM zrcc.ERRORStack 
       WHERE Stacklevel=0
       AND item IN ('$ZE','$ZV')
      GROUP BY day,seq
)
Robert Cemper · Aug 1, 2020 go to post

out of the experience: every project in ZPM needs its personal package.

otherwise, you may see this:

ERROR #5001: Resource 'rcc.PKG' is already defined as part of module 'echoserver-wsock-iris'; cannot also be listed in module 'iris-internal-websocket-client'
ERROR #6315: FErrors reporting importing XML subelement in file  'C:\InterSystems\IRIS\mgr\.modules\USER\iris-internal-websocket-client\1.0.0\module.xml', at line'3', offset '55'. skipping this item.

Robert Cemper · Aug 1, 2020 go to post

@Evgeny Shvarov 
%ETN typically stops your job.  set $ZT="^%ETN"
If you use LOG^%ETN it writes to ^ERRORS and continues the job.
this is highly comfortable to be used intry {....} catch error { do LOG^%ETN }
more details to be found in ^%ETN.int (easy to read)

Robert Cemper · Jul 30, 2020 go to post

Thanks for the hint. 
I was pretty sure there should be something similar to serve SysMgmtPortal. I just didn't know.
Obviously the authors had the same problems as me with the old structure and applied a bunch of custom queries.
I don't think I missed something:
- the class is deployed so you have to accept what it does.
differently  %ERN.int doesn't hide anything and speaks full truth. 
- next to use it you require full access rights to %SYS and a namespace change %SYS to run it.
- and finally the topmost requirement: The queries are invisible to SQL! 
Just because not being exposed as SqlProcedure.

My solution runs in any namespace on Caché / Ensemble / iris .
Without any privileges and doesn't touch any ISC copyrights!

Robert Cemper · Jul 29, 2020 go to post

in IRIS if the data type is any numeric (%Integer, %Decimal, %Float, ..... or %Boolean)  the value is NOT enclosed in  Quotes.

Robert Cemper · Jul 29, 2020 go to post

Just to outline my understanding:
my package will be rcc.subject.classname   
rarely eventually zrcc.subject.classname  if it should go to %SYS .

And I will not touch/ rename existing projects as this means changing not just the code
but also README.md , Descriptions in OEX and Articles in DC, Screenshots, Dependencies (not just my own), ...
Over all a good source for mistakes, typos, errrors, confusion, 

Robert Cemper · Jul 28, 2020 go to post

What is the rule for VERSION  in Dependencies ?
Is it an EQUAL or a MINIMUM Version.   

e.g. <Version>0.0.0</Version>  would mean any version 

<Dependencies>
        <ModuleReference>
          <Name>holefoods</Name>
          <Version>0.1.0</Version>
        </ModuleReference>
      </Dependencies>

Thanks ! 

Robert Cemper · Jul 28, 2020 go to post

The so-called SQLformat doesn't exist in Caché (at least not in the last 20 years)
But over the gateway, you can move data from Caché to PostgeSQL.  "from a table on CACHE  to a POSTGRESQL table"

So you need a trigger to do it:
- Caché has to be the active part here
you can start the upload over a webservice, an REST call, even as an SQLprocedure, or automatically by the scheduler
And if you don't want to touch your target table directly you may have a shadow-copy in PostgreSQL that
then serves as a source for your final updates.  ( some INSERT...SELECT....)

So instead of import from CSV, you do an import from a local table in PostgreSQL
Anything is better than CSV.
This would even allow filling your shadow over the day in small steps instead of a big bang (if needed)

Robert Cemper · Jul 19, 2020 go to post

I 've never seen the 1000 cols limit. (Consider the record size and the related impact to storage !)
But in a comparable situation, I used an array with the column name as a key.  => unlimited columns, slim storage.
And you can work on it in SQL and in classes.
BUT this is object thinking, not straight SQL 

Robert Cemper · Jul 18, 2020 go to post

try it without  "CONSTRAINT TestePK PRIMARY KEY (ID)"
and without "ID INT NOT NULL, " 
so you get an autoincremented unique ID by default

Robert Cemper · Jul 9, 2020 go to post

FIXED !

I have implemented an extension to %ZJSON.Generator to fix missing JSONTYPE

Standard Caché data types don't have a parameter JSONTYPE (!!) so everthing is set to (quoted) "string".
Especially numbers and boolean data must not be in quotes. 
       e.g  ....."NUMfield":124, "TrueFalse":true, ....  
instead of  ....."NUMfield":"124", "TrueFalse":"true", .... 


this extension bypasses the missing parameter for these standard data types as indicated in %ZJSON.Adaptor
/// number = %BigInt, %Currency, %Decimal, %Double, %Float, %Integer, %Numeric, %SmallInt, %TinyInt
/// boolean = %Boolean

For customized data classes it is easy to add  Parameter JSONTYPE=". . . ."
But changing sometihng in SYSLIB is a clear NO-NO to me.   ( though it might have been easier)

The extended version of %ZJSON.Generator is here:  
https://github.com/rcemper/Backport-JSON.-to-Cach-/blob/master/MissingJSONTYPE.xml

Robert Cemper · Jul 9, 2020 go to post

I didn't meet that.

Basically it's %String or a %Stream based on size.
I'd suggest having a private data type.
This allows you also to have all tricky LogicalToODBC, .... under control. (and no MAXLEN for %String)
And mapping it to %ALL or naming it %ZstringJSON or similar makes it public and update-restistant 

Robert Cemper · Jul 8, 2020 go to post

Some more findings what happens:
The output methods are code generated. And the generator just uses JSONTYPE from the data type class.
That means that even as the property parameter is available in Studio, ... you can't change it.

example: Property bool as %Boolean (JSONTYPE="string")  is just ignored  and you see ,"bool":false

This means: JSONTYPE is frozen in the data type class
Bringing Parameter JSONTYPE into the class (e.g, by %ZJSON.Adaptor) has no influence to the Generator 

To achieve the expected result you require a customized data class as suggested by  @Timothy Leavitt 

Out of 25 only these 7 6  classes are affected the rest is string which is default anyhow.

boolean %Library.Boolean.cls(JSONTYPE): Parameter JSONTYPE 
number  %Library.Currency.cls(JSONTYPE): Parameter JSONTYPE 
number  %Library.Decimal.cls(JSONTYPE): Parameter JSONTYPE 
number  %Library.Float.cls(JSONTYPE): Parameter JSONTYPE 
number  %Library.Integer.cls(JSONTYPE): Parameter JSONTYPE 
number  %Library.Numeric.cls(JSONTYPE): Parameter JSONTYPE 
number  %Library.PosixTime.cls(JSONTYPE): Parameter JSONTYPE  not in Caché

Robert Cemper · Jul 8, 2020 go to post

IRIS has added a new parameter in 25 Data Classes:   
Parameter JSONTYPE = ...
I'll see the impact  as soon as I find free time

%Library.Binary.cls(JSONTYPE): Parameter JSONTYPE
%Library.Boolean.cls(JSONTYPE): Parameter JSONTYPE
%Library.Currency.cls(JSONTYPE): Parameter JSONTYPE
%Library.Date.cls(JSONTYPE): Parameter JSONTYPE
%Library.Decimal.cls(JSONTYPE): Parameter JSONTYPE
%Library.Double.cls(JSONTYPE): Parameter JSONTYPE
%Library.EnumString.cls(JSONTYPE): Parameter JSONTYPE
%Library.FilemanDate.cls(JSONTYPE): Parameter JSONTYPE
%Library.FilemanTime.cls(JSONTYPE): Parameter JSONTYPE
%Library.FilemanTimeStamp.cls(JSONTYPE): Parameter JSONTYPE
%Library.FilemanTimeStampUTC.cls(JSONTYPE): Parameter JSONTYPE
%Library.FilemanYear.cls(JSONTYPE): Parameter JSONTYPE
%Library.Float.cls(JSONTYPE): Parameter JSONTYPE
%Library.InformixTimeStamp.cls(JSONTYPE): Parameter JSONTYPE
%Library.Integer.cls(JSONTYPE): Parameter JSONTYPE
%Library.List.cls(JSONTYPE): Parameter JSONTYPE
%Library.ListOfBinary.cls(JSONTYPE): Parameter JSONTYPE
%Library.Name.cls(JSONTYPE): Parameter JSONTYPE
%Library.Numeric.cls(JSONTYPE): Parameter JSONTYPE
%Library.PosixTime.cls(JSONTYPE): Parameter JSONTYPE
%Library.Status.cls(JSONTYPE): Parameter JSONTYPE
%Library.String.cls(JSONTYPE): Parameter JSONTYPE
%Library.StringTimeStamp.cls(JSONTYPE): Parameter JSONTYPE
%Library.Time.cls(JSONTYPE): Parameter JSONTYPE
%Library.TimeStamp.cls(JSONTYPE): Parameter JSONTYPE