Im Zuge einer Migration musste ich im SQL Server gespeicherte Bilder als Dateien exportieren. Dabei lagen keine Informationen über den Dateityp oder andere Informationen vor. Trotzdem gibt es Möglichkeiten die üblichen Formate GIF, JPG oder PNG aus dem Binärstring zu identifizieren.

In der hier verwendeten Lösung verwende ich OLE Automation. Natürlich kann man auch jede andere Sprache verwenden. Mit OLE Automation ist aber keine zusätzliche Laufzeitumgebung und kein Compiler nötig, sondern nur das übliche SQL Server Management Studio.

Ausganssituation

Für das bessere Verständnis des Scripts möchte ich hier eine vereinfachte Fassung der Ausgangssituation vorstellen. Diese besteht aus einer Tabelle People mit zwei Spalten Name und Image:

	Name	Image
Type	varchar	varbinary
Beispiel	Maxime	0xfe56a245934…

Mittels Name soll der Dateiname für das Bild bestimmt werden und in der Spalte Image befindet sich die hexadezimale Darstellung des Portraits.

Vorbereitungen

Um die OLE Automisation im SQL Server nutzen zu können, müsste die zugehörigen Prozeduren erst einmal freigeschaltet werden. Standardmäßig sind die meisten Scriptinginterfaces aus Sicherheitsgründen deaktiviert.

Der erste Schritt besteht darin zu prüfen, wie die aktuelle Einstellung aussieht:

EXEC sp_configure;

Sollte die Zeile Ole Automation Procedures des Ergebnisses beim Wert eine Eins enthalten, ist alles gut. Ist eine solche Zeile nicht enthalten, müssen erst noch die erweiterten Optionen aktiviert werden:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

Anschließend kann man die OLE-Funktionen auch aktivieren:

sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Damit sind alle Vorbereitungen abgeschlossen und ich kann zum eigentlichen Script übergeben.

Exportieren der Bilder

Das Script besteht im Grunde aus einem Cursor, der über die Tabelle iteriert und für jede Zeile das benötigte Stream-Objekt anlegt, den Binärstream hineinkopiert und es als Datei speichert:

DECLARE @Img VARBINARY(MAX)
    , @FileName VABINARY(MAX)
    , @ObjectToken INT

DECLARE IMGCURSER CURSOR FAST_FORWARD FOR 
    SELECT [Image], CAST('c:/folder/' + [Name] + '.img' AS varbinary(max)) AS Filename
    FROM   People
    WHERE  [Image] IS NOT NULL

OPEN IMGPATH 

FETCH NEXT FROM IMGCURSER INTO @Img, @FileName

WHILE @@FETCH_STATUS = 0 BEGIN

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @Img
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FileName, 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

    FETCH NEXT FROM IMGCURSER INTO @Img, @FileName

END 

CLOSE IMGCURSER
DEALLOCATE IMGCURSER

Das Script, wie es bisher vorgestellt wurde, erzeugt Dateien mit der Endung “img”. Diese kann man zwar verwenden, aber natürlich wäre es besser gebräuchlichere Endungen zu benutzen, die auch dem Bildformat entsprechen. Dazu muss man das Select-Statement für den Cursor anpassen:

SELECT [Image], 
  CAST('c:\folder\' + [Name] + 
    (CASE WHEN substring([Image], 0, 3) = 0xFFD8 
      OR substring([Image], 0, 3) = 0xD0CF THEN '.jpg' 
      WHEN substring([Image], 0, 3) = 0x424D THEN '.bmp' 
      WHEN substring([Image], 0, 3) = 0x8950 THEN '.png' 
      WHEN substring([Image], 0, 3) = 0x4749 THEN '.gif' 
      ELSE '.img' END) AS varbinary(max)) AS Filename
FROM People
WHERE [Image] IS NOT NULL

Hierbei wird sich zunutze gemacht, dass die betrachteten Bildformate am Anfang jeweils ein spezifisches Bitmuster aufweisen. Wenn trotz diesem Konstrukt noch Dateien mit der Endung “img” erzeugt werden, muss die Fallunterscheidung passend ergänzt werden. In meinem Fall reichte diese Lösung aber für etwa 15000 Bilder aus.

Aufräumen

Um das Script ausführen zu können, mussten Einstellungen geändert werden. Sicherheitshalber sollte man diese Einstellungen auch wieder zurücksetzen:

sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO