freetds with (py)pyodbc MUST have freetds as a "proxy" to connect from a linux host. The trick is understanding what parameters needs to be passed to py(pyodbc) to connect. As mssql is usually used from windows host, the default "connectionstring-builder" uses parameters that are valuable when connecting from windows, but it's just a matter to pass either as a DSN or to pass a proper argument to driver_args to enable linux hosts to be able to connect. Given that usually those are in tandem with unixodbc + freetds setup on the server, a little tinkering is required. I **think** that if you pass mssql4://dsn it works . A working DSN **may be**
*DRIVER=FreeTDS;SERVER=hostname;PORT=1433;DATABASE=dbname;UID=user;PWD=password;TDS_Version=8.0;* just to start. BTW, passing *DAL(mssql4://user:password@hostname/dbname, driver_args = {'DRIVER' : 'FreeTDS', 'TDS_Version' : '8.0'})* should result in the same exact underlying connection. On Monday, July 13, 2015 at 10:15:08 PM UTC+2, achristoffersen wrote: > > Thanks again Niphlod > > I don't think using SQL server is an option in linux-land? All the > examples I have seen uses FreeTDS. > > On Monday, July 13, 2015 at 6:34:30 PM UTC+2, Niphlod wrote: >> >> web2py doesn't use freetds by default as a driver, but SQL Server... so >> if you can connect with freetds with pypyodbc it's not said that the same >> works within web2py (unless you use the same driver args). >> That being said, the error "no driver available" smells. How did you >> install web2py ? >> >> >> >> On Monday, July 13, 2015 at 8:02:07 AM UTC+2, achristoffersen wrote: >>> >>> (x-post from stackoverflow: >>> http://stackoverflow.com/questions/31371462/web2py-cant-connect-to-mssql-via-pypyodbc-but-possible-to-connect-from-idle >>> ) >>> >>> I can connect via 'naked' pypyodbc, but not via the web2py DAL. >>> >>> I installed pypyodbc as per these instructions: >>> https://code.google.com/p/pypyodbc/wiki/Linux_ODBC_in_3_steps >>> >>> In my web2py model I have: >>> >>> import pypyodbc # ps. Anthony informs me there is no need for this. >>> px = DAL('mssql4://username:password@url,portnumber/databasename') >>> >>> In my controller I then have: >>> >>> def index(): >>> return dict(message=(px.executesql('SELECT top 1 * FROM table;'))) >>> >>> Which gives me a ticket with the following traceback: >>> >>> Traceback (most recent call last): >>> File "/home/andreas/web2py_project/web2py/gluon/restricted.py", line >>> 227, in restricted >>> exec ccode in environment >>> File >>> "/home/andreas/web2py_project/web2py/applications/welcome/models/db.py", >>> line 95, in <module> >>> px = DAL('mssql4://UID:password@URL,port/database') >>> File >>> "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/base.py", >>> line 174, in __call__ >>> obj = super(MetaDAL, cls).__call__(*args, **kwargs) >>> File >>> "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/base.py", >>> line 459, in __init__ >>> raise RuntimeError("Failure to connect, tried %d times:\n%s" % >>> (attempts, tb)) >>> 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',) >>> >>> Which I find I weird, since I try to use pypyodbc, and not pyodbc. (I >>> tried to import pypyodbc as pyodbc btw, same result). >>> >>> If I open a python prompt I can do this: >>> >>> import pypyodbc >>> cnxn = pypyodbc.connect('Driver=FreeTDS; Server=url; port= portnumber; >>> uid=username; pwd=password;database=database') >>> cursor = cnxn.cursor() >>> cursor.execute("select top 1 * from a_table where a_date > getdate() >>> order by newid()") >>> rows = cursor.fetchall() >>> for row in rows: >>> print row >>> >>> Which then outputs the desired single random row. >>> >>> So either web2py DAL is broken (not likely) or I am doing something >>> wrong? >>> >>> p.s. Web2py version: >>> >>> 2.11.2-stable+timestamp.2015.05.30.16.33.24 >>> (Running on Rocket 1.2.6, Python 2.7.6) >>> >>> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.