Issue 2: add support for file dsn's
Status:  WontFix
Owner:
Closed:  Mar 2009
Project Member Reported by mkleehammer, Nov 18, 2008
(copied from SourceForge issue 2306749)

I cannot find any online information to show that pyodbc supports file
dsn's. I would like to request this feature be added, as for now I am
forced to use other scripting solutions that does support file dsn's such
as perl's DBI:ODBC module.

Nov 18, 2008
Project Member #1 mkleehammer
pyodbc supports anything ODBC supports, you just need to use a file DSN
connection string.

The connection string is passed directly to SQLDriverConnect, which you
can find a lot of documentation for on the web.

For a file DSN, see this article:
http://www.kamath.com/tutorials/tut005_filedsn.asp

You would use something like:
cnxn = pyodbc.connect(r'file=c:\dev\xxx.dsn;UID=uid;PWD=pwd')

I recommend using a raw string (leading 'r' before the quotes) so you
don't have to escape your backslashes on Windows.

If you are using unixODBC or iODBC, you'll need to see if they support it.
If the perl module really works, then the same thing will work in pyodbc.
(You may consider turning on ODBC tracing while running the perl module to
see what it passes to SQLDriverConnect.)

Nov 18, 2008
#2 seanre...@gmail.com
Yes, I have tried using the exact string
""FILEDSN=/home/sreque/moabcfg/pbs/dsninfo.dsn;UID=;PWD=;", with and without extra
slashes.

I tried turning on odbc tracing and then running the script, but no tracing output
was created, which I find strange. I then ran a C program that uses the exact same
FILEDSN, and tracing output was generated. This C program connects using
SQLDriverConnect with the same string as the perl script is using: namely,
'FILEDSN=<path>'.

Nov 18, 2008
Project Member #3 mkleehammer
It just occurred to me, pyodbc on Linux unfortunately can't choose between the two
ODBC implementations.  (It would be great to just have one.)  This is actually chosen
when you _build_ pyodbc.  Are we sure your build is using unixODBC instead of iODBC?

Since I don't provide binary builds for Linux (yet), I can't say for sure what yours
is.  (I'll have to add a global variable for determining this.)

Did you build this?  Are you sure it is using unixODBC?  It is very odd that the
trace settings didn't do anything.  We really need that ODBC trace.
Nov 18, 2008
#4 seanre...@gmail.com
It looks like, from a cursory glance at the following page,
http://www.iodbc.org/index.php?page=docs/odbcstory, that iodbc supports tracing too.
Either way, I did an apt-get remove iodbc on my ubuntu system. Also, I know my C
program is using unixODBC because I compiled it myself to link with a unixodbc
library, and I have seen it produce unixODBC error messages before. Finally, the
python script I have that's failing is also using unixODBC judging by the error
message it produces. 

Even after removing iodbc, I still get no traces from the perl script. I even tried
using a different driver, this time connecting to a MySQL database. The perl trace
output  is showing that the script is definitely invoking ODBC functions such as
SQLPrepare, SQLFetch, and SQLFreeStmt.
Mar 18, 2009
Project Member #6 mkleehammer
I am going to have to close this since there is little I can do.  pyodbc accepts all
connection strings and merely passes them to the driver unmodified.  File DSNs
definitely work on Windows using Microsoft's ODBC implementation.  It appears there
is an issue with iODBC?

Status: WontFix