DBI connection inconsistencies
Background
I am working on a project and not fetching data from two delta databases. One of the databases uses the Microsoft SQL database engine and the other uses the MySQL engine. I need an easy way to specify a data source name (DSN) in terms of configuration, but due to inconsistencies in DSN naming conventions, this is not possible with a module DBI
(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 on the host, this connection will succeed. I've tested this many times. Feel free to check it out for yourself.
MS SQL
Now I am trying to connect to a Microsoft SQL Server using the same DSN connection string format except for the type of database driver.
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 similar to what is 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
, but it is clear that this problem is database dependent. This seems like a bad design decision. Am I missing something? If so, please provide some reasons why this project was done this way.
source to share
On windows, you can use:
DBI->connect('dbi:ODBC:driver={SQL Server};database=catalog;Server=server\\instance;',$user,$password);
Where:
- server is ip addres of the mssql server.
- instance is the name of the instance
- directory is the name of the database
On linux / unix, I suggest freetds
From DBI Documentation :
$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:
dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port
There is no standard for text following the driver name. each driver can use any syntax you want . The DBI's only requirement is that all information is provided on a single line. You should consult the documentation for the drivers you are using to describe the required syntax.
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 the database). This makes it easier to automatically build basic DSNs: "DBI: $ Database Driver = $ db; host = $ host, port = $ port" . Drivers should "do something reasonable" when submitting a DSN in this form, but if there is a part it doesn't make sense for this driver (eg "port" for Informix). should generate an error if this part is not empty.
source to share
The options required by drivers depend on the driver. An example in the DBD :: ODBC documentation:
DBI->connect('dbi:ODBC:DSN=mydsn', $user, $pass)
Based on the links posted in the comment, it seems possible to shorten the above value to
DBI->connect('dbi:ODBC:mydsn', $user, $pass)
There is no information about the possibility of accepting other parameters (for example, a means of specifying a DSN file or a DSN attachment).
source to share