Recently, I made yet another attempt to get Perl to access Microsoft SQL Server using DBD. Usually, when I want to connect to a Microsoft SQL Server, it is from Perl on Windows. So I take the easy route and use DBD::ODBC and use an ODBC connection. This time though, I wanted to connect to Microsoft SQL Server 2000 from a Linux box. Having no ODBC to fall back on, I looked for native DBD driver of some sort.
It took me several hours of struggling to make it work. I almost gave up several times, so I am writing outline to help anyone else trying to accomplish this same task.
In the end, we will use the DBD::Sybase perl module from CPAN to access the Microsoft SQL Server. Before we can do that however, we must first compile the freetds library.Note: From now on I will refer to Microsoft SQL Server as SQL Server. Please do not confuse this with a generic sql server. We can all now pause to gripe about the lack of imagination in product naming at Microsoft.
Compiling Freetds
Download and compile freetds from
http://www.freetds.org/.once you unzip and untar it, enter the directory and run:
./configure --prefix=/usr/local/freetds --with-tdsver=7.0makemake install
Configuring Freetds
Now we have the freetds compiled, but we still have configure it. This is the part that threw me off and is so different from other DBD drivers. The DBD::Sybase driver will ultimately be affected by the contents of the /usr/local/freetds/etc/freetds.conf file. If that file is not configured correctly, your DBD::Sybase connection will fail.
Okay, now that we have established there is a relationship between the freetds.conf file and the DBD::Sybase module, let's edit the freetds.conf file.
The strategic modifications I made to the freetds.conf file were:
1) uncomment the following lines and modify if necessary:
try server login = yestry domain login = no
Note: this forces the module to attempt a database login instead of a domain login. I could not get domain login to work, though I will admit I did not try very hard.
2) uncomment the following line and modify if necessary:
tds version = 7.0
This supposedly sets the default tds version to establish a connection with. I have only SQL Server 2000 servers, and they won't talk at any lower version. So I set it to 7.0. If for some reason you had older SQL Servers, you might leave it at the default 4.2.
3) create a server entry for my server sql1:
[sql1]
host = sql1
port = 1433
tds version = 8.0
[download]Note: My server here is sql1. Ping sql1 worked, so I am sure I can resolve it using DNS. You can also specifcy an ip address instead of the host name. The sql1 in the brackets is just a descriptor. It could be 'superduperserver' and it would still work as long as my 'host =' is set correctly. I tried 'tds version 7.0' for my SQL Sever 2000 and it worked. Version 5.0 though resulted in an error. You might want to verify your SQL Server is listening on port 1433 with a 'netstat -a -n' run from the command line on the SQL Server.
At this point you can verify your configuration.
/usr/local/freetds/bin/tsql -S sql1 -U sqluser
You will then be prompted for a password and if everything is well, you will see a '1)' waiting for you to enter a command. If you can't get the 1) using tsql, I doubt your DBD::Sybase perl code is going to work. Please note that sqluser is not an Active Directory/Windows Domain user, but an SQL Server user.
Compiling DBD::Sybase
Now that we have the freetds library prerequisite for DBD::Sybase installed and configured, we can compile the DBD::Sybase perl module. Obtain it from
http://www.cpan.org/ if you haven't already.
once you have untarred it and are in the directory, run:
export SYBASE=/usr/local/freetdsperl Makefile.PLmakemake install
Note: The export line is to let the compilation process know where to find the freetds libraries.Using DBD::Sybase
You are now ready to test your DBD::Sybase module.
#!/usr/bin/perluse DBI;$dsn = 'DBI:Sybase:server=sql1';my $dbh = DBI->connect($dsn, "sqluser", 'password');die "unable to connect to server $DBI::errstr" unless $dbh;$dbh->do("use mydatabase");$query = "SELECT * FROM MYTABLE";$sth = $dbh->prepare ($query) or die "prepare failed\n";$sth->execute( ) or die "unable to execute query $query error $DBI::errstr";$rows = $sth->rows ;print "$row rows returned by query\n";while ( @first = $sth->fetchrow_array ) { foreach $field (@first) { print "field: $field\n"; }}
Content extracted from:
http://www.perlmonks.org/?node_id=392385
