Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am playing with dbi:ODBC and the Port parameter. How is it possible that if I change Port to any number, the connection never fails?

my $ODBCdriver = 'SQL Server';
my $SQLserver = 'FC4FA9\SQLEXPRESS';
my $SQLdatabase = 'test';
my $SQLuser = 'username';
my $SQLpassword = 'password';
my $SQLport = '12345';

$dbh = DBI->connect("dbi:ODBC:Driver={$ODBCdriver};Server=$SQLserver;D
+atabase=$SQLdatabase;UID=$SQLuser;PWD=$SQLpassword;Port=$SQLport", {P
+rintError => 0, RaiseError => 1, AutoCommit => $AutoCommit, FetchHash
+KeyName => 'NAME_lc'}) or die "Can't connect to the database: $DBI::e
+rrstr\n";

[download]

The database is listening on 1433 (Windows)

Replies are listed 'Best First'.
Re: dbi:ODBC change Port
by Corion (Patriarch) on Nov 10, 2022 at 07:52 UTC

    If changing the port never makes the connection fail, are you sure that the Port parameter is supported by the ODBC driver?

    connectionstrings.com shows a different syntax for using a non-standard port, namely Server=host,port. So in your case, that could be:

    ...;Server=$SQLserver, $SQLport;...

    [download]
[reply]
[d/l]
[select]

      This is a good point and the thing I am trying to find out.

      The syntax of DBI shows that my syntax is correct.

      If I try the one you points out, the connection always fails with (tried it with and without empty space after comma):

      DBI connect('Driver={SQL Server};Server=RC4FA9\SQLEXPRESS,1433;Databas
+e=test;UID=;PWD=','HASH(0xc2d3c50)',...) failed: [Microsoft][ODBC SQL
+ Server Driver][DBMSLPCN]SQL Server does not exist or access denied. 
+(SQL-08001) [state was 08001 now 01000]
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()
+). (SQL-01000) at ....

      [download]
[reply]
[d/l]

        The second parameter to DBI->connect is the username and the third parameter is the password. You are passing in the options hash:

        DBI->connect(
    "dbi:ODBC:Driver={$ODBCdriver};Server=$SQLserver;Database=$SQLdata
+base;UID=$SQLuser;PWD=$SQLpassword;Port=$SQLport",
    { PrintError => 0,
      RaiseError => 1,
      AutoCommit => $AutoCommit,
      FetchHashKeyName => 'NAME_lc'
    },
);

        [download]

        I would split that up, and construct the values separately to keep things somewhat understandable:

        my $dsn = "dbi:ODBC:Driver={$ODBCdriver};Server=$SQLserver;Database=$S
+QLdatabase,$SQLport;UID=$SQLuser;PWD=$SQLpassword; ...";
my $dbi_options =     { PrintError => 0,
      RaiseError => 1,
      AutoCommit => $AutoCommit,
      FetchHashKeyName => 'NAME_lc'
    };
my $dbh = DBI->connect( $dsn, undef, undef, $dbi_options );

        [download]
[reply]
[d/l]
[select]

        that 'HASH(0xc2d3c50)' does not look good to me, perhaps there is a problem constructing your dsn?

[reply]
[d/l]
Re: dbi:ODBC change Port
by Anonymous Monk on Nov 10, 2022 at 12:20 UTC

    This is a minimal working example

    #!/usr/bin/perl -w
use strict;
use DBI;

my $ODBCdriver = 'SQL Server';
my $SQLserver  = 'RC4FA9\SQLEXPRESS';
my $SQLport    = '4433';
my $SQLdatabase= 'test';
my $SQLuser    = '';
my $SQLpassword= '';

my @drivers = DBI->available_drivers;
print join(", ", @drivers), "\n";

my $d = join(", ", @drivers);
print "DBD::ODBC ";
print "not" if ($d !~ /ODBC/);
print "installed\n";

print "Connecting...\n";
my $dbh = DBI->connect("dbi:ODBC:Driver={$ODBCdriver};Server=$SQLserve
+r; Port=$SQLport;Database=$SQLdatabase;UID=$SQLuser;PWD=$SQLpassword"
+, {PrintError => 0, RaiseError => 1, FetchHashKeyName => 'NAME_lc'}) 
+or die "Can't connect to the database: $DBI::errstr\n";
print "Connected...\n";

print "Disconnecting...\n";
$dbh->disconnect if ($dbh);
print "Disconnected...\n";

    [download]

    As explained, $SQLserver does not affect the connection which always works fine. Other syntax 'Server=$SQLserver, $SQLport' does not work (but no error messages).

[reply]
[d/l]

      This does not do what you think:

      DBI->connect("dbi:ODBC:Driver={$ODBCdriver};Server=$SQLserver; Port=$S
+QLport;Database=$SQLdatabase;UID=$SQLuser;PWD=$SQLpassword",
     {PrintError => 0, RaiseError => 1, FetchHashKeyName => 'NAME_lc'}
)

      [download]

      The second argument to ->connect must be undef or the database user, and the third argument must be the password. The fourth argument can optionally be the connection options, given as a hash.

      Your code "works" because you specify user and password in the DSN and that seems to override the arguments given to ->connect.

      I would recommend you change your code to the four-argument version.</>

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom