The first two systems I worked with using InterSystems technology were a PDP-11 running M11+ and a VAX 11/750 running M/VX. Too many years ago to count! 😊
Since then I've used most, if not all, InterSystems products up to IRIS and HealShare today.
I'm italian living in Switzerland and I work as Senior Consultant at GAIVOTA consultin SA, we provide professional services for InterSystems and other technologies.
Curiosity: apart from DC, I don't have ANY social account! 😁
To address similar cases some time ago I developed a little utility to export to a %DynamicArray the output from an SQL query.
In my case I had to export existing classes that did not extend %JSON.Adaptor (I'm not even sure %JSON.Adaptor existed at that time).
Class Community.SQL2JSON
{
/// Execute am SQL query and returns a %DynamicArray containing the rows (as %DynamocObject) returned by the query
/// RetDynArray is the returned %DynamicArray containing the results
/// Parameters used by the query must be included in the query using placeholders (?) e passed By Reference in the local variable array ParamArray
/// where the root node contains the number of parameters, ie:
/// ParamArray = 1
/// ParamArray(1) = 123
ClassMethod QueryToJSON(ByRef SQLQuery As %String, Output RetDynArray As %DynamicArray, ByRef ParamArray As %String) As %Status
{
Set sc=$$$OK
Try {
Set stSql=##class(%SQL.Statement).%New()
Set stSql.%ObjectSelectMode=0
Set rsSql=##class(%SQL.Statement).%ExecDirect(.stSql, .SQLQuery,ParamArray...)
If rsSql.%SQLCODE < 0 {
Set sc = $$$ERROR($$$GeneralError,"%SQLCODE="_rsSql.%SQLCODE_", %Message="_rsSql.%Message)
Quit
}
If '$IsObject($g(RetDynArray)) Set RetDynArray = []
While rsSql.%Next(.sc) {
If $$$ISERR(sc) Quit
Set RowDynObj={}
Set sc=..RowToDynObj(stSql,rsSql,.RowDynObj)
If $$$ISERR(sc) Quit
Do RetDynArray.%Push(RowDynObj)
}
} Catch CatchError {
#dim CatchError as %Exception.SystemException
Set sc=CatchError.AsStatus()
}
Quit sc
}
/// Convert a recordset in a %DynamicObject with property name equal to the column name
/// If RowDynObj is passed, then adds the properties are added to it, otherwise creates and returns a new dynamic object
ClassMethod RowToDynObj(StSql As %SQL.Statement, RsSql As %SQL.StatementResult, ByRef RowDynObj As %DynamicObject) As %Status
{
Set sc=$$$OK
Try {
If '$IsObject($g(RowDynObj)) Set RowDynObj= {}
For col=1:1:StSql.%Metadata.columnCount {
Set ColumnName=StSql.%Metadata.columns.GetAt(col).colName
Set ColumnValue=$Property(RsSql,ColumnName)
Do RowDynObj.%Set(ColumnName,ColumnValue)
}
} Catch CatchError {
#dim CatchError as %Exception.SystemException
Set sc=CatchError.AsStatus()
}
Quit sc
}
}
Using it is very simple:
EPTEST>Set SQLQuery="select Name, DOB as ""Birth Date"", Home_City as City from Sample.Person where Home_City=?"
EPTEST>Set ParamArray=1
EPTEST>Set ParamArray(1)="Newton"
EPTEST>Set sc=##class(Community.SQL2JSON).QueryToJSON(SQLQuery,.RetDynArray,.ParamArray)
EPTEST>Do RetDynArray.%ToJSON()
[{"Name":"Uhles,Susan D.","Birth Date":31836,"City":"Newton"},{"Name":"Ubertini,Debby N.","Birth Date":42513,"City":"Newton"},{"Name":"Harrison,Rob E.","Birth Date":62265,"City":"Newton"},{"Name":"Adams,Robert E.","Birth Date":62769,"City":"Newton"}]
EPTEST>
"But the app could be installed in any database, right?"
I believe it's wrong, the app could be installed in any NAMESPACE.
Now the question is, what role have access to the databases associated with the namespace?
Leaving mappings aside, a namespace uses two databases, "Default Database for Globals" and "Default Database for Routines" (code), usually the two databases coincide but you cannot assume it's so.
When I configure two databases for a namespace I use a single resource for both, I consider this a good practice but, again, this cannot be assumed.
A generalized solution should find the resources used by the installation destination namespace.
This is how you can get the databases used by the namespace "MYAPP":
%SYS>Set sc=##Class(Config.Namespaces).Get("MYAPP",.NsProperties)
%SYS>Write NsProperties("Routines")
MYAPP-R
%SYS>Write NsProperties("Globals")
MYAPP-G
Now, for each database you can get the associated resource with:
%SYS>Set dbr=##class(SYS.Database).%OpenId(##class(Config.Databases).GetDirectory(NsProperties("Routines")))
%SYS>Write dbr.ResourceName
%DB_MYAPP
%SYS>
%SYS>Set dbg=##class(SYS.Database).%OpenId(##class(Config.Databases).GetDirectory(NsProperties("Globals")))
%SYS>Write dbg.ResourceName
%DB_MYAPP
In this case for the MYAPP namespace you only need permission to the %DB_MYAPP resource.
If the two databases use different resources, then you need permission to both the associated resources.












Yes, it's normal, Web Applications are local configuration on each mirror member.
If/when you create a new Web Application you need to create it in all mirror members. Personally I prefer to create the application using a script (a class method) that is deployed and run (typically along with code etc.) in all members nodes.
In addition to Web Applications note that also all security related configuration (Users, Roles, Resources, SQL privileges etc.) are local to each mirror member.
It's my understanding that InterSystems is working on enhancing the mirror to include security configuration synchronization (Web Applications are part of the Security) between mirror member.