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