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.

47 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 Munir Commerce College 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  
Blogger sasi said...

Very nice post with lots of information. Thanks for sharing these updates.
Angular Training in hyderabad
Angularjs Training in Bangalore
angular training in bangalore
Angularjs course in Chennai
angular course in bangalore
Angular Training in Coimbatore
salesforce course in bangalore
Big Data Training in Coimbatore
Angularjs course in Chennai
angularjs training in btm

April 4, 2020 at 2:54 AM  
Blogger Riya Raj said...

Really wonderful blog! Thanks for taking your valuable time to share this with us. Keep us updated with more such blogs.
AWS Training in Chennai
AWS Training
AWS Training institute in Chennai
VMware Training in Chennai
AWS Training in Velachery
AWS Training in Tambaram
AWS Training in Tnagar
AWS Training in Anna nagar

April 6, 2020 at 3:09 AM  
Blogger Vivek Vichu said...

Nice Blog...Thanks for sharing the article waiting for next update...
Artificial Intelligence Course in Chennai
AI Training in chennai
ai courses in chennai
Mobile Testing Training in Chennai
C C++ Training in Chennai
javascript training in chennai
Html5 Training in Chennai
QTP Training in Chennai
Spring Training in Chennai
DOT NET Training in Chennai

April 6, 2020 at 3:15 AM  
Blogger banupriya said...

The blog you shared is very good. I expect more information from you like this blog. Thank you.
Leadership Training in chennai
leadership training companies in chennai
leadership course in chennai
leadership in chennai
google cloud certification training in chennai
mysql training in Chennai
Leadership Training in chennai
matlab Training in Chennai

May 13, 2020 at 2:09 AM  
Blogger subha said...

Awesome blog!!! thanks for your good information... waiting for your upcoming data... keep on search data
Ai & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai

June 19, 2020 at 8:38 AM  
Blogger IT said...

Great Article
Cloud Computing Projects


Networking Projects

Final Year Projects for CSE


JavaScript Training in Chennai

JavaScript Training in Chennai

The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

June 30, 2020 at 12:35 AM  
Blogger Sreyas Ram said...

valuable blog,Informative content...thanks for sharing, Waiting for the next update…
Study Abroad Consultants in Kerala
study abroad consultants in thrissur
Study Abroad Consultants in Calicut
abroad job consultancy in coimbatore
abroad job consultancy in calicut
abroad job recruitment agencies in calicut
study abroad
study in poland
study in europe
education in germany

July 8, 2020 at 11:11 PM  
Blogger Aishu said...

This blog was so creative in knowing about connecting sql. Thanks for sharing a blog like.Please keep sharing more blogs like this.
IELTS Coaching in chennai

German Classes in Chennai

GRE Coaching Classes in Chennai

TOEFL Coaching in Chennai

spoken english classes in chennai | Communication training


July 24, 2020 at 2:26 AM  
Blogger invincible01 said...

Amazing Article, Really useful information to all So, I hope you will share more information to be check and share here.


Inplant Training for cse
Inplant Training for IT
Inplant Training for ECE Students
Inplant Training for EEE Students
Inplant Training for Mechanical Students
Inplant Training for CIVIL Students
Inplant Training for Aeronautical Engineering Students
Inplant Training for ICE Students
Inplant Training for BIOMEDICAL Engineering Students
Inplant Training for BBA Students

September 20, 2020 at 2:19 AM  
Blogger riya said...

super artical
zoho aptitude questions
zoho aptitude questions 2019 pdf
zoho c aptitude questions with answers
c aptitude questions for zoho
zoho aptitude questions with answers
zoho c aptitude questions with answers pdf
zoho aptitude questions 2017 pdf
zoho digital marketing aptitude questions
zoho aptitude questions 2018 pdf
zoho technical support aptitude questions

September 21, 2020 at 2:11 AM  
Blogger riya said...

Super article
Uipath
Uipath Tutorial
Types of uipath
Uipath Architecture
Products fo Uipath
Workflow in Uipath
process of uipath
uipath installation
creating script in uipath
project types and templates in uipath

September 22, 2020 at 8:02 AM  
Blogger riya said...

usefull article...
gazetted officer
z or r twice
what is isp
space complexity
ng-focus
unexpected token o in json at position 1
do a barrel roll 20 times
cannot set headers after they are sent to the client
how to hack any instagram account 100% working
blink html google trick

September 22, 2020 at 8:02 AM  
Blogger Unknown said...

Crypto-currency as a modern form of the digital asset has received a worldwide acclaim for easy and faster financial transactions and its awareness among people have allowed them to take more interest in the field thus opening up new and advanced ways of making payments. Crypto.com Referral Code with the growing demand of this global phenomenon more,new traders and business owners are now willing to invest in this currency platform despite its fluctuating prices however it is quite difficult to choose the best one when the market is full. In the list of crypto-currencies bit-coins is one of the oldest and more popular Crypto.com Referral Code for the last few years. It is basically used for trading goods and services and has become the part of the so-called computerized block-chain system allowing anyone to use it thus increasing the craze among the public, Crypto.com Referral Code.

Common people who are willing to purchase BTC can use an online wallet system for buying them safely in exchange of cash or credit cards and in a comfortable way from the thousands of BTC foundations around the world and keep them as assets for the future. Due to its popularity, many corporate investors are now accepting them as cross-border payments and the rise is unstoppable. With the advent of the internet and mobile devices,information gathering has become quite easy as a result the BTC financial transactions are accessible and its price is set in accordance with people’s choice and preferences thus leading to a profitable investment with Crypto.com Referral Code Code. Recent surveys have also proved that instability is good for BTC exchange as if there is instability and political unrest in the country due to which banks suffer then investing in BTC can surely be a better option. Again bit-coin transaction fees are pretty cheaper and a more convenient technology for making contracts thus attracting the crowd. The BTC can also be converted into different fiat currencies and is used for trading of securities, for land titles, document stamping, public rewards and vice versa.

Another advanced block-chain project is Ethereumor the ETH which has served much more than just a digital form of crypto-currency Crypto.com Referral Code and its popularity in the last few decades have allowed billions of people to hold wallets for them. With the ease of the online world,the ETH have allowed the retailers and business organizations to accept them for trading purposes, therefore, can serve as the future of the financial system.

October 7, 2020 at 11:38 PM  
Blogger Nora Pittman said...

Our full Lace Front Wigs are all hand made with a lace cap. They are manufactured with thin lace sewn on top of the cap. Individual hairs are then sewn onto the thin lace. Each lace wig has lace all around the unit which will need to be cut prior to securing the wig to your head. You will need to cut along the hairline around your entire head. By doing so, you will be able to wear your hair anyway you like. You can even style ponytails, up-dos, etc. Once the Lace Wigs is successfully applied, it will appear that all the hair is growing directly from your head!

Lace front wigs are hand-made with lace front cap & machine weft at back. Lace front wigs are manufactured with a thin lace that extends from ear to ear across the hairline. When you receive the wig, the lace will be quite long in the front. Cut and style according to your preference, as you will need to apply adhesive along the front of the wig. Once the wig is applied, you will still have Lace Wigs with a very natural appearance.
TeamWigz Provide the Best Lace Front Wigs and Lace Wigs in Johannesburg and South Africa.

October 14, 2020 at 9:57 AM  
Blogger Vijayakash said...

Nice blog was really feeling good to read it. Thanks for this information.
aws interview questions and answers for devops
devops interview questions and answers for freshers
java interview questions and answers
selenium interview questions and answers
digital marketing interview questions and answers
hadoop interview questions and answers

October 24, 2020 at 3:19 AM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home