Troubleshooting ODBC connections
Prev Chapter 4. Preparing ODBC Next

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=auto 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.

[Tip]Tip

For debugging purposes, you may wish to link a program such as odbctest directly to FreeTDS instead of to the driver manager. [15] To do so, compile and install the ODBC driver with iODBC as normal [16], then compile and link the program:

Example 4.7. Compile odbctest without a driver manager.

	$ make odbctest.o
	$ gcc -g -o odbctest odbctest.o /usr/local/freetds/lib/libtdsodbc.a


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.

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


[15] Why? Once the program is started in the debugger, the driver entry points become viable breakpoints. Because the DM loads the driver dynamically with dlopen(3), no driver addresses even exist until the runtime linker loads it.

[16] When linking directly to FreeTDS you still need the Driver Manager's header files.

Prev Up Next
ODBC-combined configuration Home Chapter 5. Advanced Configurations