CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in 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 (12848 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 (12846 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 (12844 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 (12839 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 (12836 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) Powered by Gossamer Forum v.1.2.0

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