3

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.

Community
  • 1
  • 1
Jonathan
  • 671
  • 1
  • 8
  • 21
  • 1
    What driver is mtodbc ? Are you shure it's correct? – frhack Jun 25 '15 at 19:15
  • 2
    When changing drivers, keeping the same connection string and hoping for the best seldom works out well. For DSN connections see: http://www.connectionstrings.com/dsn/ . – tjd Jun 25 '15 at 19:26
  • @frhack I apologize. That was a typo. I meant the ODBC driver of course.. – Jonathan Jun 25 '15 at 19:27
  • In Linux I succesfully used freetds connection http://www.freetds.org/userguide/perl.htm – frhack Jun 25 '15 at 19:31

2 Answers2

4

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.

frhack
  • 4,862
  • 2
  • 28
  • 25
  • This is great information. I did not see this when reading the documentation earlier. This leaves more room for discussion, however. Unfortunately, because subclasses of DBD are not enforced to specific DSN string guidelines, this leaves room for interface differences across different database engines. This is a bad design, IMO... – Jonathan Jun 25 '15 at 21:24
  • 1
    @Jonathan There is a huge variety of drivers for DBI, including drivers for things that aren't even RDBMS's, like DBD::CSV, DBD::DBM, and DBD::WMI. You don't "connect" to these the same way you connect to a typical RDBMS, and even RDBMS's vary (some use a connection identifier, some require a database name, some even allow you to specify a separate credentials file). This is very difficult to standardize. – ThisSuitIsBlackNot Jun 25 '15 at 21:47
  • @ThisSuitIsBlackNot I understand the difficulty in providing an interface to many systems. However, from a top level module that provides a "database independent interface" these connection strings should not have to be bound to the implementation details of the underlying database engines. I see your point though.. – Jonathan Jun 25 '15 at 21:56
  • 2
    @Jonathan It's not bad design. It's not possible to standardize all db connections string. In java jdbc (the java equivalent of perl DBI) there's the same 'problem'. http://www.roseindia.net/tutorial/java/jdbc/databaseurl.html – frhack Jun 25 '15 at 21:58
  • 1
    @frhack Sounds good. Thanks for all of the information. I really appreciate it. It's been a great discussion. – Jonathan Jun 25 '15 at 22:05
1

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

ikegami
  • 367,544
  • 15
  • 269
  • 518
  • Right. I understand this.. However, this isn't consistent with a database independent interface. As a user, I would expect to supply a generic DSN to DBI and have it map that DSN to the appropriate driver parameters required by the back end database driver. Yeah? – Jonathan Jun 25 '15 at 19:34
  • Why do you expect to provide the same connection string for all databases? Why do you think providing a connection string breaks portability? – ikegami Jun 25 '15 at 19:41
  • 1
    The interface (DBI) is for query and statements execution, not for connection string / connection properties that necessarily remain db dependent – frhack Jun 25 '15 at 19:44
  • @ikegami I don't. I would expect the DSN connection string driver type to change. As a user, I would expect to be able to pass a single set of parameters and have the module itself take care of the conversion of those parameters to the driver specific connection strings. For example, I pass `host=localhost` and that would get converted to `SERVER=localhost` for an MS SQL connection. Thus decoupling the driver specific DSN string format from the user. – Jonathan Jun 25 '15 at 19:47
  • But ODBC takes a *DSN name*. It's a not a database or host or anything else MySQL has. What's wrong with using a useful name for it?! Using the wrong name won't help decouple anything. – ikegami Jun 25 '15 at 19:53
  • @ikegami I'm talking about the DBI specified DSN (i.e. the first parameter passed in to DBI->connect). I understand MySQL doesn't have a DSN parameter, but the DBI DSN that is supplied to connect to a database is not consistent across all databases, as I demonstrated in the example above. – Jonathan Jun 25 '15 at 19:54
  • Again, using the wrong name won't help decouple anything. – ikegami Jun 25 '15 at 19:56
  • FWIW: On Windows machines logged into the same AD domain as a MS SQL server I've been able to use a format like `Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;` with `DBD::ODBC`. Nice advantage is that I don't need to embed any passwords.... – tjd Jun 25 '15 at 20:14
  • 3
    From [perldoc DBI](https://metacpan.org/pod/DBI#connect): 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. – dpw Jun 25 '15 at 20:14
  • @dpw Thanks for that information. I didn't see that in the documentation previously. I overlooked it. As per the documentation: "It recommended that they (DBD drivers) support the three common names `host`, `port`, and `database` " Unfortunately, subclasses of DBD are not enforced to maintain a consistent DSN naming strategy. – Jonathan Jun 25 '15 at 21:17