Linux, connect to MSSQL 2012, LD_library_path (venv)

[achristoffersen]

Trying to connect to mssql

In models/px.py I have:

import pypyodbc
px = DAL('mssql4://username:password@url,portnumber/databasename')

In controlers/default.py I have

return px.executesql('SELECT top 1 * FROM table;')

I get this ticket:

<class 'pypyodbc.OdbcNoLibrary'> 'ODBC Library is not found. Is LD_LIBRARY_PATH set?'

 

If you run idle, and import pypyodbc I also get:

<class 'pypyodbc.OdbcNoLibrary'> 'ODBC Library is not found. Is LD_LIBRARY_PATH set?'

google a bit, I can read that the variable is a:

List of directories where the system searches for runtime libraries in addition to those hard-defined by ld and in /etc/ld.so.conf.d/*.conf files

 

But my linux mint doesnt have the variable set. And what should I set the variable to, in my virtual environment where I run web2py?

Any help to get me started much appriciated.

[achristoffersen]

I tried to add pypyodbc.conf  "/etc/ld.so.conf.d/pypyodbc.conf"

sudo vim  /etc/ld.so.conf.d/pypyodbc.conf

I added this:

/home/andreas/web2py_project/venv/local/lib/python2.7/site-packages/

and ran

sduo ldconfig

env | grep -i ld_library

but still no cigar: "env | grep -i ld_library" returns nothing.

(p.s running ubuntu 14.04)

[achristoffersen]

Okay - so I have now:

1.  sudo apt-get install tdsodbc unixodbc
2.- added path of libtdsodbc.so to /etc/odbcinst.ini ([FREETDS]Driver = path)

Now the error is not about ld_library_path but instead

Error: (u'IM002', u'[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified')

Sincerely
Andreas

On Saturday, July 11, 2015 at 2:21:52 PM UTC+2, achristoffersen wrote:

[achristoffersen]

Almost there I guess:

I modified /etc/freetds/freetds.conf to have the followin options under [global]
TDS_Version = 8.0
client_charset = UTF-8

I can now do:

import pypyodbc
conn pypyodbc.connect('Driver=FreeTDS,Server=url;port=port;uid=username;pwd=password;database=databasename')
print conn.cursor()execute('select top 1 * from a_table').fetchone()

which works.

But

if I do this

import pypyodbc
from gluon import DAL

px = DAL('mssql4://username:password@url,portnumber/databasename')

I get this error:
RuntimeError: Failure to connect, tried 5 times:
Traceback (most recent call last):
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/base.py", line 437, in __init__
    self._adapter = ADAPTERS[self._dbname](**kwargs)
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/base.py", line 57, in __call__
    obj = super(AdapterMeta, cls).__call__(*args, **kwargs)
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/mssql.py", line 160, in __init__
    if do_connect: self.reconnect()
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/connection.py", line 105, in reconnect
    self.connection = f()
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/mssql.py", line 158, in connector
    return self.driver.connect(cnxn, **driver_args)
  File "/home/andreas/web2py_project/venv/local/lib/python2.7/site-packages/pypyodbc.py", line 2434, in __init__
    self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
  File "/home/andreas/web2py_project/venv/local/lib/python2.7/site-packages/pypyodbc.py", line 2483, in connect
    check_success(self, ret)
  File "/home/andreas/web2py_project/venv/local/lib/python2.7/site-packages/pypyodbc.py", line 988, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "/home/andreas/web2py_project/venv/local/lib/python2.7/site-packages/pypyodbc.py", line 964, in ctrl_err
    raise Error(state,err_text)

Error: (u'IM002', u'[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified')

Using web2py I get this:

<type 'exceptions.RuntimeError'> Failure to connect, tried 5 times: Traceback (most recent call last): File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/base.py", line 437, in __init__ self._adapter = ADAPTERS[self._dbname](**kwargs) File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/base.py", line 57, in __call__ obj = super(AdapterMeta, cls).__call__(*args, **kwargs) File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/mssql.py", line 106, in __init__ if do_connect: self.find_driver(adapter_args,uri) File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/base.py", line 188, in find_driver raise RuntimeError("no driver available %s" % str(self.drivers)) RuntimeError: no driver available ('pyodbc',)

On Saturday, July 11, 2015 at 2:21:52 PM UTC+2, achristoffersen wrote:

[achristoffersen]

As the problem was not with LD_LIBRARY_PATH, but instead that I didn't follow these instructions https://code.google.com/p/pypyodbc/wiki/Linux_ODBC_in_3_steps I close this question and ask another one.

Thanks

[Rob Johnson]

If anyone else finds this via Google (it's the top hit), and because the link below is dead (redirects to an archive), I solved the LD_LIBRARY_PATH problem by installing python-pyodbc through APT on Ubuntu. I figured this out by looking at the pypyodbc source on GitHub to see what paths it was checking for shared obejcts, one of which was '/usr/lib/libodbc.so'.

Hope this helps.