go to post Robert Cemper · Apr 16, 2020 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) Affectedstatement 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) Affectedstatement 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
go to post Robert Cemper · Apr 16, 2020 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
go to post Robert Cemper · Apr 16, 2020 check SQL Function JSON_OBJECThttps://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_jsonobjectand JSON_ARRAY https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_jsonarray with the related examples
go to post Robert Cemper · Apr 15, 2020 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 variablesCACHEUSER>do $system.SQL.Shell()SQL Command Line Shell----------------------------------------------------[SQL]CACHEUSER>>select * from Tbl -- tableA ID HireDate Name Sub2 Title1 55192 A=Brendan Bannon 1 SQL Manager2 63375 A=Nicole Aaron 2 Support Specialist3 61155 A=Kyle Baxter 3 Senior Support Specialist4 58471 A=Prasad Kari 4 Support Specialist5 57982 A=Clive Morgan 5 Senior Support Specialist 5 Rows(s) Affectedstatement 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 Title1 55192 B=Brendan Bannon 1 SQL Manager2 63375 B=Nicole Aaron 2 Support Specialist3 61155 B=Kyle Baxter 3 Senior Support Specialist4 58471 B=Prasad Kari 4 Support Specialist5 57982 B=Clive Morgan 5 Senior Support Specialist 5 Rows(s) Affectedstatement 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 variablesCACHEUSER>do $system.SQL.Shell()SQL Command Line Shell----------------------------------------------------[SQL]CACHEUSER>>select * from Tbl where SetGlobal('^tabB')=1 ID HireDate Name Sub2 Title1 55192 B=Brendan Bannon 1 SQL Manager2 63375 B=Nicole Aaron 2 Support Specialist3 61155 B=Kyle Baxter 3 Senior Support Specialist4 58471 B=Prasad Kari 4 Support Specialist5 57982 B=Clive Morgan 5 Senior Support Specialist 5 Rows(s) Affectedstatement 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.
go to post Robert Cemper · Apr 15, 2020 instead of the explicit global you may try to use global name indirection.Suggestion: Use a %variable to escape from scoping troubles. see details : Subscript Indirection and $NAME function
go to post Robert Cemper · Apr 13, 2020 Yes it is.I missed it and entered via registration. JOIN THE WEBINAR!
go to post Robert Cemper · Apr 10, 2020 you may take this example from OpenExchange: https://openexchange.intersystems.com/package/Light-weight-EXCEL-download Al you have to do is to out-comment in ClassMethod OnPreHTTP() this line : #; set %response.ContentType="application/vnd.ms-excel" and the table goes to your browser.
go to post Robert Cemper · Apr 6, 2020 you have to mimic %Next() as %session.Data is a Multidimensional Array) set pos=-1while pos { set pos=$order(%session.Data("product",pos),1,value) /* whatever */ }
go to post Robert Cemper · Apr 6, 2020 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.
go to post Robert Cemper · Apr 5, 2020 A native COS example is available in Open Exchange now.WebSocket Client Demo in IRIS 2020.1
go to post Robert Cemper · Apr 4, 2020 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 maintainabilityI'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.
go to post Robert Cemper · Apr 1, 2020 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.3There seem to be more serious changes than just the client code itself.
go to post Robert Cemper · Mar 31, 2020 you are fully right. I just checked in Class Reference: stream class %CSP.CharacterStream extends %GlobalCharacterStream, %CSP.Stream
go to post Robert Cemper · Mar 30, 2020 I started with %Stream.GlobalBinary, ... wondering what the problem might be. Thinking over ENSEMBLE I moved to %GlobalBinaryStream
go to post Robert Cemper · Mar 30, 2020 @Tomas Vaverka Ahoi, can you watch my local terminal from remote?You describe pretty much my investigation!
go to post Robert Cemper · Mar 30, 2020 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 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.
go to post Robert Cemper · Mar 30, 2020 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.
go to post Robert Cemper · Mar 30, 2020 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,:"") }
