Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-31
17
Medium Priority
?
3,207 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:UnderSeven
  • 8
  • 6
  • 3
17 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37059626
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.
0
 

Author Comment

by:UnderSeven
ID: 37059667
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?  
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37059740
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:UnderSeven
ID: 37060452
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.
0
 
LVL 62

Expert Comment

by:gheist
ID: 37061138
For freetds connection you need to enable TCP/IP and NAMED pipes and restart the server service.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37061166
You must be doing something right.  I couldn't get the "Test sql" to work here.
0
 

Author Comment

by:UnderSeven
ID: 37063654
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?
0
 
LVL 62

Expert Comment

by:gheist
ID: 37063889
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
0
 

Author Comment

by:UnderSeven
ID: 37066239
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.
0
 

Author Comment

by:UnderSeven
ID: 37067641
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.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37066506
Any chance you could post your files since no one else here seemed to get it to work for SQL 2005 / 2008?
0
 

Author Comment

by:UnderSeven
ID: 37067642
Sure.  I'll post my full solution when I return to work tomorrow.  Objecting to my own deletion request.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37067987
Thanks, looking forward to it.
0
 
LVL 62

Expert Comment

by:gheist
ID: 37068473
2005 and up needs named pipes in addition to TCP/IP
you can always test by running some select from tsql
0
 

Accepted Solution

by:
UnderSeven earned 0 total points
ID: 37069701
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
[MSSQLLIVE]
        host = 141.255.255.255                            
        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  DBSERVER  MSSQLLIVE
#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:

[FreeTDS]
Description=v0.63 with protocol v8.0
Driver=/usr/local/lib/libtdsodbc.so
UsageCount=1

(where Driver corresponds to the path of that actual file)
And in odbc.ini you'll want something like this:
[MSSQLLIVE]
Driver          = /usr/local/lib/libtdsodbc.so
Description     = RIS LIVE
Trace           = /tmp/odbc.trace
Server          = 255.255.255.255
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.



0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37071121
Thanks.
0
 

Author Closing Comment

by:UnderSeven
ID: 37092859
I hate to give myself credit, though I do believe this answers the original question.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tech tip describes how to install the Solaris Operating System from a tape backup that was created using the Solaris flash archive utility. I have used this procedure on the Solaris 8 and 9 OS, and it shoudl also work well on the Solaris 10 rel…
Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
The goal of this video is to provide viewers with basic examples to understand and use pointers in the C programming language.
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question