Accessing MS SQL Server 2005 database (including C test source) using FreeTDS under Linux

Keywords: Database SQL Linux vim

(1) Installing freeTDS

FreeTDS provides an open source client of TDS protocol for Linux system. Because MS SQL and Sybase use the TDS protocol, they can connect MS SQL with FreeTDS in Linux.

Official website: http://www.freetds.org

Download: wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

Installation:
[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

Modify environment variables:
FREETDS_HOME=/usr/local/freetds
export PATH=$FREETDS_HOME/bin:$PATH

Library file loading:
[root@vm01 freetds-0.91]# vim /etc/ld.so.conf.d/freetds.conf
/usr/local/freetds/lib
[root@vm01 freetds-0.91]# ldconfig

(2) Modify the configuration file and connect to the database
(i) Adding data sources
[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
After completion, you can connect to SQL Server 2005 using the following commands.
[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 [using test database]
2> go
1 > select * from StuInfo [Query Table Information]
2> go
StuID Name   Age
001 zyh   24
002 zkl   21
003 Jim   24
(3 rows affected)

(ii) Modify protocol version to connect to SQL Server 2005 normally
Modify freetds configuration file
[root@vm01 test]# vim /usr/local/freetds/etc/freetds.conf 
[global]
        # TDS protocol version
#;      tds version = 4.2

tds version=8.0 (there can be no comma before this)

Connecting to SQL SERVER 2005 requires a protocol version of 8.0, while using 4.2, the connection will fail. When using the tsql command to connect, if you do not configure the data source as in step (2), you also need to modify the protocol before you can normally connect to the database using the following commands:
[root@vm01 test]# tsql -H 192.168.232.133 -p 1433 -U sa -P zkl

Note: The first p is lowercase and the last P is capitalized.

(3) Connecting database with C++ code
First, make sure that the protocol version is 8.0, then compile the following C code to test the connection to the database.

test.c:
-----------------------------------------------
  1. #include <stdio.h> 
  2. #include <string.h> 
  3. #include <stdlib.h> 
  4. #include <unistd.h>  
  5.   
  6. #Include <sybfront.h>//freetds header file 
  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"; //Database name 
  15.     char szServer[32] = "192.168.232.133:1433";//Database Server: Port 
  16.   
  17.     //Initialize db-library 
  18.     dbinit(); 
  19.         
  20.     //Connect to the database 
  21.     LOGINREC *loginrec = dblogin(); 
  22.     DBSETLUSER(loginrec, szUsername);        
  23.     DBSETLPWD(loginrec, szPassword); 
  24.     DBPROCESS *dbprocess = dbopen(loginrec, szServer);//Connect to the database 
  25.     if(dbprocess == FAIL) 
  26.     { 
  27.         printf("Conect to MS SQL SERVER fail, exit!\n"); 
  28.         return -1;  
  29.     } 
  30.     printf("Connect to MS SQL SERVER success!\n"); 
  31.         
  32.     if(dbuse(dbprocess, szDBName) == FAIL) 
  33.         printf("Open database failed!\n"); 
  34.     else 
  35.         printf("Open database success!\n"); 
  36.         
  37.     //query data base 
  38.     printf("[Query database tables]\n"); 
  39.     dbcmd(dbprocess, "select StuID, Name, Age from StuInfo"); 
  40.     if(dbsqlexec(dbprocess) == FAIL) 
  41.     { 
  42.         printf("Query table 'StuInfo' error.\n"); 
  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\tName\tAge\n", szStuID); 
  57.             while (dbnextrow(dbprocess) != NO_MORE_ROWS){                         
  58.                 printf("%s\t", szStuID); 
  59.                 printf("%s\t", szName); 
  60.                 printf("%s\n", szAge); 
  61.             } 
  62.         } 
  63.     }        
  64.   
  65.     printf("[Insert data into database tables]\n"); 
  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.\n"); 
  70.         return -1;  
  71.     } 
  72.     printf("insert into table 'StuInfo' success.\n"); 
  73.      
  74.     printf("[Delete records from database tables]\n"); 
  75.     dbcmd(dbprocess, "delete from StuInfo where StuID=888"); 
  76.     if(dbsqlexec(dbprocess) == FAIL) 
  77.     { 
  78.         printf("delete from table 'StuInfo' error.\n"); 
  79.         return -1;  
  80.     } 
  81.     printf("delete from table 'StuInfo' success.\n"); 
  82.      
  83.     //Close database connection 
  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 header file  
  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"; //Database name  
  15.     char szServer[32] = "192.168.232.133:1433";//Database Server: Port  
  16.    
  17.     //Initialize db-library  
  18.     dbinit();  
  19.          
  20.     //Connect to the database  
  21.     LOGINREC *loginrec = dblogin();  
  22.     DBSETLUSER(loginrec, szUsername);         
  23.     DBSETLPWD(loginrec, szPassword);  
  24.     DBPROCESS *dbprocess = dbopen(loginrec, szServer);//Connect to the database  
  25.     if(dbprocess == FAIL)  
  26.     {  
  27.         printf("Conect to MS SQL SERVER fail, exit!\n");  
  28.         return -1;   
  29.     }  
  30.     printf("Connect to MS SQL SERVER success!\n");  
  31.          
  32.     if(dbuse(dbprocess, szDBName) == FAIL)  
  33.         printf("Open database failed!\n");  
  34.     else  
  35.         printf("Open database success!\n");  
  36.          
  37.     //query data base  
  38.     printf("[Query database tables]\n");  
  39.     dbcmd(dbprocess, "select StuID, Name, Age from StuInfo");  
  40.     if(dbsqlexec(dbprocess) == FAIL)  
  41.     {  
  42.         printf("Query table 'StuInfo' error.\n");  
  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\tName\tAge\n", szStuID);  
  57.             while (dbnextrow(dbprocess) != NO_MORE_ROWS){                          
  58.                 printf("%s\t", szStuID);  
  59.                 printf("%s\t", szName);  
  60.                 printf("%s\n", szAge);  
  61.             }  
  62.         }  
  63.     }         
  64.    
  65.     printf("[Insert data into database tables]\n");  
  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.\n");  
  70.         return -1;   
  71.     }  
  72.     printf("insert into table 'StuInfo' success.\n");  
  73.       
  74.     printf("[Delete records from database tables]\n");  
  75.     dbcmd(dbprocess, "delete from StuInfo where StuID=888");  
  76.     if(dbsqlexec(dbprocess) == FAIL)  
  77.     {  
  78.         printf("delete from table 'StuInfo' error.\n");  
  79.         return -1;   
  80.     }  
  81.     printf("delete from table 'StuInfo' success.\n");  
  82.       
  83.     //Close database connection  
  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

Posted by ypkumar on Wed, 13 Feb 2019 02:18:20 -0800

Hot Keywords

©2024 Programmer Group