I have been successful in connecting to the sql server from and ubuntu machine using pyodbc/freeTDS
The setup works fine, but for some time after which i am getting the following error
[ERROR] (server)-('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')
here is my config
odbc.ini
[SERVERONE]
Description="test"
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Servername=sqlserver
Database=DBONE
[SERVERTWO]
Description="test"
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Servername=sqlserver
Database=DBTWO
.freeTDS.conf
[global]
tds version = 8.0
[sqlserver]
host = <SERVER IP>
instance = R2TEST
port = 1070
tds version = 8.0
text size = 2000000
python module
class base(object):
_connection = None
def __init__(self):
try:
self._conn_string = ENV.DB.CONNSTRING
logg.debug('using connection string %s'%self._conn_string)
self._connection = pyodbc.connect(self._conn_string)
except Exception:
raise
def _refresh_conn(self):
self._connection = None
self._connection = pyodbc.connect(self._conn_string)
class getConf(base):
def __init__(self):
super(SamlConf,self).__init__()
def getClientSamlConf(self, client):
_query = "EXEC DBONE..getConfig '%s'" % (client)
logg.info(_query)
row = None
try:
_cursor = self._connection.cursor()
_cursor.execute(_query)
_row = _cursor.fetchone()
if len(_row) >0 and _row[0]:
logg.info(_row)
row = ClienObj(_row)
_cursor.commit()
self._refresh_conn()
except Exception,ex:
import sys
raise DatabaseCommunicationError('Communication to the database failed '), None, sys.exc_info()[2]
return
return row
The connection string used here is
DSN=SERVERONE;UID=user;PWD=password
I am suspecting that the way connections are handled here is the cause but am not sure.
Also, the setup starts working automatically after some time and the loops through it
working > not working > working
EDIT If this matter the other components of my stack are supervisor, gunicorn(gevent async) and virtualenv
EDIT 2
Another thing i have noticed is that the problem occurs when the application has no traffic for a long time