New post
Question
· Sep 15

Have you tried the THROUGH command in IRIS 2024.2? How does it work?

#JDBC #SQL #InterSystems IRIS

Has anyone successfully tested the new THROUGH command in IRIS 2024.2 with a FOREIGN SERVER?https://docs.intersystems.com/iris20242/csp/docbook/Doc.View.cls?KEY=RSQ...

I have connected from a Docker instance to a VM. I was able to successfully set up the JDBC connection through the UI.

I then configured a foreign server with this connection:

But I am unable to send a SQL 'THROUGH' to the DB. I always get a:

I've get the same message if i try it via Management Portal.

I've also tried this:

and this

Always with the same error. According to the documentation, the syntax without ‘ and " should be correct.

Also a simple  

gives this error...

Any idea how this works?

Andreas

$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2024.2.0L (Build 201U) Wed May 15 2024 16:14:30 EDT
Discussion (11)2
Log in or sign up to continue
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...

0 0
Benjamin De Boe · Oct 24

hi @Andreas Schneider  , sorry about the embarrassingly late response! It seems my DC email notifications aren't coming through anymore.

The error you're seeing may well be because you might still be using an older JDBC or ODBC driver version. The THROUGH keyword at the start of the statement is brand new and was added to the JDBC or ODBC driver versions that ship with IRIS 2024.2 (v3.9.0 for JDBC), but if you're using a query tool such as DBeaver, they may still be using something older.

As you already found / tried, you shouldn't add any (single) quotes around the statement to be sent to the foreign server. 

The <UNDEFINED> error is puzzling. This hasn't been reported by any of the customers we know are using THROUGH today (we've worked closely with at least one!), but it seems from your $zv string you are using one of the early Developer Preview kits and not the final 2024.2.0 release. Can you please try upgrading to the official release (build 247)? I can see in our internal tracking system at least one more related change went in between the DP build you listed and the final release, and have reasonable confidence that'll sort things.

Apologies for the delayed response

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

0 0
Benjamin De Boe · Oct 29

Hi @Andreas Schneider ,

I have just downloaded the released 2024.2 container as well as the latest 2024.3, set up foreign server connections from the former to the latter, and all THROUGH SERVER commands work as expected:

[SQL]USER>>through server myhost select $zv
2.      through server myhost select $zv

| Expression_1 |
| -- |
| IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2024.3 (Build 206U) Fri Oct 11 2024 16:59:56 EDT |

1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0485s/6,382/43,267/0ms
          execute time(s)/globals/cmds/disk: 0.0028s/40/576/0ms
                                query class: %SQL.PassthroughResult.Dynamic
---------------------------------------------------------------------------
[SQL]USER>>through server myself select $zv
3.      through server myself select $zv

| Expression_1 |
| -- |
| IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2024.2 (Build 247U) Tue Jul 16 2024 09:29:04 EDT |

1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.1686s/6,388/43,347/0ms
          execute time(s)/globals/cmds/disk: 0.0031s/40/576/0ms
                                query class: %SQL.PassthroughResult.Dynamic
---------------------------------------------------------------------------
[SQL]USER>>select $zv
4.      select $zv

| Expression_1 |
| -- |
| IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2024.2 (Build 247U) Tue Jul 16 2024 09:29:04 EDT |

1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0852s/35,278/160,395/0ms
          execute time(s)/globals/cmds/disk: 0.0001s/0/411/0ms
                                query class: %sqlcq.USER.cls24
---------------------------------------------------------------------------
[SQL]USER>>

I'm afraid I have no other suggestion than to re-check whether your 2024.2 instance and the JDBC driver it uses are in sync and there are no stale classpath entries in your SQL Gateway connection or SQL Data Lens config.

FWIW, Foreign Tables use new xDBC Gateway infrastructure that only relies on the SQL Gateway connection definition for getting the server URL and credentials, so the "test" button in the SQL Gateway definition screen is not necessarily representative for xDBC Gateway connectivity. This is a temporary situation until the IRIS Wallet (secure credential score) is ready for the new Gateway (and Foreign Tables) to adopt, but we've never run into any differences in behaviour like the one you are facing.

To validate whether the xDBC Gateway can connect and possibly glean more detail from the error message, please try this, replacing the host/port/credentials as appropriate for your setup:

w ##class(%XDBC.Gateway.Connection).%GetConnection({ "host": "host.docker.internal", "port": 51774, "databasename": "USER", "datasource": "com.intersystems.jdbc.IRISDataSource", "user":"_SYSTEM", "password":"SYS","type":"jdbc" },.sc)
d $system.OBJ.DisplayError(sc)
0 0
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

1 0
Benjamin De Boe · Nov 7

Hi Andreas,

thanks for your patience.

I have now finally been able to reproduce your issue using the released 2024.2 kit, connecting to it from a JDBC client rather than the shell. I do not quite understand why the THROUGH command is throwing an error in that client-to-server context and not when invoked from the shell in code that is creating a "nested" server-to-server JDBC connection to the Foreign Server, but those seem to be the facts.

This said, the good news is the exact same scenario works fine on 2024.3. I have tried sneaking the 2024.3 JDBC driver into the 2024.2 kit, but that doesn't seem to make a difference so it's a server-side code that fixed this. We're just two weeks away from 2024.3 going GA and there's a totally stable developer preview version of it available, so I hope that gets you by.

Thanks,
benjamin

1 0
Elena E · Nov 7

Hi @Benjamin De Boe,

Sorry that you missed the notification of the comment from Andreas.
Since your subscription notifications are turned on, you should have received an email and web notification about your mention in Andreas' comment. Could you please check your inbox or spam folder for a message dated around September 28th (depending on your time zone)?

If you didn’t receive a notification about this comment, let me know(dm or teams), and we’ll begin an investigation.

@Elena Evsikova
 

0 0
Benjamin De Boe · Nov 7

I have only this morning updated my settings about notifications, which had a few things switched off that I didn't know existed as separate options. I'm wondering if some recent enhancement to the DC refining control on what to subscribe to was a little conservative in starting with notifications disabled.

I believe I should be all set now, as I got a notification for your post right away now.

1 0