topic Re: Accessing SQLServer from VMS in Operating System - OpenVMS

Accessing SQLServer from VMS

Shael Richmond — Mon, 12 Jul 2004 11:01:39 GMT

Does anybody access a SQLServer database from VMS and if so how?

We also have Oracle on VMS and I believe we can get a gateway from Oracle. But I didn't know if there were any other options available.

Re: Accessing SQLServer from VMS

Ian Miller. — Mon, 12 Jul 2004 11:15:58 GMT

parhaps you could use ODBC client on VMS - they may be one from Attunity Connec.
Or could use jdbc from java?

Re: Accessing SQLServer from VMS

Craig A Berry — Mon, 12 Jul 2004 11:58:01 GMT

There are a number of options, including Attunity or one of the other commercial ODBC vendors like EasySoft or OpenLink.

I have a preference for FreeTDS since I'm the one who ported it to VMS :-). See:

http://www.freetds.org

There is also a pure Java JDBC client:

http://jtds.sourceforge.net/

Providing more information about the specifics of "access a SQL Server database" would probably net you more concrete help about the relative advantages of the different solutions. I.e., are you just exporting data and don't need transactional control? Or do you need full programmatic access? If so, what language(s) are you using? Do you need to access Oracle, SQL Server, and RMS from within the same program? Do you need access from DCL?

Do you have the budget and authority to install a special driver on each of the target systems you want to access (something all the commercial solutions will require)?

The list could go on, but you get the idea.

Re: Accessing SQLServer from VMS

Shael Richmond — Mon, 12 Jul 2004 12:35:22 GMT

>There are a number of options, including >Attunity or one of the other commercial >ODBC vendors like EasySoft or OpenLink.

I looked at Easysoft but like you said it has a server side piece.

>I have a preference for FreeTDS since I'm >the one who ported it to VMS :-). See:

>http://www.freetds.org

I'll take a look at it.

>Providing more information about the >specifics of "access a SQL Server database" >would probably net you more concrete help >about the relative advantages of the >different solutions. I.e., are you just >exporting data and don't need transactional >control? Or do you need full programmatic >access? If so, what language(s) are you >using? Do you need to access Oracle, SQL >Server, and RMS from within the same >program? Do you need access from DCL?

The application is written in Fortran.
It would only need to access SQLserver.
The application is an interface to 3rd party product(Proficy). All it would be doing is
calling a stored procedure. Everything would be read only.

>Do you have the budget and authority to >install a special driver on each of the >target systems you want to access
>(something all the commercial solutions >will require)?

I could get the authority(and budget) but I'd prefer to keep this as a client side application only.

Re: Accessing SQLServer from VMS

Craig A Berry — Mon, 12 Jul 2004 13:46:55 GMT

Ah, good. You have fairly simple case. If you use FreeTDS you'll need to decide whether to use the dblib API or ODBC. If the latter, you'll also need a driver manager, specifically unixODBC (http://www.unixodbc.org). I think dblib would be a lot quicker to get up and running. ODBC would have the advantage that you could later swap out the innards to a different ODBC driver and leave your top-level code unchanged.

In any case you'll likely be calling C routines from FORTRAN, so you'll need to sort out how you want to do that. You can null terminate your strings and pass by reference, or you can write a layer in C that accepts values by descriptor, copies them to C strings, and passes them to the database library. In your case the "layer" might be a single routine that handles all database interaction and returns a result. Or the layer could provide a jacket around each database call. Attunity provides something like the latter, I believe.

Re: Accessing SQLServer from VMS

Martin P.J. Zinser — Mon, 12 Jul 2004 20:41:39 GMT

Hi,

depending on what you already have, you might be able to access SQL server using a Sybase client. For reference see

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarsqlsg/html/msdn_sqlintgr.asp

If I had to do it, I'd go the freetds route, but if you already have Sybase by chance it can be worth a look.

Greetings, Martin

Re: Accessing SQLServer from VMS

Craig A Berry — Tue, 13 Jul 2004 15:31:23 GMT

Martin,

With a number of pretty severe restrictions, yes, you can use the old, now-defunct Sybase clients to access MS SQL Server. If you have SQL Server before 7.0, you'll probably be fine. If you have SQL Server 7.0, you need, IIRC, SP2 since 7.0 as originally shipped accidentally broke compatibility with older versions of the TDS protocol. If you're using MS SQL 2000 or later, you're out of luck because these versions *intentionally* broke compatibility with older versions of the TDS protocol.

Under the limited circumstances in which the combination works, I believe you are limited to text and varchar fields with a maximum length of 255 characters.

Re: Accessing SQLServer from VMS

Shael Richmond — Tue, 13 Jul 2004 15:35:17 GMT

Craig,

I downloaded FreeTDS and ran the configure.com But when I run MMS I got
the following error:

%MMS-F-GWKNOPRN, There are no known sources for the current target [.INCLUDE]TDS
_SYSDEP_PUBLIC.H_IN

Also the documentation refers to using SQSH
to test with but I didn't see a VMS version.
Is there something else I can test with?

Re: Accessing SQLServer from VMS

Craig A Berry — Tue, 13 Jul 2004 17:21:26 GMT

When you untar the archive, be sure to use the -o or /ODS2 option with vmstar, assuming you are on an ODS-5 disk. Otherwise you'll get a few caret-escaped filenames that the build procedure won't find.

When you've got a successful build, edit pwd.in and freetds.conf in the top-level directory and run MMS CHECK to test. You'll also have a tsql.exe and freebcp.exe for interactive SQL and bulk copy operations, respectively. See [.doc]tsql.txt and [.doc]freebcp.txt for the documentation of these utilities.

Re: Accessing SQLServer from VMS

Shael Richmond — Tue, 13 Jul 2004 17:53:20 GMT

Does it require ODS-5?
I did it a little different and maybe that
was the problem. I untared it on my PC
then zipped it up. Then I FTP'ed it to
VMS and created a directory.
MMS only gave that one error but didn't create and exe's.

Re: Accessing SQLServer from VMS

Craig A Berry — Tue, 13 Jul 2004 18:24:51 GMT

No, it does not require ODS-5. You're generally much better off unpacking the archive on the target system, though the filename looks correct to me and the untar method may be a red herring. You might also try MMK (from www.madgoat.com) since MMS does occasionally hiccup on things MMK handles just fine.

Re: Accessing SQLServer from VMS

Ruslan R. Laishev — Fri, 12 Nov 2004 05:20:27 GMT

Hi !

Just for collection, FreeTDS port to OpenVMS:
http://starlet.deltatel.ru/~laishev/work/freetds/