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. 우리카지노는 대한민국의 바카라 업계를 장악하고 있는카지노사이트 입니다. 우리카지노가 대한 민국에서 장악한 바카라 시장점유율이 50%가 넘고 10년 넘게 온라인 바카라 시장을 장악해왔기 때문에 대한민국에서는 우리카지노를 모르는 사람은 드뭅니다. 이런 바카라 업계의 독보적인 입지 때문에 늘 유명하거나 최고만을 찾는 사람들이 카지노사이트를 찾을때는 늘
    우리카지노를 찾습니다.바카라를 처음 시작하시는 초보자분들에게도 우리카지노에서 카지노사이트를 시작하시기 좋은 환경입니다. 우리카지노사이트에서는 신규가입시 3만쿠폰을 지급 해주기 때문입니다. 사람들이 늘 1등만을 찾는 이유는 분명 있습니다. 다른 카지노사이트와는 달리 우리카지노를 이용하실시 에이전트를 끼고 게임을 하신다면 본사 이외에 활동쿠폰 및 오링쿠폰을 별도로 제공해주고 있기 때문입니다. 이러한 이유들 때문에 카지노사이트 업계에서 바카라를 즐기신다면 다들 우리카지노를 선호 하십니다. 카지노사이트에서 바카라를 이기기 물론 어렵습니다. 하지만 우리카지노의 에이전트를 끼고 바카라를 즐기신다면 승산이 있다고 봅니다. 우리카지노 에이전트의 연락처는 홈페이지로 연락하시면 언제든지 부담없이 소통가능 합니다. 카지노사이트를 선정할때는 바카라를 다른곳보다 유리하게 즐길 수 있는 카지노를 선택해야한다고 생각합니다. 그것이 바로 우리카지노 입니다. 이상으로 우리카지노와
    바카라 카지노사이트 사이의 상관관계를 알아보았습니다 바카라사이트.

    ReplyDelete
  2. Im Immo Cashflow Booster erfahren Sie, wie Sie durch smarte Immobilienanlagestrategien passiven Cashflow generieren. Sie müssen keine Bankgeschäfte tätigen, Geld leihen oder etwas kaufen.

    Eric erklärte 6 verschiedene Strategien, die verwendet werden können, um durch Leasing und Leasing einen Cashflow zu generieren. Diese Methode ist völlig neu und eigentlich eine interne Strategie.

    Anfangs war ich dieser Strategie etwas skeptisch gegenüber, denn wenn man die vermietete Wohnung wieder untervermieten möchte, wie überzeugt man dann den Vermieter.

    Aber ich habe eine bessere Ausbildung und Eric hat klare Anweisungen, wie man den Vermieter leicht überreden kann. Der Immo Cashflow Booster bietet dir Top aktuelle Strategien, welche auch jahrelang funktionieren. Und in diesem Erfahrungsbericht, werde ich dir meine persönlichen Immo Cashflow Booster Erfahrungen mitteilen.

    ReplyDelete
  3. Just 99 Web Design offers affordable web design packages for every business, from basic one-page websites to full eCommerce website design services. affordable ecommerce website design

    ReplyDelete
  4. 마징가티비는 메이저스포츠방송국으로써 100% 실시간 무료 TV 방송을 제공해주는 것외에도 많은 정보를 가지고있는 곳입니다.해외축구중계, NBA중계, 해외스포츠중계, 국내농구중계, 해외야구중계, MLB중계, 국내야구중계, KBO, 배구중계, 해외배구중계, KOVO중계, UFC중계, 테니스, 스포츠티비 스포츠방송 nba중계 스포츠중계 중계 해외축구중계프리미어중계 무료스포츠티비 월드컵중계 정보공유

    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