Help with MSSQL Connection

[Massimiliano]

Hello, 

I've a problem with odbc. 

I'm developing on OSX 10.10.3 and I found some problems connecting to sql server express 2005

I'm tried few things

1. I installed pyodbc and web2py said:

Version 2.10.4-stable+timestamp.2015.04.26.15.11.54

Database drivers available: psycopg2, pymysql, imaplib, sqlite3, pg8000, pyodbc

but from ipython:

import pyodbc 

gave me an error.

tracing web2py, it failed importing pyodbc... It's normal that web2py includes pyodbc in drivers available if it fail to import pyodbc?

2. I installed from brew unixodbc

but now, when I try to connect to mssql with the following connection strings:

db = DAL("mssql://sa:pa...@192.168.0.19\SQLEXPRESS/TESTDB")

or

db = DAL("mssql3://sa:pa...@192.168.0.19\SQLEXPRESS/TESTDB")

I got this error:

RuntimeError: Failure to connect, tried 5 times:
Traceback (most recent call last):
  File "pydal/base.py", line 435, in __init__
    self._adapter = ADAPTERS[self._dbname](**kwargs)
  File "pydal/adapters/base.py", line 53, in __call__
    obj = super(AdapterMeta, cls).__call__(*args, **kwargs)
  File "pydal/adapters/mssql.py", line 155, in __init__
    if do_connect: self.reconnect()
  File "pydal/connection.py", line 105, in reconnect
    self.connection = f()
  File "pydal/adapters/mssql.py", line 153, in connector
    return self.driver.connect(cnxn, **driver_args)
Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

Could someone help me?

The application with be deployed on debian 7, do you think there are problems also there?

Thank you

--

Massimiliano

[Massimiliano]

Maybe could be useful to someone.. .

I found that for my needs, I can got with python-tds, that is pure python.

Thank you

[Jose]

Hi Massimiliano

I have the following in FreeBSD

- unixODBC
- FreeTDS
- py-odbc

Configuration:

/usr/local/etc/odbcinst.ini

[TDS]
Description = FreeTDS
Driver = /usr/local/lib/libtdsodbc.so
UsangeCount = 1
DontDLClose = 1
CPTimeout = 60
Threading = 0
Pooling = Yes

/usr/local/etc/odbc.ini

[MY_NAME]
Driver = TDS
Description = ODBC usando FreeTDS
Trace = No
Servername = My_SQLServer
Database = my_database



/usr/local/etc/freetds.conf

(here is important indentation)
[My_SQLServer]
    host = my_host
    port = 1433
    tds version = 8.0


in model
db = DAL("mssql://dsn=MY_NAME;UID=user;PWD=password", pool_size=20)

Best regards
José

[Massimiliano]

Thank you Jose

--
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+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Massimiliano

[JC]

Hello
I had a similar problem, causes by 2 things:  1- sql configuration was wrong.  2-  I need to specify the path fro the pyodbc location.
Hope this help you

On Tuesday, June 30, 2015 at 1:49:40 PM UTC+1, Massimiliano wrote:

Hello, 

I've a problem with odbc. 

I'm developing on OSX 10.10.3 and I found some problems connecting to sql server express 2005

I'm tried few things

1. I installed pyodbc and web2py said:

Version 2.10.4-stable+timestamp.2015.04.26.15.11.54

Database drivers available: psycopg2, pymysql, imaplib, sqlite3, pg8000, pyodbc

but from ipython:

import pyodbc 

gave me an error.

tracing web2py, it failed importing pyodbc... It's normal that web2py includes pyodbc in drivers available if it fail to import pyodbc?

2. I installed from brew unixodbc

but now, when I try to connect to mssql with the following connection strings:

db = DAL("mssql://sa:...@192.168.0.19\SQLEXPRESS/TESTDB")

or

db = DAL("mssql3://sa:...@192.168.0.19\SQLEXPRESS/TESTDB")

[Derek]

Wow, that's great. I tried that a while ago and I had issues. I created issues for the developer but I wasn't aware he had fixed them.

https://github.com/denisenkom/pytds/issues/33

I had to use some hacks to get mine to work with TDS, but once I did that, I can use it in pypy, with full speedups.

On Wednesday, July 1, 2015 at 2:49:46 AM UTC-7, Massimiliano wrote:

Maybe could be useful to someone.. .

I found that for my needs, I can got with python-tds, that is pure python.

Thank you

On Tuesday, June 30, 2015 at 2:49:40 PM UTC+2, Massimiliano wrote:

Hello, 

I've a problem with odbc. 

I'm developing on OSX 10.10.3 and I found some problems connecting to sql server express 2005

I'm tried few things

1. I installed pyodbc and web2py said:

Version 2.10.4-stable+timestamp.2015.04.26.15.11.54

Database drivers available: psycopg2, pymysql, imaplib, sqlite3, pg8000, pyodbc

but from ipython:

import pyodbc 

gave me an error.

tracing web2py, it failed importing pyodbc... It's normal that web2py includes pyodbc in drivers available if it fail to import pyodbc?

2. I installed from brew unixodbc

but now, when I try to connect to mssql with the following connection strings:

db = DAL("mssql://sa:...@192.168.0.19\SQLEXPRESS/TESTDB")

or

db = DAL("mssql3://sa:...@192.168.0.19\SQLEXPRESS/TESTDB")

[黄祥]

i face the same problem, the solution above is not clear for me. any hints ?

installed pyodbc on docker container using alpine:latest image

apk add --no-cache python python-dev py-pip py-setuptools unzip wget openssl gcc build-base unixodbc-dev 

pip install pyodbc

... the rest of the code is just download and extract web2py ...

mssql server used in docker container with microsoft/mssql-server-linux:latest image, tested connect with adminer is work

FROM microsoft/mssql-server-linux:latest

ENV ACCEPT_EULA=Y \

 SA_PASSWORD=Passw0rd

private/appconfig.ini

uri       = mssql4://sa:Passw0rd@web2py-db/test

error traceback

Traceback (most recent call last):

File "/web2py/gluon/restricted.py", line 219, in restricted
exec(ccode, environment)
File "/web2py/applications/test/models/db.py", line 36, in <module>
check_reserved=['all'])
File "/web2py/gluon/packages/dal/pydal/base.py", line 169, in __call__
obj = super(MetaDAL, cls).__call__(*args, **kwargs)
File "/web2py/gluon/packages/dal/pydal/base.py", line 474, in __init__
"Failure to connect, tried %d times:\n%s" % (attempts, tb)

RuntimeError: Failure to connect, tried 5 times:
Traceback (most recent call last):

File "/web2py/gluon/packages/dal/pydal/base.py", line 454, in __init__
self._adapter = adapter(**kwargs)
File "/web2py/gluon/packages/dal/pydal/adapters/__init__.py", line 40, in __call__

obj = super(AdapterMeta, cls).__call__(*args, **kwargs)

File "/web2py/gluon/packages/dal/pydal/adapters/mssql.py", line 31, in __init__
driver_args, adapter_args, do_connect, after_connection)
File "/web2py/gluon/packages/dal/pydal/adapters/base.py", line 368, in __init__
super(SQLAdapter, self).__init__(*args, **kwargs)
File "/web2py/gluon/packages/dal/pydal/adapters/base.py", line 52, in __init__
self.reconnect()
File "/web2py/gluon/packages/dal/pydal/connection.py", line 172, in reconnect
self.connection = self.connector()
File "/web2py/gluon/packages/dal/pydal/adapters/mssql.py", line 81, in connector
return self.driver.connect(self.cnxn, **self.driver_args)
Error: ('01000', u"[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

thanks and best regards,

stifan

[Massimo Di Pierro]

you may have multiple versions of python installed.