Robert Cemper · Jan 23 2m read
Global-Streams-to-SQL #2

Some technical background information

There is not just one class in this package: rcc.gstream.cls but also rcc.gstreamT.cls

While rcc.gstream works with direct access to the stream globals, the *T version uses
a Process Private Global (PPG) as Temporary storage.
using SELECT * FROM RCC.gstreamT WHERE RCC.useT('^jpgS')=1 and similar.

2 0
0 42
Robert Cemper · Jan 23 2m read

In general Global Streams are data objects embedded in Classes / Tables.
Using and viewing them with SQL is normally a part of the access to the containing tables.


During debugging or searching for strange or unexpected behavior there could be the need to
get closer to the stored stream. No big problem with direct access to Globals with SMP or Terminal.
But with SQL you are lost.
So my tool provides dynamic access to Global Streams wherever you may need this
Special thanks to @Oliver Wilms for the inspiration for this tool.

3 0
0 48
Scott Roth · Oct 13, 2022
JDBC Gateway Server Crash

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?

0 3
0 100

I just wrote up a quick sample to help a colleague load data into IRIS from R using RJDBC, and figured it's worth sharing here for future reference.

Ultimately it was pretty simple, aside from IRIS not liking "." in column names; the workaround is to just rename the columns. Someone better at R than me could probably provide some generic approach. smiley

4 2
0 136

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?

0 1
0 92

Hi team,

I'll start with an apology as I am trying to wrap my head around the architecture of how InterSystems IRIS database management works. I am attempting to connect to the platform remotely through say a JDBC or ODBC connection in order to run queries, searches (through SQL statements) on my laptop and was trying to understand whether this would be possible? It is possible to setup an inbound client connection and wanted to better understand the architecture of how the database association works for IRIS database management. Does it use it's own internal SQL database or are we able to connect to our own database and which databases are certified to run against the platform?

0 2
0 181


I'm trying to load some data into IRIS using LOAD DATA in the IRIS terminal however I am getting blocked by this error. I have read the documentation on this page trying to set up a gateway connection but get the same Connection cannot be established error.

This is what is in the %Java Server activity log

0 2
0 157


I work on deploying IRIS using Kubernetes operator and Red Hat OpenShift. I encouraged another team working on Java application to consider using IRIS as database. My team deployed IRIS cluster using two mirrored data pods for the other team. The other team asked me for the connection information.

To learn how to use Java with IRIS, I attempted to deploy two apps from Open Exchange:

2 1
0 144

Hi ,

When I use jdbc driver to query the column info ,the "REMARKS" field always show the same as the "COLUMN_NAME" field.

When I use the sql "select * from INFORMATION_SCHEMA.columns a where a.table_name='some table name ' " to query columns info,there has a "DESCRIPTION" field ,the value is some comment for the current field.

So is there anyway to return the description to the jdbc remarks field? And is there anyway to update the desciption or remarks field? Or is there some other way that I don't know to manage column comment info?

0 1
0 175

Hello everyone!

I am writing a SQL CALL (using JDBC) to a stored procedure that outputs a structured object (Oracle Object).

However, the adapter method is not accepting the corresponding JDBC Data Type STRUCT for the output parameter, returning the following error:

0 0
0 296

The last days I've work with the great new feature: LOAD DATA With this post I would like to share my first experiences with you. The following points do not contain any order or other evaluation. These are only things that I noticed when using the LOAD DATA command. It should also be noted that these points are based on the IRIS Version 2021.2.0.617 which is a preview release. So it may be that my observations do not apply to newer IRIS versions.

6 4
2 636

One of our apps uses a class query to support a ZEN Report and works just fine in that report, producing the expected results every time. We’ve since migrated to InterSystems Reports and noticed that, for a report using the same class query, 100s of extra rows with the same column values appear at its bottom.

2 0
0 221

I want to INSERT a record in a database using JDBC in OBJECTSCRIPT. At the same time, I want to obtain the insert ID. Is there a way to achieve this using the SQL Outbound adapter?

My code is something like this now:

Property Adapter As EnsLib.SQL.OutboundAdapter;

set sql = " INSERT INTO Prenotazioni_CUP "_
" (ID, cf
" VALUES (SEQTAB.NextVal, ?) "
set status = ..Adapter.ExecuteUpdate(.rs, sql, pRequest.cfAssistito)

0 5
0 221

Once a week we are attempting to load an XML file from Workday into a MS SQL table using JDBC and Store Procedures. There is approx 102999 records in this XML file. We are struggling with processing the entire file within a reason amount of time. We feed the XML through a BPL to then populate values in a stored procedure then call the stored procedure through a Business Operation. I have tried splitting out the Business Operations to make two calls, but we still continue to see an issue loading the XML into MS SQL.

0 4
0 192
Preston Exley · May 27, 2021
JDBC counts don't match

I'm using a JDBC driver to connect PGSQL -> Cache. I'm noticing when I run SELECT queries and COUNT(*) command against the same table, I get different result sets. I'm pretty new to Cache in general - so I'm trying to understand why these would be different.

Examples (TransID and InvNum should occur in every "row"):

SELECT COUNT(*) FROM ACCT.Services = 1,090,324 WHERE ACCT.Dept = 483

SELECT TransID FROM ACCT.Services = 1,085,776 WHERE ACCT.Dept = 483

SELECT InvNum FROM ACCT.Services = 586,023 WHERE ACCT.Dept = 483

0 8
0 133
Higor Granzoto · Apr 14, 2021
Java Connection Strategy

We are planning to build a REST API with the Java Quarkus Framework.

  • There'll be a connection pool;
  • The DataSource instance will be automatically injected from the connection pool;
  • We will use JDBI to do SQL instructions and this will give us automatic Connection and Statement management;

This Caché instance already have COS applications running and consuming connections and licenses.

After doing this, we will migrate to IRIS.

0 1
0 262

When using "IDENTITY" as my primary key, I can select the last inserted ID with


Actually this is how Hibernate + Iris' Driver acquires the inserted ID when mapping with

@GeneratedValue(strategy = GenerationType.IDENTITY)

Now, considering that I am using the type "SERIAL" as my primary key instead, how can I get the last inserted ID?

Note that with "SERIAL" I can forcefully insert any value for this ID, from which Iris will continue generating values...

0 1
0 160

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

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

0 1
0 169

DataGrip is a multi-engine database environment targeting the specific needs of professional SQL developers, DataGrip makes working with databases an enjoyable and productive experience.

To work with InterSystems IRIS from DataGrip you'll need to add InterSystems JDBC driver first (once per DataGrip) and after that add all your InterSystems IRIS connections.

Part 1: Add InterSystems IRIS JDBC Driver

1. Go To File → DataSources

1 0
0 438

I have a case where I need to look up the NPI provider against an External MS SQL database to retrieve our Provider Identifier to send to a downstream system. In the past I would use a Business Process (BPL) to connect to the outside MS SQL via JDBC and get that information for me. But I was thinking instead of creating a BPL process to do this, would it be better just to link to the outside MS SQL database table to retrieve the information in a SQL statement within a DTL?

0 3
0 219

Hi Cache team, I am in the need of listing all the user defined schemas that are present my Cache db and also the user defined tables and views and Columns of those tables and views through Queries. So that I can write some JDBC code to run the queries and fetch the above metadata. Any help is appreciated.

Thanks in Advance,

Kranthi kiran.

0 2
0 1,524

Hey Developers!

This week is a voting week for the InterSystems Full Stack Contest!

So, it's time to give your vote to the best solutions built with InterSystems IRIS.

🔥 You decide: VOTING IS HERE 🔥

How to vote? This is easy: you will have one vote, and your vote goes either in Experts Nomination or in Community Nomination.

5 4
0 389