Discussion:
Access remote MariaDB database from VMS, what's the best way
Add Reply
Rich Jordan
2017-06-26 18:22:26 UTC
Reply
Permalink
Raw Message
We have a number of sites using FreeTDS with our own libraries on the VMS side to access SQL server databases. What is the best way to programatically access MariaDB databases running on remote systems from an OpenVMS I64 server? Is there an equivalent to the FreeTDS library (I haven't found references that indicate MariaDB is compatible with TDS, so presumably a different method or protocol?

Running MariaDB on the VMS system might be an option if that helps in some manner but the live databases will remain on the remote systems. I'm waiting confirmation but I'm pretty sure they are Linux boxes.

Thanks

Rich
Stephen Hoffman
2017-06-26 19:01:04 UTC
Reply
Permalink
Raw Message
Post by Rich Jordan
We have a number of sites using FreeTDS with our own libraries on the
VMS side to access SQL server databases. What is the best way to
programatically access MariaDB databases running on remote systems from
an OpenVMS I64 server? Is there an equivalent to the FreeTDS library
(I haven't found references that indicate MariaDB is compatible with
TDS, so presumably a different method or protocol?
Running MariaDB on the VMS system might be an option if that helps in
some manner but the live databases will remain on the remote systems.
I'm waiting confirmation but I'm pretty sure they are Linux boxes.
So you're either moving from Microsoft SQL Server over to MariaDB, or
you're adding a MariaDB database into the existing environment?
Okay...

I'd probably start by looking at the connector
https://mariadb.com/kb/en/mariadb/mariadb-connector-c/ or at the
unixODBC bits: http://www.unixodbc.org — the latter has been ported to
OpenVMS, the former might or might not port. The latter was also
associated with FreeTDS builds so you may already have some of the
pieces around.

More (related) data...

https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/
https://mariadb.atlassian.net/browse/MDEV-7508
https://mariadb.com/kb/en/mariadb/connect-odbc-table-type-accessing-tables-from-another-dbms/

http://www3.sympatico.ca/n.rieck/docs/openvms_notes_mysql_mariardb.html
http://unixodbc.13851.n7.nabble.com/unixODBC-for-OpenVMS-Build-td3106.html
--
Pure Personal Opinion | HoffmanLabs LLC
Rich Jordan
2017-06-26 19:55:39 UTC
Reply
Permalink
Raw Message
Post by Stephen Hoffman
Post by Rich Jordan
We have a number of sites using FreeTDS with our own libraries on the
VMS side to access SQL server databases. What is the best way to
programatically access MariaDB databases running on remote systems from
an OpenVMS I64 server? Is there an equivalent to the FreeTDS library
(I haven't found references that indicate MariaDB is compatible with
TDS, so presumably a different method or protocol?
Running MariaDB on the VMS system might be an option if that helps in
some manner but the live databases will remain on the remote systems.
I'm waiting confirmation but I'm pretty sure they are Linux boxes.
So you're either moving from Microsoft SQL Server over to MariaDB, or
you're adding a MariaDB database into the existing environment?
Okay...
I'd probably start by looking at the connector
https://mariadb.com/kb/en/mariadb/mariadb-connector-c/ or at the
unixODBC bits: http://www.unixodbc.org — the latter has been ported to
OpenVMS, the former might or might not port. The latter was also
associated with FreeTDS builds so you may already have some of the
pieces around.
More (related) data...
https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/
https://mariadb.atlassian.net/browse/MDEV-7508
https://mariadb.com/kb/en/mariadb/connect-odbc-table-type-accessing-tables-from-another-dbms/
http://www3.sympatico.ca/n.rieck/docs/openvms_notes_mysql_mariardb.html
http://unixodbc.13851.n7.nabble.com/unixODBC-for-OpenVMS-Build-td3106.html
--
Pure Personal Opinion | HoffmanLabs LLC
Hoff,
thanks. I had already been poking around the MariaDB (and MySQL) docs, and have reviewed the part for allowing remote client access. I'm not a database guy so while I helped get FreeTDS running on our sites about 10 years ago, it was a real programmer/DB guy who wrote the (higher level) libraries that our programs use so the other programmers don't have to deal with all the C constructs.

Right now this is a question from a client; they won't be replacing SQL server but one or more of their vendors uses MariaDB and it would be useful to be able to at least query, if not actually push records into those databases.

If it is doable with ODBC, which did come with FreeTDS though we never directly tried to use it then I imagine we'd have to put together some new libraries; the devs are not fans of C.

Thanks!

Rich
Craig A. Berry
2017-06-26 21:00:20 UTC
Reply
Permalink
Raw Message
Post by Rich Jordan
If it is doable with ODBC, which did come with FreeTDS though we never directly
tried to use it then I imagine we'd have to put together some new libraries; the
devs are not fans of C.
ODBC does not "come with" FreeTDS. FreeTDS includes one specific ODBC driver, which works for servers that speak the Tabular Datastream Protocol (TDS) on the wire. Which is only Microsoft and Sybase servers. It will do you no good with MariaDB.

There is no particular relationship between unixODBC, an ODBC driver manager, and any particular ODBC driver other than that the drivers should all support the same API while talking different, database-specific protocols on the wire. It does sound like ODBC might be a good solution for you as you could keep your higher level code more database independent and connect to a driver manager that would dynamically load the correct driver for the correct database based on a configuration file.

It seems reasonable to expect that the MariaDB ODBC driver would work with unixODBC, and these things are free and freely available. But free might be expensive in your environment if there is no one who wants to wrestle with porting C libraries to VMS. Consider checking out Attunity, CONNX, and/or Easysoft, or anyone else you can find who already has the drivers and driver managers that will do what you want and can support them.
Arne Vajhøj
2017-06-26 23:59:55 UTC
Reply
Permalink
Raw Message
Post by Rich Jordan
We have a number of sites using FreeTDS with our own libraries on the
VMS side to access SQL server databases. What is the best way to
programatically access MariaDB databases running on remote systems
from an OpenVMS I64 server? Is there an equivalent to the FreeTDS
library (I haven't found references that indicate MariaDB is
compatible with TDS, so presumably a different method or protocol?
Running MariaDB on the VMS system might be an option if that helps in
some manner but the live databases will remain on the remote systems.
I'm waiting confirmation but I'm pretty sure they are Linux boxes.
TDS is the Sybase & MS SQLServer protocol, so I don't think it will
work with MariaSB/MySQL.

MySQL comes with a C library which provides a very straightforward
C API.

If you are used to DB library API then I think you will find
the MySQL C library easy to use.

I am pretty sure that the library is ported to VMS. But you
will need to check.

Arne
Arne Vajhøj
2017-06-27 00:25:23 UTC
Reply
Permalink
Raw Message
Post by Arne Vajhøj
Post by Rich Jordan
We have a number of sites using FreeTDS with our own libraries on the
VMS side to access SQL server databases. What is the best way to
programatically access MariaDB databases running on remote systems
from an OpenVMS I64 server? Is there an equivalent to the FreeTDS
library (I haven't found references that indicate MariaDB is
compatible with TDS, so presumably a different method or protocol?
Running MariaDB on the VMS system might be an option if that helps in
some manner but the live databases will remain on the remote systems.
I'm waiting confirmation but I'm pretty sure they are Linux boxes.
TDS is the Sybase & MS SQLServer protocol, so I don't think it will
work with MariaSB/MySQL.
MySQL comes with a C library which provides a very straightforward
C API.
If you are used to DB library API then I think you will find
the MySQL C library easy to use.
I am pretty sure that the library is ported to VMS. But you
will need to check.
I can confirm that mysqlclient lib works fine with MySQL 5.1.

I am confident that it also works with MariaDB.

Arne
Brett Cameron
2017-06-27 18:39:04 UTC
Reply
Permalink
Raw Message
Arne, yes, it does work with MariaDB (same protocol); a port of it is used in our PHP release and we're using it internally for one or two things.

Rich, we could package this up into an installable kit if that would be of any use to you (and possibly to others as well). Email me and we can sort something out.

Regards,
Brett
Post by Arne Vajhøj
Post by Arne Vajhøj
Post by Rich Jordan
We have a number of sites using FreeTDS with our own libraries on the
VMS side to access SQL server databases. What is the best way to
programatically access MariaDB databases running on remote systems
from an OpenVMS I64 server? Is there an equivalent to the FreeTDS
library (I haven't found references that indicate MariaDB is
compatible with TDS, so presumably a different method or protocol?
Running MariaDB on the VMS system might be an option if that helps in
some manner but the live databases will remain on the remote systems.
I'm waiting confirmation but I'm pretty sure they are Linux boxes.
TDS is the Sybase & MS SQLServer protocol, so I don't think it will
work with MariaSB/MySQL.
MySQL comes with a C library which provides a very straightforward
C API.
If you are used to DB library API then I think you will find
the MySQL C library easy to use.
I am pretty sure that the library is ported to VMS. But you
will need to check.
I can confirm that mysqlclient lib works fine with MySQL 5.1.
I am confident that it also works with MariaDB.
Arne
Loading...