Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??

This is a Database question. I am trying to use Perl-DBI from a Linux system to talk to a SQL Server Database, using the new Microsoft SQL Server driver for Linux. After several hours of effort (spread across a week), I am maddeningly close but just short of success. I've established connectivity to the database (verified with Wireshark) using command line utilities, but for some as-yet undetermined reason Perl-DBI fails to connect (or even access the network).

Much of what I've accomplished thus far required piecing through a large mass of contradictory and obsolete web pages, so once I get over this hurdle I have the making of a good wiki page for setting up from scratch, which I would be happy to submit for the common good out of gratitude. Details follow:

Basic Setup

  • Ubuntu 13.04 fresh install on VM (uname -a = Linux rr 3.8.0-19-generic #29-Ubuntu SMP Wed Apr 17 18:16:28 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux)
  • perl 5, version 14, subversion 2 (v5.14.2) built for x86_64-linux-gnu-thread-multi
  • perl -MDBI -e 'DBI->installed_versions;
    Perl : 5.014002 (x86_64-linux-gnu-thread-multi) OS : linux (3.2.0-37-generic) DBI : 1.625 DBD::Sponge : 12.010002 DBD::Proxy : install_driver(Proxy) failed: Can't locate RPC/PlC +lient.pm in @INC DBD::ODBC : 1.43 DBD::Gofer : 0.015326 DBD::File : 0.41 DBD::ExampleP : 12.014310 DBD::DBM : 0.08
  • odbcinst -j
    unixODBC 2.3.0 DRIVERS............: /usr/local/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources USER DATA SOURCES..: /home/madmole/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
  • odbcinst -q -s
    [tul1system] [ODBC Driver 11 for SQL Server]
  • cat /usr/local/etc/odbcinst.ini
    [ODBC Driver 11 for SQL Server] Description=Microsoft ODBC Driver 11 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 Threading=1 UsageCount=1 [tul1system] Description=Microsoft ODBC Driver 11 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 Threading=1 UsageCount=1
  • cat ~/.odbc.ini
    [tul1system] Description = Test Driver = /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0 +.so.2270.0 Trace = Yes TraceFile = /home/madmole/sql.log Database = Mosaic [ODBC Driver 11 for SQL Server] Description = Test Driver = /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0 +.so.2270.0 Trace = Yes TraceFile = /home/madmole/sql.log Database = Mosaic
  • Microsoft SQL Server ODBC driver for Linux v.1.0

Results

I have verified I can access the database with both the isql and sqlcmd utilities (double-checked with wireshark):

  • isql -v tul1system user334 2BEvPog
    '+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> help [...] SQLRowCount returns 0 888 rows fetched
  • sqlcmd -S tul1system -U user334 -P 2BEvPog
    select * from Award go [...]

However, when I try a simple Perl-DBI program, I get an error.

% cat simple.pl #!/usr/bin/perl use DBI; my $dsn="dbi:ODBC:driver={ODBC Driver 11 for SQL Server};server=tul1sy +stem;database=Mosaic"; # SOLVED: keyword "DRIVER=" needs to be all ca +ps my $dbh = DBI->connect($dsn, "user334", "2BEvPog"); if (! defined($dbh) ) { print "***Error connecting to DSN\n"; print "***Error was:\n"; print "***$DBI::errstr\n"; # $DBI::errstr is the error } % simple.pl DBI connect('driver={ODBC Driver 11 for SQL Server};server=tul1cspeldb +02;database=Mosaic','user334',...) failed: [unixODBC][Driver Manager] +Data source name not found, and no default driver specified (SQL-IM00 +2) at ./simple.pl line 6 ***Error connecting to DSN ***Error was: ***[unixODBC][Driver Manager]Data source name not found, and no defaul +t driver specified (SQL-IM002)

I have tried many variants of connection string, including the following (with various case variations):

my $dsn="dbi:ODBC:driver={tul1system};server=tul1system;database=Mosai +c"; my $dsn="dbi:ODBC:driver={tul1system};server=tul1system;database=Mosai +c;user=user334;pwd=2BEvPog"; my $dsn="dbi:ODBC:driver={tul1system};server=tul1system;database=Mosai +c"; my $dsn="dbi:ODBC:driver={tul1system};server=tul1system;database=Mosai +c;user=user334;pwd=2BEvPog"; my $dsn="dbi:ODBC:driver=tul1system;server=tul1system;database=Mosaic" +; my $dsn="dbi:ODBC:driver=tul1system;server=tul1system;database=Mosaic; +user=user334;pwd=2BEvPog"; my $dsn="dbi:ODBC:driver={ODBC Driver 11 for SQL Server};server=tul1sy +stem;database=Mosaic;user=iser334;pwd=2BEvPog";

In all cases, not only does the dbi_connect() fail, but Wireshark confirms there is no traffic on the wire. This is clearly something fundamental, but I've run out of ideas. How does one debug a failed DBI connect()?


In reply to fail: Linux Perl-DBI to SQL Server by madmole

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others drinking their drinks and smoking their pipes about the Monastery: (5)
    As of 2015-07-04 02:53 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









      Results (57 votes), past polls