code it

Martins Tech Blog

Speichern und Lesen von Binärobjekten in MS SQL Server 2005

Hin und wieder kommt es vor, dass man Dateien im BLOB-Format in einer Tabelle ablegt. Wenn man nun "schnell" auf diese Daten zugreifen will, steht man vor einem Problem, denn der Standard-SQL-Befehlssatz bietet keine Möglichkeit, Spalten mit Binärdaten zu füllen bzw. diese Daten wieder auszulesen und in eine Datei zu speichern.

Nun hat man nicht immer eine Entwicklungsumgebung zu Hand, um ein Programm zu schreiben, das per ADO oder ADO.NET diese Spalten befüllt. Auch der SQL-Server selbst bietet Möglichkeiten, um hier z.B. mittels Management-Studio tätig zu werden. Im Folgenden beschreibt ein Beispiel das generelle Vorgehen:

Als vorbereitende Maßnahme legt man eine Datenbank namens [pictures] an, die eine Tabelle mit dem Namen [images] enthält. Diese Tabelle enthält einfachheitshalber nur einen Primärschlüssel und das eigentliche Datenfeld. Wichtig hierbei ist, dass das Datenfeld den Datentyp [varbinary](max) hat.

CREATE DATABASE pictures
GO
USE pictures
GO 
CREATE TABLE [dbo].[images](
[imageid] [int] IDENTITY(1,1) NOT NULL,
[imageblob] [varbinary](max) NOT NULL
PRIMARY KEY CLUSTERED ([imageid]))
GO
 
Nun wird die Tabelle mittels INSERT befüllt. Mittels OPENROWSET in Kombination mit der Option BULK ist es möglich, Spalteninhalte als Binärstrom aus einer Datei (im Beispiel: bild1.jpg) zu lesen.
 
INSERT [dbo].[images]([imageblob])
SELECT BulkColumn
FROM OPENROWSET( BULK 'C:\bild1.jpg',
SINGLE_BLOB) as ExternalFile
GO
 
Führt man nun einen SELECT auf die Tabelle aus, sieht man, dass die Binärdaten gespeichert wurden.
 

Der umgekehrte Weg ist leider nicht ganz so einfach. Hierzu muss man das Programm bcp bemühen. Um bcp per SQL aufzurufen, muss der Server konfiguriert werden, dass Shell-Aufrufe möglich sind. Auch das ist (notwendige Berechtigungen vorausgesetzt) per SQL-Statement möglich:

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
 
Für den eigentlichen Export ist noch eine Konfigurationsdatei erforderlich. Diese beinhaltet die Formatbeschreibung. Wird diese nicht verwendet hat die resultierende Datei zwar die passende Größe, kann aber nicht gelesen werden, da bcp beim Export einige automatische Zeichenersetzungen vornimmt. Die Formatdatei kann auf Kommandozeilenebene mit bcp erstellt und dann angepasst oder direkt mittels Editor erstellt werden.

Die resultierende Datei sollte final wie dargestellt aussehen:

Nun kann mittels eines BCP-Befehls eine Datei erstellt werden. Wichtig hierbei ist, dass immer nur 1 Spalte und eine Zeile ausgegeben wird, da a) nur eine Datei geschrieben wird und b) dies auch in der eben erstellten Formatdatei so beschrieben ist. exec master..xp_cmdshell 'bcp "select [imageblob] from [pictures].[dbo].[images] where imageid = 1" queryout C:\kopiebild1.jpg -T -S . -f c:\imageblob.fmt'

Die Datei liegt nun als kopiebild1.jpg wieder vor.

Hilfe bei Regulären Ausdrücken

Reguläre Ausdrücke sind bekanntlich ein weites Feld und nicht immer einfach. Bei meiner Suche bin ich auf die Seite Regular Expression Library (RegExLib) getroffen. Hier stehen für viele Anwendungsgebiete reguläre Ausdrücke zur Verfügung - meist mit einer Angabe der Fälle, die abgedeckt sind und die nicht abgedeckt sind. Zusätzlich findet sich hier auch ein Tool, mit dem reguläre Ausdrücke getestet werden können.