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.

In reply to Re: Confused by DBI:ODBC Driver name by mje
in thread Confused by DBI:ODBC Driver name by karlk123

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!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • 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, details, 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, summary, 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.