All
Accepted answers
Alexander Koblov · Dec 22, 2021 go to post

I would even advise to use UNION ALL, because with UNION IRIS checks if resulting set has no duplicate rows, and there is no need to do this check with INSERT.

Alexander Koblov · Dec 10, 2021 go to post

Open settings for the web application (Menu -> Web applications -> [choose web application]) where you run the production and put %CSP.Error.cls as the "Custom Error Page". Reproduce the error. You should see the actual error on the page.

Alexander Koblov · Oct 27, 2021 go to post

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 go to post 
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 go to post

Should work. What does this function returns? Does it work if you put something very simple in the bat file? E.g.

echo qq > c:\temp\test.txt
Alexander Koblov · Aug 18, 2021 go to post

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=RSQL_windowfunctions

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

Alexander Koblov · Aug 9, 2021 go to post

You are connecting to the server 'localhost'. This server sends back the certificate. And this certificate says that it is for the server "cache". "cache" is not equal to the "localhost", hence the error.

Either access the server using the name "cache" (e.g. by changing hosts file). Or change the certificate

Alexander Koblov · Jul 27, 2021 go to post

do $System.DeepSee.Reset(1) is a very strong command. It clears all BI cache and terminates all running BI processes. That is -- if users are running queries at the moment, they get the errors.

If you find that you are running this command on the regular basis -- contact InterSystems Support

Alexander Koblov · Jul 19, 2021 go to post

Hi Muhammad.

Perhaps Zen button does not have such attribute -- "onselect". Maybe you need "onclick" instead?

See the documentation for list of possible attributes

Alexander Koblov · Jul 19, 2021 go to post

Hi Kevin.

What version of Caché or InterSystems IRIS do you have? INFORMATION_SCHEMA.COLUMNS seem to be available only since Caché 2015.2

Alexander Koblov · Apr 23, 2021 go to post

In this case I think "sorts after" (]]) is better. So that locale is taken into the account.

Alexander Koblov · Apr 15, 2021 go to post

Afaik for Application Roles to take place, user should re-login -- Roles are assigned on login. So -- no need to restart the web application, but user should log out and login again.

And yes, Application Roles is a great tool to minimize privileges that are given to the user directly

Alexander Koblov · Apr 15, 2021 go to post

a) Try setting error page as %CSP.Error.cls for this web application, then errors are shown on the page itself, instead of being logged

b) Reproduce the error with ^%ISCLOG enabled:

kill ^%ISCLOG
set ^%ISCLOG = 3

Reproduce the error quickly. Disable ^%ISCLOG

set ^%ISCLOG = 0

Check for errors in ^%ISCLOG:

 zwrite ^%ISCLOG
Alexander Koblov · Feb 10, 2021 go to post

Also, you can create calculated dimension member and sort members by PROPERTIES("KEY") using following expression:

ORDER([DateOfSale].[Actual].[DaySold].Members,[DateOfSale].CurrentMember.PROPERTIES("KEY"),BDESC)

Alexander Koblov · Feb 2, 2021 go to post

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=GIKNOW_kpi#GIKNOW_kpi_configuration_properties

Alexander Koblov · Dec 2, 2020 go to post

Great!

I think what you did is a proper way to do this, because WSDL itself does not define this type, so there is no way for IRIS to figure out it automatically.