Troubleshooting ODBC connections
Supposing everything compiles and installs without trouble, how do you know if your ODBC setup works? Or, if you know it doesn't, what then?
First, try to connect with tsql. If you're intending to use freetds.conf, exercise it with tsql -S servername. If not, use TDSVER=7.0 tsql -H hostname -p port
If tsql works and isql doesn't, you've isolated the problem to the ODBC setup. FreeTDS might have some interoperability problems, but mere connection to the database isn't one of them! If tsql
doesn't work, turn on logging with TDSDUMP. The log will tell you what TCP/IP name (and address) FreeTDS is attempting to connect to, and what version of the TDS protocol it's using.
With iODBC
iODBC comes with a sample command line query program called odbctest, located in the iodbc/samples directory. Using this program you can get a listing of DSNs, connect, and issue queries.
 | For debugging purposes, you may wish to link a program such as odbctest directly to FreeTDS instead of to the driver manager. [1] To do so, compile and install the ODBC driver with iODBC as normal [2], then compile and link the program: Now you can run gdb or another debugger and set breakpoints on functions in the library without the driver manager getting in the way. |
With unixODBC
Try isql -v dsn username password, and have a look at the log. See if the right address and TDS version are being used. Adjust to taste.
Use osql
The osql utility is a Bourne shell script that checks your ODBC configuration. If it approves, it invokes the unixODBC isql utility. Cf. man osql for details on its use.
Example 4-8. Use osql to test the ODBC setup.
$ make odbctest.o $ osql -S machine -U mr_ed -P hayseed looking for odbc.ini and odbcinst.ini in /usr/local/etc reading "/usr/home/mr_ed/.odbc.ini" [machine] found in "/usr/home/mr_ed/.odbc.ini" found this section: [machine] Database = testdb Servername = machine Trace = Yes TraceFile = /tmp/unixodbc.trace looking for driver for DSN [machine] no driver mentioned for [machine] in .odbc.ini looking for driver for DSN [default] driver "FreeTDS" found for [default] in .odbc.ini found driver named "FreeTDS" FreeTDS is not a readable file looking for entry named [FreeTDS] in /usr/local/etc/odbcinst.ini driver "/usr/local/lib/libtdsodbc.so" found for [FreeTDS] in odbcinst.ini /usr/local/lib/libtdsodbc.so is a readable file Using ODBC-Combined strategy FreeTDS servername is "machine" (from /usr/home/mr_ed/.odbc.ini) looking for [machine] in /usr/home/mr_ed/.freetds.conf "/usr/home/mr_ed/.freetds.conf" is a readable file found this section: [machine] host = machine.example.com port = 2500 tds version = 7.1 machine.example.com has address 10.82.32.177 DSN: machine Driver: /usr/local/lib/libtdsodbc.so Server's hostname: machine.example.com Address: 10.82.32.177 Attempting connection as mr_ed ... + exec isql machine mr_ed hayseed -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
The reader is here advised that the isql that comes with many versions of unixODBC will truncate text and surprise in other ways without warning. If it behaves strangely, try FreeTDS's bsqlodbc before you decide you've found a FreeTDS bug.