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

an Excellent Reference.  you got the point.
for speed: If don't want to run a benchmark indirection isn't bad 
as in this case: functionality counts
yes smiley

Robert Cemper · Apr 27, 2020 go to post

I fully agree with your concerns. Especially related to parallel processing and sharding.
And modern code and design will never need this.
But there are millions of lines of old code out in the field that require these dirty tricks to survive.
And YES! You have to examine very carefully what you do.
It's a little bit like mountaineering:
Most take the cable car, some climb with ropes and a lot of fancy equipment.  While a few free climbers use nothing than their body.
You have to understand the risks and to take your decision.

Robert Cemper · Apr 26, 2020 go to post

BINGO!
You got my point. Of course, you should be careful with the storage not to mix it up. Therefore the class method to keep all related globals in sync.

Robert Cemper · Apr 22, 2020 go to post

For Multiline SQL you may do it his way:

set myquery = "SELECT TOP 5 "_
              "Name, "_
              "DOB AS bdate, "_
              "FavoriteColors "_
              "FROM Sample.Person"

no solution for coloring inside a String 

Robert Cemper · Apr 20, 2020 go to post

on "html to pdf converter freeware" google gave me 3 270 000 hits.
Why reinventing the wheel. 
Place your HTML into a local file and let someone else do the dirty job.
There are enough solutions for a call-out to trigger the conversion.

Robert Cemper · Apr 19, 2020 go to post

Sorry, it didn't work with postgreSQL. 
The error changed to a gateway error.
You can map stored procedures over the Gateway but not SQL functions:
I assume the same is happening also with Oracle.

Robert Cemper · Apr 19, 2020 go to post

Though it seems nonsense to me you can fake SQLUSER.ROW_NUMBER
 for that purpose like this:
In your namespace create this class to simulate ROW_NUMBER()

Class User.FakeRowcount
{ClassMethod RowCount() As %Integer [ SqlName = ROW_COUNT, SqlProc ]
{  quit 1  }
Robert Cemper · Apr 17, 2020 go to post

check Locktable in the management portal to find if some other process has locked the table.

by do ##class(my.class).%UnlockExtent(0,1) you just can release yourr own LOCKs

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.