add support for file dsn's

[GoogleCodeExporter]

(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.

Original issue reported on code.google.com by mkleehammer on 18 Nov 2008 at 4:28

[GoogleCodeExporter]

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.)

Original comment by mkleehammer on 18 Nov 2008 at 4:29

[GoogleCodeExporter]

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>'.

Original comment by seanre...@gmail.com on 18 Nov 2008 at 5:31

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 18 Nov 2008 at 5:39

[GoogleCodeExporter]

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.

Original comment by seanre...@gmail.com on 18 Nov 2008 at 9:56

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

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?

Original comment by mkleehammer on 18 Mar 2009 at 5:20

• Changed state: WontFix