All
Accepted answers
Sylvain Guilbaud · Jan 27, 2024 go to post

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.
Sylvain Guilbaud · Jan 25, 2024 go to post

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()

Sylvain Guilbaud · Jan 25, 2024 go to post

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
Sylvain Guilbaud · Jan 25, 2024 go to post

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

Everything works now in DBeaver :

Sylvain Guilbaud · Jan 25, 2024 go to post

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()

Sylvain Guilbaud · Jan 25, 2024 go to post

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.

Sylvain Guilbaud · Jan 25, 2024 go to post

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.

Sylvain Guilbaud · Jan 25, 2024 go to post

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]
Sylvain Guilbaud · Jan 25, 2024 go to post

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

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

Sylvain Guilbaud · Jan 9, 2024 go to post

Bonjour Robert,
I only wish to be able to ingest data using LOAD DATA with the choice regarding the format of the date in the data source. Currently the date is only supposed to be expressed in the source in the ODBC|TimeStamp format YYYY-MM-DD. 

Sylvain Guilbaud · Jan 5, 2024 go to post

Hello @Jean-Charles Cano,

messages starting with 'ConfigItem' are simply part of the informational messages in the event log indicating the start of a service/process/operation in production (specifying the process ID of the associated job).
According to your screenshots, this means that the connection failed on 12/27 at 04:25 and the service was successfully restarted (ConfigItem message), on 12/28 at 14:45, as well as on the same day at 2:50 p.m., 4:27 p.m., etc.
Maybe the problem is with the StayConnected = -1 setting.

Changing it to a positive value or to 0 will perhaps resolve the problem.

Sylvain Guilbaud · Oct 23, 2023 go to post

Hello @Robert Cemper 
thanks for this beautiful personal story ; a priceless testimonial of what happened before the IRIS era. 

If it can reassure you, in no way boring and even very pleasant from a narrative and purely literary point of view. 😊