go to post Robert Cemper · Apr 17, 2020 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
go to post Robert Cemper · Apr 17, 2020 I'm not an expert on Oracle and have no chance to test. Just an idea Instead of ROWNUM the function ROW_NUMBER() should pass the syntax check as it is not a column identifier.https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
go to post Robert Cemper · Apr 17, 2020 So a possible workaround could be to have a VIEW on Oracle including ROWNUM as a columnlike SELECT ROWNUM as row, * from whatever_tableand then map the View instead of the original table just for this purpose.
go to post Robert Cemper · Apr 17, 2020 Just as a side note.mixing Caché tables with external tables (e.g. in a JOIN) is not possible.
go to post Robert Cemper · Apr 17, 2020 %VID is a Caché internal workaround for subqueries to hide the fact that ROWNUM or ROW_NUMBER wasn't implementedhttps://cedocs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=RSQL_C189621 You can't mix it with external database access with internal featuresinstead, 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-ver15You just can win. The workload moves to the external DB and you transfer fewer records
go to post Robert Cemper · Apr 16, 2020 So I'd suggest involving WRC to check the sources where the double translation comes from.(probably since ever)
go to post Robert Cemper · Apr 16, 2020 just an idea to understand:what do you see if your .stream is a %Stream.GlobalBinary ?
go to post Robert Cemper · Apr 16, 2020 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: "
go to post Robert Cemper · Apr 16, 2020 @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.
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.
