Monday, October 4, 2010

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.

32 Comments:

Blogger a said...

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

May 26, 2011 at 11:54 AM  
Blogger madin said...

High quality replica watches swiss from a trust worthy watch dealer? here can offer you the best replica omega watches UKin high quality, including Cartier, Breitling, etc.

March 26, 2018 at 7:14 PM  
Blogger Unknown said...

Having a hard time deciding what kind of gift to buy during Christmas? best amazon products

April 9, 2018 at 11:00 PM  
Anonymous Anonymous said...

This is a newly written blog that is of interest to see
https://perfectreplicawatch.blogspot.com

April 12, 2018 at 1:34 AM  
Blogger sriram said...

It is really a great work and the way in which you are sharing the knowledge is excellent.Thanks for your informative article


software testing training in chennai

April 16, 2018 at 12:12 AM  
Blogger suvathi said...

I think this is the best article today. Thanks for taking your own time to discuss this topic, I feel happy about that curiosity has increased to learn more about this topic. Keep sharing your information regularly for my future reference.
Java Courses in chennai

June 26, 2018 at 10:39 PM  
Blogger Health Tips said...

affordable health insurance plans az Hey,This info is new to me,such an interesting and new post! I'm going to collect details about it..

September 10, 2018 at 4:57 AM  
Blogger Unknown said...

health tips for august

September 11, 2018 at 5:42 AM  
Blogger wpc said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
Web Design Training

October 15, 2018 at 9:57 PM  
Blogger yashnit said...

Thanks For Sharing your Information The Information Shared Is Very Valuable Please Keep Updating Us Time Just Went On reading the article Data Science Online Training Hadoop Science Online Training AWS Online Training Python Online Training

March 1, 2019 at 3:14 AM  
Blogger Aruna Ram said...

Such a wonderful blog!!! I have more info to your post and I like many posts providing for my development. Please Keep posting...
Power BI Training in Chennai
Power BI Training Fees in Chennai
Embedded System Course Chennai
Linux Training in Chennai
Tableau Training in Chennai
Spark Training in Chennai
Unix Training in Chennai
Oracle Training in Chennai
Oracle DBA Training in Chennai
Social Media Marketing Courses in Chennai

April 5, 2019 at 4:46 AM  
Blogger sandeep saxena said...

The innovative thinking of you in this blog. This blog makes me very useful to learn.
clinical sas training in chennai
clinical sas training fees
clinical sas Training in T Nagar
SAS Training in Chennai
SAS Institute in Chennai
Placement Training in Chennai
soft skills training in chennai
core java training in chennai

June 20, 2019 at 4:52 AM  
Blogger htop said...

thanks for sharing this information
java training in sholinganallur
java training in omr
hadoop training in sholinganallur
best hadoop training in omr
best hadoop training in chennai
best devops training institute in omr

June 20, 2019 at 11:00 PM  
Blogger htop said...

thanks for sharing this information
best python training in chennai
best python training institute in omr
python training in omr
selenium training in chennai
selenium training in omr
selenium training in sholinganallur
hadoop training in sholinganallur
best hadoop training in chennai

July 1, 2019 at 3:03 AM  
Blogger cynthiawilliams said...

The given information was excellent and useful. This is one of the excellent blog, I have come across. Do share more.
Ethical Hacking course in Chennai
Ethical Hacking Training Institute in Chennai
Hacking course in Chennai
ccna Training in Chennai
Salesforce course in Chennai
PHP Training in Chennai
Tally course in Chennai
Ethical Hacking course in OMR
Ethical Hacking course in Anna Nagar
Ethical Hacking course in Vadapalani

July 6, 2019 at 6:08 AM  
Blogger Kavi said...

Highly informative article. I was searching for such quality articles for a long time. I am happy that I found this article. Keep going!
Data Science Course in Chennai | Data Science Training in Chennai

July 19, 2019 at 3:20 AM  
Blogger Anbarasan14 said...

Thanks for this kind of worthy information. this was really very helpful to me. keep continuing.
Education Franchise India
Spoken English Franchise
Franchise For Spoken English Classes
Top Education Franchise In India
Best Education Franchise In India
Computer Education Franchise
Education Franchise India
Computer Center Franchise
Education Franchise Opportunities In India

August 6, 2019 at 3:12 AM  
Blogger jose said...

Really nice post. Thank you for sharing amazing information.
Java Training in Chennai/Java Training in Chennai with Placements/Java Training in Velachery/Java Training in OMR/Java Training Institute in Chennai/Java Training Center in Chennai/Java Training in Chennai fees/Best Java Training in Chennai/Best Java Training in Chennai with Placements/Best Java Training Institute in Chennai/Best Java Training Institute near me/Best Java Training in Velachery/Best Java Training in OMR/Best Java Training in India/Best Online Java Training in India/Best Java Training with Placement in Chennai

August 25, 2019 at 11:12 PM  
Blogger ramya said...

Great post very useful info thanks for this post ....
Aws training chennai | AWS course in chennai
Rpa training in chennai | RPA training course chennai
sas training in chennai | sas training class in chennai

December 5, 2019 at 1:40 AM  
Blogger shanjames said...

Nice Post | thank you so much for sharing the informative article share with us, your blog was creative writing ability has inspired me.
Data Science Training in Hyderabad

December 16, 2019 at 9:22 PM  
Blogger analytics Path said...

Was in search for this information from a long time. Thank you for such informative post. Looking forward for more of such informative postings.

http://www.analyticspath.com/

December 18, 2019 at 1:56 AM  
Blogger ohhanibaek said...

Thanks for sharing the Good information
Data Science Training in Hyderabad

January 6, 2020 at 3:50 AM  
Blogger Ravi said...


You write this post very carefully I think, which is easily understandable to me. Not only this, but another post is also good. As a newbie, this info is really helpful for me. Thanks to you.
Tally ERP 9 Training
tally classes
Tally Training institute in Chennai
Tally course in Chennai

February 20, 2020 at 1:39 AM  
Blogger Ravi said...


This content of information has
helped me a lot. It is very well explained and easy to understand.
seo training classes
seo training course
seo training institute in chennai
seo training institutes
seo courses in chennai
seo institutes in chennai
seo classes in chennai
seo training center in chennai

February 20, 2020 at 1:39 AM  
Blogger shreekavi said...

Nice blog! Thanks for sharing this valuable information
Best IELTS Coaching in Bangalore
IELTS Training in Bangalore
IELTS Coaching centre in Chennai
IELTS Classes in Bangalore
IELTS Coaching in Bangalore
IELTS Coaching centre in coimbatore
IELTS Coaching in madurai
IELTS Coaching in Hyderabad
Selenium Training in Chennai
Ethical hacking course in bangalore

February 27, 2020 at 2:19 AM  
Blogger Unknown said...

Nice Post | thank you so much for sharing the informative article share with us, your blog was creative writing ability has inspired me.

7 tips to start a career in digital marketing

“Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

we have offered to the advanced syllabus course digital marketing for available join now.

more details click the link now

https://www.webdschool.com/digital-marketing-course-in-chennai.html

February 27, 2020 at 2:48 AM  
Blogger Unknown said...

Thank for this blog are more informative step by step and useful contents. I here by attached my site would you see this blog

Web designing trends in 2020

When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, Whats App, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.

we have offered to the advanced syllabus course web design and development for available join now

https://www.webdschool.com/web-development-course-in-chennai.html

February 27, 2020 at 2:50 AM  
Blogger w3webschool said...

You are providing a post is very useful for develop my knowledge and I learn more info from your blog.
Digital Marketing Course In Kolkata
Web Design Course In Kolkata
SEO Course In Kolkata

March 18, 2020 at 10:28 PM  
Blogger Vijayakash said...

Top Technologies to learn

Excellent blog with lots of information. I have to thank for this. Do share more.

March 27, 2020 at 12:58 AM  
Blogger Arjundevan said...

Nice Blog, Very Informative Content,waiting for next update...
SAS Training in Chennai
sas training fees
sas course fees
sas training in Thiruvanmiyur
SAS Training in Tambaram
clinical sas training in chennai
Mobile Testing Training in Chennai
QTP Training in Chennai
Hibernate Training in Chennai
DOT NET Training in Chennai

March 27, 2020 at 3:06 AM  
Blogger vinudevan said...

Thanks for sharing informative article with us..
DOT NET Training in Chennai
best .net training institute in chennai
dot net training center in chennai
best .net training in chennai
Dot Net Training in OMR
Html5 Training in Chennai
Spring Training in Chennai
Struts Training in Chennai
Wordpress Training in Chennai
SAS Training in Chennai

March 30, 2020 at 5:26 AM  
Blogger jamuna said...

Really informative blog for all people. Thanks for sharing it.
Spoken English Classes in Chennai
Spoken English Course in Chennai
german classes
Best IELTS Coaching in Chennai
learn Japanese in Chennai
IELTS Coaching center in Chennai
best spoken english institute in chennai
TOEFL Coaching Centres in Chennai
content writing course in chennai
spanish coaching in chennai
Spoken English Classes in Anna Nagar
Spoken English Classes in Tnagar

April 3, 2020 at 2:14 AM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home