Perl DBI Connection Inconsistencies

Question

Background

I am working on a project than involves the retrieval of data from two difference databases. One of the databases is using a Microsoft SQL database engine and the other is running a MySQL engine. I need an easy way to specify the Data Source Name (DSN) from a configuration perspective, but due to inconsistencies in the DSN naming conventions, this is not possible with theDBI module (from what I have experienced).

MySQL

Consider the following connection:

my $dsn = "dbi:mysql:host=$host;database=$db_name";
my $dbh = DBI->connect($dsn, $user, $pass);

Assuming the supplied database name exists at the host, this connection will be succesful. I have tested this many times. Feel free to verify this yourself.

---

MS SQL

Now I try to connect to a Microsoft SQL server using the same DSN connection string format, with the exception of the database driver type.

my $dsn = "dbi:odbc:host=$host;database=$db_name";
my $dbh = DBI->connect($dsn, $user, $pass);

Even if the database exists on the supplied host, this connection fails, and the error message is like that shown below:

DBI connect('host=$host;database=$db_name','$user',...) failed: (mtodbc): Fetching info: [unixODBC][Driver Manager]Connnection does not exist (SQLSTATE:08003) (CODE:0) (SEVERITY:SQLException) DBD: [dbd_db_login6/checkOutConnectionW(login)] RetCode=[-1] at perl_script.pl line X

---

The DBI module is a database independent interface for Perl, yet clearly this problem is database dependent.. This seems like a bad design decision. Am I missing something? If so, please provide some reasoning why this design was done in this way.

Answer 1

In windows you can use:

DBI->connect('dbi:ODBC:driver={SQL Server};database=catalog;Server=server\\instance;',$user,$password);

Where:

• server is the ip addres of mssql server
• instance is the instance name
• catalog is the database name

In linux/unix I suggest freetds

From DBI documentation:

Connect

$dbh = DBI->connect($data_source, $username, $password)
          or die $DBI::errstr;
$dbh = DBI->connect($data_source, $username, $password, \%attr)
          or die $DBI::errstr;

Examples of $data_source values are:

dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port

There is no standard for the text following the driver name. Each driver is free to use whatever syntax it wants. The only requirement the DBI makes is that all the information is supplied in a single string. You must consult the documentation for the drivers you are using for a description of the syntax they require.

It is recommended that drivers support the ODBC style, shown in the last example above. It is also recommended that they support the three common names 'host', 'port', and 'database' (plus 'db' as an alias for database). This simplifies automatic construction of basic DSNs: "dbi:$driver:database=$db;host=$host;port=$port". Drivers should aim to 'do something reasonable' when given a DSN in this form, but if any part is meaningless for that driver (such as 'port' for Informix) it should generate an error if that part is not empty.

Answer 2

The parameters required by drivers vary by driver. The example in the DBD::ODBC documentation is

DBI->connect('dbi:ODBC:DSN=mydsn', $user, $pass)

Based on links posted in the comment, it appears possible to shorten the above to

DBI->connect('dbi:ODBC:mydsn', $user, $pass)

There's no information on the possibility of other parameters being accepted (e.g. a means of specifying a file DSN or of inlining a DSN).