Trzy sposoby na eksport obrazów z SQL Server

Dziś powiemy sobie nieco o tym jak eksportować obiekty takie jak zdjęcia czy też ogólnie pojęte obiekty BLOB przechowywane w bazie danych do rzeczywistych fizycznych plików. Do tego celu wykorzystamy trzy różne techniki w postaci narzędzia bcp, skryptów OLE DB Automation oraz pakietów Integration Services. W tym miejscu chciałbym zaznaczyć, iż nie będziemy teorii ani omawiać zalet i wad przechowywania tego typu danych wewnątrz bazy danych, po prostu stworzymy sobie prosty tutorial, który mam nadzieję okaże się pomocny. Ogólnie rzecz biorąc warto powiedzieć, że jeśli możemy to danych tego typu lepiej w bazie nie przechowywać, jednakże czasem nie ma wyjścia dlatego artykuł skierowany jest do osób będących w takiej sytuacji:) Zapraszam serdecznie do lektury.

Pierwszym sposobem jaki sobie pokażemy jest pakiet Integration Services. Technikę tą postanowiłem przedstawić jako pierwszą ze względu na to, iż wydaje mi się ona zdecydowanie najprostsza i w moim mniemaniu pozbawiona jest mniejszych bądź większych „haczyków” występujących w przypadku pozostałych metod. Zakładam, że nie wszyscy czytający są specjalistami od tego narzędzia dlatego też zrobimy wszystko krok po kroku.

Na samym początku musimy stworzyć nowy projekt w Data Tools/Visual Studio dlatego też musimy kliknąć File -> New -> Project tak jak zostało to przedstawione na poniższym zrzucie ekranowym:

W następnym kroku musimy wybrać typ projektu tj. Integration Services -> Integration Services Project. Sam projekt możemy oczywiście umieścić w określonej lokalizacji pod wybraną przez nas nazwą:

Wraz ze stworzeniem projektu automatycznie powinien stworzyć się dla nas pakiet. Będąc na zakładce Control Flow tworzymy przepływ danych czyli Data Flow Task przeciągając to zadanie z SSIS Toolbox (jeśli nie jest on widoczny należy go włączyć wybierając z górnego paska opcję View – > SSIS Toolbox) na wolne pole:

W dalszym kroku przechodzimy na zakładkę Data Flow (poprzez dwukrotne kliknięcie Data Flow Task) i tam z SSIS Toolbox przeciągamy dwa elementy, a mianowicie OLE DB Source (bądź analogiczny adapter pozwalający pobrać dane z bazy danych) oraz Export Column tak jak zostało to przedstawione poniżej:

Oba „klocki” musimy skonfigurować. W OLE DB Source wybieramy przycisk New aby stworzyć nowe źródło danych:

W oknie konfiguracyjnym musimy podać adres serwera, formę uwierzytelnienia oraz nazwę bazy danych do której będziemy się łączyć ( w tym przykładzie będzie to oczywiście AdventureWorks).

Po zatwierdzeniu przyciskiem OK w oknie konfiguracji adaptera podajemy zapytanie, które zwróci wymagane elementy tj. kolumnę w której znajduje się obiekt do eksportu oraz kolumna z pełną ścieżką gdzie ten ma się znajdować . Całość zatwierdzamy ponownie przyciskiem OK.

Pierwszy krok skonfigurowany, możemy zatem przejść do Export Column. Tutaj wskazujemy kolumnę z danymi i ze ścieżką. Mamy również dodatkowe opcje, które na ten moment nas nie interesują (możecie o nich przeczytać tutaj).

Pakiet uruchamiamy klikając F5 i jeśli wszystko się powiodło to powinniśmy zielone znaczniki z ilością wyeksportowanych wierszy:

Przechodząc do folderu możemy zauważyć, że pliki zostały prawidłowo wyeksportowane i działają bez zarzutu:

Działa całkiem przyzwoicie prawda?

Drugim sposobem na osiągnięcie tego wyniku jest wykonanie odpowiedniej komendy narzędzia bcp. Aby prawidłowo wyeksportować dane BLOB przy użyciu tego narzędzia musimy utworzyć tzw. Format file – zrobimy to w następujący sposób:

bcp AdventureWorks2012.Production.ProductPhoto format nul -T -n -f c:\export\PP.fmt

Ze względu na to, że tworzenie pliku formatu odbywa się na podstawie całej tabeli musimy go nieco zmodyfikować. Otwórzmy powstały plik w notatniku i z poniżej formy:

zmieńmy go na następującą:

Pamiętajcie, że po ostatnim wierszu pliku formatu musi być nowa linia! W innym przypadku można nieźle się napocić zanim rozwiążemy problem z ciągle pojawiającym się błędem.  Szczególnie ważne jest również zamienienie 8 na 0 w trzeciej kolumnie dzięki czemu bcp „nie dopisze” nic do pliku i będą one zwykłymi obrazkami.

BCP eksportuje jeden plik na raz dlatego też musimy w TSQL (możemy to zrobić też w inny sposób np. w Powershell) stworzyć sobie pętle które przejdzie plik po pliku i go wyeksportuje. Z poziomu TSQL aby wywołać polecenie linii komend musimy wykorzystać niezwykle niebezpieczną procedurę rozszerzoną o nazwie xp_cmdshell, które jeśli się zdecydujemy na tą metodą musi być po wykonaniu tej operacji natychmiast wyłączone ze względów bezpieczeństwa.

Samą komendę włączymy używając sp_configure w następujący sposób:

sp_configure 'show advanced options',1
reconfigure

sp_configure 'xp_cmdshell',1
reconfigure

Nasza pętla przedstawia się w następujący sposób (wykluczyliśmy te produkty, które mają przypisany obrazek no_image_available_small.gif’ czyli tak naprawdę brak obrazka) :

DECLARE @MaxID BIGINT =(SELECT MAX(ProductPhotoID) FROM AdventureWorks2012.[Production].[ProductPhoto] WHERE ThumbnailPhotoFileName<>'no_image_available_small.gif')
DECLARE @CurrentID BIGINT=(SELECT MIN(ProductPhotoID) FROM AdventureWorks2012.[Production].[ProductPhoto] WHERE ThumbnailPhotoFileName<>'no_image_available_small.gif')
DECLARE @FileName VARCHAR(100)=(SELECT LargePhotoFileName FROM AdventureWorks2012.[Production].[ProductPhoto] WHERE ProductPhotoID=+CAST(@CurrentID AS NVARCHAR(10)))
DECLARE @Query VARCHAR(4000)=''

WHILE @CurrentID<=@MaxID
BEGIN
	
	SET @Query='BCP "SELECT LargePhoto FROM AdventureWorks2012.[Production].[ProductPhoto] WHERE ProductPhotoID='+
                         CAST(@CurrentID AS NVARCHAR(10))+'" queryout C:\export\'+@FileName+' -T -f C:\export\PP.fmt'
	PRINT @Query
	EXEC xp_cmdshell  @command_string=@Query
	SET @CurrentID=(SELECT MIN(ProductPhotoID) FROM AdventureWorks2012.[Production].[ProductPhoto] WHERE ProductPhotoID>@CurrentID 
                         AND ThumbnailPhotoFileName<>'no_image_available_small.gif')
	SET @FileName =(SELECT LargePhotoFileName FROM AdventureWorks2012.[Production].[ProductPhoto] WHERE ProductPhotoID=@CurrentID)

END

Przed jej uruchomieniem usuńmy zawartość folderu (oprócz pliku formatu). Po uruchomieniu powinniśmy mieć dostępne wszystkie obrazki produktów. Nie będę tego przedstawiał w postaci zrzutu ekranowego bo jak to wygląda wszyscy wiemy, tak więc musicie zaufać mi na słowo lub przetestować powyższy skrypt samemu:)

Ostatnią metodą jaką chciałbym przedstawić jest użycie procedury sp_OAMethod będącej jedną z dostępnych w SQL Server procedur OLE Automation Sam skrypt opiera się na bardzo podobnej pętli co powyżej, z tą różnicą że zamiast wywołania bcp tworzymy ADODB.Stream za pomocą którego zapisujemy do fizycznego pliku nasze zdjęcie:

DECLARE @MaxID BIGINT =(SELECT MAX(ProductPhotoID) FROM AdventureWorks2014.[Production].[ProductPhoto] 
                          WHERE ThumbnailPhotoFileName<>'no_image_available_small.gif')
DECLARE @CurrentID BIGINT=(SELECT MIN(ProductPhotoID) FROM AdventureWorks2014.[Production].[ProductPhoto]
                             WHERE ThumbnailPhotoFileName<>'no_image_available_small.gif')
DECLARE @FileName VARCHAR(100)=(SELECT 'C:\Photos\'+ThumbnailPhotoFileName FROM AdventureWorks2014.[Production].[ProductPhoto] 
                                 WHERE ProductPhotoID=+CAST(@CurrentID AS NVARCHAR(10)))
DECLARE @Query VARCHAR(MAX)=''
DECLARE @BLOB NVARCHAR(MAX)=(SELECT LargePhoto FROM AdventureWorks2014.[Production].[ProductPhoto] 
                              WHERE ProductPhotoID=+CAST(@CurrentID AS NVARCHAR(10)))

WHILE @CurrentID<=@MaxID
BEGIN
	print @FileName
	DECLARE @ObjectToken INT
	EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
	EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
	EXEC sp_OAMethod @ObjectToken, 'Open';
	EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @BLOB;
	EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FileName, 2;
	EXEC sp_OAMethod @ObjectToken, 'Close';
	EXEC sp_OADestroy @ObjectToken;

	SET @CurrentID=(SELECT MIN(ProductPhotoID) FROM AdventureWorks2014.[Production].[ProductPhoto] 
                        WHERE ProductPhotoID>@CurrentID AND ThumbnailPhotoFileName<>'no_image_available_small.gif')
	SET @FileName =(SELECT 'C:\export\'+ThumbnailPhotoFileName FROM AdventureWorks2014.[Production].[ProductPhoto] 
                        WHERE ProductPhotoID=CAST(@CurrentID AS NVARCHAR(10)))
	SET @BLOB =(SELECT LargePhoto FROM AdventureWorks2014.[Production].[ProductPhoto] 
                         WHERE ProductPhotoID=+CAST(@CurrentID AS NVARCHAR(10)))
END

Efekt w przypadku działania tego kodu jest dokładnie taki jakbyśmy się tego spodziewali. Oczywiście to nie wszystkie dostępne metody bo do osiągnięcia podobnego efektu możemy napisać skrypt w C# czy innym języku programowania lub chociazby użyć Powershell jednakże zdecydowałem się przedstawić powyższe metody ponieważ wg. mnie są one przejrzyste i niemal każdy może je wywołać bez większego problemu. Warto również zapoznać się z technologiami takimi jak FILESTREAM czy FILETABLE ponieważ nie zawsze musimy trzymać obiekty BLOB wewnątrz bazy danych, która mimo wszystkich opcji nie jest idealnym miejscem do przechowywania tego typu obiektów. Inną sprawą jest import tego typu danych do bazy danych, który możemy wykonać bardzo podobnie do powyższych metod jednak mimo wszystko nieco inaczej, postaram się to przedstawić w wolnej chwili w ramach innego artykułu. Na ten moment dziękuję za uwagę i mam nadzieję, że artykuł okazał się przydatny.