All
Accepted answers
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
Robert Cemper · Jul 8, 2020 go to post

With a small extension, you may get closer to your result.
What you expect is a JSON Array, but %JSONExport... works on data OBJECTS, not on data TYPES { same as XMLExport() }

So I have created a small wrapper object:

Class Test.javier Extends (%RegisteredObject, %JSON.Adaptor) 
{ Property wrap As Test.elementList(%JSONFIELDNAME = " "); }

extending Test.Main:

Class Test.Main Extends %RegisteredObject 
{ ClassMethod Run()  {
  Set elem = ##class(Test.element).%New()
  Set elemList = ##class(Test.elementList).%New()
  Do elemList.Insert(elem)
#; Do elemList.%JSONExportToString(.json)
#; Do ##class(%ZJSON.Formatter).%New().Format(json)
#;; extended
 set jav=##class(Test.javier).%New()
 set jav.wrap=elemList
 Do jav.%JSONExportToString(.json)
 set json=$e($p(json,":",2,*),1,*-1)  ;  a little bit dirty 
 Do ##class(%ZJSON.Formatter).%New().Format(json)
} }

And here we go:

SAMPLES>do ##class(Test.Main).Run()
[
    {
      "field1":"testField1",
      "field2":"testField2"
    }
]
SAMPLES>
Robert Cemper · Jul 8, 2020 go to post

Just to verify if this is a %JSON specific problem I added also %XML.Adaptor to the classes.
And there is a similar problem: %ListOfObjects seems to be the bad guy. 

Do elemList.%JSONExport()
      {"ElementType":"Test.element","Size":1}
Do elemList.XMLExport()
     <elementList><ElementType>Test.element</ElementType><Size>1</Size></elementList>
zw elemList
elemList=2@Test.elementList  ; <OREF>
+----------------- general information ---------------
|      oref value: 2
|      class name: Test.elementList
| reference count: 2
+----------------- attribute values ------------------
|            Data(1) = ""
|        ElementType = "Test.element"
|            Oref(1) = "1@Test.element"
|               Size = 1  <Set>
+-----------------------------------------------------
Robert Cemper · Jul 7, 2020 go to post

in your query, I  see "Timestamp"  and Timestamp

Check the status of SUPPORT_DELIMITED_IDENTIFIERS parameter

Next check the date type of your column named Timestamp.
easiest SELECT top 5 "Timestamp","Skillset"  FROM "dbo"."iSkillsetStat"  
 also try SELECT Timestamp,Skillset  FROM dbo.iSkillsetStat
without any quoted columns  

Robert Cemper · Jul 7, 2020 go to post

You won the extra points !  yes 
It's good to know someone is reading your comments.

THANKS!

Robert Cemper · Jul 6, 2020 go to post

Just found an example:

Class Demo.Loan.BankUS Extends Ens.BusinessProcess [ ClassType = persistent, ProcedureBlock ]
{ /// 2 modes: Queue, InProc
Parameter INVOCATION = "Queue"; Property CreditRating As %Integer; Property PrimeRate As %Numeric; Method OnRequest(
    request As Demo.Loan.Msg.Application,
    Output response As Demo.Loan.Msg.Approval) As %Status
{
    Set $ZT="Trap",tSC=$$$OK
    Do {
        $$$TRACE("received application for "_request.Name)
        #;
        If $zcrc(request.Name,2)#5=0 {
            Set response = ##class(Demo.Loan.Msg.Approval).%New()
            Set response.BankName = "BankUS"
            Set response.IsApproved = 0
            $$$TRACE("application is denied because of bank holiday")
            Quit
        }
        #;
        Set tRequest = ##class(Demo.Loan.Msg.PrimeRateRequest).%New()
        Set tSC = ..SendRequestAsync("Demo.Loan.WebOperations",tRequest,1,"PrimeRate")
        #;
        Set tRequest = ##class(Demo.Loan.Msg.CreditRatingRequest).%New()
        Set tRequest.TaxID = request.TaxID
        Set tSC = ..SendRequestAsync("Demo.Loan.WebOperations",tRequest,1,"CreditRating")
        #;
        Set tSC = ..SetTimer("PT15S")
        #;
        Quit
    } While (0)
Exit
    Quit tSC
Trap
    Set $ZT="",tSC=$$$EnsSystemError Goto Exit
} /// Handle a 'Response'
Method OnResponse(
    request As Ens.Request,
    ByRef response As Ens.Response,
    callrequest As Ens.Request,
    callresponse As Ens.Response,
    pCompletionKey As %String) As %Status
{
    Set $ZT="Trap",tSC=$$$OK
    Do {
        If pCompletionKey="PrimeRate" {
            Set ..PrimeRate = callresponse.PrimeRate
        } Elseif pCompletionKey="CreditRating" {
            Set ..CreditRating = callresponse.CreditRating
        }
        Quit
    } While (0)
Exit
    Quit tSC
Trap
    Set $ZT="",tSC=$$$EnsSystemError Goto Exit
} Method OnComplete(
    request As Ens.Request,
    ByRef response As Ens.Response) As %Status
{
    Set $ZT="Trap",tSC=$$$OK
    Do {
        Set response = ##class(Demo.Loan.Msg.Approval).%New()
        Set response.BankName = "BankUS"
        Set tIsUsCitizen=($zcvt($tr(request.Nationality,"."),"u")="USA")||($zcvt($tr(request.Nationality,"."),"u")="US")
        If ('tIsUsCitizen)||(..CreditRating<50) {
            Set response.IsApproved = 0
            $$$TRACE("application is denied")
        } Else {
            Set response.IsApproved = 1
            Set response.InterestRate = ..PrimeRate+2+(5*(1-(..CreditRating/100)))
            $$$TRACE("application is approved for "_response.InterestRate_"%")
        }
        Quit
    } While (0)
Exit
    Quit tSC
Trap
    Set $ZT="",tSC=$$$EnsSystemError Goto Exit
}
Storage Default
{
 <Data name="BankUSDefaultData">
   <Subscript>"BankUS"</Subscript>
   <Value name="1">
      <Value>CreditRating</Value>
   </Value>
   <Value name="2">
      <Value>PrimeRate</Value>
   </Value>
 </Data>
 <DefaultData>BankUSDefaultData</DefaultData>
 <Type>%Library.CacheStorage</Type>
} }
 
Robert Cemper · Jul 6, 2020 go to post

#1)
- create a simple Business Process using the wizard
- compile it and see the resulting class and at least 4 related .int routines.
- then decide if you really want to do this all manually
the challenge is to properly use and service the methods you see in Ens.BusinessProcess

To make it easier you may install (or activate) the ENSDEMO namespace as an example (before IRIS. have no idea where it is gone)

#2)

you write.    ---transformation (written in DTL), but the code is manually written. ---

It might sufficient to put your code just into a CODE Block of a BP generated with the wizard.

Robert Cemper · Jul 6, 2020 go to post

My personal opinion:
WIN* is a nice Desktop OS good for mom and aunt Betty.  With an incredible paranoic approach. 
In post VMS times all *X (LinuX,AIX, OS X, Solaris(rip+), ***UNIX, ....)  are real server OS systems by nature.

Robert Cemper · Jul 6, 2020 go to post

Good observation.

Try run a "force" down of the image. For Caché it's .../bin/ccontrol force  <instance> 

The service calls itself always "Controller" .
I've seen such orphans rarely every now and then. but I don't have any explanation.
stopping it should allow a new start.  Check if there are other processes with Ensemble or Cache.exe

Robert Cemper · Jul 6, 2020 go to post

There's still alert.log - (not a big hope)
eventually, a Windows event logging may give a hint. - (less hope)

next emergency startup with -nostu

WRC = last chance

Robert Cemper · Jul 5, 2020 go to post

When I found it, I felt like Indiana Jones when he detected the Holy Grail.  smiley