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.

30
0 3 100
Article
Guillaume Rongier · Apr 9, 2019 3m read
IRIS/Ensemble as an ETL

IRIS and Ensemble are designed to act as an ESB/EAI. This mean they are build to process lots of small messages.

But some times, in real life we have to use them as ETL. The down side is not that they can't do so, but it can take a long time to process millions of row at once.

To improve performance, I have created a new SQLOutboundAdaptor who only works with JDBC.

BatchSqlOutboundAdapter

Extend EnsLib.SQL.OutboundAdapter to add batch batch and fetch support on JDBC connection.

30
2 7 853

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.

20
0 0 135

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)

00
0 5 118
Question
David Satorres · Aug 11, 2020
JDBC Gateway Server

Hello all,

I'm trying to setup the JDBC Gateway Server so customers can connect to IRIS remotely using JDBC and not ODBC. But I'm facing a problem connecting, as our system department tells me IRIS is using the loopback address (127.0.0.1) and that makes remote systems cannot connect to port 53773 (the default port for that).

So, I would like to change this 127.0.0.1 host for the hostname but I cannot see where to do it:

10
0 15 407

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.

00
0 4 97
Question
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

00
0 8 96
Question
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.

00
0 0 176

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

SELECT LAST_IDENTITY() FROM %TSQL_sys.snf;

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...

00
0 1 91

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...

00
0 0 114

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

10
0 0 286

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?

00
0 3 155

Keywords: Python, JDBC, SQL, IRIS, Jupyter Notebook, Pandas, Numpy, and Machine Learning 

1. Purpose

This is another 5-minute simple note on invoking the IRIS JDBC driver via Python 3 within i.e. a Jupyter Notebook, to read from and write data  into an IRIS database instance via SQL syntax, for demo purpose. 

30
2 1 1,486

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.

00
0 2 774

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.

50
0 4 327

Hi Community!

We are pleased to invite all the developers to the upcoming InterSystems Full Stack Contest Kick-off Webinar! The topic of this webinar is dedicated to the Full Stack Contest.

On this webinar, we’ll demo the IRIS Full Stack template and answer the questions on how to develop, build and deploy full stack applications in InterSystems IRIS.

Date & Time: Monday, September 21 — 11:00 AM EDT

Speakers:  
🗣 @Evgeny Shvarov, InterSystems Developer Ecosystem Manager
🗣 @Raj Singh, InterSystems Product Manager - Developer Experience

10
0 2 210
Article
Anssi Kauppi · Jun 30, 2020 3m read
Replicating Audit Log Near Real Time
Many organisations implement centralised log management systems to separate and centralise the log data in order to e.g. automate threat detection (and response) and to comply with regulatory requirements. The primary systems of interest are the various user facing applications, but increasingly also other kinds of systems including integration platforms.
10
1 1 190

The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.

SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.

80
0 11 8,430