<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <HTML ><HEAD ><TITLE >Troubleshooting ODBC connections</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REL="HOME" TITLE="FreeTDS User Guide" HREF="index.htm"><LINK REL="UP" TITLE="Preparing ODBC" HREF="prepodbc.htm"><LINK REL="PREVIOUS" TITLE="ODBC-combined configuration" HREF="odbcombo.htm"><LINK REL="NEXT" TITLE="Advanced Configurations" HREF="configs.htm"><LINK REL="STYLESHEET" TYPE="text/css" HREF="userguide.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8"></HEAD ><BODY CLASS="SECT1" BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#840084" ALINK="#0000FF" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="3" ALIGN="center" ><SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > User Guide: A Guide to Installing, Configuring, and Running <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="bottom" ><A HREF="odbcombo.htm" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="80%" ALIGN="center" VALIGN="bottom" >Chapter 4. Preparing ODBC</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="bottom" ><A HREF="configs.htm" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="ODBCDIAGNOSE" >Troubleshooting ODBC connections</A ></H1 ><P >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?</P ><P >First, try to connect with <B CLASS="COMMAND" >tsql</B >. If you're intending to use <TT CLASS="FILENAME" >freetds.conf</TT >, exercise it with <B CLASS="COMMAND" >tsql -S <TT CLASS="REPLACEABLE" ><I >servername</I ></TT ></B >. If not, use <B CLASS="COMMAND" >TDSVER=7.0 tsql -H <TT CLASS="REPLACEABLE" ><I >hostname</I ></TT > -p <TT CLASS="REPLACEABLE" ><I >port</I ></TT ></B ></P ><P >If <B CLASS="COMMAND" >tsql</B > works and <B CLASS="COMMAND" >isql</B > doesn't, you've isolated the problem to the ODBC setup. <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > might have some interoperability problems, but mere connection to the database isn't one of them! If <B CLASS="COMMAND" >tsql</B > doesn't work, turn on logging with <CODE CLASS="ENVAR" >TDSDUMP</CODE >. The log will tell you what TCP/IP name (and address) <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > is attempting to connect to, and what version of the TDS protocol it's using.</P ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="WITH.IODBC" >With iODBC</A ></H2 ><P > <SPAN CLASS="PRODUCTNAME" >iODBC</SPAN > comes with a sample command line query program called <B CLASS="COMMAND" >odbctest</B >, located in the <TT CLASS="FILENAME" >iodbc/samples</TT > directory. Using this program you can get a listing of DSNs, connect, and issue queries. <DIV CLASS="TIP" ><P ></P ><TABLE CLASS="TIP" WIDTH="100%" BORDER="0" ><TR ><TD WIDTH="25" ALIGN="CENTER" VALIGN="TOP" ><IMG SRC="../images/tip.gif" HSPACE="5" ALT="Tip"></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><P >For debugging purposes, you may wish to link a program such as <B CLASS="COMMAND" >odbctest</B > directly to <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > instead of to the driver manager. <A NAME="AEN2052" HREF="#FTN.AEN2052" ><SPAN CLASS="footnote" >[1]</SPAN ></A > To do so, compile and install the <SPAN CLASS="SYSTEMITEM" >ODBC</SPAN > driver with <SPAN CLASS="PRODUCTNAME" >iODBC</SPAN > as normal <A NAME="AEN2058" HREF="#FTN.AEN2058" ><SPAN CLASS="footnote" >[2]</SPAN ></A >, then compile and link the program: <DIV CLASS="EXAMPLE" ><A NAME="E.G.ODBCTEST.NODM" ></A ><P ><B >Example 4-7. Compile <TT CLASS="FILENAME" >odbctest</TT > without a driver manager.</B ></P ><PRE CLASS="SCREEN" > <SAMP CLASS="PROMPT" >$ </SAMP ><KBD CLASS="USERINPUT" >make odbctest.o</KBD > <SAMP CLASS="PROMPT" >$ </SAMP ><KBD CLASS="USERINPUT" >gcc -g -o odbctest odbctest.o /usr/local/freetds/lib/libtdsodbc.a</KBD ></PRE ></DIV > Now you can run <B CLASS="COMMAND" >gdb</B > or another debugger and set breakpoints on functions in the library without the driver manager getting in the way.</P ></TD ></TR ></TABLE ></DIV ></P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="WITH.UNIXODBC" >With unixODBC</A ></H2 ><P >Try <B CLASS="COMMAND" >isql -v <TT CLASS="REPLACEABLE" ><I >dsn</I ></TT > <TT CLASS="REPLACEABLE" ><I >username</I ></TT > <TT CLASS="REPLACEABLE" ><I >password</I ></TT ></B >, and have a look at the log. See if the right address and TDS version are being used. Adjust to taste.</P ><DIV CLASS="SECT3" ><H3 CLASS="SECT3" ><A NAME="WITH.UNIXODBC.OSQL" >Use <B CLASS="COMMAND" >osql</B ></A ></H3 ><P >The <B CLASS="COMMAND" >osql</B > utility is a Bourne shell script that checks your ODBC configuration. If it approves, it invokes the unixODBC isql utility. Cf. <B CLASS="COMMAND" >man osql</B > for details on its use. <DIV CLASS="EXAMPLE" ><A NAME="E.G.ODBCDIAGNOSE.OSQL" ></A ><P ><B >Example 4-8. Use <B CLASS="COMMAND" >osql</B > to test the ODBC setup.</B ></P ><PRE CLASS="SCREEN" > <SAMP CLASS="PROMPT" >$ </SAMP ><KBD CLASS="USERINPUT" >make odbctest.o</KBD > <SAMP CLASS="PROMPT" >$ </SAMP ><KBD CLASS="USERINPUT" >osql -S machine -U mr_ed -P hayseed</KBD > 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></PRE ></DIV ></P ><P >The reader is here advised that the <B CLASS="COMMAND" >isql</B > that comes with many versions of unixODBC will truncate text and surprise in other ways without warning. If it behaves strangely, try <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN >'s <B CLASS="COMMAND" >bsqlodbc</B > before you decide you've found a <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > bug. </P ></DIV ></DIV ></DIV ><H3 CLASS="FOOTNOTES" >Notes</H3 ><TABLE BORDER="0" CLASS="FOOTNOTES" WIDTH="100%" ><TR ><TD ALIGN="LEFT" VALIGN="TOP" WIDTH="5%" ><A NAME="FTN.AEN2052" HREF="odbcdiagnose.htm#AEN2052" ><SPAN CLASS="footnote" >[1]</SPAN ></A ></TD ><TD ALIGN="LEFT" VALIGN="TOP" WIDTH="95%" ><P >Why? Once the program is started in the debugger, the driver entry points become viable breakpoints. Because the DM loads the driver dynamically with <CODE CLASS="FUNCTION" >dlopen(3)</CODE >, no driver addresses even <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >exist</I ></SPAN > until the runtime linker loads it. </P ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" WIDTH="5%" ><A NAME="FTN.AEN2058" HREF="odbcdiagnose.htm#AEN2058" ><SPAN CLASS="footnote" >[2]</SPAN ></A ></TD ><TD ALIGN="LEFT" VALIGN="TOP" WIDTH="95%" ><P >When linking directly to <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > you still need the Driver Manager's header files.</P ></TD ></TR ></TABLE ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="odbcombo.htm" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.htm" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="configs.htm" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >ODBC-combined configuration</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="prepodbc.htm" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Advanced Configurations</TD ></TR ></TABLE ></DIV ></BODY ></HTML >