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).
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.
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.
In windows you can use:
DBI->connect('dbi:ODBC:driver={SQL Server};database=catalog;Server=server\\instance;',$user,$password);
Where:
In 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 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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments