All
Accepted answers
Robert Cemper · Apr 17, 2020 go to post

So a possible workaround could be to have a  VIEW on Oracle including ROWNUM as a column
like SELECT ROWNUM as row, * from  whatever_table
and then map the View instead of the original table just for this purpose.
 

Robert Cemper · Apr 17, 2020 go to post

Just as a side note.
mixing Caché tables with external tables (e.g. in a JOIN) is not possible.

Robert Cemper · Apr 17, 2020 go to post

%VID is a Caché internal workaround for subqueries to hide the fact that ROWNUM or ROW_NUMBER wasn't implemented
https://cedocs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=RSQL_C189621 

You can't mix it with external database access with internal features
instead, use the RowNumber implementation of your external DB (they vary by product)
https://www.w3schools.com/sql/sql_top.asp

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15
You just can win. The workload moves to the external DB and you transfer fewer records

Robert Cemper · Apr 16, 2020 go to post

So I'd suggest involving WRC to check the sources where the double translation comes from.
(probably since ever)

Robert Cemper · Apr 16, 2020 go to post

just an idea to understand:
what do you see if your .stream is a %Stream.GlobalBinary ? 

Robert Cemper · Apr 16, 2020 go to post

see this example to reproduce and explain that there is an unnecessary conversion on the way

as you showed in your example
              "text": "Condição de pagamento sujeito a análise de crédito: "

Robert Cemper · Apr 16, 2020 go to post

@Rubens Silva 
That sounds to me like double encoding.
I'd suggest using a HEX Editor  (e.g. PSpad) to examine your files.
UTF-8 means that some characters have more than 8 bit.
By converting an already converted string you may get those strange effects.  
And you found the way to avoid this already yourself.
 

Robert Cemper · Apr 16, 2020 go to post

I just tested a variable parameter list in SQL. It works: (simplified example)

Class User.SQLvar
{
ClassMethod MyLIST(var...) As %String [ SqlName = MyLIST, SqlProc ]
{ set result="^"
 for i=1:1:var set result=result_var(i)_"^"
  quit result }
}

with SQL:

 CACHE>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
[SQL]CACHE>>select MyList(1,2,3)
3.      select MyList(1,2,3)
Expression_1
^1^2^3^
 
1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0538s/33292/149491/0ms
          execute time(s)/globals/cmds/disk: 0.0002s/0/428/0ms
                          cached query class: %sqlcq.CACHE.cls1
---------------------------------------------------------------------------
[SQL]CACHE>>select MyList(1,2,3,4,5,6)
Expression_1
^1^2^3^4^5^6^
 
1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0545s/33591/158533/4ms
          execute time(s)/globals/cmds/disk: 0.0002s/0/440/0ms
                          cached query class: %sqlcq.CACHE.cls2
---------------------------------------------------------------------------
[SQL]CACHE>>

so you can generate your JSON  straght as ClassMethd

Robert Cemper · Apr 16, 2020 go to post

With 2013.* it is not built-in,  but you can mimic it:

every Classmethod can be projected as SQLprocedure.  

ClassMethod MyJSON(par1 As %String, par2 As %String ) As %String(MAXLEN==")  [ SqlName = My_JSON, SqlProc ]
{  
    set result = ""   /* now you assemble your JSON  in this string */
   quit result 
}

I'm not sure if you can pass a variable list of arguments from SQL.
But this is the basic mechanic behind it

Robert Cemper · Apr 15, 2020 go to post

Based on the changed question you may consider this solution with just 1 Common Class:

If you look to the  Storage definition you see something similar:

<SQLMap name="Map1">
<Data name="....
</Data>
<Global>^tabA</Global>

Now you change the <Global> node to

<Global>@($s($d(%GLOBAL):%GLOBAL,1:"^tabA"))@</Global>

this means if there is no variable %GLOBAL you see ^tabA  but if you set %GLOBAL you see ^tabB 

to set the variable also directly from SQL  you may add this ClassMethod as SQLprocedure

ClassMethod SetGlobal(global As %String = "tabA") As %Integer [ SqlName = SetGlobal, SqlProc ]
{  set %GLOBAL=global  quit $$$OK }

now see the effect. first the test data:

 CACHEUSER>zw ^tabA
^tabA(1)="A=Brendan Bannon^55192^SQL Manager"
^tabA(2)="A=Nicole Aaron^63375^Support Specialist"
^tabA(3)="A=Kyle Baxter^61155^Senior Support Specialist"
^tabA(4)="A=Prasad Kari^58471^Support Specialist"
^tabA(5)="A=Clive Morgan^57982^Senior Support Specialist"
 
CACHEUSER>zw ^tabB
^tabB(1)="B=Brendan Bannon^55192^SQL Manager"
^tabB(2)="B=Nicole Aaron^63375^Support Specialist"
^tabB(3)="B=Kyle Baxter^61155^Senior Support Specialist"
^tabB(4)="B=Prasad Kari^58471^Support Specialist"
^tabB(5)="B=Clive Morgan^57982^Senior Support Specialist"

and now the SQL query

 CACHEUSER>zw   ; no variables
CACHEUSER>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
[SQL]CACHEUSER>>select * from Tbl  -- tableA
 
ID      HireDate        Name    Sub2    Title
1       55192   A=Brendan Bannon        1       SQL Manager
2       63375   A=Nicole Aaron  2       Support Specialist
3       61155   A=Kyle Baxter   3       Senior Support Specialist
4       58471   A=Prasad Kari   4       Support Specialist
5       57982   A=Clive Morgan  5       Senior Support Specialist
 
5 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0007s/20/955/0ms
          execute time(s)/globals/cmds/disk: 0.0004s/6/1324/0ms
                          cached query class: %sqlcq.CACHEUSER.cls6
---------------------------------------------------------------------------
[SQL]CACHEUSER>>COS set %GLOBAL="^tabB"  ; going for Global ^tabB
[SQL]CACHEUSER>>select * from Tbl -- tableB
 
ID      HireDate        Name    Sub2    Title
1       55192   B=Brendan Bannon        1       SQL Manager
2       63375   B=Nicole Aaron  2       Support Specialist
3       61155   B=Kyle Baxter   3       Senior Support Specialist
4       58471   B=Prasad Kari   4       Support Specialist
5       57982   B=Clive Morgan  5       Senior Support Specialist
 
5 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0005s/20/955/0ms
          execute time(s)/globals/cmds/disk: 0.0003s/6/1324/0ms
                          cached query class: %sqlcq.CACHEUSER.cls6
---------------------------------------------------------------------------

 next with pure SQL

 CACHEUSER>zw   ; no variables
CACHEUSER>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
[SQL]CACHEUSER>>select * from Tbl where SetGlobal('^tabB')=1
 
ID      HireDate        Name    Sub2    Title
1       55192   B=Brendan Bannon        1       SQL Manager
2       63375   B=Nicole Aaron  2       Support Specialist
3       61155   B=Kyle Baxter   3       Senior Support Specialist
4       58471   B=Prasad Kari   4       Support Specialist
5       57982   B=Clive Morgan  5       Senior Support Specialist
 
5 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0008s/27/3394/0ms
          execute time(s)/globals/cmds/disk: 0.0003s/6/1350/0ms
                          cached query class: %sqlcq.CACHEUSER.cls10
---------------------------------------------------------------------------
[SQL]CACHEUSER>>

The SQL procedure SetGlobal is independent of the rows:
It will be processed before the row selecting.  So we set our local variable %GLOBAL there.
As it is part of the WHERE clause we have to check the return value.
So it works with any SLQ Query tool. eg. Mgmt Portal.
Setting the variable works of course also for object access.  

Robert Cemper · Apr 6, 2020 go to post

you have to mimic %Next() as %session.Data is a Multidimensional Array)

set pos=-1
while pos { set pos=$order(%session.Data("product",pos),1,value) /* whatever */ }
Robert Cemper · Apr 6, 2020 go to post

COS is the acronym for Caché Object Script.
It is the oldest programming language in Caché , Ensemble, ... .
BTW. Your new approach looks promising and safe.

Robert Cemper · Apr 4, 2020 go to post

If I understand you correct you expect something  like

INSERT INTO new.MESSAGE (SELECT * from  EnsLib.HL7.Message  where id = ? )

this may work for rather modest designed relational tables but I doubt it will work for some complex structure as EnsLib.HL7.Message .

Subclassing is not the way to solve your problem for 2 reasons:

  • A subclass has always to match the .IsA() class type of its parent   
    (e.g. Employee as subclass of Person. Employee is also always a Person)
  • SubClasses share the storage with its parent.
    There are dirty tricks to avoid this but I assume you do not intend to change      EnsLib.HL7.Message.

There are ways to do something like this if you fiddle around in the storage structures. 
BUT: for reasons of transparency, documentation, and maintainability
I'd strongly recommend to use DTL. 
Fiddling around with COS in Ensemble is a rather tricky exercise.
Based on practical experience I can confirm that your successors will never forget you.

Robert Cemper · Apr 1, 2020 go to post

Ok It works in IRIS 2020.1    (e.g against /csp/samples/Web.SocketTest.cls)

The attempt of a backport failed for IRIS 2019.1 and Cache2018.1.3
There seem to be more serious changes than just the client code itself.

Robert Cemper · Mar 30, 2020 go to post

I started with %Stream.GlobalBinary, ... wondering what the problem might be.

Thinking over ENSEMBLE I moved to %GlobalBinaryStream

Robert Cemper · Mar 30, 2020 go to post

Hi Jenna,

I took some time to verify my suspicion.  (Caché 18.1)

You depend on the type of stream that is used in your object

#A)  %Library.GlobalBinaryStream or %Library.GlobalCharacterStream

  • if you have no stream yet you run do obj.MyStream.Write("whatever")
    then your stream will land in ^SSA.DocumentCacheS as expected
  • but if you get an already existing external stream and set obj.MyStream = myStreamOref
    then just the oref / OID of the stream is set including ^CacheStream.

Which seems to be your case   sadcrying​​​​​​​  angry
it's not a big surprise as ENSEMBLE may still use the old style. 

#B) using %Stream.GlobalBinary, ...

  • Both cases ended as expected with the stream in ^SSA.DocumentCacheS
    It seems to me that a CopyFromStream happens during the assignment.
    I'd name it expected behavior.   smiley
Robert Cemper · Mar 30, 2020 go to post

Not as string.
Parameters are stored as sub-object with the class object in  %Dictionary.* classes.
And serve as Information for the code to be compiled.

Robert Cemper · Mar 30, 2020 go to post

PARAMETER and DISPLAYLIST are both compiler directives and you can't mix them.

But you may workaround it by writing your own pair of DisplayToLogical /  LogicalToDisplay ClassMethods for this property´.


Parameter Active = 1;
Parameter Inactive =2;
Parameter Production = 3;
Parameter Upkeep 4;
/// DISPLAYLIST = ", Active, Inactive, Production, Upkeep",
Property Status As %String(VALUELIST = ",1,2,3,4") [ Required ];
ClassMethod StatusDisplayToLogical(%val) As %String
{ Quit $case(%val,..#Active:1,..#Inactive:2,..#Production:3,..#Upkeep:4,:"") }
ClassMethod StatusLogicalToDisplay(%val) As %String
{ Quit $case(%val,1:..#Active,2:..#Inactive,3:..#Production,4:..#Upkeep,:"") }
 
Robert Cemper · Mar 29, 2020 go to post

@Julius Kavay is fully correct.

The compiler was first written ~10 years before the idea of LLVM started  @ University of  Illinois.
Various interfaces allow your development very close to the data store. 

I'd suggest you contact your Sales Engineer @ InterSystems (Darmstadt ?) for disclosure of internals.