I wrote an article about the creation of a REST service that maybe can help you.

https://community.intersystems.com/post/creating-rest-service-iris

You can check the ClassMethod TestPost where a data is received from a post call and is sent finally to a business operation where is transformed into a DynamicObject and parsed to another class. This is the business operation:

Class WSTEST.BO.PersonSaveBO Extends EnsLib.REST.Operation
{

Parameter INVOCATION = "Queue";

Method savePerson(pRequest As WSTEST.Object.PersonSaveRequest, Output pResponse As WSTEST.Object.PersonSaveResponse) As %Status
{
	try {
      set person = ##class("WSTEST.Object.Person").%New()
      #dim request as %DynamicObject = {}.%FromJSON(pRequest.JSON)
      set person.PersonId = request.PersonId
      set person.Name = request.Name
      set person.LastName = request.LastName
      set person.Sex = request.Sex
      set person.Dob = request.Dob

      set tSC = person.%Save()
      set pResponse = ##class("WSTEST.Object.PersonSaveResponse").%New()
      set pResponse.PersonId = person.PersonId
      set pResponse.Name = person.Name
      set pResponse.LastName = person.LastName
      set pResponse.Sex = person.Sex
      set pResponse.Dob = person.Dob
      
   }catch{
       Set tSC="Error saving the person"
   }
   Quit tSC
}

XData MessageMap
{
<MapItems>
  <MapItem MessageType="WSTEST.Object.PersonSaveRequest">
    <Method>savePerson</Method>
  </MapItem>
</MapItems>
}

}

UserTable and DataTable are related? I'm guessing that if Condition1 is the relation between the tables you would try this query:

SELECT 
    COUNT(CASE WHEN data.a = "Condition1" then 1 ELSE NULL END) as "ValueA", 
    COUNT(CASE WHEN data.b = "Condition2" then 1 ELSE NULL END) as "ValueB", 
    COUNT(CASE WHEN data.c = "Condition3" then 1 ELSE NULL END) as "ValueC", 
user.id
 FROM UserTable user left join DataTable data on user.id = data.user

In my opinion the best approach is to create a BO for each database connection and invoke them from a BPL, getting the results and doing whatever you want. 

Are you using an aggregate function in your query? You can see in the documentation that %SQLCODE = 0 is returned for queries with aggregates as SUM or AVG because it's returning a row even with a null value of the aggregation function.

You can send the List by reference to the Python method, populate it inside the method and use the populated list from the objectscript who call it.

Something like this:

set listOfStrings = ##class("%Library.ListOfDataTypes").%New()
do ..TestPython(listOfStrings)
$$$TRACE("What a beautiful trace! There are "_listOfStrings.Count()_" element(s)")
...
...
ClassMethod TestPython(ByRef listOfStrings As %List) [ Language = python ]
{

	import iris
	
	
	listOfStrings.Insert("One")
	listOfStrings.Insert("Two")
	listOfStrings.Insert("Three")
	
	return 1
}
for x=1:1:rs.%ResultColumnCount{
     //Get the value of each column
         set colValue = rs.%GetData(x)
         //Get the name of each column
         set colName = cols.GetAt(x).colName
         set colType = rs.GetColumnType(x)

Have you tried this code?

If you want something low-code you can create a Data Transformation with a HL7 message as source and as destination a class created ad-hoc with the structure that you need. Take a look to this example:

The destination in a class called RestTest.FirstRestObject with this definition:

Class RestTest.FirstRestObject Extends (%Persistent, %JSON.Adaptor, Ens.Util.MessageBodyMethods, Ens.Request)
{

/// Description
Property Name As %String;
/// Description
Property LastName As %String;
Property Childs As list Of NestedRestObject;
}

This object has an SQL representation wich you could use later for your analysis:

If your business service extends from Ens.BusinessService you can use this instruction to get the name of the Business Service:

set businessServiceName = ..%ConfigNameGet()

I would try to export all the classes of the production and import it into a new namespace. After that try to open a DTL and check if it works. If there is no error the problem would be a corruption in the original namespace.

Last update about Hibernate from Global Summit 2023! Hibernate 6.1 will be supported by IRIS and it will be integrated in Hibernate, no need to be included into your Java project as an independent library. 

Well, at the end, the decision process is like the Osiris judgement, you have to put pros and cons in a balance: performance, scalability, easy use, flexibility, etc.

From my experience working with Java,  hibernate is not the decisive criteria by itself. I think that the problem is that the architect usually does not know well enough the potential of IRIS and decide to take the easy way.

Have you tried to update your 2022.1 version with the last one? You just need to run the installer of the last version to launch the update. 

You can check it from System Monitor Log of each Mirror member. You can see an example of the log:

05/22/23-07:22:26 [SYSTEM MONITOR] System Monitor started in %SYS
05/22/23-07:26:07 [SYSTEM MONITOR] Mirror status changed. Member type = Failover, Status = Backup
05/22/23-07:33:01 [SYSTEM MONITOR] System Monitor started in %SYS
05/22/23-07:33:01 [SYSTEM MONITOR] Mirror state: Member type = Failover, Status = Synchronizing
05/22/23-07:33:31 [SYSTEM MONITOR] Mirror status changed. Member type = Failover, Status = Backup
05/22/23-07:35:55 [SYSTEM MONITOR] System Monitor started in %SYS
05/22/23-07:35:56 [SYSTEM MONITOR] Mirror state: Member type = Failover, Status = Transition
05/22/23-07:36:26 [SYSTEM MONITOR] Mirror status changed. Member type = Failover, Status = Waiting
05/22/23-07:36:26 1 alerts posted in messages.log
05/22/23-07:40:26 [SYSTEM MONITOR] Mirror status changed. Member type = Failover, Status = Backup
05/22/23-07:58:15 [SYSTEM MONITOR] System Monitor started in %SYS
05/22/23-07:58:15 [SYSTEM MONITOR] Mirror state: Member type = Failover, Status = Waiting
05/22/23-08:06:43 [SYSTEM MONITOR] Mirror status changed. Member type = Failover, Status = Backup
05/22/23-08:10:37 [SYSTEM MONITOR] System Monitor started in %SYS
05/22/23-08:10:37 [SYSTEM MONITOR] Mirror state: Member type = Failover, Status = Waiting
05/22/23-08:34:40 1 alerts posted in messages.log
05/22/23-08:35:10 [SYSTEM MONITOR] Mirror status changed. Member type = Failover, Status = Backup
05/22/23-10:50:25 [SYSTEM MONITOR] System Monitor started in %SYS
05/22/23-10:50:25 [SYSTEM MONITOR] Mirror state: Member type = Failover, Status = Waiting
05/22/23-11:00:15 [SYSTEM MONITOR] Mirror status changed. Member type = Failover, Status = Backup
05/26/23-11:03:06 [SYSTEM MONITOR] System Monitor started in %SYS
05/26/23-11:03:07 [SYSTEM MONITOR] Mirror state: Member type = Failover, Status = Transition
05/26/23-11:03:37 [SYSTEM MONITOR] Mirror status changed. Member type = Failover, Status = Backup
05/26/23-11:05:08 [SYSTEM MONITOR] Mirror status changed. Member type = Failover, Status = Primary
05/26/23-11:05:10 1 alerts posted in messages.log