MS SQL query from Perl in UNIX

hello:
  I need to access a database running in MS SQL Server 7.0 (NT4.0) from a perl script in a UNIX (BSDI4.1) computer.  I have in the past succesfully installed DBI/DBD drivers for MySQL and used them with no problems, because of the many tutorials I found on the Internet on how to do this, but I am completely at a loss with what is needed to interface to an MS SQL Server :(
Please help! when it comes to db access I am completely dense (but I learn fast!), so I would appreciate a very detailed description on what modules I need to install and how to connect and access the data programmatically (examples?).  Do I need DBD:ODBC? DBI::? or what?

   Thanx in advance!
   -dZ.
LVL 18
DropZoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ventolinCommented:
MSSQL::DBlib and MSSQL::Sqllib

http://www.algonet.se/~sommar/mssql/

MSSQL::Dblib and MSSQL::Sqllib are two Perl classes for accessing Microsoft SQL Server® from Perl. MSSQL::Dblib provides a
direct interface to DB-Library. This module is basically a port of Michael Peppler's Sybperl. MSSQL::Sqllib is built on top of
MSSQL::Dblib and provides an high-level interface, so you can say things like:

     @result = sql("SELECT * FROM sysdatabases");
     foreach $row (@result) {
        print "dbid: $$row{dbid} name: $$row{name}\n";
     }
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ventolinCommented:
Microsoft does not supply a Unix version of DB-Library for MS SQL Server. So your perl code cannot be on a unix machine.

What alternatives are there?

A bunch actually, it appears. In terms of client libraries there are:

     Open Client from Sybase
     ODBC
     FreeTDS

I'll cover these below, but first some words in general on DBI/DBD. In case you did not know, DBI is the de-facto standard in the Perl
world for talking to database servers. DBI is a standardized high-level interface, and then you use a driver whose name always start
with DBD::. There are non-DBI modules out there for legacy or other reasons. (My MSSQL modules for instance.) But if you don't
have any old code to support, using DBI is probably the right path for you. You find further information at what appears to be the DBI
home page.

Open Client from Sybase

DB-Library was originally developed by Sybase, and you can use their version of DB-Library (part of a product called Open Client) or
their newer CT-Library. Sybase bundles them in a product called Open Client. As the Perl interface you would then use Michael
Peppler's Sybperl. With regards to SQL7, the same restrictions apply as when using MSSQL::* with SQL7. With regards to SQL6.5
and SQL6.0, I am told that there are no restrictions in usability, but it is nothing I have verified.

Sybperl is the collective name for three different modules: DBD::Sybase, Sybase::CTlib and Sybase::DBlib. DBD::Sybase is a DBI
driver using CT-Library, while the other two offers direct interfaces to the client library. As MSSQL::DBlib evolved from
Sybase::DBlib, it would not be too much of a task to retarget MSSQL::Sqllib to use this library instead. It's not on my todo list, but see
this page for some hints.

ODBC

Microsoft does not provide any ODBC libraries for Unix, but there are a few third-party drivers out there. As this is an area beyond my
interest, I don't have any up-to-date information. But two I know of are Merant (formerly Intersolv) and OpenLink Software. A search
on the Web is likely to reveal a few more, as well as comments and reviews on these products. When shopping a driver, make sure that
it supports SQL7, so you can use all datatypes without restrictions.

On the Perl side you would use DBD::ODBC.

Personally, this is the approach I would recommend, as you would use something which is fully supported in all ends.

FreeTDS

TDS -- Tabular Data Stream -- is the protocol that SQL Server talks with its clients. This is a proprietary protocol, owned by
Microsoft (and Sybase, who have their version). Nevertheless there is a project out there which aims at cracking (or whatever they do)
this protocol and put the work in the public domain. Check out the FreeTDS home page for further details. There appears to be a
DBD::FreeTDS that goes along with it.
0
DropZoneAuthor Commented:
ventolin:
   THANX! while waiting for your answer I did some searches in CPAN and found DBI and DBD::ODBC. I installed DBI fine, but while attempting o install DBD::ODBC it said it does not provide *any* drivers so I would have to look for my own. It *did* bring a distribution of the iodbc drivers from OpenLink (which is -- i think -- one of the things you suggested). Anyways, I compiled and installed iodbc fine (read: I didn't get any errors on stdout), but still DBD::ODBC claims that I need to specify the path where my "Driver Manager" is located.  I am lost once again, since the iodbc installed in /usr/local/bin, but DBD::ODBC keeps complaining.  Readme's and other Docs that came with the modules just say its as simple as "perl Makefile.PL, make, make install...etc.", but other than that it doesn't tell me anything else I need to do.

So... here is my current situation:
1. DBI module is installed with Perl5
2. iodbc Driver Manager (OpenLink software) is installed in /usr/local/lib
3. DBD::ODBC requires to set the following ENV vars first:
  DBI_DSN (done)
  DBI_USER (done)
  DBI_PASS (done)
  ODBCHOME (?????)
4. plus the INSTALL file from DBD::ODBC mentions something about an odbc.ini file, so I created one following its examples with the following data:
; START
[ODBC Data Sources]
RODOPISQL2      = Rodopi

[RODOPISQL2]
Driver          = /usr/local/lib/oplodbc.so.1
Description     = Sample OpenLink DSN
Host            = (the ip)
Username        = -----
Password        = -----
ServerType      = WinNT 4.0
Database        = ----
FetchBufferSize = 99
ReadOnly        = yes
TraceFile       = /tmp/odbc.trace
Trace           = On
; END
5. DBD::ODBC still won't compile, asking for the "Driver Manager" installation directory (?)

Can you help with this? would it be better to use Sybase's DB-Lib?

    -dZ.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

ventolinCommented:
sorry, but i don't know much more than this regarding the unix odbc libraries. you could always try some of the other suggestions.
0
ventolinCommented:
0
DropZoneAuthor Commented:
Thank you nevertheless.... :)
  -dZ.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.