New post
AllAccepted answers
Andreas Schneider · Jan 14

David,
I saw this demo as an opportunity to explore the potential of this type of integration (foreign server), as the concept appears to offer a highly versatile and general-purpose solution.Initially, my plan was to integrate DuckDB; however, I encountered some challenges during the process. Consequently, I chose to begin with the simplest integration, with the intention of gradually expanding it to include additional variants over time.

If the namespaces are running on a single instance, the solution you described seems like an excellent approach and likely offers better performance as well. By the way: Your approach might also work with multiple instances if the database is integrated as a remote database?! However, I haven't had a chance to test this, so I'm not sure how the IRIS SQL engine would handle such a setup.

Andreas

Andreas Schneider · Jan 8

That's an interesting observation! I would have expected the IRIS SQL syntax to be used here as well, even if it's referring to an external table from a MySQL database.

In IRIS, you need to use TOP x: IRIS Documentation - TOP Clause. As for MySQL, you're absolutely right—the SQL syntax there specifies LIMIT.

It seems to me that the IRIS SQL Engine might not determine the columns precisely before passing them to the external SQL Engine (in this case, MySQL). Ideally, an IRIS SQL translation into the language of the external SQL engine would occur here. This approach ensures true data virtualization. Sounds quite sophisticated! 😊

Andreas Schneider · Jan 8

I haven’t had a chance to test it myself yet—the sheer scope of this site has been a bit overwhelming for me so far: Connect using Microsoft Entra authentication - JDBC Driver for SQL Server | Microsoft Learn.

It does seem like your guess might be correct—you could still be missing a dependency. As noted on this page, the required dependencies can vary depending on the specific parameters you're working with. Wishing you good luck with it! 😊

By the way, are you trying to connect using a query tool or via IRIS (gateway)?

Andreas Schneider · Jan 6

Hey Steve, you said 'no subqueries,' but I figured Common Table Expressions with window functions were fair game, right? 😉
This is just a shot based on your example data. Not entirely sure I nailed your explanation about 'previous' and 'next'? 🤔

Maybe this will help:

WITH RankedEntries AS 
(
    SELECT 
        TheNO,
        TheID,
        TheDate,
        Item,
        ROW_NUMBER() OVER (PARTITION BY TheID, Item ORDER BY TheDate ASC) AS ItemRank
    FROM MultipleEntries
)
SELECT 
    TheNO,
    TheID,
    TheDate,
    Item,
    CASE 
        WHEN ItemRank = 1 THEN 'new'
        ELSE 'old'
    END AS Status
FROM RankedEntries
ORDER BY TheNO;


Andreas

Andreas Schneider · Jan 3

@Enrico Parisi , thank you so much for the tip! It really helped me get on the right track. I've now integrated the creation of the SQLConnection directly into the IRIS initialization script, and using ObjectScript for this works seamlessly.
It would be wonderful if InterSystems could highlight such inconsistencies in the documentation or perhaps even prevent the INSERT via SQL directly to avoid confusion.

Now that the SQLConnection from IRIS to DuckDB can be used directly within the Docker container, I look forward to sharing my experiences with this exciting combination in the near future. 🙂
See here: Demo DB Container Projekt @ github

Andreas
 

Andreas Schneider · Dec 30, 2024

Ben,

Thank you for the explanation!
In addition to a (new) driver version, it would be helpful to know what problem this driver solves or what new feature this (new) driver offers. If the driver is no longer necessarily distributed with an IRIS release, such information could provide more clarity and context. It would be great if INtersystems could make this information available in the future, e.g. via a github repo: https://github.com/intersystems-community/iris-driver-distribution.
I wish you all a very happy new year!
Andreas

Andreas Schneider · Nov 29, 2024

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, 2024

@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, 2024

@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, 2024

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, 2024

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