New post
AllAccepted answers
Alexander Koblov · Aug 18, 2021

InterSystems IRIS does not have a MEDIAN SQL function. However, it has different window functions.

For example, Percent_Rank might help you here.

create table test(c1 varchar(10), c2 integer)

insert into test(c1,c2) 
select 'a',1
union all
select 'b',2
union all
select 'c',3
union all
select 'd',3
union all
select 'e',3
union all
select 'f',4
union all
select 'g',5
union all
select 'h',6

select c1, c2, Percent_Rank () OVER ( 
    ORDER BY c2 desc
) rank_no 

from test
order by c2 desc

c1  c2  rank_no
h   6   0
g   5   .1428571428571428571
f   4   .2857142857142857143
c   3   .4285714285714285714
d   3   .4285714285714285714
e   3   .4285714285714285714
b   2   .8571428571428571429
a   1   1

Doc: https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=RS...

Also, it's possible to create a user-defined aggregate function. See https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

Alexander Koblov · Feb 2, 2021

Hi Lee.

"For example, for the error log I am trying to display, it's a table that has just 16 rows and 6 columns. The columns I turn into dimensions. If I crossjoin more than 3 of the columns, I will timeout/freeze."

Can you provide reproducible case? It would be interesting to look into this. Not that crossjoining of three dimensions is a best practice, but it should work quickly for 16 rows.

Now to your questions.

  • How do we prevent double columns for the 1st column?

Go to Widgets -> [your widget] -> Data Properties.

Define properties for your columns.

On the ID property put checkbox "hidden"

  • How to prevent putting commas in the numbers:

Put "#" in the format field [0]

*How to format date to be returned in external format.

As far as I know, you should do this in the SQL query itself.

[0] https://docs.intersystems.com/iris20203/csp/docbook/Doc.View.cls?KEY=GIK...

Alexander Koblov · Dec 1, 2020

Hi.

You need to specify

Parameter ARGUMENTSTYLE = "message";

For example:

Class delme.SoapService Extends %SOAP.WebService [ Language = objectscript, ProcedureBlock ]
{

Parameter ARGUMENTSTYLE = "message";

Parameter SERVICENAME = "MyService";

Parameter NAMESPACE = "http://tempuri.org";

Method Test(x As %String) As %String(XMLNAME="Root") [ WebMethod ]
{
    Return "Test"
}

}

Then response is following:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:s="http://www.w3.org/2001/XMLSchema">
   <SOAP-ENV:Body>
      <Root xmlns="http://tempuri.org">Test</Root>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Alexander Koblov · Nov 29, 2020

"Quick analysis shows that CSP Gateway for each request opens a new TCP connection to Cache SuperServer (port 1972) allocating CSP session and license slot."

That is not true.

  • Web Gateway (fka CSP Gateway) has pool of connections that it uses to handle incoming requests. If all existing connections are busy and its number is less than "Maximum Server Connections" [0] then Web Gateway indeed creates new connection. And keeps it in the pool of available connections until this connection is not used for "No Activity Timeout".

  • Each new HTTP request does not allocate CSP session. If the request identifies itself as a part of already existing session (via cookie or other means) then the CSP session is not created. Also, if this is a request to the REST or SOAP Service that has sessions disabled then session is not created.

  • Each new HTTP request does not allocate license unit. HTTP requests for existing sessions use the same license unit. HTTP requests for new sessions usually do allocate the license unit.

"SuperServer has no queues or pools"

  • It's possible to configure SuperServer to have pool of processes to be ready to handle incoming TCP connections [1]

[0] https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=GC...
[1] https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

Alexander Koblov · Nov 6, 2020

Hi Ramesh.

"Invalid cursor state" is an error returned by ODBC driver. Ensemble just shows this error to you.

It's expected that you don't get this error when you run the proc directly on SQL Server -- because there is no ODBC driver involved in this case.

What you can try is to run the same query from some other ODBC tool (e.g. WinSQL) and see if you are getting the same error message.

If you see the same problem -- the likely the issue is within ODBC-driver+SQL Server. If you don't see the same problem then indeed, something might require changing on Ensemble side.

Also -- try googling this error message "Invalid cursor state". As this error message comes from Microsoft ODBC driver there are perhaps discussions on Microsoft sites. For example, I found this one
https://social.msdn.microsoft.com/Forums/en-US/f6466a82-caf7-4053-94a4-a...