How can I create a SQLGateway connection using code?

Question

Question

#Docker #Management Portal #SQL #Testing #InterSystems IRIS

Hi everyone,

I’m currently experimenting with the SQL Gateway Connection, CREATE FOREIGN SERVER, and the THROUGH command (IRIS Documentation). To speed up my tests, I’ve combined several DemoDataSets in a Docker container and would like to automate the creation of SQL Gateway Connections using code.

(By the way, if you’re interested, the Docker container is available here: Demo DBs IRIS.)

To achieve this, I’m using SQL to create a connection by running a command like INSERT INTO %Library.sys_SQLConnection. While this successfully displays the connection in the Management Portal, it cannot actually be used.

Testing the connection results in this error: Connection failed. <ILLEGAL VALUE>decode+1^%apiGTW

After some investigation, I traced the issue to how the password is handled. Specifically, if I manually enter something into the password field in the Management Portal (even though the DuckDB InMemory DB doesn’t require a password), save it, and then test the connection again, it works perfectly.

It seems like there’s some sort of processing happening in the UI—possibly encrypting the password—that isn’t applied when the connection is created directly via SQL.

Has anyone encountered this before, or does anyone know how to handle this? I’d love to hear your insights or any possible solutions!

Thanks! 😊

Product version: IRIS 2024.3

$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2024.3 (Build 217U) Thu Nov 14 2024 17:37:22 EST

Discussion (3)2

Log in or sign up to continue

Enrico Parisi · Jan 3

Hi @Andreas Schneider,

you can create a SQLGateway connection using ObjectScript code, if you create it using SQL then, as you discovered, you have an issue with the password because using ObjectScript the password is "processed" before saving it.

If you want/need a pure SQL solution without (directly) coding/using ObjectScript you can define and use a Stored Procedure (that use ObjectScript 😉) to change the password, something like:

CREATE PROCEDURE Community_SqlGw.SetPassword(ConnectionName VARCHAR(50), NewPassword VARCHAR(50))
RETURNS BIT
LANGUAGE OBJECTSCRIPT
{
 New gw,sc
 Set gw=##class(%SQLConnection).NameIndexOpen(ConnectionName)
 If gw="" Quit 0
 Set gw.pwd=NewPassword
 Set sc=gw.%Save()
 Quit (sc=1)
}

Then, after creating the GW Connection, change the password:

select Community_SqlGw.SetPassword('MyGwConnectionName','MyGwPassword')

1 0

score 0 · Answer 1 · 2025-01-02T12:25:23-05:00

A little fun fact: Once you have the connection fixed, you can test directly with a configured FOREIGN SERVER to an InMemory DuckDB. This is then very easy to do (reading CSV, Parquet-Files):

score 0 · Answer 2 · 2025-01-03T13:14:11-05:00

@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