#JDBC

Hi Community,

Watch this video to see how to connect to InterSystems Cloud Services from your Java application using the InterSystems JDBC driver:

⏯ Connecting to InterSystems Cloud Services with Java

Hi There, 

I am having issues trying to obtain a JDBC driver which is backwards compatible with java 6 for a solution which will call my IRIS instance, I have already logged a WRC ticket which is looking unlikely that there will be any development to create a driver for such legacy tech. 

The Java 6 app is end of life, however won't be replaced until after my project goes live, hence why I ask the question. 

I am also investigating the use of an adaptor to bridge between Java 6 and IRIS 2022.

Hi,

Does anyone have experience with connecting to IRIS with OLEDB through an OLEDB-ODBC bridge? In this case the client can only speak OLEDB at this moment.

Regards,

Marcel

I have questions about Java Versions needed for setting up a %JDBC External Language Server

I'm trying to start the %JDBC. External Language Server my local instance of IRIS (2023.1.2). I've recently upgraded Java to version 21.0.1 

When starting the External Language Server, I get this error message:

I was attempting to test a Linked JDBC View to MS SQL database and noticed I could not connect. When I look at the JDBC Gateway I noticed that at the Server Level it was down. However the page keeps timing out when ever I attempt to make any changes or start it, it will not respond.

I thought I had found the Cache command to start it but it will not start. Is the following steps correct?

Ran into an issue this morning, that I am having a hard time trying to track down what might have caused the issue. We have a Business Rule that sends HL7 ADT to a Business Process that inserts the data into a MS SQL Server using a Custom Business Operation (SQL Outbound Adapter).

Early yesterday morning the Custom Business Operation reset its connection through the Java Gateway Server to the MS SQL Database, when it reconnected to the Java Gateway/MS SQL Database it stopped processing. So, the Business Rule had over 40,000 queued up to process.

I am currently adding a field to our Existing messaging from Epic, however there might be a possibility I need to back load data into the Ancillary system. While I have the previous messages that can be sent, they do not have this additional field that I am adding to the message.

I can do a lookup against Epic Clarity SQL Database; however, I don't want to throw a wrench into the workflow if the system cannot connect to the Epic Clarity SQL Database.

• What is the best solution for this?

Hi! I've extended my demo repository, andreas5588/demo-dbs-iris, to make it easy to test the FOREIGN SERVER and FOREIGN TABLE features in IRIS.

To achieve this, I created a namespace called FEDERATION. The idea is as follows:

1. Set up JDBC connections for each namespace.
2. Create a FOREIGN SERVER within the FEDERATION namespace for each connection.
3. Define a FOREIGN TABLE a least for one table based on each foreign server.

The Script:  demo-dbs-iris/src/sql/02_create_foreign_server.sql

IRIS does not support executing SQL statements that combine tables from different namespaces.

Hi Community,
I am getting below "The driver cannot be loaded" error on a MacBook.

obdbc8.jar file is present in the mentioned folder.
Looking forward to resolve the above issue or workaround
Thanks

I am trying to troubleshoot an issue I am having with the JDBC Gateway Server crashing at the %SYS level. I have numerous Linked Tables that are using JDBC to connect to a MS SQL database/tables to query values. I have written queries in other class files and within the class definition of the linked table to do the queries.

1. Should I be running the query in the class definition of the linked table as a class method? or is it best practice to run the query as a class method outside of the class definition of the linked table?

2. I was reading documentation and came across the Config.

Hello. Currently, we are developing using Cache 2018 version.
Our team is working on improving an existing legacy program so that it can also be used on the web.

Before asking my question, here is the development environment.

• IDE: IntelliJ
• Framework: Spring Boot, MyBatis
• DB Connection: JDBC (using the library provided by InterSystems)

Currently, we are successfully mapping global data through the %PERSISTENT class and able to query it with SQL. However, the problem is that the retrieved "Korean" data is all broken.

Due to MySQL's interpretation of SCHEMA differing from the common SQL understanding (as seen in IRIS/SQL Server/Oracle), our automated Linked Table Wizard may encounter errors when attempting to retrieve metadata information to build the Linked Table.

(This also applies to Linked Procedures and Views)

When attempting to create a Linked Table through the Wizard, you will encounter an error that looks something like this:

ERROR #5535: SQL Gateway catalog table error in 'SQLPrimaryKeys'. Error: ' SQLState: (HY000) NativeError: [0] Message: [MySQL][ODBC 8.3(a) Driver][mysqld-5.5.5-10.4.

Here is my code:

Method getStocks(pRequest As Stock.Message.Req, Output pResponse As Ens.StreamContainer) As %Status
{
     s tSC = pRequest.NewResponse(.pResponse)
     q:$$$ISERR(tSC) tSC

     #dim pRS As EnsLib.SQL.GatewayResultSet

     s tSC = ..Adapter.ExecuteQuery(.pRS, "select jsonb_agg(s) #>> '{}' FROM prod.stocks s where s.""Warehouse"" = ?", pRequest.Warehouse)
     q:$$$ISERR(tSC) tSC

     s pResponse = ##class(Ens.StreamContainer).%New()
     s pResponse.Stream = ##class(%GlobalCharacterStream).%New()

     i pRS.Next() {
          d pResponse.Stream.CopyFrom(pRS

Is it planned that LOAD DATA takes into account several DATE/DATETIME formats with, for example, a parameter indicating the format used in the source data?

example :

LOAD DATA .../...
USING
{
  "from": {
    "file": {
       "dateformat": "DD/MM/YYYY"
    }
  }
}

We connect to MS SQL Databases using the Microsoft JDBC Driver 12.2 using the following URL

jdbc:sqlserver://<server>:<port>;database=<database name>;trustServerCertificate=true;integratedSecurity=true;authenticationScheme=NTLM;domain=osumc;authentication=NotSpecified

They want to migrate the databases to the Azure Cloud and in doing so we need the Authentication to change to go through Microsoft Entra.

Consider a Natural Key with an Identity (Serial) field.

I cannot seem to acquire the generated value after persisting my entity. That is, the returned entity by Spring Data's "save" does not have the generated value.

The value is generated by the database, and I can query it after repository.save(entity).

I have done some testing and created a Github repo with it...

https://github.com/jesjobom/iris-hibernate-key-test
Am I missing something?

Get the quick and easy steps for setting up ODBC/JDBC connections to InterSystems IRIS® data platform and InterSystems IRIS for Health™:

• Setting Up JDBC Connections
• Setting Up ODBC Connections

Now in an easy-to-use, checklist format to help you work through the process on your own system.

Enjoy!

When using standard SQL or the object layer in InterSystems IRIS, metadata consistency is usually maintained through built-in validation and type enforcement. However, legacy systems that bypass these layers—directly accessing globals—can introduce subtle and serious inconsistencies.

Understanding how drivers behave in these edge cases is crucial for diagnosing legacy data issues and ensuring application reliability.
The DATATYPE_SAMPLE database is designed to help analyze error scenarios where column values do not conform to the data types or constraints defined in the metadata.

If you're migrating from Oracle to InterSystems IRIS—like many of my customers—you may run into Oracle-specific SQL patterns that need translation.

Take this example:

SELECT (TO_DATE('2023-05-12','YYYY-MM-DD') - LEVEL + 1) AS gap_date
FROM dual
CONNECT BY LEVEL <= (TO_DATE('2023-05-12','YYYY-MM-DD') - TO_DATE('2023-05-02','YYYY-MM-DD') + 1);

In Oracle:

• LEVEL is a pseudo-column used in hierarchical queries (CONNECT BY). It starts at 1 and increments by 1.
• CONNECT BY LEVEL <= (...) determines how many rows to

Is there a way problematically to see what Operations/Services are attached to a EnsLib.JavaGateway.Service and if there are issues with the EnsLib.JavaGateway.Service to stop/start those Services/Operations that might be attached to it?

Thanks

Is it possible to authenticate an xDBC (ODBC/JDBC) connection to InterSystems IRIS via (a 3rd party) OAuth server?

For REST APIs this is possible, but could this be achieved with OAuth? 

Out-of-the-box the ODBC/JDBC Drivers don't seem to have this option, but maybe some custom code could enable this? perhaps via Delegated Authentication and some OAuth classes customization, or some other way?

Has anyone done this already and can share how it was implemented, or someone with some guideline suggestions?

Hello all,

On one of my team's systems, we utilize a business operation with the EnsLib.SQL.OutboundAdapter to make SQL queries to another IRIS system using JDBC. To authenticate the connection, we utilize a user account on the target system.

We recently had a failure where this user account expired due to inactivity, causing all queries to error until the account was reactivated on the target system.

I have been trying to track down an issue we are seeing in our TEST environment with Memory usage.

We have Several BP's for years now that take a HL7 message, parse it apart, and make calls to a Custom EnsLib.SQL.OutboundAdapter to have it execute Insert/Select/Update/Delete stored procedures against a MS SQL Database via JDBC connection. We are using Microsoft's JDBC 12.2 driver to do this.

What we are seeing is that IRIS.WorkQueue globals are being defined for these calls but then the IRIS.WorkQueue is not being cleaned up and taking up large amounts of Memory.

Hello, dear colleagues.

I need to connect to a remote JavaGateway from an Ensemble service.

I am trying to use the EnsLib.JavaGateway.Service with a remote host where the JVM is running.

I can successfully ping the remote Java Gateway from EnsLib.JavaGateway.Service, and Ensemble reports that the service status is OK.

There are no network issues, and all necessary ports are accessible.

My requests work without any problems when I specify localhost in EnsLib.JavaGateway.Service.

I am using a JDBC connection to MS SQL server to execute a stored procedure to select data and bring it into InterSystems as a EnsLib.SQL.Snapshot. I loop through the EnsLib.SQL.Snapshot using a while loop, but I also want to iterate through the Columns within that Row to do logic.

Is there a way to iterate through the Columns of the current Row of the EnsLib.SQL.Snapshot so I can apply logic/rules for further processing?

Thanks

Scott

In the previous article, we talked about ODBC and connecting from C#. And now, let's look at JDBC and Java. The InterSystems JDBC driver is the recommended, high-performance way to integrate your Java applications.

Here is a step-by-step guide to getting your Java application connected to an IRIS instance using the JDBC driver.

Step 1: Obtain and Include the InterSystems IRIS JDBC Driver

Unlike ODBC drivers, which are often installed system-wide, JDBC drivers are typically distributed as JAR files that must be included in your Java project's classpath.

If InterSystems IRIS is installed on your local machine or another you have access to, you can find the file in install-dir/dev/java/lib/ or similar, where install-dir is the installation directory for the instance. Conversely, you can download the jar file from Driver packages page.

Or as suggested by @Dmitry Maslennikov in the comments, use the maven central repository for Maven:

<dependency>
    <groupId>com.intersystems</groupId>
    <artifactId>intersystems-jdbc</artifactId>
    <version>3.10.5</version>
</dependency>

or for Gradle:

implementation("com.intersystems:intersystems-jdbc:3.10.5")

Include the jar file in Project:

• Maven/Gradle: If you use a build tool, the simplest method is to add the InterSystems JDBC driver as a dependency in your pom.xml or build.gradle file. This automatically downloads and manages the JAR.
• Manual: For simple projects, you must place the JAR file in a project directory (e.g., /lib) and explicitly add it to your classpath when compiling and running.

I am currently experiencing frustration with trying to Authenticate an Active Directory account through JDBC as the Hospital System moves from OnPrem SQL Server to using Azure SQL Server with Microsoft Entra Authentication.

Microsoft cannot give me a straight answer of what is required from a JDBC standpoint to authenticate from a Linux environment.

I am working with WRC, but we are both struggling to find the specific answer for JDBC. If I use my local desktop and JDBC to connect through DBeaver I don't have an issue.

I have a business service that actively reads data from a remote Postgres database. OnProcessInput opens a XDBC (actually JDBC) connection, executes an SQL query, fetches several thousand rows, iterates the resultset, and closes the connection. On each iteration I also need to update each source row in the remote database using PreparedStatement.

In other words, in every OnProcessInput call I have a long running SELECT statement and several thousands small UPDATE statements.

When we need to integrate Caché/IRIS with other relational databases, one common question arises: “How do I set up the JDBC connection?”.
The official documentation doesn’t always provide a straightforward step-by-step guide, which can be frustrating, especially for beginners.

In this article, I’ll walk you through the entire process of configuring a JDBC connection with MySQL, from downloading the connector to linking tables in Caché/IRIS.

Note: JDBC connections in Caché/IRIS have some limitations, especially regarding caching and the use of ResultSet.

I’ve been exploring options for connecting Google Cloud Pub/Sub with InterSystems IRIS/HealthShare, but I noticed that IRIS doesn’t seem to ship with any native inbound/outbound adapters for Pub/Sub. Out of the box, IRIS offers adapters for technologies like Kafka, HTTP, FTP, and JDBC, which are great for many use cases, but Pub/Sub appears to be missing from the list.

Has anyone here implemented such an integration successfully?

For example:

• Would it make sense to leverage IRIS’s Business Service + REST API adapter to connect to Google’s Pub/Sub REST endpoints?