Linux下使用FreeTDS访问MS SQL Server 2005数据库(包含C测试源码)

http://blog.csdn.net/helonsy/article/details/7207497

(1)安装freeTDS

FreeTDS为Linux系统提供了TDS协议的开源客户端。由于MS SQL和Sybase使用的恰是TDS协议，所以在Linux中可以用FreeTDS连接MS SQL。

官网：http://www.freetds.org

下载：wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

安装：
[root@vm01 ~]# tar -zxvf freetds-stable.tgz
[root@vm01 ~]# cd freetds-0.91
[root@vm01 freetds-0.91]# ./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib
[root@vm01 freetds-0.91]# make
[root@vm01 freetds-0.91]# make install

修改环境变量：
FREETDS_HOME=/usr/local/freetds
export PATH=$FREETDS_HOME/bin:$PATH

库文件加载：
[root@vm01 freetds-0.91]# vim /etc/ld.so.conf.d/freetds.conf
/usr/local/freetds/lib
[root@vm01 freetds-0.91]# ldconfig

(2) 修改配置文件,连接数据库
(i) 添加数据源
[root@vm01 test]# vim /usr/local/freetds/etc/freetds.conf
#zkl add
[SQL2005]
        host = 192.168.232.133
        port = 1433
        tds version=8.0
#client charset = ISO-8859-1
完成后，使用如下命令即可连接到 SQL Server 2005 .
[root@vm01 test]# tsql -S SQL2005 -U sa -P zkl
locale is "zh_CN.GB18030"
locale charset is "GB18030"
using default charset "GB18030"
1> use test [使用test数据库]
2> go
1> select * from StuInfo [查询表信息]
2> go
StuID Name Age
001 zyh 24
002 zkl 21
003 Jim 24
(3 rows affected)

(ii) 修改协议版本以正常连接SQL Server 2005
修改 freetds 配置文件
[root@vm01 test]# vim /usr/local/freetds/etc/freetds.conf
[global]
        # TDS protocol version
#;      tds version = 4.2

tds version=8.0 (这个前面不能有逗号)

连接 SQL SERVER 2005 需要使用的协议版本为 8.0，而使用 4.2 时，连接将会失败。使用 tsql 命令连接时，如果不像步骤(2)中那样配置数据源，则同样需要修改协议，然后才能使用如下命令正常连接数据库：
[root@vm01 test]# tsql -H 192.168.232.133 -p 1433 -U sa -P zkl

注意：第一个p为小写，后面的P是大写。

(3) 使用C++代码连接数据库
首先，确保协议版本是 8.0，然后编译以下C代码，测试与数据库的连接。

test.c:

 1     #include <stdio.h> 
 2     #include <string.h> 
 3     #include <stdlib.h> 
 4     #include <unistd.h>  
 5       
 6     #include <sybfront.h> //freetds头文件 
 7     #include <sybdb.h> //freetds 
 8       
 9       
10     int main(void) 
11     { 
12         char szUsername[32] = "sa"; 
13         char szPassword[32] = "zkl"; 
14         char szDBName[32] = "test"; //数据库名 
15         char szServer[32] = "192.168.232.133:1433";//数据库服务器:端口 
16       
17         //初始化db-library 
18         dbinit(); 
19             
20         //连接数据库 
21         LOGINREC *loginrec = dblogin(); 
22         DBSETLUSER(loginrec, szUsername);        
23         DBSETLPWD(loginrec, szPassword); 
24         DBPROCESS *dbprocess = dbopen(loginrec, szServer);//连接数据库 
25         if(dbprocess == FAIL) 
26         { 
27             printf("Conect to MS SQL SERVER fail, exit!
"); 
28             return -1;  
29         } 
30         printf("Connect to MS SQL SERVER success!
"); 
31             
32         if(dbuse(dbprocess, szDBName) == FAIL) 
33             printf("Open database failed!
"); 
34         else 
35             printf("Open database success!
"); 
36             
37         //查询数据库 
38         printf("[查询数据库表]
"); 
39         dbcmd(dbprocess, "select StuID, Name, Age from StuInfo"); 
40         if(dbsqlexec(dbprocess) == FAIL) 
41         { 
42             printf("Query table 'StuInfo' error.
"); 
43             return -1;  
44         } 
45           
46         DBINT result_code; 
47         char szStuID[20]={}; 
48         char szName[80]={}; 
49         char szAge[10]={}; 
50         int rows = 0; 
51         while ((result_code = dbresults(dbprocess)) != NO_MORE_RESULTS){ 
52             if (result_code == SUCCEED){ 
53                 dbbind(dbprocess, 1, CHARBIND, (DBINT)0, (BYTE*)szStuID); 
54                 dbbind(dbprocess, 2, CHARBIND, (DBCHAR)0, (BYTE*)szName); 
55                 dbbind(dbprocess, 3, CHARBIND, (DBCHAR)0, (BYTE*)szAge); 
56                 printf("StuID	Name	Age
", szStuID); 
57                 while (dbnextrow(dbprocess) != NO_MORE_ROWS){                         
58                     printf("%s	", szStuID); 
59                     printf("%s	", szName); 
60                     printf("%s
", szAge); 
61                 } 
62             } 
63         }        
64       
65         printf("[插入数据到数据库表]
"); 
66         dbcmd(dbprocess, "insert into StuInfo(StuID, Name, Age) values(888,'James',28)"); 
67         if(dbsqlexec(dbprocess) == FAIL) 
68         { 
69             printf("insert into table 'StuInfo' error.
"); 
70             return -1;  
71         } 
72         printf("insert into table 'StuInfo' success.
"); 
73          
74         printf("[删除数据库表中的记录]
"); 
75         dbcmd(dbprocess, "delete from StuInfo where StuID=888"); 
76         if(dbsqlexec(dbprocess) == FAIL) 
77         { 
78             printf("delete from table 'StuInfo' error.
"); 
79             return -1;  
80         } 
81         printf("delete from table 'StuInfo' success.
"); 
82          
83         //关闭数据库连接 
84         dbclose(dbprocess); 
85      
86         return 0; 
87     }

 1     #include <stdio.h>  
 2     #include <string.h>  
 3     #include <stdlib.h>  
 4     #include <unistd.h>   
 5        
 6     #include <sybfront.h> //freetds头文件  
 7     #include <sybdb.h> //freetds  
 8        
 9        
10     int main(void)  
11     {  
12         char szUsername[32] = "sa";  
13         char szPassword[32] = "zkl";  
14         char szDBName[32] = "test"; //数据库名  
15         char szServer[32] = "192.168.232.133:1433";//数据库服务器:端口  
16        
17         //初始化db-library  
18         dbinit();  
19              
20         //连接数据库  
21         LOGINREC *loginrec = dblogin();  
22         DBSETLUSER(loginrec, szUsername);         
23         DBSETLPWD(loginrec, szPassword);  
24         DBPROCESS *dbprocess = dbopen(loginrec, szServer);//连接数据库  
25         if(dbprocess == FAIL)  
26         {  
27             printf("Conect to MS SQL SERVER fail, exit!
");  
28             return -1;   
29         }  
30         printf("Connect to MS SQL SERVER success!
");  
31              
32         if(dbuse(dbprocess, szDBName) == FAIL)  
33             printf("Open database failed!
");  
34         else  
35             printf("Open database success!
");  
36              
37         //查询数据库  
38         printf("[查询数据库表]
");  
39         dbcmd(dbprocess, "select StuID, Name, Age from StuInfo");  
40         if(dbsqlexec(dbprocess) == FAIL)  
41         {  
42             printf("Query table 'StuInfo' error.
");  
43             return -1;   
44         }  
45            
46         DBINT result_code;  
47         char szStuID[20]={};  
48         char szName[80]={};  
49         char szAge[10]={};  
50         int rows = 0;  
51         while ((result_code = dbresults(dbprocess)) != NO_MORE_RESULTS){  
52             if (result_code == SUCCEED){  
53                 dbbind(dbprocess, 1, CHARBIND, (DBINT)0, (BYTE*)szStuID);  
54                 dbbind(dbprocess, 2, CHARBIND, (DBCHAR)0, (BYTE*)szName);  
55                 dbbind(dbprocess, 3, CHARBIND, (DBCHAR)0, (BYTE*)szAge);  
56                 printf("StuID	Name	Age
", szStuID);  
57                 while (dbnextrow(dbprocess) != NO_MORE_ROWS){                          
58                     printf("%s	", szStuID);  
59                     printf("%s	", szName);  
60                     printf("%s
", szAge);  
61                 }  
62             }  
63         }         
64        
65         printf("[插入数据到数据库表]
");  
66         dbcmd(dbprocess, "insert into StuInfo(StuID, Name, Age) values(888,'James',28)");  
67         if(dbsqlexec(dbprocess) == FAIL)  
68         {  
69             printf("insert into table 'StuInfo' error.
");  
70             return -1;   
71         }  
72         printf("insert into table 'StuInfo' success.
");  
73           
74         printf("[删除数据库表中的记录]
");  
75         dbcmd(dbprocess, "delete from StuInfo where StuID=888");  
76         if(dbsqlexec(dbprocess) == FAIL)  
77         {  
78             printf("delete from table 'StuInfo' error.
");  
79             return -1;   
80         }  
81         printf("delete from table 'StuInfo' success.
");  
82           
83         //关闭数据库连接  
84         dbclose(dbprocess);  
85       
86         return 0;  
87     }

Makefile:
-----------------------------------------------
default:
gcc test.c -o test -L/usr/local/freetds/lib -lsybdb -I/usr/local/freetds/include