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

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

I have been converting a perl cgi script from mySQL use to MS SQL. In doing so, I have had problems just connecting to MS SQL, so I tested code out at the command prompt. Server is Win 2003 x64 and using Active Perl. I have configured the ODBC manager in both 32 and 64 bit incarnations. The confusion is with the ODBC driver name. The code below is what I use to initiate a connection.

#!c:/perl/bin/perl # DBI ODBC Test BEGIN { push @INC, '/gfs/www/cgi-bin/pers/karlk'; } # push @INC, '..'; use Time::localtime; use DBI; use strict; my $db_name; my $db_type = 'MSSQL'; my $db_access_path; my $dbh; my $db_err = ''; my $db_errstr = ''; my @dns; my $d; my %sql_srvr_type = ('mySQL' => 'mysql', 'MSSQL' => 'ODBC:DRIVER={MS SQL 2005}'); # SQL S +erver my %sql_srvr_name = ('mySQL' => 'localhost', 'MSSQL' =>'localhost'); # + \\SQLEXPRESS my %sql_srvr_port = ('mySQL' => '3306', 'MSSQL' => '1433'); my %sql_user = ('mySQL' => 'xxx', 'MSSQL' => 'yyy'); my %sql_pwd = ('mySQL' => 'xxx', 'MSSQL' => 'yyy'); my $db_name = 'online_labs_qa'; # dbi:DriverName:database=database_name;host=hostname;port=port $db_access_path = "DBI:$sql_srvr_type{$db_type};server=$sql_srvr_name{ +$db_type};database=$db_name;UID=$sql_user{$db_type};PWD=$sql_pwd{$db_ +type}"; print "DBI Connect: $db_access_path\n"; my @dsns = DBI->data_sources('ODBC'); foreach my $d (@dsns) { print "Driver = $d\n"; } # connect print "Get DBI handle\n"; $dbh = DBI->connect($db_access_path) || die "Could not connect to data +base: $DBI::errstr"; if (!defined($dbh)) { # connect error $db_err = $dbh->err; $db_errstr = $dbh->errstr; print "Connect Error: $db_err, $db_errstr\n"; } else { print "Handle = $dbh\n"; }

It prints out the connection string, what the available ODBC drivers are, and the handle if it connects. The confusion is with the ODBC driver name. If I use "{SQL Server}", it connects properly but that is not the ODBC System DSN I setup. That was "{MS SQL 2005}", and that shows up as one of the 2 ODBC drivers. When I use "{MS SQL 2005}", I get the following output.

DBI Connect: DBI:ODBC:DRIVER={MS SQL 2005};server=localhost;database= +online_labs_qa;UID=xxx;PWD=yyy Driver = DBI:ODBC:MySQL Driver = DBI:ODBC:MS SQL 2005 Get DBI handle DBI connect('DRIVER={MS SQL 2005};server=localhost;database=online_lab +s_qa;UID=xxx;PWD=yyy','',...) failed: [Microsoft][ODBC Driver Manager +] Data source name not found and no default driver specified (SQL-IM0 +02)(DBD: db_login/SQLConnect err=-1) at odbc_test.pl line 49 Could not connect to database: [Microsoft][ODBC Driver Manager] Data s +ource name not found and no default driver specified (SQL-IM002)(DBD: + db_login/SQLConnecterr=-1) at odbc_test.pl line 49.

So what's up? Why does one work and the other not? I have another issue in getting this to work via IIS6 in CGI, but that's another post. Any insight is greatly appreciated!

Replies are listed 'Best First'.
Re: Confused by DBI:ODBC Driver name
by poj (Abbot) on Dec 23, 2011 at 22:00 UTC

    Try using a different string for the stored connection something like this

    use strict; use DBI; print "Driver = $_\n" for DBI->data_sources('ODBC'); my $db_name = 'online_labs_gs'; my $db_type = 'MSSQL'; my %SERVER = ( MSSQL => { 'dsn' => 'DBI:ODBC:MS SQL 2005', 'user' => 'yyy', 'pwd' => 'yyy', }, MSSQL1 => { 'dsn' => "DBI:ODBC:driver={SQL Server};server=localhost;dbq=$db_n +ame", 'user' => 'yyy', 'pwd' => 'yyy', }, MySQL => { 'dsn' => "dbi:mysql:$db_name:localhost:3306", 'user' => 'xxx', 'pwd' => 'xxx', }, ); my $hr = $SERVER{$db_type}; my $dbh = DBI->connect($hr->{'dsn'},$hr->{'user'},$hr->{'pwd'}, {RaiseError => 1});
    poj

      Have you tested your DSN? Does it connect to the database?

        Yes I have. Using {SQL Server}, I can connect to the database and access things. I just wonder if the SQL Server driver manages to somehow make a connection by some knowledge it has of the system. So far, things work just fine using it vs. what I expect to use as the DSN on a local machine. It might be different connecting to remote server, but I haven't done that yet.

Re: Confused by DBI:ODBC Driver name
by nimdokk (Vicar) on Dec 23, 2011 at 20:47 UTC
    I'm not sure if this would address your issue, but you might try use DBD::ODBC and then in your connection string: driver={SQL Server} instead of DRIVER={MS SQL 2005}. I'm sure there's more to it than that. Perhaps some other monks who do more database programming have better ideas. Just my 2 ¢
Re: Confused by DBI:ODBC Driver name
by mje (Curate) on Jan 05, 2012 at 11:39 UTC

    At first I thought that 'DBI:ODBC:xxxx' should be 'dbi:ODBC:xxx' but it appears after all these years of using DBI and writing DBDs I never knew "DBI" was valid.

    I think you are confusing driver names and DSNs (Data Source Names). DBI's datasources method returns a list of strings which may be passed to DBI's connect method to connect to those data sources and each one looks like:

    dbi:ODBC:my_dsn

    where "my_dsn" is a DSN (to be honest, the above is more accurately "dbi:ODBC:DSN=mydsn" but the reasons are too complicated to explain here but are due to maintaining backwards compatibility). The DSN is the name you assign to a data source you create in the ODBC Administrator. It is a way to collect all the attributes required for the connection. The DSN data will name the ODBC driver used to connect to the database.

    When you use "DRIVER={mydriver}" you are using a so called "DSNless" connection string. You are telling the ODBC Driver manager you want to use the ODBC driver called "mydriver" and it will go away, load that ODBC driver then pass the connection string to it. For DSNless connection strings you have to pass other attributes the ODBC Driver needs to tell it how to connect to the database. When you use DSN=xxx the ODBC driver manager looks up the DSN xxx, finds the name of the driver to load, loads it and passes the connection string to it. The driver looks up the other attributes it needs to connect from the DSN. The connection using DSNs thus allows you to use a much shorter connection string and also allows you to change what the DSN points to without changing your code.

    So to your precise problem. The DSNs you set up are called "MySQL" and "MS SQL 2005" and to connect to them you should use "dbi:ODBC:DSN=MS SQL 2005". These are data sources you should be able to see in the ODBC administrator and I presume the one called "MS SQL 2005" specifically says it should be using the MS SQL Server ODBC Driver. When you use "dbi:ODBC:DRIVER={xxx};attrbute2=val;attribute3=val;" the xxx needs to be the name of the ODBC Driver NOT a DSN. You can find the valid names for the ODBC Drivers you have installed by going into the ODBC Administrator, clicking on the Drivers tab and looking at the Name column.

    Read the DBD::ODBC FAQ for a whole load more information on connection strings.

      At first I thought that 'DBI:ODBC:xxxx' should be 'dbi:ODBC:xxx' but it appears after all these years of using DBI and writing DBDs I never knew "DBI" was valid.

      I think upper case DBI is tolerated, but not necessarily valid. The DBI docs state:

      The $data_source value must begin with "dbi:driver_name:". The driver_name specifies the driver that will be used to make the connection. (Letter case is significant.)

      The wording is a little bit problematic here: Is letter case significant only for the driver name or for the entire data source?

      At least once in parse_dsn, the DBI docs use a data source string starting with upper case "DBI":

      ($scheme, $driver, $attr_string, $attr_hash, $driver_dsn) = DBI->parse +_dsn("DBI:MyDriver(RaiseError=>1):db=test;port=42"); $scheme = 'dbi'; $driver = 'MyDriver'; $attr_string = 'RaiseError=>1'; $attr_hash = { 'RaiseError' => '1' }; $driver_dsn = 'db=test;port=42';

      Looking at the result of the example, at least parse_dsn() should ignore case for the scheme part of the data source.

      It does so at least on Windows (DBI 1.607) and on Linux (DBI 1.6.16):

      C:\>perl -MDBI -MData::Dumper -e "print qq[$DBI::VERSION\n],Dumper([DB +I->parse_dsn('DBI:foo:bar')])" 1.607 $VAR1 = [ 'dbi', 'foo', undef, undef, 'bar' ]; C:\>perl -v This is perl, v5.10.0 built for MSWin32-x86-multi-thread Copyright 1987-2007, Larry Wall Perl may be copied only under the terms of either the Artistic License + or the GNU General Public License, which may be found in the Perl 5 source ki +t. Complete documentation for Perl, including FAQ lists, should be found +on this system using "man perl" or "perldoc perl". If you have access to + the Internet, point your browser at http://www.perl.org/, the Perl Home Pa +ge. C:\>
      > perl -MDBI -MData::Dumper -e 'print "$DBI::VERSION\n",Dumper([DBI->p +arse_dsn("DBI:foo:bar")])' 1.616 $VAR1 = [ 'dbi', 'foo', undef, undef, 'bar' ]; > perl -v This is perl 5, version 12, subversion 3 (v5.12.3) built for x86_64-li +nux-thread-multi Copyright 1987-2010, Larry Wall Perl may be copied only under the terms of either the Artistic License + or the GNU General Public License, which may be found in the Perl 5 source ki +t. Complete documentation for Perl, including FAQ lists, should be found +on this system using "man perl" or "perldoc perl". If you have access to + the Internet, point your browser at http://www.perl.org/, the Perl Home Pa +ge. >

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)