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 [email protected].
For more options, visit https://groups.google.com/d/optout.
