FreeTDS 是一個 Linux 函式庫，他重新實作了 TDS(Tabular Data Stream, 表列資料串流?) 協定，讓在 Linux 平台運行的程式也可以透過此函式庫存取支援 TDS 的 Sybase SQL 或是 MS SQL Server 資料庫。本篇文章針對如何使用 FreeTDS 存取 MS SQL 做簡易教學。
TDS
TDS 是一個應用層(Application layer)協定，最初由 Sybase 開發，後來在微軟與 Sybase 終止合作關係後便各自發展。也因此兩者之間的 TDS 雖然基於相同的基礎，但後續實作有所不同，細節可以參考 Sybase 與微軟的線上手冊。而重新實作 TDS 的 FreeTDS 也並非支援所有 API，其支援 API 可參考 User Guide.
Installation
在 Ubuntu 或是 Debian 上可以直接透過套件管理程式 apt-get 安裝
$ sudo apt-get install freetds-dev tds-odbc libsybdb5 libct4libdbd-freetds freetds-common freetds-bin
修改 /etc/freetds/freetds.conf，在設定檔下方加入所要連接的 MS SQL Server 資訊：
[server_name] # 主機名稱
host = 111.222.33.44 # IP位置
port = 1433 # Port 預設為 1433
tds version = 7.0 # TDS 版本，參考對應表
完成後再 shell 命令列作個簡易的測試，確認目前的設定已經可以連接 MS SQL Server
$ tsql -S server_name -U username Password: locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1>
正確連接至 SQL Server 將會出現 1> 符號，若有出現錯誤訊息則需再重新檢視設定。
Initialization
以下為參考官方 User Guide 建立的簡單範例第一部分。這段程式碼主要執行了資料庫的連接與關閉，對於 Query 的方式會在後面一段說明，屆時再將 Query 的程式碼插入到註釋 “add source code later” (highlight) 的部分即可。
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <assert.h>
#include <errno.h>
#include <unistd.h>
#include <libgen.h>
#include <syslog.h>
#include <sybfront.h> /* sybfront.h always comes first */
#include <sybdb.h> /* sybdb.h is the only other file you need */
#define DEBUG_PRINT(...) printf(__VA_ARGS__)
#define SQLSERVER_IP "111.222.33.44:1433"
#define SQLSERVER_USER "MyUsername"
#define SQLSERVER_PSWD "MyPassword"
#define SQLSERVER_DB "MyDatabase"
#define SQLSERVER_TESTCMD "SELECT * FROM sample_table"
/* message handler */
int msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity,
char *msgtext, char *srvname, char *procname, int line)
{
enum {changed_database = 5701, changed_language = 5703 };
char log[256], temp[128];
if (msgno == changed_database || msgno == changed_language)
return 0;
if (msgno > 0) {
sprintf(log, "Msg %ld, Level %d, State %d\n",
(long) msgno, severity, msgstate);
fprintf(stderr, "Msg %ld, Level %d, State %d\n",
(long) msgno, severity, msgstate);
if (strlen(srvname) > 0) {
sprintf(temp, "Server '%s', ", srvname);
strcat(log, temp);
}
if (strlen(procname) > 0) {
sprintf(temp, "Procedure '%s', ", procname);
strcat(log, temp);
}
if (line > 0) {
sprintf(temp, "Line %d", line);
strcat(log, temp);
}
sprintf(temp, "\n");
strcat(log, temp);
fprintf(stderr, "%s", log);
syslog(LOG_ERR, "%s", log);
}
fprintf(stderr, "%s\n", msgtext);
syslog(LOG_ERR, "%s", msgtext);
if (severity > 10) {
fprintf(stderr, "error: severity %d > 10, exiting\n", severity);
syslog(LOG_ERR, "error: severity %d > 10, exiting\n", severity);
exit(severity);
}
return 0;
}
/* error handler */
int err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr,
char *dberrstr, char *oserrstr)
{
if (dberr) {
fprintf(stderr, "Msg %d, Level %d\n %s\n\n",
dberr, severity, dberrstr);
syslog(LOG_ERR, "Msg %d, Level %d\n %s",
dberr, severity, dberrstr);
} else {
fprintf(stderr, "DB-LIBRARY error:\n\t%s\n", dberrstr);
syslog(LOG_ERR, "DB-LIBRARY error:\n\t%s", dberrstr);
}
return INT_CANCEL;
}
int init_sql(DBPROCESS **db)
{
LOGINREC *login;
/* initial db-library */
if (dbinit() == FAIL) {
DEBUG_PRINT("%s: dbinit() failed\n", __FUNCTION__);
return -1;
}
/* setup error/message handler */
dberrhandle(err_handler);
dbmsghandle(msg_handler);
/* setup username and password */
if ((login = dblogin()) == NULL) {
DEBUG_PRINT("%s: unable to allocate login structure\n", __FUNCTION__);
return -1;
}
DBSETLUSER(login, SQLSERVER_USER);
DBSETLPWD(login, SQLSERVER_PSWD);
/* connect to SQL server */
*db = dbopen(login, SQLSERVER_IP);
if(*db == NULL) {
DEBUG_PRINT("%s: unable to connect to %s as %s\n",
__FUNCTION__, SQLSERVER_IP, SQLSERVER_USER);
return -1;
}
if (dbuse(*db, SQLSERVER_DB) == FAIL) {
DEBUG_PRINT("%s: unable to use to database %s\n",
__FUNCTION__, SQLSERVER_DB);
return -1;
} else {
DEBUG_PRINT("Open database success!\n");
}
return 0;
}
int main(int argc, char *argv[])
{
DBPROCESS *dbproc;
RETCODE rc;
init_sql(&dbproc);
/*** add source code later ***/
/*** add source code later ***/
dbclose(dbproc);
dbexit();
exit(0);
}
該略說明程式碼：header file 務必加入 sybfront.h 與 sybdb.h 兩個檔案。接下來定義的部分需要按照使用者實際的情況填入 IP:Port、登入名稱、密碼與資料庫名稱等資訊。SQLSERVER_TESTCMD 則是後面會執行的 SQL 語法，也必須確保可以在資料庫找到對應的資料以利這份測試程式可以順利工作。init_sql() 函式為初始化的重點，主要執行內容包含：
- dbinit() 初始化 DB-Library
- 設定 error handler 與 message handler。這並非必要，但是可以協助開發者與使用者在遇到問題時提供多一些資訊。
- 設定登入使用者名稱與密碼
- dbopen() 連接 SQL Server，同時會建立資料結構回傳至 DBPROCESS 指標。
- dbuse() 選擇要使用的資料庫。
此外，main() 函式的最後則有 dbclose() 與 dbexit() 做關閉的動作。目前為止這樣的程式就可以與 SQL Server 連接了！即便沒有任何 Query 資料的動作，我們也可以在此先驗證與資料庫的網路與設定上是否可以連接。直接利用 CLI 編譯程式：
$ gcc freetds_test.c -lsybdb -Wall -o freetds_test
Query
接下來是 query 資料的部分，下面的程式段應該插入到上面行號 137 的地方。
/* query */
dbcmd(dbproc, SQLSERVER_TESTCMD);
if (dbsqlexec(dbproc) == FAIL) {
DEBUG_PRINT("dbcmd() failed.\n");
exit(-1);
}
/* fetch result */
while ((rc = dbresults(dbproc)) != NO_MORE_RESULTS) {
struct COL {
char *name;
char *buffer;
int type, size, status;
} *columns, *pcol;
int ncols;
int row_code;
if (rc == FAIL) {
DEBUG_PRINT("dbresults failed\n");
exit(1);
}
ncols = dbnumcols(dbproc);
if ((columns = calloc(ncols, sizeof(struct COL))) == NULL) {
perror(NULL);
exit(1);
}
/*
* Read metadata and bind.
*/
for (pcol = columns; pcol - columns < ncols; pcol++) {
int c = pcol - columns + 1;
pcol->name = dbcolname(dbproc, c);
pcol->type = dbcoltype(dbproc, c);
pcol->size = dbcollen(dbproc, c);
if (SYBCHAR != pcol->type) {
pcol->size = dbwillconvert(pcol->type, SYBCHAR);
}
printf("%*s ", pcol->size, pcol->name);
if ((pcol->buffer = calloc(1, pcol->size + 1)) == NULL){
perror(NULL);
exit(1);
}
rc = dbbind(dbproc, c, NTBSTRINGBIND,
pcol->size+1, (BYTE*)pcol->buffer);
if (rc == FAIL) {
DEBUG_PRINT("dbbind(%d) failed\n", c);
exit(1);
}
rc = dbnullbind(dbproc, c, &pcol->status);
if (rc == FAIL) {
DEBUG_PRINT("dbnullbind(%d) failed\n", c);
exit(1);
}
}
printf("\n");
/*
* Print the data to stdout.
*/
while ((row_code = dbnextrow(dbproc)) != NO_MORE_ROWS){
switch (row_code) {
case REG_ROW:
for (pcol=columns; pcol - columns < ncols; pcol++) {
char *buffer = pcol->status == -1?
"NULL" : pcol->buffer;
printf("%*s ", pcol->size, buffer);
}
printf("\n");
break;
case BUF_FULL:
assert(row_code != BUF_FULL);
break;
case FAIL:
DEBUG_PRINT("dbresults failed\n");
exit(1);
break;
default:
printf("Data for computeid %d ignored\n", row_code);
}
}
/* free metadata and data buffers */
for (pcol=columns; pcol - columns < ncols; pcol++) {
free(pcol->buffer);
}
free(columns);
/*
* Get row count, if available.
*/
if (DBCOUNT(dbproc) > -1)
DEBUG_PRINT("%d rows affected\n", DBCOUNT(dbproc));
/*
* Check return status
*/
if (dbhasretstat(dbproc) == TRUE) {
printf("Procedure returned %d\n", dbretstatus(dbproc));
}
}
前面 dbcmd() 是將字串指令加入到 command buffer (視實際使用情況，可以使用 dbfcmd() 取代)，可以將多組字串分次寫入 buffer 中，原先 buffer 內的值並不會被覆蓋，直到執行到 dbsqlexec() 才會正式將指令送入 SQL Server 同時清空 command buffer。然後透過 dbresults() 取得執行結果，SQL Server 的 Reference 上說明此程式必須(MUST)呼叫到其回傳 NO_MORE_RESULT 為止，因此這邊的範例程式也是如此做法。
最後則是將結果取回，bind 到對應的參數取得正確的數值：
- 呼叫 dbnumcols() 取得回傳資料共有多少欄位。若命令回傳的欄位數目是固定的，則可以跳過此 API。
- 使用取得個欄位的名稱 dbcolname()、類別 dbcoltype()、與長度 dbcollen()。同樣如果已經了解個欄位的名稱與型別則可略過此步。
- 重點 API dbbind() 是將欄位對應到要儲存的參數。注意！執行 dbbind() 並非立即將欄位值取回，而是設定該欄位的參數。而欄位的值也有可能是 NULL，所以要以 dbnullbind() 檢查該欄位是否為 NULL，以免造成後續資料的處理錯誤。
- bind 所有需要取值的欄位後，呼叫 dbnextrow() 取得下一列的資料，如果還未執行過則是從第一列開始取值。微軟也強烈建議此 API 需要連續呼叫直到其返回 NO_MORE_ROWS 為止，這裡我們也配合其建議撰寫。接著判斷是否取得正確的Regular Row，若是則印出該列所有元素。
- 最後在釋放釋放所有動態配置記憶體後，執行 dbhasretstat() 與 dbretstatus() 詢問 Sever 是否有任何回傳狀態。
結論
透過 FreeTDS 這個函式庫可以簡單且高效率的存取 MS SQL Server，不需要再使用 ODBC 去實作。
延伸閱讀
- DB-Library for C Reference – 微軟 SQL Server 線上參考手冊
- DB-Library for C Reference – Sybase 線上參考手冊
- ODBC from C tutorial – Linux ODBC API Programming
- TDS 5.0 Funcational Specification – Sybase TDS 規範
- Tabular Data Stream Protocol – 微軟 TDS 規範