MSSQL connection string port and driver workaround
294 views
Skip to first unread message
Dmitri Zagidulin
Sep 30, 2009, 3:38:57 PM9/30/09
to web2py-users
I have a web2py app (running on Ubuntu Linux) that has to connect to a
MS SQL Server.
I installed pyodbc, and the FreeTDS ODBC driver.
I defined a driver in odbcinst.ini, etc, etc. And could now connect
from the command line, via isql, to the remote database.
In web2py, the following connection string:
db = SQLDB('mssql://myuser:mypas...@xxx.x.x.x:PPP/mydatabase) #
(PPP being the port number)
did not work. I traced the matter down to gluon/sql.py lines 833-836:
the engine being created did not seem to be actually using the port
number that was passed in via the connection string.
The port is parsed via regex on line 827, but then is not actually
used in creating the pyodbc connection.
Also, the engine was assuming that the ODBC driver was named 'SQL
Server' (mine was named 'FreeTDS').
To make it work temporarily, I ended up commenting out
#self._pool_connection(lambda : pyodbc.connect(cnxn))
and replacing it with:
self._pool_connection(lambda : pyodbc.connect
('SERVER=xxx.x.x.x;PORT=PPP;DATABASE=mydatabase;UID=myuser;PWD=mypassword;DRIVER=
{FreeTDS}'))
(which basically hardcodes my connection and bypasses the connection
string in any models - not great).
So, my question is, what's the right way to fix this? Can you extend
the connection string syntax so that we can pass in the driver name,
and also use the port that is passed in?
(For example, SQLAlchemy allows other arguments in the connect string,
like mssql:// ... /mydatabase?driver={FreeTDS} )
MS SQL Server.
I installed pyodbc, and the FreeTDS ODBC driver.
I defined a driver in odbcinst.ini, etc, etc. And could now connect
from the command line, via isql, to the remote database.
In web2py, the following connection string:
db = SQLDB('mssql://myuser:mypas...@xxx.x.x.x:PPP/mydatabase) #
(PPP being the port number)
did not work. I traced the matter down to gluon/sql.py lines 833-836:
the engine being created did not seem to be actually using the port
number that was passed in via the connection string.
The port is parsed via regex on line 827, but then is not actually
used in creating the pyodbc connection.
Also, the engine was assuming that the ODBC driver was named 'SQL
Server' (mine was named 'FreeTDS').
To make it work temporarily, I ended up commenting out
#self._pool_connection(lambda : pyodbc.connect(cnxn))
and replacing it with:
self._pool_connection(lambda : pyodbc.connect
('SERVER=xxx.x.x.x;PORT=PPP;DATABASE=mydatabase;UID=myuser;PWD=mypassword;DRIVER=
{FreeTDS}'))
(which basically hardcodes my connection and bypasses the connection
string in any models - not great).
So, my question is, what's the right way to fix this? Can you extend
the connection string syntax so that we can pass in the driver name,
and also use the port that is passed in?
(For example, SQLAlchemy allows other arguments in the connect string,
like mssql:// ... /mydatabase?driver={FreeTDS} )
mdipierro
Sep 30, 2009, 5:51:08 PM9/30/09
to web2py-users
Yes. If you send me a patch to do this I will take it.
Thank you for reporting the issue.
On Sep 30, 2:38 pm, Dmitri Zagidulin <dzagidu...@gmail.com> wrote:
> I have a web2py app (running on Ubuntu Linux) that has to connect to a
> MS SQL Server.
> I installed pyodbc, and the FreeTDS ODBC driver.
> I defined a driver in odbcinst.ini, etc, etc. And could now connect
> from the command line, via isql, to the remote database.
>
> In web2py, the following connection string:
>
> db = SQLDB('mssql://myuser:mypassw...@xxx.x.x.x:PPP/mydatabase) #
Thank you for reporting the issue.
On Sep 30, 2:38 pm, Dmitri Zagidulin <dzagidu...@gmail.com> wrote:
> I have a web2py app (running on Ubuntu Linux) that has to connect to a
> MS SQL Server.
> I installed pyodbc, and the FreeTDS ODBC driver.
> I defined a driver in odbcinst.ini, etc, etc. And could now connect
> from the command line, via isql, to the remote database.
>
> In web2py, the following connection string:
>
Dmitri Zagidulin
Sep 30, 2009, 6:42:51 PM9/30/09
to web...@googlegroups.com
Sure, will do!
0 new messages