SUMMARY: Perl + Solaris --> MSSQL

Well, I should have known to preface my query by saying that, yes, I had 
already looked at CPAN. This would have avoided somewhat laconic answers of 
the type "www.cpan.org has the DBI/DBD modules you need."

As it turns out, even though CPAN does seem to have what's needed, I was 
unable to make it work. In summary, there should be 3 possible ways to 
establish a connection between Perl and an MS-SQL Server database:

1) DBD::Sybase. Yes, Sybase, because SQL Server is based on the same 
database engine. This requires to install the DBI module, of course, and to 
compile FreeDTS, with your SYBASE environment variable set to 
/usr/local/freetds. See www.freetds.org for more information. The 
compilation of the DBD::Sybase module failed its tests with a core dump for me.

2) Sybperl. This is a wrapper around the Sybase C APIs. Again, this didn't 
compile.

3) DBD::ODBC. Interfaces directly with ODBC on the SQL Server. No luck 
there either.

Sam Nelson was the only person who claimed to actually have gotten this to 
work. So, I will quote his response here in extenso:

At 12:17 AM 2/28/2002, Sam Nelson wrote:
>I just did this last week.  It costs money, though.  You need the DBI and
>DBD::ODBC modules for Perl, and then you need an ODBC-ODBC bridge package.
>You install the client end of the bridge on the Solaris machine and the 
>server end on the Windows machine that runs the MSSQL server.  The ODBC 
>bridge I'm using comes from Easysoft: http://www.easysoft.com for more 
>details.  If you just want, say, a five-connection licence for the Windows 
>bridge server, it'll cost you about $700 (it cost me UKP500).
>
>We spent a bit more and bought the Solaris bridge server as well as an ODBC
>Oracle driver.  In this direction, you can use it to link together Access
>and Oracle databases.  Over the week I was testing (I found one real bug in
>the ODBC/Oracle driver for them) the tech-support office was extremely 
>helpful.
>
>I have no connection with Easysoft---I'm just a satisfied customer.
>
>An example of the bridge working can be seen if you aim a browser at
>http://yen.cs.stir.ac.uk/cgi-bin/sam/dwwho where you'll see the list of users
>currently logged in to PCs in our local domain.  The webserver is Apache 
>1.3.20 running on Solaris 8, querying the PC-information database on our 
>MSSQL Server maintained by Altiris eXpress Deployment Server (see 
>http://www.altiris.com/ for more details---again, just a satisfied 
>customer!) and "who's logged in in our NT domain?" is a question I've been 
>trying to answer ever since we've had Windows on this site.

Since this required successful installation of DBD::ODBC, I didn't try this 
myself either. Finally, I had to sell my soul to the Devil and resort to 
running my script on a Windows machine. Windows will often let you play in 
its sandbox, but will only play in yours kicking and screaming. I installed 
DBI and DBD::ODBC on Windows with no problems (would you believe this!). I 
recommend installing the modules directly from the "perl -MCPAN -e shell" 
prompt, however, which will carefully verify your dependencies for a clean 
install.

Here's a working script that can dump the list of databases on the server 
(after you have properly configured the ODBC control panel on your machine 
AND on the server):


         #!/usr/bin/perl -w

         $data_source = "DBI:ODBC:Badges";
         $username    = "snafu";
         $password    = "foobar";

         use DBI;
         use DBD::ODBC;

         # Connect to the Sybase db server.
         $dbh = DBI->connect($data_source, $username, $password)
                 || die "Can't connect: $DBI::errstr";

         # Run a basic SQL statement.
         $sql_statement = "select catalog_name from 
information_schema.schemata";
         $sth = $dbh->prepare($sql_statement)
                 || die "Can't prepare the SQL statement: $DBI::errstr";
         $sth->execute || die "Can't execute the SQL statement: $DBI::errstr";

         # Get the data back.
         while (($db) = $sth->fetchrow_array)
         {
           print ("$data_source contains database $db\n");
         }

         # Destroy objects and close things down.
         $sth->finish;
         $dbh->disconnect;

Many thanks to those who replied:
Ken Germann <kigermann@attbi.com>
Laloo Yadav <laloo77@rediffmail.com>
Larye Parkins <LParkins@niaid.nih.gov>
Lee Heagney <lheagney@sonic.ee.ucl.ac.uk>
Neil Hunt <Neil.Hunt@fujitsu.com.au>
Robert Banniza <RPBanniz@ascensionhealth.org>
Sam Nelson <Sam.Nelson@cs.stir.ac.uk>
Todd Urie <rtu@alumni.rowan.edu>
Wesley Wannemacher <wesw@aol.net>

My original message:
>SunMgr's,
>
>I would like to connect from an Ultra-5 with Solaris 8 + Perl 5.6.1, to a 
>Microsoft SQL Server system to synchronize our list of usernames with our 
>security/badge system.
>
>I am unable to find documentation on what Perl modules need to be 
>installed for this, how to compile them into the Perl installation, or how 
>they must be called.
>
>I would greatly appreciate help from people who have accomplished this 
>feat before. Thanks!!!


+===========================================================+
| Fabrice "Script It!" Guerini  Blue Martini Software, Inc. |
| Senior Operations Engineer    2600 Campus Drive           |
| Tel: (650) 356-7576           San Mateo, CA 94403-2522    |
| Fax: (650) 356-4001           www.bluemartini.com         |
+===========================================================+
_______________________________________________
sunmanagers mailing list
sunmanagers@sunmanagers.org
http://www.sunmanagers.org/mailman/listinfo/sunmanagers