Looking for examples on connecting to MS SQL Server with freetds in C

I am attempting to connect to a ms sql server in C code.  I have successfully gotten tsql to connect and query my database, but I can not figure out how to do this at all in code.  I would love an example as this seems like it should be fairly basic and yet has been a challenge.

The bottom of this example is where my code breaks, it does not print the fail message, instead if just dies.

I suspect I may have put in a sybase version, which is likely why my code is failing, but I have not been able to find any ms sql server examples for C.  

I am using lLinux dicom-dose 2.6.32-5-686 #1 SMP Fri Aug 26 09:15:47 UTC 2011 i686 GNU/Linux
 connecting to ms sqlserver 2008, I have v 0.92 of freetds and unixODBC 2.3.0 installed, though they may not be in the usual places.

When I run the below code I get the following error:
FreeTDS: db-lib: exiting because client error handler returned INT_EXIT for msgno 20009

#include <stdio.h>
#include <unistd.h>
#include <sys/param.h>
#include <sybfront.h>
#include <sybdb.h>
#include <syberror.h>

#define  UID       "usr"  // Information censored
#define  PWD       "pwd"
//#define  PROGNAME  "sqllive"
#define  DBSERVER  ""
#define  DBNAME    "RISDB"

int main(void)
  LOGINREC *login;
  DBPROCESS *dbconn;
  char hostname[MAXHOSTNAMELEN];
  int max_len = MAXHOSTNAMELEN;
  DBCHAR accession[10];
  DBCHAR examdesc[10];
  DBCHAR examcode[255];

  if (dbinit() == FAIL) {
    fprintf(stderr, "Could not init db.\n");
    return 1;
  /* Allocate a login params structure */
  if ((login = dblogin()) == FAIL) {
    fprintf(stderr, "Could not initialize dblogin() structure.\n");
    return 2;

  /* Initialize the login params in the structure */
  DBSETLPWD(login, PWD);
  if (gethostname(hostname, max_len) == 0)
    DBSETLHOST(login, hostname);
  /* Now connect to the DB Server */
  if ((dbconn = dbopen(login, DBSERVER)) == NULL) {
    fprintf(stderr, "Could not connect to DB Server: %s\n", DBSERVER);
    return 3;

Open in new window

Who is Participating?
UnderSevenAuthor Commented:
Right so here is what I had to do.   First you'll want the latest version of freetds (and in my oppinion, unixodbc) built on your instance.  Assuming you have this part done there are a few key files you need to be aware of, they are: freetds.conf, odbc.ini and odbcinst.ini (the odbc files only if you build unix odbc).

Also note that freetds will create a shared library space /usr/local/lib/ if one does not already exist.  

freetds.conf was the method I first used to connect and in fact the method tsql (test-sql) will use to test your connection.  It will probably reside in /usr/local/etc/freetds.conf

It will have lots of lines already generated, but to create a connection to a mssql server you'll want something like this:

# Live
        host =                            
        port = 1433
        tds version =7.0

Obviously I changed the ip for this post, but the gist is easy to get.  If you have DNS you can also just type in the host name, but I prefer to use straight ip addresses.  1433 is the default port for mssql server, tds version 7.0 seems to be the agreed on lowest version number you can use that will work.

If this is all set you should be able to now on a shell prompt type something like:
tsql -S MSSQLLIVE -U username -P password

This should then work, obviously I'm using my example where -S refers to the server as named in the freetds.conf file.

However, this method is not ideal for MSSQL, primarily because it seems to correspond to the library libsybdb.a when creating code, and if the name didn't give it away, this library seems to be primarily keyed to sybase.  It will work, but it will not be pleasant to work with.  For my above code example I should of entered this in the definitions:

#define  UID       "usr"  // Information censored
#define  PWD       "pwd"
#define  DBNAME    "RISDB"

This would of connected and retrieved data, but when dealing with columns it would not have been pretty.  Therefore I opted to use a different method.

For odbc you;'ll need those two files I mentioned before, if you do a find on them you should find them and they should be empty.  In odbcinst.ini for MSSQL you'll want something that looks like this:

Description=v0.63 with protocol v8.0

(where Driver corresponds to the path of that actual file)
And in odbc.ini you'll want something like this:
Driver          = /usr/local/lib/libtdsodbc.so
Description     = RIS LIVE
Trace           = /tmp/odbc.trace
Server          =
Port            = 1433
Database        = RISDB
TDS_VERSION     = 7.0

Where once again driver refers to a place the actual file resides.  Trace is for logging purposes and you can do that where ever you want.  I've had it said tmp is a pretty unsecure location to log at.  

This above part threw me for a while, because despite having had that entered, it still wouldn't work.  The reason why is because I assumed it was looking at one file for odbc.ini and it was in fact looking for another.  Use the tool osql to troubleshoot this and it will actually tell you where it thinks it is looking for files.  It turns out it was looking for a file called .odbc.ini which wasn't coming up in my find attempts for 'odbc.ini'.  Once I put the proper information in the file '.odbc.ini' where it was actually looking, everything worked.  

Using odbc you can use the library libtdsodbc.a for your code, which is going to have functions that closely, if not exactly match the ones microsoft provides for visual studio.  In fact if you look up help on the functions in this library they'll literally link you to microsoft's msdn site.  So this is the ideal method for connecting to and coding with a link to ms sql server.

Dave BaldwinFixer of ProblemsCommented:
FreeTDS and Unix ODBC have not been updated to connect to SQL Server 2005 and 2008.  In other questions about this, no one has been able to get them to work.  There are commercial companies with ODBC connections that apparently work but they are not free.  You might be able to hack the last versions of FreeTDS to work but I wouldn't know where to tell you to start on that.
UnderSevenAuthor Commented:
I'm confused why would I be able to connect with tsql but not in C?  In theory I could just scrape returned text from that and use it in code.  

So you're saying that the only way to do this is use the prompt tools I've already gotten to work and just scrape the text?  
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Dave BaldwinFixer of ProblemsCommented:
How are you using T-SQL on a Linux box?  What tools?

I'm saying that every question I've seen about using FreeTDS fails when they try to use it with SQL Server 2005 or 2008.  It does appear to work with SQL Server 2000 but Microsoft keeps changing the driver interface with the newer versions and FreeTDS has not been updated since 2004.
UnderSevenAuthor Commented:
When I say tsql I am referring to the freetds tool tsql, "Test sql" which is a diagnostic tool they give you to test the connection.  It allowed you to try connecting using freetds and then even running sql.  using the test sql utility I was able to connect to my sqlserver instance and run a query against one of the views, confirming that it worked.
For freetds connection you need to enable TCP/IP and NAMED pipes and restart the server service.
Dave BaldwinFixer of ProblemsCommented:
You must be doing something right.  I couldn't get the "Test sql" to work here.
UnderSevenAuthor Commented:
I don't see how enabling tcp/ip is at issue here, I've already successfully connected to the server, I just havn't managed to do it in code.  If I can do it from the testing sql prompt provided with freetds then wouldn't tcp/ip already have to be enabled?
Yes, but named pipes is an essential component fro freetds/jtds driver (or SQL2000 clients) to work at all.

btw you connect with empty hostname like having sybase on localhost
UnderSevenAuthor Commented:
Well I figured it out, it was in my configuration.

The odbc.ini and freetds.conf files were not being properly invoked in the above code.  Where I had an ip address listed, I needed a reference to the conf file in order for it to work, and work it does.
UnderSevenAuthor Commented:
I've requested that this question be deleted for the following reason:

The question was answered, but unfortunately none of the suggests were related to the ultimate solution.
Dave BaldwinFixer of ProblemsCommented:
Any chance you could post your files since no one else here seemed to get it to work for SQL 2005 / 2008?
UnderSevenAuthor Commented:
Sure.  I'll post my full solution when I return to work tomorrow.  Objecting to my own deletion request.
Dave BaldwinFixer of ProblemsCommented:
Thanks, looking forward to it.
2005 and up needs named pipes in addition to TCP/IP
you can always test by running some select from tsql
Dave BaldwinFixer of ProblemsCommented:
UnderSevenAuthor Commented:
I hate to give myself credit, though I do believe this answers the original question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.