Skip to main content

Connecting to SQL Server from OS X perl

I've been spending my coding time in the offhours working on Perl instead of Ruby. My coding time in general has been very limited, which is part of the reason for the length of time between updates. :)

My latest project is to pull data out of a Microsoft SQL Server database for analysis. I'm using perl for various reasons: I need a crossplatform environment, and I need certain libraries that only work on perl. Some of the target users for my code run on Windows.

I know that Ruby runs on Windows but it's not the platform of choice for Ruby developers. The vast majority seem to develop either on OS X or Linux. So Ruby on Windows isn't at the maturity that ActiveState perl is on Windows.

In fact, I don't even run native perl anymore on my MacBook Pro. I've switched over to ActiveState perl because I don't need to compile anything every time I want to install new CPAN libraries. And because it's ActiveState, I'm that much more confident it will work on other platforms.

The bottom line is that perl makes the most sense for what I'm trying to do. I vastly prefer ruby to perl but I don't mind working in perl when I have to.

So how to connect to SQL Server from perl? My first thought was that I could use ODBC. My research quickly took me to DBD::ODBC in CPAN. After spending some time Googling for other examples and trying to get it working, I wasn't getting anywhere.

It took me some more research until I realized that DBD::ODBC is only one piece of the whole picture. I also need an ODBC driver manager and an ODBC driver.

The two main ODBC driver managers for Unix/Linux are unixODBC and iODBC. Fortunately, iODBC is already included in OS X.

For the ODBC driver itself, I wound up using FreeTDS. Because FreeTDS is only available as source code, I had to use MacPorts to download and compile the code. MacPorts installs everything in /opt/local by default.

So here was the process:

1. Install FreeTDS. If you are using ActiveState perl as I am, you must force MacPorts to build FreeTDS as 32-bit because ActiveState is 32-bit only. If you go with the default of compiling FreeTDS as 64-bit (or x86_64) then you will get this error when you call the FreeTDS library code from ActiveState perl:

[iODBC][Driver Manager]dlopen(/opt/local/lib/libtdsodbc.so, 6): no suitable image found. Did find:
/opt/local/lib/libtdsodbc.so: mach-o, but wrong architecture (SQL-00000) at test.pl line 26

So to fix this error, edit the MacPorts configuration file:

sudo vi /opt/local/etc/macports/macports.conf

Uncomment this line:

set build_arch i386

Once you build and install FreeTDS, ensure that you can use it to talk with the database. I used tsql:

TDSVER=8.0 tsql -H 10.10.10.1 -p 1433 -U 'DOMAIN\user' -P 'password'

2. After you do this, test your ODBC driver manager and driver. I used iODBC because it comes with OS X. It comes with a utility called iodbctest. I used similar parameters to tsql when testing:

iodbctest "Driver=/opt/local/lib/libtdsodbc.so;Server=10.10.10.1;Port=1433;TDS_Version=8.0;uid=DOMAIN\user;pwd=password;Database=Database"

Note a few things:
  • Other sites tell you to make entries in odbc.ini, odbcinst.ini, or freetds.conf. If you set all parameters on the command line then you don't need to tweak these other config files.
  • The Driver parameter is set to the full path for libtdsodbc.so. This is the actual ODBC driver. The name or path may differ, depending on your OS and ODBC driver software.
  • I haven't created an ODBC data source on my Windows SQL Server host so far. Some sites say you need to do this but I found it worked without this.
3. Use the perl code with DBD::ODBC to call the database. Here's some sample code. Note how I first populate the data source ($dsn) with the parameters I used with iodbctest, and then I pass this to the DBI->connect method.
#!/usr/bin/perl

use strict;
use DBI;

my $user = 'user';
my $pass = 'password';
my $driver = "/opt/local/lib/libtdsodbc.so";
my $db_server = "10.10.10.1";
my $db_name = 'Database';
my $port = 1433;
my $tds_version = "8.0";

my $dsn = join "", ("dbi:ODBC:",
   "Driver=$driver;",
   "Server=$db_server;",
   "Port=$port;",
   "UID=$user;",
   "PWD=$pass;",
   "TDS_Version=$tds_version;",
   "Database=$db_name",
);

my $db_options = {PrintError => 1, RaiseError => 1, AutoCommit => 0, };

my $dbh = DBI->connect($dsn, $db_options);
$dbh->{LongReadLen} = 32768;
my $sql = qq/select * from table/;
my $sth = $dbh->prepare($sql);
$sth->execute();
my @row;
while (@row = $sth->fetchrow_array) {
    print join(", ", @row), "\n";
}
Note on 12/29/10: I added this after the DBI->connect: $dbh->{LongReadLen} = 32768; I was getting "Data truncated" errors when accessing ntext fields. This link was a real help and helped me solve the problem.

Comments

  1. Hi,

    i'm trying to the same as you above but when i try and test my connection with iODBC i get the error you warn about above even after set build_arch i386. I checked this with file libtdsodbc.so. Do you know why this might be happening?

    Thanks

    ReplyDelete
  2. Nice article, it is very helpful valuable and informative for me. Thanks for sharing these information with all of us. whatsapp mod

    ReplyDelete
  3. PG SLOT มีเกมหลากหลายให ้เลือก พรอ้ มกราฟฟิกแอนนเิมชนั่ สวยงามทสี่ ดุ ในยคุ นี้แจ็คพอตแตกง่าย pgslot โปรโมชนั่ พเิศษส าหรับทงั้สมาชก เกา่ และสมาชกิ ใหม่ ระบบฝาก-ถอนเงินออโต ้เงินเข ้าเร็วทันใจมีแอดมิ ดูแลตลอด 24 ชวั่ โมง ต ้องที่ pgslot.bid มั่นคงจ่ายจริง ไม่ว่าคุณจะถอนเงิ มากมายเท่าไหร่ ทางเว็บจ่ายเต็มไม่มีหัก pgslot ปั่นง่ายแตกง่าย

    pg slot ผู ้ให ้บริการเว็บตรง ที่แตกง่ายที่สุด ปั่นสนุก ฝากเงินรับเครดิตฟร สูงสุด 15000 บาท สมัคร พีจีสล็อต หรือ PGSLOT กับเราเพื่อความมั่นคงของคุณเอง.

    ReplyDelete
  4. 토토사이트 이용을 하다 보면 먹튀 피해에 대한 두려움이 갑자기 생기거나 두려움을 가지고 이용하는 경우가 대반사입니다. 토토마추자는 그러한 두려움을 없애드리기 위해 만들어진 먹튀검증커뮤니티 입니다. 다양한 검증 활동을 통하여 축적된 데이터로 방문해 주시는 모든 회원님들께 무료로 제공해드리고 있습니다. 토토사이트를 이용 하는 회원님들은 메이저사이트를 이용하고 싶은 마음이 크다는 걸 알 수 있습니다. 하지만 해당 토토사이트가 메이저인지 아닌지 확인할 방법은 민간인에게 어려움이 있다는 걸 알 수 있습니다. 이용전 꼭 토토마추자를 통하여먹튀검증 서비스를 받아보시기 바랍니다 안전놀이터.
    국내에 합법 토토사이트는 배트맨토토 에서 발매하는 정식서비스만 이용을 할 수 있으나 배팅금액 제한이 있기에 우후죽순 사설토토사이트가 생겨나고 있습니다. 대체적으로 이를 방지를 하려면 배트맨토토 자체에서 배팅금액 한도를 늘리는 수밖에 없다고 생각됩니다.

    ReplyDelete
  5. Check out today’s coronavirus live update, the availability of the nearest hospital ICU bed, and the ambulance contact number in Covid Surokkha. Visit: ambulance service in dhaka

    ReplyDelete

Post a Comment

Popular posts from this blog

Building a Hadoop cluster

I've recently had to build a Hadoop cluster for a class in information retrieval . My final project involved building a Hadoop cluster. Here are some of my notes on configuring the nodes in the cluster. These links on configuring a single node cluster and multi node cluster were the most helpful. I downloaded the latest Hadoop distribution then moved it into /hadoop. I had problems with this latest distribution (v.21) so I used v.20 instead. Here are the configuration files I changed: core-site.xml: fs.default.name hdfs://master:9000 hadoop.tmp.dir /hadoop/tmp A base for other temporary directories. hadoop-env.sh: # Variables required by Mahout export HADOOP_HOME=/hadoop export HADOOP_CONF_DIR=/hadoop/conf export MAHOUT_HOME=/Users/rpark/mahout PATH=/hadoop/bin:/Users/rpark/mahout/bin:$PATH # The java implementation to use. Required. export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/CurrentJDK/Home hdfs-site

Creating a Hackintosh

I've always wanted to create a "Hackintosh", i.e. a standard PC that runs OS X. My PC is over 5 years old so it was time for a refresh. I figured this was the best time to give the Hackintosh a go. Hardware CPU:  Intel Quad Core i7 4790 3.6 Ghz Motherboard:  GIGABYTE GA-Z97-HD3 Audio:  ALC 887 Network: Realtek 8111F-VL Network Card:  4 Antennas 802.11ac WiFi BCM94360CD Wireless Network Card Graphics Card:  nVidia 750 GTX Memory:  Corsair Vengeance DDR3-1600 32 GB (4x8 GB) Hard Drive : Seagate ST3000DM001 3 TB SATA3 7200 rpm DVD:  Samsung SH-224DB 24X BIOS Changes The first step was to change the BIOS settings to support OS X. Disabling VT-d is the only setting that is clearly required; the others are questionable but were done by others so I thought they were worth trying. F7  to load Optimized Defaults M.I.T. Advanced Frequency Settings Extreme Memory Profile (X.M.P.): Enabled Miscellaneous Settings PCIe Slot Configuration:  Gen