Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Accessing Microsoft SQL Server from Linux using DBD::Sybase

by CleverFox (Novice)
on Sep 20, 2004 at 15:18 UTC ( [id://392385]=perltutorial: print w/replies, xml ) Need Help??

Accessing Microsoft SQL Server from Perl
by Lindsay Leeds (2004 Sep 20)

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.0
make
make 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 = yes
try 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

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 www.cpan.org if you haven't already.

once you have untarred it and are in the directory, run:

export SYBASE=/usr/local/freetds
perl Makefile.PL
make
make 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/perl

use 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";
   }
}

Good luck!

 

 


 

 

 

 

 

 

 


 



 

 

 

Replies are listed 'Best First'.
Re: Accessing Microsoft SQL Server from Linux using DBD::ODBC
by imp (Priest) on Oct 27, 2006 at 16:20 UTC
    Excellent guide.

    Another option is to use DBD::ODBC with iodbc and freetds. This gives you the ability to use bound parameters.

    Note that freetds has to be compiled for iodbc, and DBD::ODBC does as well. Here is the install sequence I used about a year ago (check for new versions):

    # Install libiodbc wget http://www.iodbc.org/downloads/iODBC/libiodbc-3.52.3.tar.gz && tar -xzf libiodbc-3.52.3.tar.gz && cd libiodbc-3.52.3 && ./configure && make && make install # Install freetds wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stab +le.tgz tar -xzf freetds-stable.tgz && cd freetds-0.63 && ./configure -with-iodbc && make && make install # Configure odbc.ini echo "[foo] Database=foo Server=10.10.10.164 TDS_Version=8.0 Port=1433 Driver=/usr/local/lib/libtdsodbc.so" > /etc/odbc.ini # install DBD::ODBC perl -MCPAN -e'get DBD::ODBC' && cd ~/.cpan/build/DBD-ODBC-1.13 && export ODBCHOME=/usr/local && perl Makefile.PL && make && make install
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by Tux (Canon) on May 24, 2019 at 07:35 UTC

    A very valuable post, giving me all the hints I needed

    I reply to this old post to share how I got my project to work on openSUSE Tumbleweed where I needed to connect to a remote MSSQL database over a VPN, and updates to my OS caused the connection to fail.

    Using DBD::Sybase only cause troubles beyond tears. There were major issues with both UTF-8 handling and transactions, so I dropped that approach.

    I got it to work on two different approaches:

    I assume you have installed perl, DBI and DBD::ODBC, for which you most likely need:

    $ sudo zypper in unixODBC unixODBC-devel

    So, here are my two approaches:

    1. Using DBD::ODBC with Microsoft drivers
      -- Repositories https://packages.microsoft.com/sles/12/mssql-server-2017/ https://packages.microsoft.com/sles/15/prod/ https://packages.microsoft.com/opensuse/15/prod/

      You might be able to get away with just the first, but I also wanted to test against a local MSSQL database, so I could do tests off-line

      Even though the repos do seem to work fine on Tumbleweed, there is a mismatch beteen TW, SLES 12, SLES 15, and Leap 15, whcih makes me feel rather uncomfortable

      $ sudo zypper in mssql-cli mssql-server mssql-tools msodbcsql17

      Here you might be getting away with just msodbcsql17, but it makes testing a lot easier if you have all.

      Then set up you ODBC (just showing the essential parts)

      $ cat /etc/unixODBC/odbcinst.ini [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1 UsageCount=2
      $ cat ~/.odbc.conf [mssql] Description = A MSSQL databe Driver = ODBC Driver 17 for SQL Server Server = fully.qualified.host.name User = PerlMonk Password = Password Database = my_data

      I found it to be essential to use the fully qualified hostname in the configuration (also put it in /etc/hosts for speed) as these Microsoft ODBC drivers do not work on "localhost", "127.0.0.1", or "myhostname".

      Note that at the moment of writing, this approach only worked for me with an older perl (5.24.0) compiled against older openssl (1.1.0), as my current perl (5.28.2 built against openssl-1.1.1) refused to connect.

      In perl, now just connect using

      use DBI; my $dbh = DBI->connect ("dbi:ODBC:mssql");
    2. Using DBD::ODBC with FreeTDS

      This approach does not require additional repositories. All you need is available in the base repos.

      $ sudo zypper in freetds-{config,devel,doc,tools} lib{ct4,dbi-drivers- +dbd-freetds,sybdb5,tdsodbc0}

      Maybe you do not need all of those and maybe installing just the one you need will automatically install the rest, but this is the complete list.

      Then set up you ODBC (just showing the essential parts)

      $ cat /etc/unixODBC/odbcinst.ini [FreeTDS] Description=FreeTDS unixODBC Driver Driver=/usr/lib64/libtdsodbc.so.0 Setup=/usr/lib64/libtdsodbc.so.0 UsageCount=1
      $ cat ~/.odbc.conf [freetds] Description = A MSSQL databe Driver = FreeTDS TDS version = 7.2 Trace = No Server = fully.qualified.host.name Port = 1433 User = PerlMonk Password = Password Database = my_data Client Charset = UTF-8

      Note that at the moment of writing, this approach worked for me with the most recent perl (5.30.0) with compiled with -Duseshrplib against openssl 1.1.1, so I can use all the features of modern perl!

      In perl, now just connect using

      use DBI; my $dbh = DBI->connect ("dbi:ODBC:freetds");

    A thing to note, is that the first approach will return uniqueidentifier fields as ASCII (like ECF63B31-2F2B-4B17-94B9-1894E35D01C1), whereas the second approach returns them binary, so you might need to convert them if you need to show them for debugging or export purposes:

    use Data::UUID; my $uuid_conv = Data::UUID->new; while (my $row = $sth->fetchrow_hashref) { $row->{a_uuid} = $uuid_conv->to_string ($row->{a_uuid}); }

    Enjoy, Have FUN! H.Merijn
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by RMGir (Prior) on Sep 21, 2004 at 13:55 UTC
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by mpeppler (Vicar) on Sep 21, 2004 at 14:50 UTC
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by briantillman (Initiate) on Jul 01, 2009 at 18:29 UTC
    Thanks! This worked very well for me while migrating from mysql to MS SQL 2005. A few things to note:

    I'm using Perl 5.8.5 with libdbi 0.6.5 on RHEL 4u7 and as such had to use DBD-Sybase-1.04; newer versions of DBD-Sybase require a later version of libdbi.

    If your M$ SQL server requires SSL, you can compile freetds with tls and ssl:

    --with-gnutls --with-openssl=/usr/include

    This ofcourse requires the development packages of gnutls and openssl

    Also note, that with SQL Server 2005, I had to set my TDS version to 8.0, this also needs to be done at compile time and configured in freetds.conf:

    --with-tdsver=8.0

    Many Thanks!

      Thanks a million briantillman and CleverFox. Couldn't have done this without you guys. After following your steps, I got a script to connect and make queries. This will save me a LOT of time.

      I have
      • MS SQL Server 2005
      • Perl 5.8.5
      • libdbi 0.6.5
      • RHEL 4 nahant u 5

      Needed DBD-Sybase-1.04 as briantillman pointed out (from http://www.peppler.org/freeware/dbd-sybase.html).

      For noobs such as myself, needed to use the following to grab my versions:

      • cat /etc/redhat-release
      • rpm -qa | grep libdbi
      • perl -v

      Other observations (which could be triggered by my older versions of things): the  export SYBASE=/usr/local/freetds wouldn't work with the latest DBD-Sybase-1.11. I would get

      # perl Makefile.PL Unknown Client Library version - assuming FreeTDS.

      Also when doing a make with DBD-Sybase-1.11 and my versions, I would get the following:

      /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI/Driv +er_xst.h: In function `dbixst_bounce_method': /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI/Driv +er_xst.h:14: error: `my_perl' undeclared (first use in this function) /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI/Driv +er_xst.h:14: error: (Each undeclared identifier is reported only once /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI/Driv +er_xst.h:14: error: for each function it appears in.) /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI/Driv +er_xst.h: In function `dbdxst_bind_params': /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI/Driv +er_xst.h:54: error: `my_perl' undeclared (first use in this function) /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI/Driv +er_xst.h: In function `dbdxst_fetchall_arrayref': /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI/Driv +er_xst.h:75: error: `my_perl' undeclared (first use in this function) make: *** [Sybase.o] Error 1

      One last note, I am able to connect to MS SQL Server 2005 with tds version = 7.0.

Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by zenworks (Initiate) on Dec 02, 2010 at 18:26 UTC

    Thank you for posting this

    the only thing I had to do in addition to your instructions was to edit /etc/ld.so.conf to include the path for libct.so.4, which is installed by the freetds installation portion. This location is /usr/local/freetds/lib

    and then, run ldconfig

    this worked for me using RHEL 5.5, Perl 5.8.3, MS SQL Server 2005

Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by Anonymous Monk on Jul 17, 2014 at 14:28 UTC

    A very useful and clear article, thank you!

    Worth knowing: By default, if you use the tsql date commands, you get the date from DBD::Sybase in a rather inconvenient format, with the year, day and month like this: "Jul 8 2014".

    If you add

    $dbh->syb_date_fmt('ISO');

    Just after you open the database handle, DBD::Sybase then presents date data in a more useful format, "2014-07-08".

    More details at Stack Overflow:
    http://stackoverflow.com/questions/14286821/perl-rewrite-sql-date-format/14287790#14287790

      I get the following error :  perl: symbol lookup error: /usr/bin/perl5/DBI/1.607/exec/5.10/lib/perl5/auto/DBI/DBI.so: undefined symbol: Perl_Istack_sp_ptr But I can connect perfectly with the below:  /efs/dist/fsf/freetds/0.82/exec/bin/tsql -S DBName -I $HOME/freetds.conf -U userName -P passwOrd what might be the issue?
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by purush1985 (Initiate) on Apr 24, 2015 at 12:41 UTC
    It is working for me. I did give up server times. Finally this document helped me. Thanks a lot for this article.
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by Anonymous Monk on Feb 27, 2009 at 17:46 UTC
    This was a great help, thanks! 2 things I had to do differently: 1) I had to sudo make install freetds and Sybase 2) I had to specify the path to freetds: export LD_LIBRARY_PATH=/usr/local/freetds/lib
      Oh, and I forgot about #3: I had to edit dbimp.c replacing BLK_VERSION_150 with BLK_VERSION_100 jww
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by Anonymous Monk on Mar 09, 2011 at 17:56 UTC
    Thank you for the post , it save us a lot of time for researching other options. It works well on Fedora , Perl 5.8 and Freetds 0.82 best regards,
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by enky123 (Initiate) on Feb 28, 2014 at 08:21 UTC
    Hi .... U have mentioned connecting to MSSQL by using FreeTDS as /usr/local/freetds/bin/tsql -S sql1 -U sqluser. I need to pass more arguments to it, i.e. Query & and redirect the same to an output file in the same commandline. so can this be possible ? /usr/local/freetds/bin/tsql -S sql1 -U sqluser -I Query -o Outputfile. Please Help.
        Hi, I have tried passing arguments as a Query & an Output file. But that's not working. Please take a look below. see below syntax, (This works !!!!!) /usr/local/freetds/bin/tsql -S sql1 -U sqluser -P sqlpassword. But the below command doesnt work. /usr/local/freetds/bin/tsql -S sql1 -U sqluser -P sqlpassword -I Query -o Outputfile.
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by Anonymous Monk on Oct 10, 2007 at 17:47 UTC
    Thanks for step #1 under configuring FreeTDS. I couldn't get tsql to connect to a server for which I was using an IP and not a hostname. I added that to the freetds.conf and it worked like a charm!
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by Anonymous Monk on Dec 04, 2008 at 18:26 UTC
    This worked perfectly for me. Thank you so much!! Steve
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by Anonymous Monk on Dec 16, 2010 at 17:33 UTC
    Hi, Thanks for the steps. I have a few perl programs that use Sybase::DBlib and Sybase::CTlib. I installed FreeTDS and updated the freetds.conf. Do I need to recompile Sybase::DBlib and Sybase::CTlib after changing the LD_LIBRARY_PATH and SYBASE env variable to point to the freetds paths? Please advise.
Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase
by Anonymous Monk on Sep 19, 2013 at 20:56 UTC

    THANK YOU! THANK YOU! THANK YOU!

    This is the most straight forward and comprehensive guide to getting the DBD::Sybase module to compile and run cleanly.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perltutorial [id://392385]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (3)
As of 2024-03-29 02:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found