http://www.perlmonks.org?node_id=1033737

madmole has asked for the wisdom of the Perl Monks concerning the following question:

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

Results

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

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)

[download]

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";

[download]

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()?

Replies are listed 'Best First'.
Re: fail: Linux Perl-DBI to SQL Server
by mje (Curate) on May 16, 2013 at 08:24 UTC

    I see you solved this for yourself but just a few notes which might help clarify things for any one else reading this.

    The odbcinst.ini and odbc.ini are just mirror images of the system used on Windows where keys in the registry have those names. The odbcinst key/file defines your drivers and the odbc key/file defines your data sources (note you don't NEED the latter as there are DSN-less connections).

    These keys/files are related so when you create/install a driver called "tul1system" in the odbcinst file and create a DSN in the odbc file the Driver keyword in the DSN should point to the name of the driver in the odbcinst file (the bit in []) and be "tul1system" not "/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0" as you had although because of a quirk in the way unixODBC works the latter can still work.

    Your odbc.ini file was incorrect as it should start with a DSN name in []. So it should have been:

    [mydsnname]
Description         = Test
Driver              = tullsystem
Trace               = Yes
TraceFile           = /home/madmole/sql.log
Database            = Mosaic

    [download]

    whereas you created a DSN called "ODBC Driver 11 for SQL Server" and had some other keywords/attributes in there that were not associated with any DSN.

    It makes no sense from what you posted for the isql command to work as you attempted to connect to the DSN call tul1system and you didn't appear to have one (unless it was defined in the system DSN file /usr/local/etc/odbc.ini or you just omitted "tul1system" from the start of your catted odbc.ini file when you pasted it).

    When you attempted to connect in Perl with "dbi:ODBC:driver={ODBC Driver 11 for SQL Server};server=tul1system;database=Mosaic" what happens is DBI uses the 'dbi:ODBC' part to identify and load the DBD::ODBC driver and DBD::ODBC calls SQLDriverConnect with "driver={ODBC Driver 11 for SQL Server};server=tul1system;database=Mosaic". unixODBC parses that string to identify the driver (by the way I think you need to use DRIVER uppercase not lowercase), looks in the odbcinst.ini file for that driver, finds the shared object under the driver attribute, loads it and calls SQLDriverConnect in the driver shared object.

    The so called curly brackets notation you refer to has nothing to do with DSN-less connections. ODBC connection strings have attributes separated by semicolons and the {} are a quoting system telling the driver manager etc to ignore special characters until the trailing } is seen. People conventionally do DRIVER={some driver} because it has spaces in but I don't believe you need to.

    A DSN-less connection in Perl looks like "dbi:ODBC:DRIVER={some driver};attr1=value1;attr2=value2" where attrN are the attributes the driver defines and the same names as those you use in a DSN.

    One last thing you should be aware of is some legacy support in DBD::ODBC for ODBC 2. Before ODBC 3 came along there was only an ODBC API SQLConnect which took 3 arguments - DSN name, username and password. The DSN name was limited to 32 characters. When ODBC 3 came along they added SQLDriverConnect which took a connection string which was not limited in size and could hold all sorts of attributes e.g., UID and PWD are the attributes for your username and password. Because loads of people had Perl code used to ODBC 2 DBD::ODBC calls SQLDriverConnect and if that fails SQLConnect. This matters since if you have a DSN called mydsn and attempt to connect with 'dbi:ODBC:mydsn' DBD::ODBC first calls SQLDriverConnect which will fail and then calls SQLConnect which will succeed and you wasted some time. If you'd used 'dbi:ODBC:DSN=mydsn' the first SQLDriverConnect would have worked. There are other good reasons for doing this - see ODBC - The Connection Process and look for Perl DBD::ODBC.

    You may also find What is DBD::ODBC? and Linux/UNIX ODBC useful reading.
[reply]
[d/l]

      Thanks for the informative post. One comment: your speculation a paste error causing the missing first line of the catted .odbc.ini file was correct (I've updated the original post to add it). This is why the isql command succeeded.

      Furthermore, the original problem using the curly braces as quoting syntax was (again as you point out) the "DRIVER" keyword needs to be capitalized (one of the case variations I did not try). When I fixed that the original version of simple.pl started working.

[reply]
Re: fail: Linux Perl-DBI to SQL Server
by pvaldes (Chaplain) on May 15, 2013 at 22:26 UTC

    It seems that you have two problems here, the program can not find neither the database nor the driver...

    1 - I could be wrong (I'm not familiar with the MicrosoftSQL DB) but I think that DBI use dbname=$mydatabase instead to database=$mydatabase, please check this.

    2 - I find also strange the structure of the driver part. What is the purpose of the "{}"?. (UPDATED, you want to say: q{}?)

    Check the name of your driver with something like:

    perl -MDBI -e 'my @drivers = DBI->available_drivers; print join(", ", @drivers), "\n";'

    (UPDATED: mmmh, the name is "Microsoft ODBC Driver 11 for SQL Server" or "ODBC Driver 11 for SQL Server"?)

[reply]
[d/l]

      Solved

      I have figured out what is going on and succeeded in connecting. The root of the problem is understanding the concept of a DSN. The basic syntax is:


      dbi:ODBC:dsn_name

      where dsn_name is a string that is used to index into a section header of one of the .ini files (e.g. odbcinst.ini).

      The problem is that there is an alternate syntax known as DSN-less connection strings (curly brackets) which essentially contains connection information inline. For some reason I never got this to work correctly, I believe because the DSN-less syntax never connected to the location of the driver .so file. When I refashioned my example to use DSN syntax the example succeeded. My updated code fragment looks like this:

      % cat simple.pl
#!/usr/bin/perl
use DBI;
my $dsn="dbi:ODBC:DSN=tul1system";
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
}

      [download]

      Note that the other information like database name and IP address now come from the .ini file.

[reply]
[d/l]

Back to Seekers of Perl Wisdom