Suppressing connection error with DBI and DBD:ODBC SQL Server Native Client 10.0

Question

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.

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
Making this change produced expected results, the error message is suppressed. — Bryan

bohica bohica · Accepted Answer · 2010-11-19T09:53:19

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 $@.

Suppressing connection error with DBI and DBD:ODBC SQL Server Native Client 10.0

2 Answers