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.

Arne

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

Hi.

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.

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.

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?

Ah, it is a shame the way things has gone...

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

Jan-Erik.

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

On 2021-06-15, Jan-Erik Söderholm <jan-erik.soderholm@telia.com> wrote:
>
> Ah, it is a shame the way things has gone...
>
> 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".
>

$ set response/mode=good_natured

You may end up having to learn Linux after all Jan-Erik. :-)

Simon.

--
Simon Clubley, clubley@remove_me.eisner.decus.org-Earth.UFP
Walking destinations on a map are further away than they appear.

Den 2021-06-16 kl. 14:58, skrev Simon Clubley:
> On 2021-06-15, Jan-Erik Söderholm <jan-erik.soderholm@telia.com> wrote:
>>
>> Ah, it is a shame the way things has gone...
>>
>> 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".
>>
>
> $ set response/mode=good_natured
>
> You may end up having to learn Linux after all Jan-Erik. :-)
>
> Simon.
>

Right...
There is Linux in our TV set-top box, but I do not have to see it.

But seriously... I do not know how much there is in the "client" kit
from Oracle today. But earlier it wasn't much more then an object library
and some .h files for the SQL*Net APIs. Then a SQLNET.INI (or whatever
the name was) with some host parameters for the servers...

And SQL*Net was earlier pretty version agnostic, so even old client
kits worked OK using more modern Oracle servers.

Ah well, where did I put those retirement forms...

On 16/06/2021 15:04, Jan-Erik Söderholm wrote:
> Den 2021-06-16 kl. 14:58, skrev Simon Clubley:
>> On 2021-06-15, Jan-Erik Söderholm <jan-erik.soderholm@telia.com> wrote:
>>>
>>> Ah, it is a shame the way things has gone...
>>>
>>> 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".
>>>
>>
>> $ set response/mode=good_natured
>>
>> You may end up having to learn Linux after all Jan-Erik. :-)
>>
>> Simon.
>>
>
> Right...
> There is Linux in our TV set-top box, but I do not have to see it.
>
> But seriously... I do not know how much there is in the "client" kit
> from Oracle today. But earlier it wasn't much more then an object library
> and some .h files for the SQL*Net APIs. Then a SQLNET.INI (or whatever
> the name was) with some host parameters for the servers...
>
> And SQL*Net was earlier pretty version agnostic, so even old client
> kits worked OK using more modern Oracle servers.
>
> Ah well, where did I put those retirement forms...

The Oracle client is able to go up, or down, 2 major versions. Useful
for me when I had 8.1.7, 11G and 12 coming. I just used the 10 client
(on Windows, VMS and Linux) and all was good.

Plenty of tools there for command line, or APIs

--
Chris

On 6/16/2021 10:04 AM, Jan-Erik Söderholm wrote:
> Den 2021-06-16 kl. 14:58, skrev Simon Clubley:
>> On 2021-06-15, Jan-Erik Söderholm <jan-erik.soderholm@telia.com> wrote:
>>> 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".
>>
>> $ set response/mode=good_natured
>>
>> You may end up having to learn Linux after all Jan-Erik. :-)
>
> Right...
> There is Linux in our TV set-top box, but I do not have to see it.

Note that SQL Relay can be setup for HA:

http://sqlrelay.sourceforge.net/sqlrelay/features/ha.html

> But seriously... I do not know how much there is in the "client" kit
> from Oracle today. But earlier it wasn't much more then an object library
> and some .h files for the SQL*Net APIs. Then a SQLNET.INI (or whatever
> the name was) with some host parameters for the servers...
>
> And SQL*Net was earlier pretty version agnostic, so even old client
> kits worked OK using more modern Oracle servers.

Oracle client comes in various bundles and vary a bit for different
platforms but it can include: OCI (C API), OCCI (C++ API),
ODBC driver, OLE DB provider, ADO.NET provider, type 2 JDBC driver,
type 4 JDBC driver, embedded SQL precompiler C, embedded SQL
precompiler Cobol.

My guess is that the VMS people are only interested in
OCI (C API), type 4 JDBC driver and the two embedded
SQL pre-compilers.

Type 4 JDBC driver is not an issue as it is cross
platform.

That leaves OCI and the pre-compilers that rely on OCI.

So yes - not much is needed. And OCI may continue to
work for many many years, if the wire protocol does not
change.

But if OCI on VMS is not supported and the version
combo (client version, server version) is not supported,
then it becomes a business risk.

It may continue to work. But it could also break when
a new server version comes out (because they updated the
wire protocol).

If that happens either VMS will loose connectivity or
the server cannot be upgraded due to VMS.

CIO's will not like that risk.

Arne