New post
AllAccepted answers
Andreas Schneider · Nov 29

The  container image IMAGE=containers.intersystems.com/intersystems/iris-community:latest-cd from today also contains just the intersystems-jdbc-3.10.0.jar driver

The db version of this container image reports as "InterSystems IRIS Version 2024.3.0.209", this is the release from here General Availability of InterSystems IRIS, InterSystems IRIS for Health, right?! 

@Benjamin De Boe maybe you can clearify from where this version of the iris-driver-distribution repository on GitHub -> intersystems-jdbc-3.10.1.jar. came from? 

Andreas

Andreas Schneider · Oct 29

@Benjamin De Boe  your hints helped me a lot! I tested the THROUGH command via the SQL shell and it WORKS there! It also works via the SQL command window in the management portal! As far as I understand it, neither of them use a standard SQL interface (JDBC\ODBC), right?

Because it does not work when I send the command to IRIS via JDBC! 

I have used the latest driver known to me (intersystems-jdbc-3.9.0.jar 555kb).

I have also checked which driver was supplied with InterSystems IRIS version 2024.3.0.206. A new driver could have been included there, but the JDBC driver is identical to InterSystems IRIS version 2024.2.0.247 both in name and in the size of the file.

You may be able to try your test environment via JDBC to see if you have the same problem. Thanks!

Andreas

Andreas Schneider · Oct 28

@Benjamin De Boe Thank you so much for trying to help me with this!

I've have already installed IRIS 2024.2 (v3.9.0 for JDBC). But I had done an update installation. So I removed IRIS and reinstalled it from scratch to make sure that was not the reason for the problems.

The version details are: IRIS for Windows (x86-64) 2024.2 (Build 247U) Tue Jul 16 2024 09:57:03 EDT,
JDBC Driver Version:    3.9.0.0

The sql gateway connections goes to a IRIS docker container: InterSystems IRIS Version 2024.3.0.206  I've tested connections via sql gateway connection dialog. -> Works

#1 on the screenshot works without any error. The next example use this connection and it works for the CREATE FOREIGN TABLE (#3)

But #2 "THROUGH SERVER" didn't work. I get this message:
 Error: [SQLCODE: <-400>:<Es ist ein nicht behebbarer Fehler aufgetreten >]
[Error: <<UNDEFINED>Query+14^%SQL.PassthroughResult.XDBC.1 *%0IRISServer>]
[Location: <ServerLoop>]

I've done my tests with my tool SQL DATA LENS as I said with the latest jdbc driver. But I've used also SQLWorkbench/J and SQuirrel to make sure that there is not a problem with SQL DATA LENS.

With all tools, I only get the above error message.

Any idea?

Andreas

Andreas Schneider · Sep 16

I've been running my tests on an IRIS docker image. I have now switched to a Windows VM, just to see if this works better (IRIS for Windows (x86-64) 2024.2 (Build 247U) Tue Jul 16 2024 09:57:03 EDT).
I have also extended my attempts. Like the first time, I used a gateway connection to an IRIS 2024.2 instance (on a docker image), the second GATEWAY connection goes to an MS SQL Server 2022. 

Unfortunately, the THROUGH command still does not work for me. However, the 'old' CREATE FOREIGN TABLE works through the previously created FOREIGN SERVER. This indicates that the configuration of the FOREIGN SERVER and its connection is correct.

  1. THROUGH to a IRIS 2024.2 Server works not
  2. CREATE FOREIGN TABLE to a IRIS 2024.2Server works  
  3. THROUGH to a MS SQL Server 2022 Server works not
  4. CREATE FOREIGN TABLE to a MS SQL Server 2022 Server works

But now the error is a different one ;-)

I have no idea how the THROUGH command works...

Andreas Schneider · Jun 1

I've tried it on Windows 11. With your information I've searched again and found (my) problem now! First: Thanks for that! 

What was the problem?  
I've installed IRIS using the custom option and selected to install ALL items on the hard disk. Then I cannot find the odbc driver dll in the following folder:

All the other drivers are there: dotnet, java and so on.

Now I've tried a client installation on a fresh Windows... the folders are still empty. BUT the ODBC Data Source Administrator shows the 2024.1 IRIS ODBC driver! 
Again: The CLIENT install works!

The files are located here:


The puzzle is solved. Thanks to all of you!

Andreas
 

Andreas Schneider · Apr 28

You can change the default schema (= what is used if no schema is specified in the query) here:


However, if you use the schema extensively in your data model to structure the model, you should always use the schema in the query. Working with fully qualified object names is simply best practice and avoids misunderstandings. It seems better not to rely on such things like a default schema because it can changed (or diff from server to server). This becomes even more important when the same object names exist in different schemas.

jm2c
Andreas

Andreas Schneider · Mar 12, 2023

I'm not sure what the reason is but with 2023.1 there are some errors in %SQL_Diag.Result when loading data from my datasets. The errors are different from 2022.3.
After changing some columns in my data model from NOT NULLABLE to NULLABLE the errors are gone and the performance with IRIS 2023.1 is the same as with 2022.3

Andreas Schneider · Mar 12, 2023

I've upgraded my small test project https://github.com/andreas5588/openflights_dataset from 2022.2.0.368.0 to IRIS 2023.1.0.207.0 and I've discovered that the SQL execution time was much slower than expected.
Within this project I load the data using "LOAD DATA FROM FILE" sql statement and it looks like that this is taking up the time.
A "docker-compose build" with IRIS 2022.2.0.368.0 took about about 17 seconds on my machine, the same with IRIS 2023.1.0.207.0 took 300 seconds!
The image with IRIS 2022.2.0.368.0 is 2.87Gb and the image file with IRIS 2023.1.0.207.0 image is 3.21Gb.

This is the only change I've made https://github.com/andreas5588/openflights_dataset

Any idea whats going on?

Andreas
 

Andreas Schneider · Dec 26, 2022

That looks strange. If you execute the query via JDBC the error ist the same. A CAST to the same datatype as provided from Config.ConfigFile works:

SELECT
       CAST(ID AS VARCHAR(512)),
       CAST(CPFName AS VARCHAR(255)),
       CAST(Comments AS VARCHAR(4096)),
       CAST(Name AS VARCHAR(64)),
       CAST(Product AS VARCHAR(64)),
       CAST(SectionHeader AS VARCHAR(255)),
       CAST(Version AS VARCHAR(7))
FROM Config.ConfigFile
Andreas Schneider · Aug 14, 2022

Hi! Unfortunately there is no way to UPDATE the %DESCRIPTION information on a table or column with SQL.
But you can add these information while CREATEing a table like this:

or this way for a column:

Please see CREATE TABLE | InterSystems SQL Reference | InterSystems IRIS Data Platform 2022.1 for more details.

By default the %DESCRIPTION information is projected to the Remarks column via JDBC:

As you can see the text comes from the %DESCRIPTION property:

This works also for the columns.
This is from JDBC:


This is from INFORMATION_SCHEMA.COLUMNS:



Andreas