iODBC error trying to connect to MS SQL Server in PHP with unixODBC/FreeTDS

Question

I am trying to connect to a remote MS SQL Server db from PHP on Mac (eventually on an Ubuntu server( with FreeTDS and unixODBC, but even though I seem to have everything set up properly, I'm getting iODBC errors, and I'm not sure how to get around them.

I'm using MacPorts, so my config is:

/opt/local/etc/freetds.conf::

[bti_db]
host = 123.45.67.89 (IP address changed to protect the innocent)
port = 14333
tds version = 8.0

/opt/local/etc/odbcinst.ini:

[FreeTDS]
Description = TDS Driver (Sybase/MSSQL)
Driver = /opt/local/lib/libtdsodbc.so
Setup = /opt/local/lib/libtdsS.so
FileUsage = 1

/opt/local/etc/odbc.ini:

[bti_dsn]
Driver = FreeTDS
Description = My Database
Trace = no
Servername = bti_db
Database = btidata

However, whenever I try to connect with odbc_connect() using the 'bti_dsn'

$conn = odbc_connect('bti_dsn;, $user, $pw);

I get this error:

Warning: odbc_connect() [function.odbc-connect]: SQL error: [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQL state IM002 in SQLConnect

In the ODBC section my phpinfo(), I see ODBC Library defined as iodbc, and PHP is compiled with '--with-iodbc=/usr', so I'm guessing that config is my problem. How can I get around this so that it uses unixODBC/FreeTDS I have set up?

Thanks.

Answer 1

It looks like it's not looking in your odbc.ini file. Maybe it's looking for /etc/odbc.ini and /etc/odbcinst.ini?

Answer 2

iODBC is installed by default as part of Mac OS X; has been since Jaguar (10.2.x). There's no need for UnixODBC on Macs, and it can lead to lots of errors if you're not a serious expert. There is a specific guide to using PHP with iODBC on Mac OS X. For best results, you may also want to upgrade to the latest iODBC for Mac OS X.

/opt/local/etc should not be added to your $PATH, through .profile or otherwise.

PHP is definitely finding iODBC before UnixODBC, but this should not be a problem; UnixODBC and iODBC are generally (and are meant to be fully) API-equivalent ODBC driver managers. If you're really concerned about that part, you can change the $DYLD_LIBRARY_PATH (Mac OS X's version of Linux's $LD_LIBRARY_PATH) -- but if PHP was linked against the iODBC Frameworks, as opposed to the dylibs, this won't make any difference.

(Note that $DYLD_LIBRARY_PATH also must include /opt/local/lib or your FreeTDS driver won't load.)

For the specific error your report -- PHP needs to have a couple of environment variables set, if you're not using the Mac's default ODBC configuration files (System level are in /Library/ODBC/odbc[inst].ini; User level are in ~/Library/ODBC/odbc[inst].ini ... if there are ~/.odbdc[inst].ini files present, they should be blended into the ~/Library/ODBC/ files and replaced by symlinks to the same).

If you don't want to use iODBC, or don't want to use those default files, you have to set $ODBCINI to target the odbc.ini file where you've defined your DSN, and $ODBCINSTINI to target the odbcinst.ini file which registers the driver you want to use.

Assuming you want to do all of the above, lines like these should be added to your *.php files (optimally via a require or include statement to minimize future editing) --

putenv("DYLD_LIBRARY_PATH=/path/to/odbcsdk/lib;$DYLD_LIBRARY_PATH");
putenv("ODBCINSTINI=/path/to/odbcinst.ini");
putenv("ODBCINI=/path/to/odbc.ini");

I can't be exact about the DYLD_LIBRARY_PATH setting, because you didn't specify where your UnixODBC libraries are. However, if you are OK with iODBC being the driver manager, and just want your FreeTDS libraries to load, the following should work --

putenv("DYLD_LIBRARY_PATH=/opt/local/lib;$DYLD_LIBRARY_PATH");
putenv("ODBCINSTINI=/opt/local/etc/odbcinst.ini");
putenv("ODBCINI=/opt/local/etc/odbc.ini");

I hope this helps.

P.S. In your DSN definition, this line --

Driver = FreeTDS

-- should be rewritten. Either the human-friendly driver name should be wrapped in braces ({FreeTDS}), or the full-path to the driver library (/opt/local/lib/libtdsodbc.so) should be the value instead.

Driver = {FreeTDS}
Driver = /opt/local/lib/libtdsodbc.so

I'm presuming that you also have something like the following index entry in your odbcinst.ini --

[ODBC Drivers]
FreeTDS = Installed

-- and something like the following index entry in your odbc.ini --

[ODBC Data Sources]
bti_dsn = FreeTDS

...but now I notice that your $conn line may just need correction. Look at the arguments to odbc_connect.

$conn = odbc_connect('bti_dsn;, $user, $pw);

That should probably look more like --

$conn = odbc_connect("bti_dsn", "$user", "$pw");