Re: Confused by DBI:ODBC Driver name

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.

Comment on Re: Confused by DBI:ODBC Driver name

Replies are listed 'Best First'.

Re^2: Confused by DBI:ODBC Driver name
by afoken (Chancellor) on Jan 05, 2012 at 13:28 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 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';
[download]

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:\>
[download]

> 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.

>
[download]

Alexander

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

[reply]
[d/l]
[select]


The stupid question is the question not asked
	PerlMonks