TechNet Team Blog Austria

Informationen und News abseits der offiziellen TechNet vom TechNet Team Blog Austria

Wie bekomme ich binäre Dokumente (BLOBs) aus dem SQL Server?

Wie bekomme ich binäre Dokumente (BLOBs) aus dem SQL Server?

  • Comments 1
  • Likes

Das Leben als Datenbank-Administrator steckt voller Überraschungen. Mal gehören Indizies erstellt oder neu aufgebaut, Volltextkataloge rebuildet, Datenbanken gebackupt, gewartet, restored und natürlich ist ein DB-Admin Ansprechpartner für alle weiteren auftretenden Probleme und Wünsche der IT und der Anwender.

So hatte ich vor kurzem die Anforderung, in einer Datenbank gespeicherte Bilder “herauszuholen”, also klassische BLOBs (Binary Large Objects) als Dateien zu speichern.

In meiner Datenbank existiert eine Tabelle “MyFiles” mit einer Spalte vom Typ IMAGE. Das Pendant ab SQL Server 2005 wäre der “neue” Datentyp VARBINARY; in meinem Fall handelt es sich um eine konvertierte DB aus SQL 2000 in SQL 2005.

In der Spalte ”FIL_Content” ist der Dateistream einer Datei enthalten, z.B. JPG-Bilder, Word- und Excel-Dokumente, PDFs, (BLOB), also ganz egal welche Files (ab SQL 2008 gibt es übrigens einen eigenen Filestream-Datentyp. ;-)

Die binären Dokumente können nicht simpel mit Copy & Paste herausgeholt werden, nur die entsprechende Applikation liest und schreibt die Dateien von und in die Datenbank. Diese binären Informationen aus der Tabelle sollen als Files gespeichert werden um sie weiterzuverwenden.

So sieht die vereinfachte Tabelle aus, Auszulesen ist die Spalte FIL_Content:

Tabelle MyFiles

Natürlich könnte ich eine kleine .NET Applikation dafür bauen, aber ich wollte eine möglichst rasche Lösung. Und ich habe sie auch gefunden! Ein kleines T-SQL Script im SQLTeam.com Forum von "Peso". Dieses habe ich analysiert und wie folgt angepasst.

save2file.sql Script

(Das ganze Script gibt es hier zum Download, es läuft in SQL Server 2005 und 2008.)

 

 

Ich habe das Script als Stored Procedure “save2file” angelegt. Die Funktionsweise kurz erklärt:

Mit einem Cursor (Zeile 16) wird die gesamte Tabelle “MyFiles” durchlaufen und die selektierten Felder in Variablen eingelesen (Zeile 21). Dann wird der Pfad aus Feld FIL_ID und Dateiname zusammengebaut und ausgegeben (Zeile 25-28). Jetzt kommt der Clou: Es wird ein ADODB-Stream als OLE-Objekt angelegt (Zeile 30) und mit den Werten befüttert (Zeile 31 bis 34). Dieser Stream wird auf die Festplatte geschrieben (Zeile 33, 34) und das OLE Objekt zerstört (Zeile 36). Und weiter mit dem nächsten Datensatz (Zeile 38).

Anzupassen ist natürlich der Pfad, wohin die Dateien gespeichert werden sollen (Zeile 27).

Ausgeführt wird das Script wie gewohnt mit

exec save2file

Voila! Wenn alles stimmt, wurden die binary Contents als Files in den gew. Pfad extrahiert:

save2file erzeugte Files

Hinweis: Diese Vorgangsweise ist sehr rasch anwendbar, aber nicht unbedingt für “daily use” geeignet. Je nach Datenmenge ist das Erstellen eines OLE-Objekts nicht unbedingt “günstig”.

Aber für den Ab-und-zu Export verdient das Script meine Empfehlung als “best practice”!

Eine weitere Methode: KB258038: How To Access and Modify SQL Server BLOB Data by Using the ADO Stream Object mit dem VB Beispiel “Saving the Data in a SQL Server Image Column to a File on the Hard Disk” und “Transfer the Image Stored in a .gif File to an Image Column in a SQL Server Table”.

In Teil Zwei geht es dann um den umgekehrten Weg – wie bekomme ich Files per T-SQL als BLOB in die Datenbank

Möge T-SQL immer so hilfreich sein! ;-)

Beitrag von Toni Pohl

Comments
  • Hallo,

    danke für den tollen Ansatz! Wahrscheinlich liegt es wie so oft an einem Details.

    Das Script schreibt mir in das  SQL Ausgabefenster alle Namen der Dateien, aber leider nichts auf die Festplatte. Was mache ich falsch. Die ID habe ich raus genommen da die nicht als Integer vorliegt.

    Hier mein Script und wie es aufgerufen wird?

    Ein Tipp wäre super, da ich mit OLE keine Erfahrung habe und schon Stunden damit verbringe ohne weiter zu kommen.

    Ganz herzlichen Dank für jede Hilfe im voraus

    Jan Hardt

    PS: eMail Jan.Hardt@web.de

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER PROC [dbo].[save2file]

    AS

    -- original script www.sqlteam.com/.../topic.asp by "Peso"

    -- modified script www.atwork.at/.../save2file.txt by Toni Pohl

    declare @ObjectToken INT

    declare @TheImage VARBINARY(MAX)

    declare @Save2File VARCHAR(255)

    declare @FileName varchar(255)

    DECLARE TheCursor CURSOR FAST_FORWARD FOR

    SELECT  rtrim(strSourceFilename), Blob -- OR: FIL_Thumbnail

    FROM dbo.BLOB

    OPEN TheCursor

    FETCH NEXT FROM TheCursor INTO @FileName, @TheImage

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- SET @Helper = right('00000' + rtrim(convert(varchar, @id)), 5)

    -- Pfad anpassen!

    SET @Save2File = 'C:\Temp\' + '_' + @FileName -- @Helper +

    PRINT @Save2File

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT -- Creates an instance of an OLE object.

    EXEC sp_OASetProperty @ObjectToken, 'Type', 1 -- Sets a property of an OLE object to a new value.

    EXEC sp_OAMethod @ObjectToken, 'Open' -- Calls a method of an OLE object.

    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @TheImage

    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @Save2File, 2

    EXEC sp_OAMethod @ObjectToken, 'Close'

    EXEC sp_OADestroy @ObjectToken -- Destroys a created OLE object.

    FETCH NEXT FROM TheCursor INTO @FileName, @TheImage

    END

    CLOSE TheCursor

    DEALLOCATE TheCursor

    Aufruf mit

    use ff

    exec save2file

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment