Debian, FreeTDS et SQL Server

Contexte

Lorsqu’on travaille dans un environnement hétérogène composé de plateformes Windows et Linux, il peut s’avérer utile d’accéder à partir d’un serveur Unix à un serveur de base de données de type SQL Server (2000, 2005, ...), j’aime l’interop…

Le développement de SQL Server est notamment né à l’origine de la collaboration entre Microsoft et Sybase. Le protocole de communication utilisé pour les échanges clients/serveur se nomme TDS, et sa version Opensource FreeTDS, avec une communauté toujours active.

Dans notre environnement, nous utilisons le serveur de listes SYMPA. Ce dernier permet de synchroniser les [emails des] membres des listes qu’il gère avec une base externe (synchro automatique toutes les n minutes, ou lors de l’envoi d’un message). Dans notre système d’information, celle-ci est une base SQL Server qui est utilisé par la plateforme d’applications mais aussi par le serveur de listes (en consultation ou en insertion).

Debian, installation

Sous Debian, le paquetage à installer est libct3 :

apt-get install libct3

Le paquetage installe le tout dans le répertoire /etc/freetds.

Configuration

Le fichier freetds.conf est une base de départ. Parmi les paramétrages intéressants, nous avons la section globale ([global]), où l’on peut varier la version du protocole à utiliser (selon le serveur SQL Server en face), fixer explicitement l’encodage à utiliser pour la communication client/serveur, ou créer des sections (de la forme [masection]) spécifiques à vos serveurs SGBD (IP d’accès, protocole, port, ...).

Pour un dialogue avec un SQL Server 2000, en UTF, ci-après, un exemple de configuration freetds.conf :

$ less /etc/freetds.conf

[global]
        # TDS protocol version
        tds version = 8.0
        client charset = UTF-8
        # si besoin de traces
;       dump file = /var/log/freetds.log
;       debug level = 10

        # si besoin de gérer les timeout d'exécution des commandes SQL et du timeout de connexion
;       timeout = 10
;       connect timeout = 10

[monserveur-sql]
host = 10.75.40.31
port = 1433
tds version = 8.0

Dans le client (Perl, PHP, ...) qui utilisera FreeTDS, celui-ci fera appelle au libellé du serveur défini dans freetds.conf, ici monserveur-sql, cela a l’avantage d’abstraire l’IP réelle.

Exemple de client en Perl

Le but de ce programme est de lire un mail stocké sur le disque, de le parser, et d’inscrire les divers éléments (entêtes, corps, ...) qui le composent dans une table en base SQL Server.

use DBI;
use Encode;

use lib '/home/sympa/bin';
use Log;

# infos. sql
# le nom du serveur du freetds.conf
my $server = "monserveur-sql";
my $user = "loginsql";
my $pwd = "pwdsql";
my $bdd = "mabase";

sub encoding
{
    my $body = shift;
    &Encode::encode_utf8($body);
}

sub msgtosql
{
   my $file = $ENV{'PATHMSG'};
   my $adrlist = $ENV{'LISTADDR'};
   my $listname='';
   my $domain='';
   my $bodyencode='';

   if ($adrlist =~ /^(.*)\@(.*)$/) {
        $listname = $1;
        $domain = $2;
   } else {
        &do_log('err',"Match of list address '$adrlist' failed");
        return undef;
   } 

   # lecture du message (fichier texte)
   # et prise en compte des champs a inserer dans la base 
   my $parser = new MIME::Parser;
   $parser -> output_to_core(1);
   my $msg = $parser -> parse_open("$file");

   my $head = $msg -> head;
   my $body = $msg -> body_as_string;
   my $hdrall = $msg -> header_as_string;
   my $hdrdate = $head->get('Date');
   my $hdrfrom = $head->get('From');
   my $hdrsubject = $head->get('Subject');
   my $hdrto = $head->get('To');
   my $messageid = $head->get('Message-Id');

   $bodyencode = $body;

   $dbh = DBI->connect("dbi:Sybase:$server","$user","$pwd") or die 'connect';
   $dbh->do("use $bdd");     

   # insertion du message en base

   $SQL = sprintf "INSERT INTO msgs (body,creatstamp,hdrall,hdrdate,hdrfrom,hdrfromspc,hdrsubject,hdrto,list) VALUES (%s,getdate(),%s,%s,%s,%s,%s,%s,%s)", $dbh->quote($bodyencode),$dbh->quote($hdrall), $dbh->quote($hdrdate), $dbh->quote($hdrfrom), $dbh->quote($hdrfrom),$dbh->quote($hdrsubject),$dbh->quote($hdrto),$dbh->quote($listname);

  # on test l'insert, si catch alors on force l'encodage a utf-8
  # l'erreur vient de freeTDS qui ne pt encoder (via iconv) directement en UTF-8, ne connaissant pas parfois l'encodage origine 
  # pas d'encoding automatique car des msgs peuvent etre deja en utf-8
  {
          # trap die pour logguer l'erreur SQL
          local $SIG{'__DIE__'} =
                sub { do_log('err',$_[0]); die $_[0]; };
          eval { $dbh->do($SQL) || die "Error SQL $DBI::errstr"; };
          if($@) {
                do_log('notice',"[catch sql] try to encode in utf-8...");
                $bodyencode = &Encode::encode_utf8($body);
                $SQL = sprintf "INSERT INTO msgs (body,creatstamp,hdrall,hdrdate,hdrfrom,hdrfromspc,hdrsubject,hdrto,list) VALUES (%s,getdate(),%s,%s,%s,%s,%s,%s,%s)", $dbh->quote($bodyencode),$dbh->quote($hdrall), $dbh->quote($hdrdate), $dbh->quote($hdrfrom), $dbh->quote($hdrfrom),$dbh->quote($hdrsubject),$dbh->quote($hdrto),$dbh->quote($listname);

                unless($dbh->do($SQL)) {
                        do_log('err',"Error $DBI::errstr for $SQL");
                }
          }
  }

   $dbh->disconnect;

   return 1;
}

do_openlog("LOCAL2", "unix", 'archivetosql');

&msgtosql();

Au passage, un excellent site sur Perl, sur tous les sujets du langage.

Conclusion

Lorsque dans un système d’information, il cohabite un grand nombre de serveurs hétérogènes, le portage de protocoles peut-être un moyen de faire communiquer les systèmes. Une autre solution, de plus en plus répandue, est l’utilisation des services Web (SOAP, REST, ...), dans une approche SOA.

Ressources

Quelques sur le sujet.