I just wasted four hours on the most idiotic thing, so I thought I'd document it here as self-reference.
Background: to connect to some Oracle db, I'm using the excellent pypyodbc module, which is a pure-Python ODBC implementation - basically a not-so-thin layer on top of your installed ODBC providers - that works great with Python 3. If you have to support multiple database vendors (in my case, Oracle, MSSQL, DB2 and maybe others), it makes sense to avoid packing a module for each product and just let ODBC work its magic.
The main problem with ODBC has always been the dark magic involved in crafting connection strings. Each driver provides different options, and when the syntax is not correct, in most cases there is precious little feedback. This is why we have sites like connectionstrings.com.
In my case, the connection string I was using worked fine with TNS names (the stuff in tnsnames.ora) like this:
Driver={Oracle in OraClient11g_home1};DBQ=myTnsServiceName; Uid=myUsername; Pwd=myPassword;
However, I did not want to rely on that particular catalog (which is often misconfigured/broken in the real world), and would rather specify the usual host, port and sid trimurti. So I went on connectionstrings.com and found the following:
Driver={Oracle in OraClient11g_home1}; Server=serverSID; Uid=myUsername; Pwd=myPassword;
... and then I spent four hours figuring out why it wasn't working. I turned on all tracing options, spent ages reading tracing logs, tried umpteen different values for SERVER... all for nought: from logs, it was clear that my SERVER option was completely disregarded and replaced with some default "orcl" values.
Desperate, I eventually thought of daring the (usually unwieldy) original driver documentation from Oracle. And lo, I've found in the FAQ doc for Oracle ODBC, on page 13, a very helpful table listing all the options you can specify in a connection string. "SERVER" was nowhere to be seen. Ouch.
It turns out the trick was to keep using "DBQ" and just replace it with the standard Oracle network syntax:
Driver={Oracle in OraClient11g_home1}; DBQ=myserver.mydomain.com:1521/mySid; Uid=myUsername; Pwd=myPassword;
In the end, I wasted 4 hours because I thought googling would have been faster than Reading The Fine Manual. Lesson learnt.
9 comments:
Thanks for this! Though, you've helped me successfully put off Ring TFM and continue Googling instead... :)
Saved my day so much!
This was wicked helpful! :) Thanks so much, it was driving me crazy trying to figure out how to do this!
this was perfect, thank you very much, like others this was hard to find. Google searches didn't show the answers in the first pages, only confusing and misleading posts. Finally I found this post. It should be the first thing that comes up on any search in this areas because it works! Thanks for reference to the guide also.
Any idea what this would look like for a localhost Express database?
Another one caught in this trap
Thanks.. This finally got my connection going with help from
our DBA as well.
still after 6 years, this is still the best post, this solved my case. thanks so much
wow, great answer. Yep, wasted the same 4 hours ...
Post a Comment