FreeTDS ile SqlServer Bağlantısı

Linux tabanlı çözüm kümelerinde her ne kadar pek kullanım alanı bulmasa da, ticari dünyada zaman zaman Microsoft SQL Server veritabanı sunucusuna bağlanmanız ve üzerinde işlem yapmanız gerekebilmektedir.

Java gibi yüksek seviyeli dillerde ODBC sürücüleri üzerinden çeşitli çözümler olmakla beraber bu bölümde biz C üzerinden en alt seviyede native protokolü kullanarak SqlServer ile haberleşme konusu üzerinde duracağız.

FreeTDS

FreeTDS, TDS (Tabular Data Stream) protokolünün LGPL lisanslı özgür bir gerçekleştirimidir.

Tabular Data Stream (TDS), bir veritabanı sunucusu ile ona bağlı istemciler arasındaki iletişim ve veri transferini modelleyen, TCP/IP tabanlı bir protokoldür.

Protokol Sybase Inc. tarafından geliştirilmiş ve ilk olarak 1984 yılında Sybase SQL Server ürününde kullanılmıştır.

1990 yılında Sybase ve Microsoft firmalarının aralarında yapmış oldukları teknoloji işbirliği anlaşmasını takiben, Microsoft firması da Sybase SQL Server kodunu temel alarak kendi veritabanı sunucusu olan SQL Server ürününü geliştirdi. Bu nedenle Microsoft SQL Server ürününde de TDS protokolü kullanılmaktadır.

Linux platformlarında TDS protokolünün FreeTDS gerçekleştirimi oldukça kararlı durumda olup, C dilinin yanı sıra Php, Ruby, C++ vb. dillerde de alt katmanda FreeTDS kullanan farklı kütüphane alternatifleri mevcuttur.

TDS protokolünün 5.0 versiyonu Sybase tarafından dokümante edilmiş olmakla birlikte, diğer versiyonlarına dair bilgiler genel kullanıma açılmamıştı. 2008 yılında Microsoft, daha önce hayata geçirdiği Open Specification Promise doğrultusunda TDS protokol detaylarını genel kullanıma açtı ve bu tarihten sonra kütüphaneler daha güvenilir hale geldi.

Not: TDS 5.0 versiyonu ile Sybase sunuculara bağlanılabiliyor olmasına karşın bu versiyon Microsoft tarafından desteklenmemektedir. Microsoft SQL Server bağlantıları için protokolün 7.X versiyonları kullanılmalıdır.

Konsol İstemcisi - Sqsh

Linux sistemlerde kullanılmak üzere, Sybase tarafından geliştirilen isql konsol arayüzündeki temel fonksiyonaliye ve ek olarak kullanım kolaylığı açısından bazı yeni fonksiyonlara sahip sqsh uygulaması geliştirilmiştir. Uygulamayı paket yöneticinizle aşağıdaki gibi sisteminize kurabilirsiniz:

$ sudo apt-get install sqsh

Sqsh ile bir sunucuya bağlanırken temel olarak aşağıdaki parametreler kullanılır:

Parametre	Açıklama
-S	Sunucu adresi
-U	Kullanıcı Adı
-P	Parola (parametre olarak girilmez ise konsolda tekrar sorulacaktır)
-D	Veritabanı Adı

Örnek olarak 172.16.2.139 ip adresindeki example_db veritabanına testuser kullanıcı adı ve tstpwd123 parolasıyla bağlanalım ve bolgeler tablosundaki kayıtları görelim:

$ sqsh -S 172.16.2.139 -U testuser -P tstpwd123 -D example_db
sqsh-2.1.7 Copyright (C) 1995-2001 Scott C. Gray
Portions Copyright (C) 2004-2010 Michael Peppler
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'

1> select * from bolgeler
2> go
 id          isim
 ----------- ---------------------------------------
           1 Akdeniz B�lgesi
           2 Dogu Anadolu B�lgesi
           3 Ege B�lgesi
           4 G�neydogu Anadolu B�lgesi
           5 I� Anadolu B�lgesi
           6 Marmara B�lgesi
           7 Karadeniz B�lgesi

Yukarıdaki sonuç kümesine baktığımızda bazı karakterlerin düzgün görüntülenmediğini, bazılarının ise değiştirildiğini görmekteyiz (ğ -> g vb.)

Sorunun çözümü için sunucuya bağlantı kurarken kullanılacak karakter seti kümesi olarak UTF-8'i belirtmemiz gereklidir. Her ne kadar sqsh uygulamasının yardım sayfasında -J UTF-8 gibi bir parametre geçirmek suretiye bu işlemin yapılabildiği yazsa da kullandığımız versiyonda (2.1.7) bu şekilde çözüm üretemedik. Karakter problemini, sunucu bazlı genel ayarlamaların yapılmasına imkan veren freetds.conf dosyası üzerinden yapacağımız tanımlamalarla çözeceğiz.

freetds.conf

FreeTDS kütüphanesi ile çalışırken öntanımlı olarak /etc/freetds/freetds.conf dosyası okunmaktadır.

Bu dosyada genel olarak kütüphanenin davranışını değiştirebilecek tanımlamalar bulunmaktadır. Ayrıca belirli bir SQL sunucu için özel ayarların da buradan yapılmasına imkan verilmektedir.

Dosyanın genel içeriği ve örnek sunucu bazlı tanımlamalar aşağıdaki gibidir:

[global]
        # TDS protocol version
;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.  
        # Try setting 'text size' to a more reasonable limit 
        text size = 64512

# A typical Sybase server
[egServer50]
        host = symachine.domain.com
        port = 5000
        tds version = 5.0

# A typical Microsoft server
[egServer70]
        host = ntmachine.domain.com
        port = 1433
        tds version = 7.0

[mssql]
        host = 172.16.2.139
        port = 1433
        tds version = 7.0
        client charset = UTF-8

Yukarıda anlaşılabileceği üzere, tüm sunucuları etkileyecek ayarlar [global] bölümü altında yer almakta, aynı zamanda [mssql] örneğindeki gibi belirli bir sunucua isim verilerek (DNS ismi olması gerekmiyor), sunucu bazlı ek ayarlamalar yapma şansı da bulunmaktadır.

Örneğimizde mssql adında bir sunucu ismi tanımladık ve client charset değerini UTF-8 olacak şekilde değiştirdik.

Bu tanım sonrasında hem sqsh uygulamasından hem de freetds kullanan diğer uygulamalarda, sunucu isim/ip parametresinde mssql ismini kullanabilir ve konfigürasyon dosyasında bu bölümde belirtilmiş ayarların aktif olmasını sağlayabiliriz. Bir önceki select örneğimizi tekrar edelim:

$ sqsh -S mssql -U testuser -P tstpwd123 -D example_db
1> select * from bolgeler
2> go
 id          isim                                        
 ----------- ---------------------------------------
           1 Akdeniz Bölgesi
           2 Doğu Anadolu Bölgesi
           3 Ege Bölgesi
           4 Güneydoğu Anadolu Bölgesi
           5 İç Anadolu Bölgesi
           6 Marmara Bölgesi
           7 Karadeniz Bölgesi

.sqshrc

Sqsh ile çalışırken kullanım ortamınızı daha konforlu hale getirmek için ek ayarlamaları ev dizininiz altındaki .sqshrc dosyası üzerinden tanımlayabilirsiniz (henüz hiç ayar yapılmadı ise dosyanın oluşturulması gerekecektir)

Örneğin yukarıdaki çıktı formatı yerine öntanımlı MySQL konsol arayüzündekine benzer bir fomrmat kullanılmasını istiyorsanız, go komutunu -m pretty parametresi ile çalıştırmalısınız. Bu komutu her çalıştırdığımızda parametresini girmek zorunda kalmamak için bir alias tanımlayabiliriz.

Aşağıdaki satırı ~/.sqshrc dosyanıza girin:

\alias go='\go -m pretty'

Şimdi tekrar bölge listesini sorgulayalım:

$ sqsh -S mssql -U testuser -P tstpwd123 -D example_db
1> select * from bolgeler
2> go
+=============+==================================================+
|          id | isim                                             |
+=============+==================================================+
|           1 | Akdeniz Bölgesi                                  |
+-------------+--------------------------------------------------+
|           2 | Doğu Anadolu Bölgesi                             |
+-------------+--------------------------------------------------+
|           3 | Ege Bölgesi                                      |
+-------------+--------------------------------------------------+
|           4 | Güneydoğu Anadolu Bölgesi                        |
+-------------+--------------------------------------------------+
|           5 | İç Anadolu Bölgesi                               |
+-------------+--------------------------------------------------+
|           6 | Marmara Bölgesi                                  |
+-------------+--------------------------------------------------+
|           7 | Karadeniz Bölgesi                                |
+-------------+--------------------------------------------------+

Diğer bazı kullanışlı örnekler için http://www.sypron.nl/sqsh.html adresine bakabilirsiniz.

Kütüphane Kullanımı

FreeTDS kütüphanesini C uygulamalarında kullanabilmek için aşağıdaki komutla geliştirme paketini sisteminize yükleyebilirsiniz:

$ sudo apt-get install freetds-dev

Aşağıdaki örnek uygulamayı mssql_connect.c adıyla kaydedip şu şekilde derleyebilirsiniz:

$ gcc -o mssql_connect mssql_connect.c -lsybdb

Örnek kodumuzu listeleyip önemli yerlerini detaylandırmaya çalışalım:

/* mssql_connect.c */
#include <stdio.h>
#include <unistd.h>
#include <stdlib.h>
#include <sybfront.h>
#include <sybdb.h>
#include "../common/debug.h"

struct mssql_column {
    char *name;
    char *buffer;
    int type;
    int size;
    int status;
};

void display_usage (const char *name)
{
    printf("Usage: %s SERVER USER PASS DATABASE QUERY\n", name);
}

int database_mssql_errhandler (DBPROCESS * dbproc, int severity, int dberr,
        int oserr, char *dberrstr, char *oserrstr)
{
    (void) dbproc;
    (void) oserr;
    (void) oserrstr;

    if (dberr) {
        errorf("Sqlserver Msg %d, Level %d", dberr, severity);
        errorf("%s", dberrstr);
    } else {
        debugf("%s", dberrstr);
    }
    return INT_CANCEL;
}

int main (int argc, char *argv[])
{
    LOGINREC *login;
    DBPROCESS *dbproc;
    struct mssql_column *columns = NULL;
    struct mssql_column *pcol = NULL;
    int row_code;
    int ncols;
    int nrows;
    int ret;
    int c;

    if (argc != 6) {
        display_usage(argv[0]);
        exit(1);
    }

    const char *server      = argv[1];
    const char *username    = argv[2];
    const char *password    = argv[3];
    const char *database    = argv[4];
    const char *query       = argv[5];

    if (dbinit() == FAIL) {
        errorf("Couldn't init MSSQL library");
        exit(1);
    }
    /* Maximum 5 seconds for sql login */
    dbsetlogintime(5);

    /* Set error handler callback function */
    dberrhandle(database_mssql_errhandler);

    login = dblogin();
    dbsetluser(login, username);
    dbsetlpwd(login, password);
    if ( (dbproc = dbopen(login, server)) == NULL) {
        errorf("Couldn't open sqlserver db connection");
        exit(1);
    }
    if (dbuse(dbproc, database) == FAIL) {
        errorf("Couldn't change to db: %s", database);
        exit(1);
    }
    if (dbfcmd(dbproc, query)  == FAIL) {
        errorf("Couldn't create sql statement");
        exit(1);
    }
    if (dbsqlexec(dbproc) == FAIL) {
        errorf("Couldn't execute sql query");
        exit(1);
    }

    ncols = dbnumcols(dbproc);
    infof("%d columns found", ncols);

    while ( (ret = dbresults(dbproc)) != NO_MORE_RESULTS) {
        if (ret == FAIL) break;

        if ( (columns = calloc(ncols, sizeof(struct mssql_column))) == NULL) {
            errorf("Couldn't allocate columns");
            break;
        }

        for (pcol = columns; pcol - columns < ncols; pcol++) {
            c = pcol - columns + 1;
            pcol->name = dbcolname(dbproc, c);
            pcol->type = dbcoltype(dbproc, c);
            pcol->size = dbcollen(dbproc, c);
            if (pcol->type != SYBCHAR) {
                pcol->size = dbwillconvert(pcol->type, SYBCHAR);
            }
            debugf("col: %d, type: %d, size: %d, name: %s", c, pcol->type, pcol->size, pcol->name);
            if ( (pcol->buffer = malloc(pcol->size + 1)) == NULL) {
                errorf("Couldn't allocate space for row buffer");
                break;
            }
            if (dbbind(dbproc, c, NTBSTRINGBIND, pcol->size + 1, (BYTE*)pcol->buffer) == FAIL) {
                errorf("Couldn't bind to %s", pcol->name);
                break;
            }
            if (dbnullbind(dbproc, c, &pcol->status) == FAIL) {
                errorf("Couldn't make null bind to %s", pcol->name);
                break;
            }
        }

        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: %s\t", pcol->name, buffer);
                }
                printf("\n");
                break;

            case BUF_FULL:
                errorf("buffer full");
                break;

            case FAIL:
                errorf("failed");
                exit(1);
                break;

            default:
                printf("Data for computeid %d ignored\n", row_code);
            }

        }
    }

    /* Free metadata and data */
    for (pcol = columns; pcol - columns < ncols; pcol++) {
        free(pcol->buffer);
    }
    free(columns);

    /* Get row count if available */
    if ( (nrows = dbcount(dbproc)) > -1) {
        debugf("Affected rows: %d", nrows);
    }

    dbclose(dbproc);
    dbfreebuf(dbproc);
    dbloginfree(login);

    return 0;
}

Kütüphanenin İlklendirilmesi: dbinit

FreeTDS kütüphanesinin kullanıldığı uygulamalarda, kütüphane içerisinden herhangi bir fonksiyon çağrılmadan önce, dbinit() fonksiyonunun çağrılmış olması şarttır.

dbinit() dahili bazı veri yapılarının doldurulmasını ve yerel spesifik tarih vb. format bilgilerini okumak için freetds içerisinden çıkan -varsa- /etc/freetds/locales.conf dosyasını okur.

locales.conf dosyasının bu şekilde okunması deprecated bir özellik olmuştur. Güncel kütüphane versiyonları sistemin yerel (locale) ayarlarından bu bilgileri temin etmektedir. Ancak gene de locales.conf dosyası bulunursa işlenmektedir.

Bu işlemin uygulamanın main fonksiyonu içerisinde yapılmasında fayda vardır. Ancak herhangi bir sebeple dbinit işleminin bir fonksiyon içerisinden koşullu olarak sonradan yapılması gerekiyorsa, mutlaka statik bir değişkenle kütüphanenin ilklendirme işleminin daha önce yapılıp yapılmadığını tutmanız zorunludur. İlklendirme işleminin tekrar edilmesi, takibi zor hatalara yol açabilmektedir.

Hata İşleme: dberrhandle

Kütüphanenin hata ve uyarı durumlarında çağıracağı calback fonksiyonunu, dberrhandle() fonksiyonu ile belirtilmelidir.

Bu fonksiyonun prototipi aşağıdaki gibidir:

typedef int (*EHANDLEFUNC) (DBPROCESS * dbproc, int severity, 
            int dberr, int oserr, char *dberrstr, char *oserrstr);

Fonksiyon çağrıldığında dberr parametresi 0'dan farklı ise, kritik bir veritabanı hatası olduğu anlaşılır.

Bağlantı Kurma ve Veritabanı Seçimi

Bağlantı kurmak için öncelikle kullanıcı adı ve parola bilgileri LOGINREC veriyapısı içerisine doldurulmalıdır.

Bunun için öncelikle LOGINREC tipinde bir değişken, dblogin() fonksiyonu ile ilklendirilir, ardındandbsetluser ve dbsetlpwd fonksiyonları ile ilgili parametreleri ayarlanır.

Sonraki adımda hazırlanan LOGINREC veri yapısı ve sunucu bilgisini (burada IP adresi, DNS üzerinden çözülebilen bir hostname veya freetds.conf içerisinde tanımlanmış bir sunucu adı kullanılabilir) parametre olarak alıp, geriye sürecin ilerleyen aşamalarında sürekli kullanılacak olan DBPROCESS handle döndürecek olan dbopen() fonksiyonu çağrılır.

Herhangi bir sebeple hata alınırsa, ilgili hata mesajının detayı hata işlemeleri için önceden belirlenmiş olan callback fonksiyonundan alınabilir.

Bağlantı zaman aşımı süresini kontrol altına almak isterseniz, dbopen() fonksiyonu çağırmadan önce dbsetlogintime(int seconds) prototipindeki fonksiyonu kullanarak saniye cinsinden bir limit de tanımlayabilirsiniz.

Bağlantı gerçekleştikten sonra dbuse() fonksiyonu ile üzerinde çalışılacak olan veritabanı seçimi işlemi yapılmaktadır.

Sorgu Çalıştırma ve Yanıt İşleme

Veritabanı üzerinde çalıştırılacak olan sorgu, öncelikle dbfcmd() fonksiyonu ile hazırlanır. Ardından dbsqlexec() fonksiyonu ile çalıştırılır.

Sorgu bu şekilde işletildikten sonra geriye dönen değerlerin saklanacağı uygun veri yapıları oluşturulmalıdır. Bunun için uygulama kaynak kodumuzun ilk bölümünde, struct mssql_column şeklinde bir yapı tanımladık. Bu yapıyı ihtiyaçlarınız doğrultusunda genişletebilirsiniz.

Tanımladığımız yapıyı, işletmiş olduğumuz sorgunun yanıt kümesindeki her bir sütun ile ilgili veri tipi, uzunluk ve sütun ismi bilgilerini işlemek için kullanacağız.

Örneğimizi geri dönen sütun sayısını önceden bilemeyeceğimiz, her türlü sorgu için çalışacak şekilde hazırladık. Dolayısıyla öncelikle göndermiş olduğumuz sorgu yanıtının kaç sütundan oluştuğunu öğrenmemiz gerekiyor. Bu işlem için dbnumcols() fonksiyonunu kullanıyoruz.

Sütun sayısını öğrendikten sonra ilgili bilgileri hazırlamış olduğumuz struct mssql_column veri yapısında saklamak üzere bellekte yer ayırıyoruz.

Ardından sorgu yanıtındaki satırları işlemeye geçmeden hemen önce, sütunlarla ilgili sütun ismi, tipi ve veri uzunluğu bilgilerini sırasıyla dbcolname(), dbcoltype() ve dbcollen() fonksiyonlarıyla elde ediyoruz.

Sütun ile ilgili bilgileri bu şekilde öğrendikten sonra, hazırlamış olduğumuz veri yapısında yanıtları saklayacağımız yerleri hazırlıyoruz. Bu noktada kodumuzu kısa tutmak adına, metin dışındaki tiplerin, dbwillconvert() fonksiyonuyla metin tabanlı bir formata dönüştürüldüğünde gereken uzunluğu hesaplatıp, metne dönüştüğü zamanki uzunluğu için yetecek kadar bellekte alan açıyoruz. Örnek olarak 4 byte'lık INT tipindeki bir sütun için dbwillconvert() sonrası sütun boyutunun 11 olarak geleceğini göreceğiz zira 4 byte'lık bir işaretli INT değerini metne dönüştürüp saklayabilmek için 11 byte uzunluğunda bir alan gereklidir.

Gerçek ortamda gönderdiğiniz sorgularla ilgili bilgi sahibi olacağınızdan, tüm sütunları metin tabanlı dönüşüme zorlamak yerine, struct mssql_column veri yapısı içerisindeki genel amaçlı buffer değişkenini bir union yapısı ile değiştirip, sütun tipine göre union içerisinde INT, FLOAT vb. veri tipleri kullanabilir ve metin dönüşümü yapmadan doğrudan bu alanların içerisine yazılmasını sağlayabilirsiniz.

Bellekteki alanlar hazır edildikten sonra her bir sütunu dbbind() fonksiyonu ile yanıt setine nasıl bağladığımızı belirtmemiz gerekiyor. Örneğimizde bind tipi olarak hep NTBSTRINGBIND değerini kullandık. Yukarıdaki ek notumuz doğrultusunda eğer metin dönüşümü uygulamayacaksanız bunun yerine INTBIND, REALBIND, BIGINTBIND vb. diğer veri tipleri için uygun binding değerlerini de kullanabilirsiniz.

Her bir sütun için gerekli bind işleminin yanı sıra NULL değerler için de dbnullbind() fonksiyonuyla bir adet binding işleminin daha yapılması gereklidir.

Not: Sütun ve binding tipleri için sabitler, kütüphane içerisinden çıkan sybdb.h dosyası içerisinde yer almaktadır.

Şimdi artık sıra satırları işlemeye geldi. Bunun için dbnextrows() fonksiyonu NO_MORE_ROWS değeri döndürmediği müddetçe iterasyonla tüm bilgileri alabiliriz.

Örnek uyguladığımızda her bir satırda aldığımız değerleri, sütun ismi ile birlikte ekrana bastırdık.

Yanıt satırlarının işlenmesi tamamlandıktan sonra sistem kaynaklarını serbest bırakıyoruz.

Bağlantının Sonlandırılması

Veritabanı ile ilgili işlemlerimiz tamamlandıysa açık olan bağlantımızı dbclose() fonksiyonuyla kapatmamız gerekir.

Son olarak kullandığımız DBPROCESS ve LOGINREC değişkenlerini de dbfreebuf() ve dbloginfree() fonksiyonlarıyla da geride artık kalmayacak şekilde temizliyoruz.

Örnek Kullanım

Hazırlamış olduğumuz uygulama ile bir miktar veri içeren bolgeler ve iller adında 2 tablo üzerinde INNER JOIN sorgusu çalıştıralım:

$ ./mssql_connect mssql testuser tstpwd123 example_db \
    "SELECT iller.*, bolgeler.isim AS bolge_adi FROM iller \
    INNER JOIN bolgeler ON iller.bolge_id=bolgeler.id ORDER BY isim"
info: 5 columns found (main mssql_connect.c:91)
debug: col: 1, type: 56, size: 11, name: id (main mssql_connect.c:110)
debug: col: 2, type: 47, size: 8, name: plaka (main mssql_connect.c:110)
debug: col: 3, type: 56, size: 11, name: bolge_id (main mssql_connect.c:110)
debug: col: 4, type: 47, size: 400, name: isim (main mssql_connect.c:110)
debug: col: 5, type: 47, size: 200, name: bolge_adi (main mssql_connect.c:110)
id: 13  plaka: 06  bolge_id: 5  isim: Ankara    bolge_adi: İç Anadolu Bölgesi    
id: 11  plaka: 07  bolge_id: 1  isim: Antalya   bolge_adi: Akdeniz Bölgesi    
id: 1   plaka: 08  bolge_id: 7  isim: Artvin    bolge_adi: Karadeniz Bölgesi    
id: 9   plaka: 16  bolge_id: 6  isim: Bursa     bolge_adi: Marmara Bölgesi    
id: 4   plaka: 28  bolge_id: 7  isim: Giresun   bolge_adi: Karadeniz Bölgesi    
id: 7   plaka: 34  bolge_id: 6  isim: İstanbul  bolge_adi: Marmara Bölgesi    
id: 2   plaka: 53  bolge_id: 7  isim: Rize      bolge_adi: Karadeniz Bölgesi    
id: 6   plaka: 55  bolge_id: 7  isim: Samsun    bolge_adi: Karadeniz Bölgesi    
id: 15  plaka: 58  bolge_id: 5  isim: Sivas     bolge_adi: İç Anadolu Bölgesi    
id: 3   plaka: 61  bolge_id: 7  isim: Trabzon   bolge_adi: Karadeniz Bölgesi    
id: 8   plaka: 77  bolge_id: 6  isim: Yalova    bolge_adi: Marmara Bölgesi    
debug: Affected rows: 15 (main mssql_connect.c:159)

Not: debug.h dosyasını Kaynak Dosyalar bölümünden edinebilirsiniz.