在Linux下連接MSSQL是一件很痛苦的事，因為微軟同志沒有提供任何接口給開發人員，大約他們認為要用MSSQL的，只可能是windows的操作系統。還好，MSSQL是從Sybase衍生出來的，有一些哥們做了一些Sybase的Linux下的連接庫，這些連接庫同時也能支持MSSQL，FreeTDS就是這樣的一個東東。 這篇文章的受用讀者，我想是那些希望在Linux或Unix下編寫C或C++程序來連接MSSQL2000的兄弟們，因為我就是這樣的。同時，那些寫PHP的哥們也可以參考一下，當然如果你是用PHP的，你恐怕還要知道APACHE以及PHP腳本的安裝，或者關於PHP自定義Module的開發，可以參考我以前寫的一篇BlogPHP5自定義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，可能會出現一些安全問題。 本文僅起拋磚引玉的作用，希望有高手可以指點我程序中的問題。敬請期待下一篇文章：Linux下使用C/C++訪問數據庫——Oracle之OCI篇
本文出自 “玄武·巴依” 博客，轉載請與作者聯系！
