0

Writing a script to get SQL Server instance names from a table, then attempting to connect to each of these instances to pull back database configuration information. All database instances involved are some version of SQL Server. If the connection fails (due to a bad password, instance is down, etc.) the intention is to print a user-defined error message ("Unable to connect to $inst, skipping.") and continue through the list. I'm having trouble suppressing the default error message from ODBC (SQL Server Native Client 10.0).

Connection is attempted like this:

eval {
  my $dbh = DBI->connect(
    "dbi:ODBC:Driver={SQL Server Native Client 10.0};Server=<instance_name>;Uid=<user_name>;Pwd=<password>;",
    { PrintError => 0, RaiseError => 1, AutoCommit => 1 }
  );
};

It is my (probably incorrect) understanding that PrintError => 0 should suppress the error message and RaiseError => 1 will cause DBI to die if the connect method fails, at which point I can check $@ for the error and print a user-defined message. I have also looked at the HandleError attribute but have not had any success.

Is this a completely unrealistic scenario, or is this a result of the ODBC driver I'm working with?

Per bohica's suggestions, working code looks like:

eval {
  my $dbh = DBI->connect(
    "dbi:ODBC:Driver={SQL Server Native Client 10.0};Server=<instance_name>;",
    "Username",
    "Password",
    { PrintError => 0, RaiseError => 1, AutoCommit => 1 }
  );
};

Username and password were moved out of connection string and passed as separate parameters to DBI connect method.

Bryan
  • 17,112
  • 7
  • 57
  • 80
  • 1
    You are missing username/password arguments in your example so your hashref of DBI attributes will be seen as the username. Just because you put UID/PWD in the connection string does not mean you can omit DBI's 2nd and 3rd argument. – bohica Mar 04 '11 at 10:14
  • Making this change produced expected results, the error message is suppressed. – Bryan Mar 04 '11 at 17:01

2 Answers2

3

Assuming you fix the problem Pedro mentions then, PrintError=>0 suppresses errors and you might want to look at PrintWarn as well. The RaiseError=>1 will cause the connect to die if the connect fails and in your example the error will be in $@.

bohica
  • 5,932
  • 3
  • 23
  • 28
  • Thanks, PrintError is set to 0. Also set PrintWarn to 0 and no change. RaiseError is set to 1, but when the connect fails and I check $@ outside of the eval block, it is undefined. – Bryan Nov 19 '10 at 15:32
  • perl -le 'use DBI; my $dbh; eval {$dbh = DBI->connect("dbi:ODBC:baugi","sa","wrongpassword", {PrintError => 0, PrintWarn => 0, RaiseError => 1});}; print "Error is $@\n";' Error is DBI connect('baugi','sa',...) failed: [unixODBC][Easysoft][SQL Server Driver][SQL Server]Login failed for user 'sa'. (SQL-28000) at -e line 1 – bohica Dec 09 '10 at 18:02
0

connect is a class method; you call it with DBI->connect, which returns a db handle ($dbh in your case).

Pedro Silva
  • 4,672
  • 1
  • 19
  • 31