New post
AllAccepted answers
Alexander Koblov · Oct 27, 2021

SQL Gateway uses %JDBC.Server to connect to 3rd party databases via JDBC.

SQL Gateway connection is the database connection settings.
%JDBC.Server settings are the settings for the java process that handles these connections.

"The driver cannot be loaded" most likely means that you specified wrong path to the jar file with the MSSQL driver. Or IRIS process does not have permission to access this jar file.

Try also running two following commands from the terminal. They might give some hint on what's wrong

do $system.SQLGateway.TestConnection("ConnectionName")
zwrite %objlasterror
Alexander Koblov · Aug 19, 2021 
USER>d ^quine
 set q = $C(34) kill a //stolen from Wikipedia Java example
 set a($I(a)) = " set q = $C(34) kill a //stolen from Wikipedia Java example"
 set a($I(a)) = " set a($I(a)) = "
 set a($I(a)) = " write a(1),! "
 set a($I(a)) = " for i=1:1:a { "
 set a($I(a)) = "    write a(2),q,a(i),q,! "
 set a($I(a)) = " }"
 set a($I(a)) = " for i=3:1:a {"
 set a($I(a)) = "    write a(i),!"
 set a($I(a)) = " }"
 write a(1),! 
 for i=1:1:a { 
    write a(2),q,a(i),q,! 
 }
 for i=3:1:a {
    write a(i),!
 }
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...