Looks like I've gotten past the "Driver could not be loaded issue." I'm now getting:

Remote JDBC error: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Certificates do not conform to algorithm constraints".

This appears to be a certificate issue on the SQL Server side, and neither property trustServerCertificate=true nor encrypt=false has any effect.

I've gone over the driver configuration dozens of times, verifying the driver classname, classpath, permissions and tried many different variations of properties. The result from the IRIS terminal test:

USER>d $system.SQLGateway.TestConnection("SqlServerJDBC")

Connection failed:

USER>zw %objlasterror
%objlasterror="0 "_$lb($lb(5023,$c(0),,,,,,,,$lb(,"USER",$lb("e^TestJConnection+15^%apiGTW^1","e^TestConnection+25^%apiGTW^1","e^zTestConnection+1^%SYSTEM.SQLGateway.1^1","d^^^0"))))/* ERROR #5023: Remote Gateway Error:  */

Doesn't seem to tell me much ...

@Rodolfo Moreira, thank you for posting your solution. I've been struggling with getting either ODBC or JDBC configured for communicating with MSSQL ... and while JDBC still isn't working, your post allowed me to establish an ODBC connection to the SQL server.

With IRIS 2021.1 on RHEL 8.4, the only steps (performed in <iris-install-dir>/bin) required were:

 [root@HCSVR bin]# mv odbcgateway.so odbcgateway.so.old

[root@HCSVR bin]# cp odbcgatewayur64.so odbcgateway.so

[root@HCSVR bin]# chown irisusr:irisusr odbcgateway.so

@Alexander Koblov, can you clarify the relationship between the JDBC Gateway and the SQL Gateway Connection option for JDBC? Is the JDBC Gateway the same thing as the %JDBC.Server located under External Language Servers in IRIS 2021.1, since there is no "JDBC Gateway" menu option in the Management Console?

I'm currently attempting to set up a JDBC SQL Gateway connection for MSSQL Server and am unable to get the  jre16 (or jre8) driver to load. OpenJDK 1.8 is installed, JAVA_HOME is set, $JAVA_HOME/bin is in the path, and IRIS has been restarted. We're running IRIS/HealthConnect on RHEL 8.4. I noticed that when clicking the "Test" button in the connection configuration page that the %JDBC.Server is started before getting the error "Connection failed. The driver cannot be loaded" so there appears to be some relationship between the two, but even with logging turned on I'm not seeing any reference to the MSSQL driver's failure to load.

Do you have a space character between the ! and SFTP in your service settings? You shouldn't ... I'm not sure that's related to the problem you're having but that's the first thing I would fix.

Can you establish a connection to the target system from the HealthConnect server itself, using the command-line sftp utility? I believe you mentioned FileZilla in another post, but it wasn't clear that you were attempting to connect from your own desktop or the HealthConnect server itself (which I suppose could be the same thing for a local, personal installation).

Finally, to be clear, you've created a Credentials entry in Ensemble (Interoperability in most recent versions) | Configure | Credentials, called something other than !SFTP, that contains the user ID and password for the remote system? And you've specified that entry name in the Credentials field? You mention "login information in Basic Settings" which I would assume means credentials, but I just want to make sure ...

In the latest versions of HealthConnect/IRIS for Health, sftp is now a specific protocol that can be selected from a drop-down list in the Connection Settings section. And if you are on one of the latest versions, make sure you're selecting the correct Credentials field as there are two: Credentials in the Basic Settings section, and SFTP Passphrase Credentials in the SFTP section. The former is where you want your logon user ID and password credentials selected; the latter is specifically for the passphrase used to unlock the private key when using public/private key authentication. It does nothing for SFTP password-based authentication.

PID:3 is a list, meaning it has the potential to contain multiple ID numbers separated by the repetition character (normally a tilde). If you only want the first ID number to appear on the target field, don't use a for each loop. Just use a single set rule as shown below:

Note that there's no repetition indicator in the target path, and we're selecting the first repetition in the source:

Sure, just set the value of target.{PID:3} to source.{PID:3.1}. It will overwrite the entire field with just what was in the first sub-field:

Like this (assuming you want the first iteration of PID:3.1 to overwrite all iterations of PID:3):

I will add that I am very grateful to ISC for finally adding this foreach feature to the routing rule functionality (it appears to have become available with 2020.4). I have one quibble, though ... it would be wonderful to be able to exit the loop without returning from the ruleset (i.e. "break"). The current mechanism forces you to loop through all repetitions regardless of the desired condition being met before the final loop iteration is reached.

A design pattern where I might wish to assign a value to a variable and exit the loop when the condition is met (rather than executing a send) is something I can envision doing regularly.

If you're attempting to determine whether the same value appears in a field for all repetitions of a given repeating segment, the ..RegexMatch() function, virtual document bracket syntax and an appropriately constructed regular expression should be able to handle this:

EDIT: My original example included a caret ("^") as the regex begin string anchor, but it appears the RegexMatch() method forces the pattern the pattern to be begin and end anchored. In other words, the pattern supplied as the 2nd argument is automatically wrapped with "^" at the beginning and "$" at the end. You can add them if you like, but doing so doesn't change the behavior.

I would assume that @Jonathan Lent has a scheduled purge job in Task Manager; ISC ships HealthConnect with one (Ens.Util.Tasks.Purge) that can be configured for a retention period, along with a few other settings:

If he's overriding the OnDelete method, I suppose it could be used to delete the on-disk file.

Hi Ed, it appears to be the '>1 expression. Try using =0 instead, or perhaps <1.

Syntactically the '>1 logical construct is valid, but to me seems unconventional when there's a single logical operator that performs the exact same function.

The EnsLib.* classes don't have inherent categories. You can certainly add a Category property to classes that you create, though.

Multiple Production Items will use the same class (for example, the HL7 TCP Services/Processes), and categories are a Production Item property. You would need to determine first which items are using that class, then get the categories from those items.

Is that what you're looking to do?

The "easy" options indicated by the source code do some interesting things, but showing %IO* isn't among them. I'll keep looking 

Is the mechanism for doing this something you can share?