Joseph Lasso Joseph Lasso - 1 year ago 52
Perl Question

How can I specify the port number with DBD::ODBC?

I currently use the following code to connect to a database in my Perl script:

my $dsn = 'dbi:ODBC:MYDATABASE';
my $database = 'uat_env';
my $user = 'user';
my $auth = 'password';

my $dbh = DBI->connect($dsn, $user, $auth, {
RaiseError => 1,
AutoCommit => 1
}) or die("Couldn't connect to database");

$dbh->do('use '.$database);

Now the port has changed from 1433 to 40450.

I am having trouble changing the port in the DSN. I thought this change would work but I am receiving a "DSN not found" error:

my $dsn = 'dbi:ODBC:MYDATABASE;Port=40450';

Any idea why this isn't working?

Answer Source

There are two formats for a DBI data source string for ODBC. You can say either


or you can abbreviate that to


which is what you have. If you use just the DSN then you can't add any more parameters, so your dbi:ODBC:MYDATABASE;Port=40450 is looking for DSN MYDATABASE;Port=40450 which clearly doesn't exist

The proper way to do this is to set up a new DSN which has a copy of all the parameters of MYDATABASE, but with a different port name

At a guess, I would say you may be able to write


but I can't be sure and I have no way of testing

If your requirements are simple then you can supply all of the parameters instead of a DSN, like this

dbi:ODBC:Driver={SQL Server};Server=;Port=40450

but you will have to supply the correct driver if you aren't using a SQL Server ODBC connection, and other parameters may be necessary

You should start by examining the values in the MYDATABASE DSN and go from there