Perl ODBC connection without password

I want my Perl script to use ODBC connection string to connect to DB. This works fine with code below. But I also want it to use username+password that I have entered into the ODBC connection. I don't want to supply these from within the script.

Anyone know how to achieve this?

use DBI;

my $strConn = "dbi:ODBC:MyDB";
my $username = "username";
my $password = "password";

# Does work
$dbh = DBI->connect( $strConn, $username, $password, { PrintError => 1, RaiseError => 1 } );

# Does not work
#$dbh = DBI->connect( $strConn, undef, undef, { PrintError => 1, RaiseError => 1 } );

if ($dbh)
{
   pri开发者_开发技巧nt "OK\n";
} else {
   print "FAIL\n";
}

Note: DB must have password set (blanking pwd not an option).

Operating system is Windows 2008 R2 (64-bit). The ODBC connection is defined in System DSN as 32-bit. Perl-version is 32-bit. Connection is done to MSSQL 2008 R2.

Assuming you are using Windows you could use the Win32::ODBC module.

use Win32::ODBC;

my $dbh = new Win32::ODBC("odbc_connection_name");    

if ($dbh)
{
   print "OK\n";
} else {
   print "FAIL\n";
}

You need to realise that for historical reasons DBD::ODBC tries to call SQLConnect before SQLDriverConnect if the connection string does NOT include DSN=. The ODBC call SQLConnect takes 3 arguments dsn name, username and password. It is not clear from the ODBC documentation if passing NULL (in C) for the username and password implies they are not to be used or not. However, if you specify your DBI connect first argument as dbi:ODBC:DSN=mydsn and omit the username/password arguments to connect (or pass them as undef) DBD::ODBC calls SQLDriverConnect and the ODBC driver can find other DSN details from your DSN. However, I've not tried this and I'm slightly concerned the call to SQLDriverConnect in DBD::ODBC passes SQL_DRIVER_NOPROMPT as the only argument and does not include SQL_DRIVER_COMPLETE. If you try the above and it does not work try changing the call to SQLDriverConnect to be SQL_DRIVER_NOPROMPT|SQL_DRIVER_COMPLETE and recompile DBD::ODBC. If this them works RT it on rt.cpan.org or answer here and I'll look at changing it.

UPDATE 4-Nov-11 I added an odbc_driver_complete attribute to DBD::ODBC in 1.32_2 dev release. I'm not suggesting it fixes any issue the OP had but it adds new functionality.

How would you prefer the username and password be passed in?

Here's a command-line version.

use 5.010;
use strict;
use warnings;
use Getopt::Long qw<GetOptions>;

GetOptions( \my %options, qw<user|u password|pwd|p> );
# Non-option arguments will be left on @ARGV
# this script also accepts script.pl [USER] [PASSWORD]
usage( 'User not set!' )     unless ( $options{user} //= shift );
usage( 'Password not set!' ) unless ( $options{password} //= shift );

$dbh 
    = DBI->connect( 
      $strConn
    , @options{ qw<user password> }
    , { PrintError => 1, RaiseError => 1 } 
    );

Or

$dbh
    = DBI->connect( join( 
      ';'
    , 'DBI:ODBC:driver={SQL Server}'
    , "Server=$SQL_SERVER"
    , "Database=$SQL_DATABASE"
    , "UID=$option{user}"
    , "PWD=$option{password}"
    ));

Try it with just the first argument and no 'undef, undef' for the user/passwd. The DBD::ODBC FAQ seems to indicate that you don't need the other two arguments.