Link to home
Start Free TrialLog in
Avatar of UnderSeven
UnderSeven

asked on

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  "1.1.1.1"
#define  DBNAME    "RISDB"



int main(void)
{
printf("0\n");
  LOGINREC *login;
printf("00\n");
  DBPROCESS *dbconn;
printf("1\n");
  char hostname[MAXHOSTNAMELEN];
printf("2\n");
  int max_len = MAXHOSTNAMELEN;
printf("3\n");
  DBCHAR accession[10];
printf("4\n");
  DBCHAR examdesc[10];
printf("5\n");
  DBCHAR examcode[255];


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

  /* Initialize the login params in the structure */
  DBSETLUSER(login, UID);
  DBSETLPWD(login, PWD);
//  DBSETLAPP(login, PROGNAME);
printf("3\n");
  if (gethostname(hostname, max_len) == 0)
    DBSETLHOST(login, hostname);
printf("4\n");
  /* 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

Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

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.
Avatar of UnderSeven
UnderSeven

ASKER

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?  
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.
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.
You must be doing something right.  I couldn't get the "Test sql" to work here.
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
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.
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.
Any chance you could post your files since no one else here seemed to get it to work for SQL 2005 / 2008?
Sure.  I'll post my full solution when I return to work tomorrow.  Objecting to my own deletion request.
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
ASKER CERTIFIED SOLUTION
Avatar of UnderSeven
UnderSeven

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.
I hate to give myself credit, though I do believe this answers the original question.