in reply to Re: dbi:ODBC change Port
in thread dbi:ODBC change Port

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]

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

    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]

      This is a good suggestion and makes the call more readable. I implemented it. However, it does not solve the issue. Calling:

      my $dsn = "dbi:ODBC:Driver={$ODBCdriver};Server=$SQLserver,$SQLport;Da
+tabase=$SQLdatabase;UID=$SQLuser;PWD=$SQLpassword; ...";

      [download]

      Note that I moved ',$SQLport' to Server. This syntax never connects. This is probably because it is not the syntax expected by Perl DBI (all examples on Perlmonks too also use '; Port=1433' which instead always connects no matter the port I use -> this indicates - I guess - that the parameter is not used. To be honest, I have no ideas.

[reply]
[d/l]

        The Perl DBI does not care about the contents of (the parameters part of) the DSN. This is between you and the ODBC driver, which is why I linked to https://connectionstrings.com.

        As none of the two approaches seem to work, I suggest you look closer at the ODBC driver and the version you have, and then find out what the proper connection string is.

[reply]

        are the three dots part of your dsn?

[reply]
Re^3: dbi:ODBC change Port
by bliako (Abbot) on Nov 10, 2022 at 09:10 UTC

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

[reply]
[d/l]

In Section Seekers of Perl Wisdom