Perl Guru Forums: Perl Programming Help: DBI: How to use DBI with MSSQL server?

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: DBI:
How to use DBI with MSSQL server?

bulrush
User

Jul 1, 2015, 10:10 AM

Post #1 of 5 (19306 views)

How to use DBI with MSSQL server?	Can't Post

* Ubuntu 14.04.1 LTS (Please ignore my sig, this is a different machine)
* Perl 5.18.2

In my Perl code I'm already accessing a Postgres db with:

Code

use DBI; 
use DBD::Pg;

What modules do I need to access an MS SQL database? I have the IP address, a db user and password for it, but not the version of MS SQL yet. There doesn't appear to be a DBD::MSSQL module for Perl.

Thanks!
-----

(This post was edited by bulrush on Jul 1, 2015, 10:26 AM)

FishMonger
Veteran / Moderator

Jul 1, 2015, 10:26 AM

Post #2 of 5 (19304 views)

Re: [bulrush] How to use DBI with MSSQL server? [In reply to]	Can't Post

You need to use ODBC to connect to MSSQL.
http://search.cpan.org/~mjevans/DBD-ODBC-1.52/ODBC.pm

bulrush
User

Jul 1, 2015, 10:29 AM

Post #3 of 5 (19302 views)

Re: [FishMonger] How to use DBI with MSSQL server? [In reply to]	Can't Post

Ok. I installed unixodbc, unixodbc-dev, libodbc1, libtool, autotools-dev and several other packages that comes with unixodbc. I don't use ODBC much so I'm a newbie, and the Perl syntax for DBI::ODBC is:

Code

$dbh = DBI->connect('dbi:ODBC:DSN=mydsn', 'user', 'password');

I vaguely remember what a DSN is, I think it's a file with info for ODBC. But how do I make a DSN on Ubuntu 14.04?

EDIT: I found a dir /etc/ODBCDataSources but there is no odbc.ini in it. Do I have to make the odbc.ini myself? I found this helpful but it does not mention installing an MSSQL odbc library driver: https://help.ubuntu.com/community/ODBC

Found these files but none look like they belong to MSSQL.

Code

chuck@ubuntucomp:/etc/ODBCDataSources$ ls -l /usr/lib/odbc 
total 2288 
-rw-r--r-- 1 root root 567536 Sep 24  2013 virtodbc_r.so 
-rw-r--r-- 1 root root 559216 Sep 24  2013 virtodbc.so 
-rw-r--r-- 1 root root 609104 Sep 24  2013 virtodbcu_r.so 
-rw-r--r-- 1 root root 600784 Sep 24  2013 virtodbcu.so

In my DSN do I point to virtodbc.so?
-----

(This post was edited by bulrush on Jul 1, 2015, 10:43 AM)

bulrush
User

Jul 1, 2015, 11:27 AM

Post #4 of 5 (19297 views)

Re: [bulrush] How to use DBI with MSSQL server? [In reply to]	Can't Post

/etc/odbc.ini:

Code

[ODBC Data Sources] 
ResponseBDO = Response database desc in /etc/odbc/ini 
 
[ResponseBDO] 
Driver = /usr/lib/odbc/virtodbc.so 
Description = Response MSSQL ODBC driver 
SERVER = 12.19.1.9 
PORT =  
USER = myuser 
Password =  
Database = dbname 
 
[Default] 
Driver = /usr/lib/virtodbc.so 
Description = Default ODBC driver 
SERVER = 12.19.1.9 
USER = myuser 
Database = test

Perl command that gets error below:

Code

$respdsn="dbi:ODBC:DSN=ResponseBDO;host=$respserver"; 
$respdbh=DBI->connect($respdsn, $respdbuser, $respdbpw, 
{AutoCommit=>0,RaiseError=>1,PrintError=>0} 
);

When I run the Perl program I get this error:

Code

DBI connect('DSN=ResponseBDO','myuser',...) failed: 
 [unixODBC][OpenLink][Virtuoso iODBC Driver]CL033:  
Connect failed to localhost:1111 = localhost:1111. (SQL-S2801) at 
 /home/chuck/perl/progs/fedex.pl line 1532. 
 at /usr/lib/perl5/DBI.pm line 684.

When I run 'odbcinst -q -d' I get:

Code

chuck@ubuntucomp:~/perl/gilson/jimv/fedex$ odbcinst -q -d 
odbcinst: SQLGetPrivateProfileString failed with .

Why is the driver trying to talk to localhost:1111?
-----

(This post was edited by bulrush on Jul 6, 2015, 4:03 AM)

FishMonger
Veteran / Moderator

Jul 1, 2015, 12:27 PM

Post #5 of 5 (19294 views)

Re: [bulrush] How to use DBI with MSSQL server? [In reply to]	Can't Post

It's been awhile since I configured ODBC on a *nix system and don't remember the details. I'll look into it when I have more time.

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives