Apache and mod_wsgi: cannot connect mssql by pyodbc

[Omi Chiba]

I'm having a exactly the same problem with this. Just like Adam describe, I can connect mssql if I run web2py with rocket.

Pyodbc error with web2py under Apache and mod_wsgi

I tried with two different PC (Windows XP and 7) but the same error happens.

Is there anyone who are connecting mssql with Apache and mod_wsgi ?

Error ticket on web2py:

It simply says failed to connect five times

<type 'exceptions.RuntimeError'>(Failure to connect, tried 5 times:

'NoneType' object has no attribute 'connect')

My setting is:

• Windows XP/7 32bit
  
• Python:  2.7.2
  
• web2py: 1.98.2
  
• pyodbc-2.1.11.win32-py2.7
  
• mod_wsgi-win32-ap22py27-3.3.so
  
• pywin32-216.win32-py2.7
  
• httpd-2.2.21-win32-x86-openssl-0.9.8r
  

I tried Adam's way but encountered so many errors to build. I did built it by visual C++ 2008 Express compiler but the same error.

[Ross Peoples]

Strangely, I haven't had issues connecting to MSSQL using Linux, but I have never tried on a Windows machine. Usually, the problem lies with pyodbc (or ODBC specifically). Does it give more information about what object it's trying to call 'connect' on?

[Omi Chiba]

Oh my god, I just gave up using IIS and now I need to give up my OS.

You're probably correct because some people mention about windows binary file and they tried to build by themself.

I hope someone share right binary file.

>Does it give more information about what object it's trying to call 'connect' on?

Not really. 

[Willoughby]

You don't have to build anything - just download the source and run that.

The windows binary doesn't support MSSQL - you instead run the source file and it will pull in the proper driver:
http://www.web2py.com/book/default/chapter/06#Dependencies

[Omi Chiba]

> just download the source and run that.

I downloaded "pyodbc-2.1.11.zip" and extracted. Can you explain how to "run that" ? 

[Omi Chiba]

I just tried python setup.py install and it finished without any error, reboot apache, but I still see the same error.

So, it's not binary but pyodbc itself doesn't support running under wsgi for windows ??

[Ross Peoples]

According to the pyodbc's connection strings info: http://code.google.com/p/pyodbc/wiki/ConnectionStrings you will probably have to use a different driver than FreeTDS, since that's a *nix-only driver (I think). Microsoft has their own driver you can use on Windows machines.

Can you manually connect to MSSQL from a Python console session?

>>> import pyodbc

>>> conn = pyodbc.connect("DRIVER=SQL Server};SERVER=dns_or_ip_of_server;UID=username;PWD=password;DATABASE=database_name")

If you can, then you will probably need to update your DAL initialization with the name "SQL Server" for DRIVER.

[Ross Peoples]

There should be an extra { in there, sorry:

>>> import pyodbc

>>> conn = pyodbc.connect("DRIVER={SQL Server};SERVER=dns_or_ip_of_server;UID=username;PWD=password;DATABASE=database_name")

[Omi Chiba]

Ross,

Yes, I can connect from console and web2py under rocket.

It only fails to connect under wsgi on Apache. There are some post says it happens because it fails (cannot find) to "import pyodbc" and we can change Apache config to recognize the path just like environment setting on windows pc.... but I'm not sure. 

[Willoughby]

Can you post your Traceback?

[Ross Peoples]

Sounds like there's a problem with wsgihandler.py or main.py's wsgibase() function. I've never really gotten too involved with this part of web2py. My guess would be that you would somehow need to add pyodbc to the sys.path. But again, I don't know much about how WSGI works.

Is there more of a WSGI expert that can weigh in here? 

[Omi Chiba]

This is my Traceback. (Changed id, password..etc to 'xxxxx')

Traceback (most recent call last):
File "C:\web2py\gluon\restricted.py", line 192, in restricted
exec ccode in environment
File "C:/web2py/applications/Pricelist/models/db.py", line 8, in
<module>
db = DAL("mssql2://xxxxx:xxxxx@xxxxxx/xxxxxxx")
File "C:\web2py\gluon\dal.py", line 3981, in __init__
raise RuntimeError, "Failure to connect, tried %d times:\n%s" %
(attempts, error)
RuntimeError: Failure to connect, tried 5 times:

'NoneType' object has no attribute 'connect'

[Willoughby]

That indicates to me that web2py is running without the mssql driver.
When you start web2py, your window should list MSSQL/DB2 driver under the "Database drivers available" line...

[Omi Chiba]

I see the lines when I run under rocket just like you did from console
but not sure how to check what's happening under wsgi.

Look like you're using windows. Are you able to connect mssql under
wsgi ??

>  Clipboard01.jpg
> 43K表示ダウンロード

[Willoughby]

Sorry, I'm just starting out so I'm afraid that question is above my head at this point...

As Ross said, maybe someone with deep WSGI understanding will chime in.

[Omi Chiba]

Thank you both ! I really appreciate for your help!!!

I will start looking for alternative like mod_proxy from tomorrow
while we're waiting for super hero.

[Ovidio Marinho]

Is can facilitate 

http://ochiba77.blogspot.com/2011/09/how-to-connect-db2-with-python-pyodbc.html
      

       Ovidio Marinho Falcao Neto
             ovid...@gmail.com
               83   8826 9088 - Oi

             83   9334 0266 - Claro
                   Paraiba-Brasil

2011/9/15 Omi Chiba <ochi...@gmail.com>

[Omi Chiba]

Ovidio,

Do you mean if I can connect to db2 with wsgi ? If so, the answer is
no. It's also using pyodbc just like mssql.

You can connect from console and web2py under rocket for sure.

On 9月15日, 午後5:19, Ovidio Marinho <ovidio...@gmail.com> wrote:
> Is can facilitate
>
> http://ochiba77.blogspot.com/2011/09/how-to-connect-db2-with-python-p...
>
>        Ovidio Marinho Falcao Neto
>              ovidio...@gmail.com

>                83   8826 9088 - Oi
>              83   9334 0266 - Claro
>                    Paraiba-Brasil
>

> 2011/9/15 Omi Chiba <ochib...@gmail.com>

>
>
>
>
>
>
>
> > I'm having a exactly the same problem with this. Just like Adam describe, I
> > can connect mssql if I run web2py with rocket.
> > Pyodbc error with web2py under Apache and mod_wsgi

> > <https://groups.google.com/forum/#!searchin/web2py/pyodbc$20apache/web...>

>
> > I tried with two different PC (Windows XP and 7) but the same error
> > happens.
> > Is there anyone who are connecting mssql with Apache and mod_wsgi ?
>
> > Error ticket on web2py:
> > It simply says failed to connect five times
>
> > <type 'exceptions.RuntimeError'>(Failure to connect, tried 5 times:
> > 'NoneType' object has no attribute 'connect')
>
> > My setting is:
>

> >    - Windows XP/7 32bit
> >    - Python:  2.7.2
> >    - web2py: 1.98.2
> >    - pyodbc-2.1.11.win32-py2.7
> >    - mod_wsgi-win32-ap22py27-3.3.so
> >    - pywin32-216.win32-py2.7
> >    - httpd-2.2.21-win32-x86-openssl-0.9.8r