Mentre mi stavo godendo la tranquillità dell'ufficio semideserto, vengo disturbato via messenger dal solito Andrea che irrompe con le seguenti parole:

Andrea Montanari scrive (16.19):
come "carichi" velocemente un'immagine in una colonna da SSMSE?

Con le precedenti versioni di SQL Server, la risposta a questa domanda non era banale.
ADO e ADO.NET ci offrono da tempo gli strumenti adatti per aggirare elegantemente il problema come riportato nei seguenti articoli della Knowledge Base:

• HOW TO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
• HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual Basic.NET 
• HOW TO: Read and Write a File to and from a BLOB Column by Using ADO.NET and Visual Basic .NET 
• HOW TO: Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET

Inoltre nel numero di giugno 2001 di SQL Server Magazine, Michael Otey ha affrontato il problema della memorizzazione di immagini in SQL Server tramite l'oggetto Stream introdotto con ADO 2.6.

Michael nel numero di ottobre 2003 ha scritto un aggiornamento di questo articolo prendendo in esame le nuove tecniche offerte da ADO.NET, ma è disponibile online solo agli abbonati alla rivista.

Ora poniamoci questa domanda: se io volessi adottare una soluzione "pura" in T-SQL come dovrei fare?
Tempo fa un utente postò una richiesta del genere su microsoft.public.it.sql ed io proposi il seguente esempio che purtroppo richiedeva necessariamente di uno script lato client. Tramite ADO andavo a leggere un file jpeg sottoforma di stream binario e lo passavo come parametro di input ad una stored procedure che effettuava l'insert vero e proprio nel database.
Prima di tutto ho preparato il seguente script T-SQL:

USE tempdb 
GO 

/* Definisto la tabella dbo.Students */ 
CREATE TABLE dbo.Students( 
StudentID int NOT NULL IDENTITY PRIMARY KEY, 
FirstName varchar(10) NOT NULL, 
LastName varchar(10) NOT NULL, 
Photo image NULL 
) 
GO 

/* La popolo */ 
INSERT dbo.Students(FirstName, LastName) VALUES('Lorenzo', 'Benaglia') 
INSERT dbo.Students(FirstName, LastName) VALUES('Luca', 'Bianchi') 
INSERT dbo.Students(FirstName, LastName) VALUES('Andrea', 'Montanari') 
GO 

/* I Books Online riportano: 
** If the table does not have in row text, SQL Server saves space by not initializing 
** text columns when explicit or implicit null values are placed in text columns with 
** INSERT, and no text pointer can be obtained for such nulls. 
** 
** Infatti i puntatori risulteranno invalidi: 
*/ 
SELECT TEXTPTR(Photo) Puntatore 
FROM dbo.Students 
GO 

/* Output: 

Puntatore 
---------------------------------- 
NULL 
NULL 
NULL 

(3 row(s) affected) 

*/ 

/* Il Capitolo 11 del Resource Kit di SQL Server 2000*1 riporta: 
** When you use an UPDATE statement to modify a text, ntext, or image column, 
** the UPDATE initializes the column. This means a valid text pointer is assigned 
** to the column, and at least one data page is allocated for the column. 
** 
** Quindi per inizializzare la colonna Photo applico un UPDATE a tutte le righe: 
*/
UPDATE dbo.Students 
SET Photo = NULL 
GO 

SELECT TEXTPTR(Photo) Puntatore 
FROM dbo.Students 
GO 

/* Output: 

Puntatore 
---------------------------------- 
0xFDFFCC00000000001D00000001000000 
0xFDFFCD00000000001D00000001000100 
0xFDFFCE00000000001D00000001000200 

(3 row(s) affected) 

*/ 

/* Dichiaro la stored procedure dbo.up_AddPhoto */ 
CREATE PROCEDURE dbo.up_AddPhoto( 
 tudentID int, 
@Photo image 
) 
AS 
DECLARE @ptrval varbinary(16) 

/* Recupero il puntatore per l'utente specificato */ 
SELECT @ptrval = TEXTPTR(Photo) 
FROM dbo.Students 
WHERE StudentID =  tudentID 

/* Inserisco l'immagine */ 
WRITETEXT dbo.Students.Photo @ptrval @Photo 
GO 

Come potete notare la stored procedure utilizza la funzione WRITETEXT() per scrivere lo stream binario passato come parametro di input nella colonna Photo della tabella Students.
Supponiamo di avere il file C:\WUtemp\Lorenzo.jpg e di volerlo importare in Students associandolo allo studente Lorenzo (con StudentID = 1).
Per fare questa operazione mi sono servito del seguente script VBScript:

Dim stmPhoto
Dim cmd
Const adTypeBinary = 1
Const CN_STRING = "Provider=SQLOLEDB;Initial Catalog=tempdb;Data Source=localhost;Integrated Security=SSPI"
Const adCmdStoredProc = 4
Const adInteger = 3
Const adVarBinary = 204
Const adParamInput = 1
Const adExecuteNoRecords = 128
 
' Definisco un oggetto ADODB.Stream
Set stmPhoto = CreateObject("ADODB.Stream")
 
' Carico il file Lorenzo.jpg in uno Stream
stmPhoto.Type = adTypeBinary
stmPhoto.Open
stmPhoto.LoadFromFile "c:\WUtemp\Lorenzo.jpg"
 
' Definisco un oggetto ADODB.Command
Set cmd = CreateObject("ADODB.Command")
With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.up_AddPhoto"
    .Parameters.Append .CreateParameter("@StudentID", adInteger, adParamInput, , 1)
    .Parameters.Append .CreateParameter("@Photo", adVarBinary, adParamInput, 8000, stmPhoto.Read)
    .ActiveConnection = CN_STRING
    ' Eseguo la stored procedure
    .Execute, ,adExecuteNoRecords
End With
 
MsgBox "Inserimento effettuato con successo.", vbInformation
 
' Pulizia
stmPhoto.Close
Set stmPhoto = Nothing
Set cmd = Nothing

Quindi, carico l'immagine in un oggetto ADODB.Stream e mi limito ad eseguire la stored procedure passando come parametri di input l'ID che mi identifica la riga da aggiornare e lo stream binario.
"l'accrocchio" funziona, però oltre ad essere un po' complesso, necessita in ogni caso di uno script lato client esterno a SQL Server.

Cosa ci offre SQL Server 2005 per agevolarci la vita? Nell'articolo Leggere un file XML con SQL Server 2005 avevo già avuto modo di parlare del BULK rowset provider e della funzione OPENROWSET, vediamo come ci può tornare utile ora.
Il BULK rowset provider della funzione OPENROWSET è in grado di leggere da un file senza caricare i dati in una tabella di destinazione. Ciò consente di utilizzare OPENROWSET con un semplice comando di SELECT. Se lo desideriamo possiamo caricare l'intero file in una variabile varbinary, varchar o nvarchar specificando una di queste 3 opzioni:

• SINGLE_BLOB 
Restituisce il contenuto di data_file come set di righe a riga singola e colonna singola del tipo varbinary(max).

• SINGLE_CLOB 
Leggendo data_file come ASCII, restituisce il contenuto come set di righe a riga singola e colonna singola del tipo varchar(max), utilizzando le regole di confronto del database corrente.

• SINGLE_NCLOB
Leggendo data_file come UNICODE, restituisce il contenuto come set di righe a riga singola e colonna singola del tipo nvarchar(max), utilizzando le regole di confronto del database corrente.

A questo punto abbiamo tutto quello che ci serve.

USE tempdb;
GO

/* Definisto la tabella dbo.Students */ 
CREATE TABLE dbo.Students(
StudentID int NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL,
Photo varbinary(max) NOT NULL
);
GO

DECLARE @Photo varbinary(max);

/* Carico l'intero file in una variabile varbinary(max) */
SET @Photo = (
      SELECT Q.BulkColumn
      FROM OPENROWSET(BULK 'c:\WUtemp\Lorenzo.jpg', SINGLE_BLOB) AS Q
);

/* Popolo la tabella dbo.Students */
INSERT dbo.Students VALUES('Lorenzo', 'Benaglia', @Photo);
GO

SELECT *
FROM dbo.Students;
GO

/* Output:

StudentID   FirstName  LastName   Photo
----------- ---------- ---------- ------
1           Lorenzo    Benaglia   0x4749...

(1 row(s) affected)

*/

/* Pulizia */
DROP TABLE dbo.Students;

Come vedete, in quattro righe di codice T-SQL siamo riusciti nell'intento che ci eravamo prefissi, adottando una soluzione enormemente più semplice ed efficace rispetto a quelle necessarie con le precedenti versioni di SQL Server.

*¹ http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx