当前位置: 首页 > 工具软件 > FreeTDS > 使用案例 >

Ubuntu下安装Freetds,并编程连接MSSQL

姚晋
2023-12-01

1. 环境
Ubuntu14.04.05
freetds-stable.tar.gz

2. 安装freetds
下载freetds : http://www.freetds.org
解压 : tar zxvf freetds-stable.tar.gz

cd freetds1.00

sudo ./configure --prefix=/usr/local/freetds --with-tdsver=8.0--enable-msdblib --enable-dbmfix --with-gnu-ld  --enable-shared --enable-static

sudo make
sudo make install

3. 配置freetds
sudo vi /etc/ld.so.conf
加入一行:
/usr/local/freetds/lib
然后运行以下指令使更改生效:
ldconfig

其他配置环境变量方法:

很多时候,我们的.h/.so/.a/bin文件都不在Linux发行版所指定的默认路径下,这时可以通过~/.bashrc来增加搜索路径。
#增加.so搜索路径
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/homexx/lib  
#增加.a搜索路径
export  LIBRARY_PATH=$LIBRARY_PATH:/home/xx/soft/lib
#增加bin搜索路径
export PATH=$PATH:/home/liheyuan/soft/bin 
#增加GCC的include文件搜索路径
export C_INCLUDE_PATH=$C_INCLUDE_PATH:/home/xx/soft/include 
#增加G++的include文件搜索路径
export CPLUS_INCLUDE_PATH=$CPLUS_INCLUDE_PATH:/home/xx/include

4. 编程c连接MSSQL
头文件:

#include <sybdb.h>

实例:

#include <stdio.h>
#include <sybdb.h>
#include <string.h>

#define SQLDBIP "192.168.1.158"
#define SQLDBPORT "1433"
#define SQLDBNAME "Tomonitor"
#define SQLDBUSER "sa"
#define SQLDBPASSWD "abc123"

#define SQLDBCMD "select id,aa,bb from tb_abc"
#define SQLDBSERVER SQLDBIP":"SQLDBPORT

int main()
{
printf("aaaaaaaaaaaaaaaaaa\n");
LOGINREC *loginrec;
DBPROCESS *dbprocess;
char DBTable_id[100];
char DBTable_name[250];
char DBTable_description[250];

dbinit();   //init db_library
loginrec = dblogin();
DBSETLUSER(loginrec,SQLDBUSER);
DBSETLPWD(loginrec,SQLDBPASSWD);

if((dbprocess = dbopen(loginrec,SQLDBSERVER)) == FAIL)
{
printf("SQLDB: Conect MS SQL SERVER Fail\n");
return 1;
}

printf("SQLDB: ConnectEMS Conect MS SQL SERVER Success\n");

if(dbuse(dbprocess,SQLDBNAME) == FAIL)
{
printf("SQLDB: Open database name Fail\n");
return 1;
}

printf("SQLDB: Open database name Success\n");

dbcmd(dbprocess,SQLDBCMD);
if(dbsqlexec(dbprocess) == FAIL)
{
printf("SQLDB: Select EXEC SQL sentence Error\n");
dbclose(dbprocess);
return 1;
}


printf("%-10s%-20s%-20s\n","ID","NAME","Description");
memset(DBTable_id,'\0',sizeof(DBTable_id));
memset(DBTable_name,'\0',sizeof(DBTable_name));
memset(DBTable_description,'\0',sizeof(DBTable_description));

if(dbresults(dbprocess) == SUCCEED)
{
dbbind(dbprocess,1,CHARBIND,(DBINT)0,(BYTE*)DBTable_id);
dbbind(dbprocess,2,CHARBIND,(DBINT)0,(BYTE*)DBTable_name);
dbbind(dbprocess,3,CHARBIND,(DBINT)0,(BYTE*)DBTable_description);

while(dbnextrow(dbprocess)!=NO_MORE_ROWS)
{
// printf("%s,%s,%s\n",DBTable_id,DBTable_name,DBTable_description);
printf("%s\t",DBTable_id);
printf("%s\t",DBTable_name);
printf("%s\n",DBTable_description);
}
}

char aa[30];
strcpy(aa,"insert into tb_abc(aa,bb,dd) values('23d','fwe','few')");
/*strcpy(aa,"update tb_abc set aa='abcabcabcabc' where id=");
char id[5];
int ID=2;
sprintf(id,"%d",ID);
strcat(aa,id);
strcat(aa,"");
printf("aa is:%s\n",aa);*/
//dbcmd(dbprocess,aa);
dbcmd(dbprocess,aa);
//dbcmd(dbprocess,"insert into tb_abc(aa,bb,dd) values('32d','fer3w','43r')");
if(dbsqlexec(dbprocess) == FAIL)
{
printf("SQLDB: insert EXEC SQL sentence Error\n");
dbclose(dbprocess);
return 1;
}

printf("SQLDB: insert EXEC SQL sentence Sucess\n");

dbcmd(dbprocess,SQLDBCMD);
if(dbsqlexec(dbprocess) == FAIL)
{
printf("SQLDB: Select EXEC SQL sentence Error\n");
dbclose(dbprocess);
return 1;
}


printf("%-10s%-20s%-20s\n","ID","NAME","Description");
memset(DBTable_id,'\0',sizeof(DBTable_id));
memset(DBTable_name,'\0',sizeof(DBTable_name));
memset(DBTable_description,'\0',sizeof(DBTable_description));

if(dbresults(dbprocess) == SUCCEED)
{
dbbind(dbprocess,1,CHARBIND,(DBINT)0,(BYTE*)DBTable_id);
dbbind(dbprocess,2,CHARBIND,(DBINT)0,(BYTE*)DBTable_name);
dbbind(dbprocess,3,CHARBIND,(DBINT)0,(BYTE*)DBTable_description);

while(dbnextrow(dbprocess)!=NO_MORE_ROWS)
{
// printf("%s,%s,%s\n",DBTable_id,DBTable_name,DBTable_description);
printf("%s\t",DBTable_id);
printf("%s\t",DBTable_name);
printf("%s\n",DBTable_description);
}
}

dbclose(dbprocess);
return 0;
}

5. 编译

gcc freetdstest.c -o freetdstest

error :

undefined reference to `dbopen' 
undefined reference to `dbinit'
undefined reference to `dbcmd' 

lib文件没有链接成功

gcc freetdstest.c -o freetdstest -lsybdb

链接参数(环境变量设置好后不用添加如下参数):
 -L/usr/local/freetds1.00/lib
 -I/usr/local/freetds1.00/include
 gcc freetdstest.c -o freetdstest -lsybdb -L/usr/local/freetds1.00/lib  -I/usr/local/freetds1.00/include

编译成功。
6. 测试链接MSSQL

@ubuntu:~/Documents$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.00.14
             freetds.conf directory: /usr/local/freetds/etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: no
                             GnuTLS: no
                               MARS: no

查看版本

tsql -H 192.168.x.x  -p 1433 -U sa

error:

error 20009 (severity 9) unable to connect adaptive server is unavailable

分析:
freetds版本问题,修改配置文件
修改/usr/local/etc/freetds.conf,修改global项中tds version配置为:

[global]  
tds version = 7.0 
 类似资料: