CODEWITHSUNDEEP
pythonsql-serverpyodbc
Alex
Alex

Reputation: 4180

pyodbc: How to test whether it's possible to establish connection with SQL server without freezing up

I am writing an app with wxPython that incorporates pyodbc to access SQL Server. A user must first establish a VPN connection before they can establish a connection with the SQL server. In cases where a user forgets to establish a VPN connection or is simply not authorized to access a particular server, the app will freeze for up to 60+ seconds before it produces an error message. Often, users will get impatient and force-close the app before the error message pops up.

I wonder if there is a way to test whether it's possible to connect to the server without freezing up. I thought about using timeout, but it seems that timeout can be used only after I establish a connection

A sample connection string I use is below:

connection = pyodbc.connect(r'DRIVER={SQL Server};SERVER=ServerName;database=DatabaseName;Trusted_Connection=True;unicode_results=True')

Upvotes: 1

Views: 4997

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

took a while before it threw an error message about server not existing or access being denied

Your comment conflates two very different kinds of errors:

  • server not existing is a network error. Either the name has no address, or the address is unreachable. No connection can be made.

  • access being denied is a response from the server. For the server to respond, a connection must exist. This is not to be confused with connection refused (ECONNREFUSED), which means the remote is not accepting connections on the port.

SQL Server uses TCP/IP. You can use standard network functions to determine if the network hostname of the machine running SQL Server can be found, and if the IP address is reachable. One advantage to using them to "pre-test" the connection is that any error you'll get will be much more specific than the typical there was a problem connecting to the server.

Note that not all delay-inducing errors can be avoided. For example, if the DNS server is not responding, the resolver will typically wait 30 seconds before giving up. If an IP address is valid, but there's no machine with that address, attempting a connection will take a long time to fail. There's no way for the client to know there's no such machine; it could just be taking a long time to get a response.

Upvotes: 1

xbb
xbb

Reputation: 2163

See https://code.google.com/archive/p/pyodbc/wikis/Connection.wiki under timeout

Note: This attribute only affects queries. To set the timeout for the actual connection process, use the timeout keyword of the pyodbc.connect function.

So change your connection string to:

connection = pyodbc.connect(r'DRIVER={SQL Server};SERVER=ServerName;database=DatabaseName;Trusted_Connection=True;unicode_results=True', timeout=3)

should work

Upvotes: 2

Related Questions