在Linux下连接MSSQL是一件很痛苦的事，因为微软同志没有提供任何接口给开发人员，大约他们认为要用MSSQL的，只可能是windows的操作系统。还好，MSSQL是从Sybase衍生出来的，有一些哥们做了一些Sybase的Linux下的连接库，这些连接库同时也能支持MSSQL，FreeTDS就是这样的一个东东。 这篇文章的受用读者，我想是那些希望在Linux或Unix下编写C或C++程序来连接MSSQL2000的兄弟们，因为我就是这样的。同时，那些写PHP的哥们也可以参考一下，当然如果你是用PHP的，你恐怕还要知道APACHE以及PHP脚本的安装，或者关于PHP自定义Module的开发，可以参考我以前写的一篇Blog（PHP5自定义Module开发）。下面开始我们的探索之旅：一、相关软件
首先我们需要FreeTDS的安装包，可以在这个地址下载到：http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz现在的最新版是0.82其次就是大家需要自己搭建C++的开发环境了。二、软件安装、配置# tar zxvf freetds-stable.tgz（解压）# ./configure --prefix=/usr/local/freetds /（指定FreeTDS安装路径） --with-tdsver=8.0 --enable-msdblib （设置TDS版本，支持SQL Server 2000）# make # make install 将freetds的库文件所在路径配置到LD_LIBRARY_PATH参数中： $ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/freetds/lib/: 这么作的目的是为了避免加载FreeTds库文件加载不上的情况。三、程序开发不多说了，还是直接上代码：/* * SyBaseManager.h * * Created .: Feb 18, 2009 * Author: Steven Wee */
#ifndef SYBASEMANAGER_H_ #define SYBASEMANAGER_H_
#include "../Common/CheckStringTools.h"
#include <string> #include <vector> #include <iostream> #include <assert.h> #include <errno.h> #include <stdlib.h> #include <string.h>
#include <sybfront.h> #include <sybdb.h>
using namespace std;
class SybaseManager { public: SybaseManager(std::string hosts, std::string userName, std::string password, std::string dbName, unsigned int port); ~SybaseManager(); /* * Init SQL Server * @param hosts: Host IP address * @param userName: Login UserName * @param password: Login Password * @param dbName: Database Name * @param port: Host listen port number */ void initConnection(); /* * Making query from database * @param mysql: MySQL Object * @param sql: Running SQL command */ bool runSQLCommand(std::string sql); /** * Destroy MySQL object * @param mysql MySQL object */ void destroyConnection(); bool getConnectionStatus(); vector<vector<string> > getResult(); protected: void setUserName(std::string userName); void setHosts(std::string hosts); void setPassword(std::string password); void setDBName(std::string dbName); void setPort(unsigned int port); private: bool IsConnected; DBPROCESS *dbProcess; vector< vector<string> > resultList; unsigned int DEFAULTPORT; char * HOSTS; char * USERNAME; char * PASSWORD; char * DBNAME; };
#endif /* SYBASEMANAGER_H_ */
/* * SyBaseManager.cpp * * Created .: Feb 18, 2009 * Author: Steven Wee */ #include "SybaseManager.h"
SybaseManager::SybaseManager(std::string hosts, std::string userName, std::string password, std::string dbName, unsigned int port) { IsConnected = false; this ->setHosts(hosts); this ->setUserName(userName); this ->setPassword(password); this ->setDBName(dbName); this ->setPort(port); }
SybaseManager::~SybaseManager() { destroyConnection(); }
void SybaseManager::setDBName(string dbName) { if ( dbName.empty() ) { std::cout << "DBName is null! Used default value: master" << std::endl; this ->DBNAME = new char[5]; strcpy(this ->DBNAME, "master"); } else { this ->DBNAME = new char[dbName.length()]; strcpy(this ->DBNAME, dbName.c_str()); } }
void SybaseManager::setHosts(string hosts) { if ( hosts.empty() ) { std::cout << "Hosts is null! Used default value: localhost" << std::endl; this ->HOSTS = new char[9]; strcpy(this ->HOSTS, "localhost"); } else { this ->HOSTS = new char[hosts.length()]; strcpy(this ->HOSTS, hosts.c_str()); } }
void SybaseManager::setPassword(string password) { if ( password.empty() ) { std::cout << "Password is null! Used default value: " << std::endl; this ->PASSWORD = new char[1]; strcpy(this ->PASSWORD, ""); } else { this ->PASSWORD = new char[password.length()]; strcpy(this ->PASSWORD, password.c_str()); } }
void SybaseManager::setPort(unsigned int port) { if ( port ) { std::cout << "Port number is null! Used default value: 0" << std::endl; this ->DEFAULTPORT = 0; } else { this ->DEFAULTPORT = port; } }
void SybaseManager::setUserName(string userName) { if ( userName.empty() ) { std::cout << "UserName is null! Used default value: sa" << std::endl; this ->USERNAME = new char[4]; strcpy(this ->USERNAME, "sa"); } else { this ->USERNAME = new char[userName.length()]; strcpy(this ->USERNAME, userName.c_str()); } }
void SybaseManager::initConnection() { string Charset = "UTF-8"; dbinit(); LOGINREC *loginREC = dblogin(); DBSETLUSER(loginREC, this ->USERNAME); DBSETLPWD(loginREC, this ->PASSWORD); DBSETLCHARSET(loginREC, Charset.c_str()); dbProcess = dbopen(loginREC, this ->HOSTS); if ( dbProcess == FAIL ) { std::cout << "Connect to SQL Server failed!" << std::endl; } if ( dbuse( dbProcess, this ->DBNAME ) == FAIL ) { std::cout << "Use table failed!" << std::endl; } }
bool SybaseManager::runSQLCommand( string sql ) { dbcmd(dbProcess, sql.c_str()); if ( dbsqlexec(dbProcess) == FAIL ) { std::cout << "Query from database failed!" << std::endl; } DBINT result_code; vector<string> objectValue; StringTools stringTools;
sql = stringTools.filterString(sql);
while ( (result_code = dbresults(dbProcess)) != NO_MORE_RESULTS ) { struct Column { char* colName; char* colBuffer; int colType, colSize, colStatus; } *columns, *pCol; int nColumns; int rowNo; if ( result_code == SUCCEED ) { nColumns = dbnumcols(dbProcess); if ( (columns = (Column*)calloc(nColumns, sizeof(struct Column))) == NULL ) { std::cout << "Error at bind data" << std::endl; return false; } for ( pCol = columns; pCol - columns < nColumns; pCol++ ) { int colNo = pCol - columns + 1; pCol ->colName = dbcolname(dbProcess, colNo); pCol ->colType = dbcoltype(dbProcess, colNo); pCol ->colSize = dbcollen(dbProcess, colNo); if ( SYBCHAR != pCol ->colType ) { pCol ->colSize = dbwillconvert(pCol ->colType, SYBCHAR); }
if ( (pCol ->colBuffer = (char*)calloc(1, pCol ->colSize + 1)) == NULL ) { std::cout << "Check column buffer error!" << std::endl; return false; }
if ( dbbind(dbProcess, colNo, STRINGBIND, pCol ->colSize + 1, (BYTE*)pCol ->colBuffer) == FAIL ) { std::cout << "Running dbbind() error!" << std::endl; return false; }
if ( dbnullbind(dbProcess, colNo, &pCol ->colStatus) == FAIL ) { std::cout << "Running dbnullbind() error!" << std::endl; return false; } }
while ( (rowNo = dbnextrow(dbProcess)) != NO_MORE_ROWS ) { objectValue.clear(); switch ( rowNo ) { case REG_ROW: for ( pCol = columns; pCol - columns < nColumns; pCol++ ) { const char* columnBuffer = pCol ->colStatus == -1 ? "NULL" : pCol ->colBuffer; objectValue.push_back(stringTools.Trim(columnBuffer)); // std::cout << columnBuffer << std::endl; } break; case BUF_FULL: assert( rowNo != BUF_FULL ); break; case FAIL: std::cout << "Get result error!" << std::endl; break; default: std::cout << "Get result ignore, row number:" << rowNo << std::endl; } this ->resultList.push_back(objectValue); } for ( pCol = columns; pCol - columns < nColumns; pCol++ ) { free( pCol ->colBuffer ); } free( columns ); /* if ( DBCOUNT(dbProcess) > -1 ) { std::cout << "Affected rows:" << DBCOUNT(dbProcess) << std::endl; } */ if ( dbhasretstat(dbProcess) == TRUE ) { std::cout << "Procedure returned " << dbhasretstat(dbProcess) << std::endl; } } } return true; }
void SybaseManager::destroyConnection() { dbclose(dbProcess); }
bool SybaseManager::getConnectionStatus() { return IsConnected; }
vector< vector<string> > SybaseManager::getResult() { return this ->resultList; }
四、修改建议本人在以后的完善中，打算把runSQLCommand(char * sql)函数分解成两个或者三个函数，分别执行select和insert等语句。在程序中，我并没有强制要求参数必须为const，可能会出现一些安全问题。本文仅起抛砖引玉的作用，希望有高手可以指点我程序中的问题。
