[Info-vax] SQL Relay

[Arne Vajhøj]

On 6/15/2021 6:41 PM, Jan-Erik Söderholm wrote:
> Den 2021-06-15 kl. 21:54, skrev Arne Vajhøj:
>> VSI has ported SQL Relay client to VMS (Alpha and Itanium).
>>
>> That solves the specific problem of connecting to Oracle
>> databases when Oracle support for Oracle client on VMS goes
>> away.
>>
>> But it actually provides a lot more. It gives VMS connectivity to
>> practically all databases using a single API. And for free. What is
>> not to like about that.
>>
>> :-)
>>
>> So I started playing a bit with it. And after a few bumps on the
>> road then it actually works great.
>>
>> I accessed Oracle, DB2, PostgreSQL, MySQL, Rdb and Mimer databases.
>>
>> I tested with:
>> * C using C API
>> * C++ using C++ API
>> * Pascal using VMS API
>> * Fortran using VMS API
>> * C using embedded SQL (Oracle only)
>> * Cobol using embedded SQL (Oracle only)
>>
>> The difference between C API, C++ API and VMS API is just the normal 
>> difference:
>>
>> stat = sqlrcur_sendQuery(curs, "SELECT f1,f2 FROM t1");
>>
>> int stat = curs->sendQuery("SELECT f1,f2 FROM t1");
>>
>> stat := sqlr$cur_sendquery(curs, 'select f1,f2 from t1');
>>
>> Full code exampples, build commands and setup are at:
>>     https://www.vajhoej.dk/arne/articles/vmstd2.html
>>
>> Note that the VMS API makes it easy to use from languages like Cobol,
>> Fortran, Pascal and Basic, which is a nice since most native
>> database API's today are C/C++ centric.

> If I have understood this, it is not that SQL Relay just gives you
> direct access to lot of databases. It "only" gives you access to
> a "SQL Relay server" somewhere using this "SQL Relay client" kit.
> 
> It is then this server (on Linux or Windows) that has the actual
> accesses to the databases.

Correct.

That should be very clear from the pictures in the link provided.

> This is very similar to the "Oracle Rdb Transparent Gateway to ODBC data"
> where you got access to any database having an ODBC driver for Windows
> using the basic Rdb development tools and Rdb SQL syntax.
> 
> We currently use "Oracle Rdb Transparent gateway for Oracle" to access
> an Oracle DB (prob 11 or so) on an AIX server. This used an (old) Oracle
> client kit and the SQL*Net network protocol. I am looking for some
> alternative since this gateway is not available on anything later than
> Alpha. These both came from the same "Database Integrator" (DBI) kit.
> 
> The DBI kit gave you direct (no additional server needed) access to
> databases like Oracle, DB2, Sybase and MS SQL Server using the native
> database client network APIs. And using the same SQL interface in your
> applications. We can switch between the remote Oracle 11 database
> and a local Rdb database for testing by redefining a logical name
> that pointing to the database. No source code changes. To the code,
> both databases looks like an Rdb database.

If there is no extra server, then you will need client libraries
on VMS. Either by the DB vendor or by Oracle.

The extra SQL Relay server is a burden. But it enables
the use of client libraries on Linux or Windows.

Which for various reasons are way more common than VMS.

> Now, this SQL Relay kit for VMS does not have any database interfaces by
> it's own, does it? So you cannot access *any* databases at all without
> having a separate SQLRelay server running too?

Correct.

> It's not a big deal to write some code using the SQL Relay APIs,
> it is the need to have an additional Linux or Windows server in
> between that I find to be an "issue".

It would be nice if all database vendors provided client
libraries for VMS.

But they don't.

This is what enables connectivity in the situation where we are.

And it requires the SQL Relay server.

Arne