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...