[C++知识库]【c/c++】使用freetds操作SQL Server

官网：http://www.freetds.org/
文档：https://www.freetds.org/userguide/samplecode.html#e.g.samplecode.dblib.send

wget  http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar zxvf freetds-stable.tgz
cd freetds-0.91/
 ./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib
 make && make install
 export PATH=$PATH:/usr/local/freetds/bin/
 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/freetds/lib/
 export TDSVER=8.0
 source  /root/.bashrc
 ldconfig

没有 TDSVER=8.0 的环境变量会有 Unexpected EOF from the server 问题

#include<stdio.h>  
#include<string.h>  
#include<stdlib.h>  
#include<unistd.h>  
#include<sybfront.h>  
#include<sybdb.h>  
 
int err_handler(DBPROCESS*, int, int, int, char*, char*);
int msg_handler(DBPROCESS*, DBINT, int, int, char*, char*, char*, int);

/*  手动：tsql -H 192.168.1.188 -p1433 -U sa -Ppassword  */
/*  gcc test.cpp -L /usr/local/freetds/lib/ -lsybdb -I /usr/local/freetds/include/ */
int main(void)  
{    
    char szUsername[32]= "sa";  
    char szPassword[32]= "password";  
    char szDBName[32]= "databaseName";  
    char szServer[32]= "192.168.1.188:1433";        //服务器ip地址和端口号  
  
    //初始化db-library  
    if ( !dbinit() )
    {
	fprintf(stderr, "%s:%d: dbinit() failed\n",
			szServer, __LINE__);
			exit(1);
    }

    //连接数据库  
    LOGINREC *loginrec=dblogin();  

    dberrhandle(err_handler);
    dbmsghandle(msg_handler);

    // 设置登录的用户名
    DBSETLUSER(loginrec,szUsername);  
    // 设置登录密码
    DBSETLPWD(loginrec,szPassword);  
    // 连接sqlserver服务器地址和端口号,这里才是连接
    if (loginrec ){
        printf("loginrec success\n");  
    }

    DBPROCESS *dbprocess=dbopen(loginrec, szServer);  
    if(!dbprocess){  
        printf("Connect MSSQLSERVER fail\n");  
        return 0;  
    }else{  
        printf("Connect MSSQLSERVER success\n");  
    }  
    // 连接数据库
    if(dbuse(dbprocess,szDBName)==FAIL){  
        printf("Open data basename fail\n");  
    }else{  
        printf("Open data basename success\n");  
    }  
    // 查询数据库中表中的内容
    dbcmd(dbprocess,"select * from Table1");  //查询数据表中的内容，更具实际情况更改数据表名称  
    // 执行命令
    if(dbsqlexec(dbprocess)==FAIL){  
        printf("Query table error\n");  
    }  
    else   
        printf("Query table success\n");  
  
    DBINT result_code;  
    char szID[1024];  
    char szBeginTime[1024];  
    char szDescription[1024];  
    // 查看命令执行的结果。
    while((result_code=dbresults(dbprocess))!=NO_MORE_RESULTS)  
    {  
        if(result_code==SUCCEED){  
            // 查询第1列
            dbbind(dbprocess,1,CHARBIND,(DBINT)0,(BYTE*)szID);  
            // 查询第2列
            dbbind(dbprocess,2,CHARBIND,(DBCHAR)0,(BYTE*)szBeginTime);  
            // 查询第3列
            //dbbind(dbprocess,3,CHARBIND,(DBCHAR)0,(BYTE*)szDescription);  
            // 跳到下一行
            while(dbnextrow(dbprocess)!=NO_MORE_ROWS){  
                // 打印本行的数据
                printf("ID=%s\n",szID);  
                printf("Name=%s\n",szBeginTime);  
                //printf("szBeginTime=%s\n",szDescription);  
            }  
        }  
    }  
  
    //关闭数据库连接  
    dbclose(dbprocess);  
    return 0;  
}


int msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line)
{									//1
	enum {changed_database = 5701, changed_language = 5703 };	//2
	if (msgno == changed_database || msgno == changed_language)
		return 0;
	if (msgno > 0) {
		fprintf(stderr, "Msg %ld, Level %d, State %d\n",
				(long) msgno, severity, msgstate);
		if (strlen(srvname) > 0)
			fprintf(stderr, "Server '%s', ", srvname);
		if (strlen(procname) > 0)
			fprintf(stderr, "Procedure '%s', ", procname);
		if (line > 0)
			fprintf(stderr, "Line %d", line);
		fprintf(stderr, "\n\t");
	}
	fprintf(stderr, "%s\n", msgtext);
	if (severity > 10) {						//3
		fprintf(stderr, "%s: error: severity %d > 10, exiting\n",
		"188", severity);
		exit(severity);
	}
	return 0;							//4
}


int err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr,char *dberrstr, char *oserrstr)
{									//5
	if (dberr) {							//6
		fprintf(stderr, "%s: Msg %d, Level %d\n",
		"188", dberr, severity);
		fprintf(stderr, "%s\n\n", dberrstr);
	} else {
		fprintf(stderr, "%s: DB-LIBRARY error:\n\t", "188");
		fprintf(stderr, "%s\n", dberrstr);
	}

	return INT_CANCEL;						//7
}