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

Hin und wie­der kommt es vor, dass man Dateien im BLOB-Format in einer Tabelle ablegt. Wenn man nun "schnell" auf diese Daten zugrei­fen will, steht man vor einem Problem, denn der Standard-SQL-Befehlssatz bie­tet keine Möglichkeit, Spalten mit Binärdaten zu fül­len bzw. diese Daten wie­der aus­zu­le­sen und in eine Datei zu speichern.

Nun hat man nicht immer eine Entwicklungsumgebung zu Hand, um ein Programm zu schrei­ben, das per ADO oder ADO.NET diese Spalten befüllt. Auch der SQL-Server selbst bie­tet Möglichkeiten, um hier z.B. mit­tels Management-Studio tätig zu wer­den. Im Folgenden beschreibt ein Beispiel das gene­relle Vorgehen:

Als vor­be­rei­tende Maßnahme legt man eine Datenbank namens [pic­tures] an, die eine Tabelle mit dem Namen [images] ent­hält. Diese Tabelle ent­hält ein­fach­heits­hal­ber nur einen Primärschlüssel und das eigent­li­che Datenfeld. Wichtig hier­bei 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 mit­tels INSERT befüllt. Mittels OPENROWSET in Kombination mit der Option BULK ist es mög­lich, 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 gespei­chert wurden.

Der umge­kehrte Weg ist lei­der nicht ganz so ein­fach. Hierzu muss man das Programm bcp bemü­hen. Um bcp per SQL auf­zu­ru­fen, muss der Server kon­fi­gu­riert wer­den, dass Shell-Aufrufe mög­lich sind. Auch das ist (not­wen­dige Berechtigungen vor­aus­ge­setzt) 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 eigent­li­chen Export ist noch eine Konfigurationsdatei erfor­der­lich. Diese beinhal­tet die Formatbeschreibung. Wird diese nicht ver­wen­det hat die resul­tie­rende Datei zwar die pas­sende Größe, kann aber nicht gele­sen wer­den, da bcp beim Export einige auto­ma­ti­sche Zeichenersetzungen vor­nimmt. Die Formatdatei kann auf Kommandozeilenebene mit bcp erstellt und dann ange­passt oder direkt mit­tels Editor erstellt werden.

Die resul­tie­rende Datei sollte final wie dar­ge­stellt aussehen:

Nun kann mit­tels eines BCP-Befehls eine Datei erstellt wer­den. Wichtig hier­bei ist, dass immer nur 1 Spalte und eine Zeile aus­ge­ge­ben wird, da a) nur eine Datei geschrie­ben wird und b) dies auch in der eben erstell­ten Formatdatei so beschrie­ben ist.

exec master..xp_cmdshell 'bcp "sel­ect [image­b­lob] from [pictures].[dbo].[images] where imag­eid = 1" query­out C:\kopiebild1.jpg -T -S . -f c:\imageblob.fmt'

Die Datei liegt nun als kopiebild1.jpg wie­der vor.