Troubleshooting ODBC Errors
Sometimes, Windows doesn't want to tell you anything useful. Sometimes, PHP wants to flat lie to you. I ran into a couple roadblocks recently while setting up PHP on a Windows server, and I thought it might be nice of me to share the experience. That way, next time this happens, I only need to read my own blog to see how I handled it last time.
Recently, I was doing some testing with the ODBC driver for a somewhat exotic data format. For this use case, I needed to get it working with PHP Version 7.0.2 running on IIS 7. The initial set up, while somewhat tricky, went well enough. I was able to open up the DSN in LibreOffice Base and both view and edit the data. Frankly, at this point, I thought I was home free.
The next step was to to access the data via PHP. I wrote up a simple "Hello World1" and the page loaded flawlessly. Then I added a call to my data.
$connect = odbc_connect($dsn, "", "") or
die("ODBC Error:: ".odbc_error()." :: ".odbc_errormsg()." :: ".$dsn);
$query = "SELECT field1 FROM table1 ";
$result = odbc_exec($connect, $query);
$field1 = odbc_result($result, 1);
Which netted me:
There is a problem with the resource you are looking for, and it cannot be displayed.
...which was spectacularly unhelpful. However, after a little thought, it occurred to me that this was probably as opaque as it was on purpose. If an error message is too precise, it will both a) help hackers and b) be ugly. So, after looking around, I found some code to add to the web.config file. This file should be in the root of the site. If it's not there, create a blank one and add this to it:
<httpErrors errorMode="Detailed" />
Once I did that, the same page netted me this:
Now, that was considerably more useful. "Undefined"? How can it be undefined? The PHP.ini file I had said this:
; Note that ODBC support is built in, so no dll is needed for it.
In fact, the DLL for ODBC wasn't even listed among those you could comment out because, well, it wasn't needed.
After a bit of fruitless research, I found what the DLL used to be called, back when it was needed, and added it anyway:
Did a quick iisreset, reloaded the browser and got a page full of field1.
So, there you have it. There are ways to make your IIS server talk. Probably want to change it back on Production servers, when you aren't troubleshooting. And INI documentation is, occasionally, not accurate. Not much you can do there but bark up the wrong tree for a while, and then trust your instincts. Hopefully, you can cycle through that process a little faster than I did.