the log is managed by the ^FSLogChannel global.
set ^FSLogChannel(channelType) = 1For example, to enable logging for all types of information, enter:
set ^FSLogChannel("all") = 1ChannelType is one of the following:
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
defmain():
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().png)
By investigating a bit further, I've discovered the line you should avoid in your SSLConfig.properties :
# cipherSuites = TLS_AES_256_GCM_SHA384
.png)
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 = 123456Everything works now in DBeaver :
.png)
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
defmain():
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().png)
I'm using intersystems-jdbc-3.7.1.jar in my both tests (java and DBeaver)
.png)
.png)
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 :.png)
I omit to mention it, but yes, all IP addresses are allowed :
.png)
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]
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.png)
.png)
.png)
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;
publicclasscheckConnection{
publicstaticvoidmain(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 checkConnectionAs you can see keystore.jks and SSLConfig.properties are in the same directory as the java class :
.png)
After the test, the javatls.log is empty
your method works fine.
Without the resource the <PROTECT> error is raised :
.png)
With the resource you have access to the method :
.png)
Thanks @Brett Saviano
This is very useful and it works with IRIS 2023.3, without Private Web Server (removed from IRIS 2023.2)
.png)
A big applause 👏 to our Global Masters Heroes!
Congrats @Enrico Parisi, @Tani Frankel, @Daniel Aguilar
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.
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.
.png)
Changing it to a positive value or to 0 will perhaps resolve the problem.
Hello @Abhishek Raj
you can have a look at FHIR-HL7v2-SQL-Demo : it converts HL7v2 messages to HL7 FHIR
You're right @Alexey Maslov ; in @Pierre LaFay 's case, $INCREMENT is better as this avoids gaps in the numerical sequence.
Hi @Pierre LaFay
you can also use $SEQUENCE
$SEQUENCE is intended specifically for integer increment operations involving multiple simultaneous processes.
Congrats @Enrico Parisi @Luis Angel Pérez Ramos and @Daniel Aguilar 😀
Congrats to all the winners and all the participants ; greatful job and outcomes
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. 😊
Congratulations to you @Luis Angel Pérez Ramos
Thank you @VICTORIA CASTILLO
Thank you @Ben Spead