Port handling with pyodbc broken

[Former user]

Beginning with r4387, custom ports are no longer working for me. Using the SQL Server driver installed on my machine (version 6.00.6001.18000), I get observe the following behavior when connecting to a database with a custom port number:

pyodbc.connect('driver={SQL Server};server=myserver.example.com,1433;uid=omitted;pwd=omitted') <pyodbc.Connection object at 0x01A4A380> pyodbc.connect('driver={SQL Server};server=myserver.example.com;port=1433;uid=omitted;pwd=omitted') Traceback (most recent call last): File "<stdin>", line 1, in <module> pyodbc.Error: ('08001', '08001 MicrosoftSQL Server DriverDBNETLIBInvalid connection. (14); 01000 MicrosoftSQL Server DriverDBNETLIBConnectionOpen (Invalid Instance()). (14); 01S00 MicrosoftSQL Server DriverInvalid connection string attribute (0)')

I get the same error if I attempt to use the mssql database dialect in sqlalchemy:

see http://paste.turbogears.org/paste/9521

Ticket #634 originally addressed this issue and it seems to have re-emerged in r4387.

I presume the PORT= syntax works for some drivers, but not for mine.

If I can provide more information to help resolve this problem, please let me know. We test against pyodbc and rely on this connection mechanism for our MSSQL databases, so I've marked this as high. I'll gladly submit a patch, but need to know where the PORT= syntax came from (and if it needs to be supported in certain environments).

[Former user]

The more I think about this problem, the more I think we may have actually gone over this already. I found out two things: 1) I can still use the host,port syntax manually. I thought this syntax stopped working, but I found the problem was with Powershell (in which the comma is a special character). 2) If I use the SQL Native Client, the port= syntax works just fine.

Perhaps the solution is to check the "driver" parameter, and if it's "SQL Server" use the host,port syntax... and if it's anything else (or "SQL Native Client"), use the Port= syntax. Maybe there's a more robust solution.

Comments?

[Mike Bayer]

the thinking here has focused on the notion of encouraging users to use DSNs instead of hosts to connect via ODBC. You can do this right now if you use the use_dsn flag.

otherwise if theres platform-specific things that determine "host,port" versus "Port=" then yeah thats how we usually do that. DSNs eliminate the need for SQLA to be involved.

[Michael Trier]

Corrected in b8878ef62a7215dbad8bcc0dd345d981023fd938 for 0.4.8 and 306bee4ecd1ddcab860f9b39866b2f57b6ec509a for 0.5.0

[Mike Bayer]

Removing milestone: 0.4.8 (automated comment)