Hi,

I know your question was about ObjectScript, but if Python is allowed in your environment, I suggest to use it to get JSON : 

resultsetJSON.py :

import iris
import json
import getpass
import os
if 'IRISHOSTNAME' in os.environ:
    hostname = os.environ['IRISHOSTNAME']
else:
    hostname = input('hostname [localhost] :') or "localhost"
if 'IRISPORT' in os.environ:
    port = os.environ['IRISPORT']
else:
    port = input('port [1972] :') or "1972"
if 'IRISUSERNAME' in os.environ:
    username = os.environ['IRISUSERNAME']
else:
    username = input('login [_SYSTEM] :') or "_SYSTEM"
if 'IRISPASSWORD' in os.environ:
    password = os.environ['IRISPASSWORD']
else:
    password = getpass.getpass('password [SYS]:') or 'SYS'
if 'IRISNAMESPACE' in os.environ:
    namespace = os.environ['IRISNAMESPACE']
else:
    namespace = input('namespace [IRISAPP] :') or "IRISAPP"
connection_string = hostname + ":" + port + "/" + namespace
connectionIRIS = iris.connect(connection_string, username, password)
cursorIRIS = connectionIRIS.cursor()
print("Connected to",connection_string)

query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
cursorIRIS.execute(query)
 
resultSet = cursorIRIS.fetchall()

list = []
result = {"results":len(resultSet),"items":list}
for x in resultSet:
    row = {"name":x[0],"age":x[1],"dob":x[2]}
    list.append(row)
print(json.dumps(result))
 
connectionIRIS.close()
Python
Python

Result :

python resultsetJSON.py
hostname [localhost] :
port [1972] :51779
login [_SYSTEM] :
password [SYS]:
namespace [IRISAPP] :
Connected to localhost:51779/IRISAPP
{"results": 3, "items": [{"name": "Pantaleo,Mary Q.", "age": 36, "dob": "Monday 07 December 1987"}, {"name": "Underman,Laura I.", "age": 71, "dob": "Friday 16 May 1952"}, {"name": "Huff,Lydia J.", "age": 26, "dob": "Sunday 09 November 1997"}]}
Shell Session
Shell Session

{
"results":3,
"items":[
{
"name":"Pantaleo,Mary Q.",
"age":36,
"dob":"Monday 07 December 1987"
},
{
"name":"Underman,Laura I.",
"age":71,
"dob":"Friday 16 May 1952"
},
{
"name":"Huff,Lydia J.",
"age":26,
"dob":"Sunday 09 November 1997"
}
]
}
JSON
JSON

Very good article ; thank you @Luis Angel Pérez Ramos 
For those who want to extend the discovery of FHIR, you can access the online documentation on the official web site.

Thanks @Alberto Fuentes for this very good example of LLM + Embedded Python.

Welcome to Developer Community moderators @Enrico Parisi 
'Hoping you're doing well.

Hi @Theo Stolker 

the log is managed by the ^FSLogChannel global.

 set ^FSLogChannel(channelType) = 1

For example, to enable logging for all types of information, enter:

 set ^FSLogChannel("all") = 1

ChannelType is one of the following:

• Msg — Logs status messages.
• SQL — Logs SQL-related information.
• _include — Logs information related to searches that use the _include and _revinclude parameters.
• all — Logs all three types of information.

That's very handy ; thank you @Evgeny Shvarov 

Thanks @Evgeny Shvarov ; that is very handy

Here a simple python test used :

import iris
import ssl
import getpass
import os
 
def main():
    connection_string = "k8s-092c0f86-acbb1223-47d44444fb-7667ad082a377a9e.elb.us-east-1.amazonaws.com:443/USER"
    try:
        username = os.environ['CLOUDLOGIN']
    except:
        username = getpass.getpass('Login:')
    try:
        password = os.environ['CLOUDPASSWORD']
    except:
        password = getpass.getpass('Password:')

    context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
    context.verify_mode=ssl.CERT_REQUIRED
    context.check_hostname = False
    context.load_verify_locations("certificateSQLaaS.pem")

    connection = iris.connect(connection_string, username, password, sslcontext=context)
 
    print("connected")
    tablename = "data.movie"
    cursor = connection.cursor()
    try:
        cursor.execute("DROP TABLE "+tablename)   
        print(tablename+" dropped succesfully") 
    except InterfaceError as err:
        print(f"Unexpected {err=}")
    except Exception as err:
        print("ERROR WHILE DROPPING TABLE "+tablename)
        print(f"Unexpected {err=}, {type(err)=}") 
    try:
        cursor.execute("CREATE TABLE "+tablename+" (title varchar(500), year int, score numeric)")
        print(tablename+" created succesfully")
    except Exception as err:
        print("ERROR WHILE CREATING TABLE"+tablename) 
        print(f"Unexpected {err=}, {type(err)=}")
    data = [
        ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
        ("Monty Python's The Meaning of Life", 1983, 7.5),
        ("Monty Python's Life of Brian", 1979, 8.0),
    ]
    try:
        cursor.executemany("INSERT INTO "+tablename+" VALUES(?, ?, ?)", data)
        print("data succesfully inserted in "+tablename)

    except Exception as err:
        print("ERROR WHILE INSERTING DATA IN"+tablename) 
        print(f"Unexpected {err=}, {type(err)=}")
    connection.commit()
    connection.close()
    print("disconnected")
 
if __name__ == "__main__":
    main()
Python
Python

By investigating a bit further, I've discovered the line you should avoid in your SSLConfig.properties : 

# cipherSuites = TLS_AES_256_GCM_SHA384

By commenting it, everything's working.

But again, you only need 2 settings to make it work :

trustStore = keystore.jks
trustStorePassword = 123456

A big thank to @Evgeny Shvarov who made my day 😁

By simply removing extra lines in the SSLSetting.properties in order to only keep these 2 lines :

trustStore = /Users/guilbaud/keystore.jks
trustStorePassword = 123456
Java
Java

Everything works now in DBeaver :

Despite it still not work for me with DBeaver/Java, the good news it that it works with Python :

import iris
import ssl
import time
 
def main():
    connection_string = "k8s-092c0f86-acbb1223-47d44444fb-7667ad082a377a9e.elb.us-east-1.amazonaws.com:443/USER"
    username = "SQLAdmin"
    password = "********"
 
    context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
    context.verify_mode=ssl.CERT_REQUIRED
    context.check_hostname = False
    context.load_verify_locations("certificateSQLaaS.pem")

    connection = iris.connect(connection_string, username, password, sslcontext=context)
 
    print("connected")
    # when finished, use the line below to close the connection
    time.sleep(55)
    connection.close()
    print("disconnected")
 
if __name__ == "__main__":
    main()
Python
Python

I'm using intersystems-jdbc-3.7.1.jar in my both tests (java and DBeaver)

Hi @Evgeny Shvarov 
keystore.jks and SSLConfig.properties are well copied in /Applications/DBeaver.app/Contents/MacOS (and even /Applications/DBeaver.app/Contents/Eclipse) ; but DBeaver still answers :

[InterSystems IRIS JDBC] Communication link failure: Socket is closed

As you've seen I've also made a test outside DBeaver, with a simple java test which fails as well, with the same message.

And all configuration files are located in my local java directory test.

I've regenerated the keystore.jks several times with the keytool command. Same result.

I've understood that the current SQL connections are in fact the result of the queries run from the SQL Query tools in the portal.

A shutdown + restart didn't fix the issue

And quite amazingly, in the metrics I see 43 SQL connections :

I omit to mention it, but yes, all IP addresses are allowed : 

If I activate the debug in the SSLConfig.properties, I don't receive too much information :

logFile (class java.lang.String) = javatls.log
protocol (class java.lang.String) = TLSv1.3
cipherSuites (class java.lang.String) = TLS_AES_256_GCM_SHA384
trustStore (class java.lang.String) = keystore.jks
trustStoreType (class java.lang.String) = JKS
trustStorePassword (class java.lang.String) = 123456
RNG seeding time 1 msec
Supported CipherSuites:
[TLS_AES_256_GCM_SHA384, TLS_AES_128_GCM_SHA256, TLS_CHACHA20_POLY1305_SHA256, TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256, TLS_ECDHE_ECDSA_WITH_CHACHA20_POLY1305_SHA256, TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256, TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256, TLS_DHE_RSA_WITH_AES_256_GCM_SHA384, TLS_DHE_RSA_WITH_CHACHA20_POLY1305_SHA256, TLS_DHE_DSS_WITH_AES_256_GCM_SHA384, TLS_DHE_RSA_WITH_AES_128_GCM_SHA256, TLS_DHE_DSS_WITH_AES_128_GCM_SHA256, TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384, TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384, TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256, TLS_DHE_RSA_WITH_AES_256_CBC_SHA256, TLS_DHE_DSS_WITH_AES_256_CBC_SHA256, TLS_DHE_RSA_WITH_AES_128_CBC_SHA256, TLS_DHE_DSS_WITH_AES_128_CBC_SHA256, TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384, TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384, TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256, TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256, TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384, TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384, TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256, TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256, TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA, TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA, TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_RSA_WITH_AES_256_CBC_SHA, TLS_DHE_DSS_WITH_AES_256_CBC_SHA, TLS_DHE_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_DSS_WITH_AES_128_CBC_SHA, TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA, TLS_ECDH_RSA_WITH_AES_256_CBC_SHA, TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA, TLS_ECDH_RSA_WITH_AES_128_CBC_SHA, TLS_RSA_WITH_AES_256_GCM_SHA384, TLS_RSA_WITH_AES_128_GCM_SHA256, TLS_RSA_WITH_AES_256_CBC_SHA256, TLS_RSA_WITH_AES_128_CBC_SHA256, TLS_RSA_WITH_AES_256_CBC_SHA, TLS_RSA_WITH_AES_128_CBC_SHA, TLS_EMPTY_RENEGOTIATION_INFO_SCSV]
Enabled CipherSuites:
[TLS_AES_256_GCM_SHA384]
Bash
Bash

Hi @Benjamin De Boe,

thanks for this useful article.

I'm not able to connect to a running IRIS Cloud SQL instance, neither from DBeaver nor from a simple java test.

DBeaver :

Version 23.3.3.202401211839

Java :

java checkConnection
checkConnection caught exception: java.sql.SQLException: [InterSystems IRIS JDBC] Communication link failure: Socket is closed

import java.sql.*;
import javax.sql.*;
import com.intersystems.jdbc.*;
import java.sql.Connection;

public class checkConnection{
  public static void main(String[] args) {
    try {

      String dbUrl = 
        "jdbc:IRIS://k8s-092c0f86-acbb1223-47d44444fb-7667ad082a377a9e.elb.us-east-1.amazonaws.com:443/USER"; 
      String user = "SQLAdmin";
      String pass = "********";

      IRISDataSource ds = new IRISDataSource();
      ds.setURL(dbUrl);
      ds.setUser(user);
      ds.setPassword(pass);
      ds.setConnectionSecurityLevel(10);
      Connection dbconnection = ds.getConnection();
      System.out.println("Connected to InterSystems IRIS via JDBC.");


     
// Use IRISDataSource to open a connection
// Execute a query and get a scrollable, updatable result set.
      String sql="Select sysdate as now";
      PreparedStatement pstmt = dbconnection.prepareStatement(sql);
      java.sql.ResultSet rs = pstmt.executeQuery();

// Move to the first row of the result set and change the name.
      rs.next();
      System.out.println("\n date = " + rs.getString("now"));


// Close objects and catch any exceptions.
      pstmt.close();
      rs.close();
      dbconnection.close();
    } catch (Exception ex) {
      System.out.println("checkConnection caught exception: "
             + ex.getClass().getName() + ": " + ex.getMessage());
    }
  } // end main()
} // end class checkConnection
Java
Java

As you can see keystore.jks and SSLConfig.properties are in the same directory as the java class :

After the test, the javatls.log is empty

Hi @Pierre LaFay 

your method works fine. 

Without the resource the <PROTECT> error is raised : 

With the resource you have access to the method :