Una delle operazioni più "noiose" che un DBA può incontrare sulla sua strada è la copia di file binari (pdf, documenti word, excel, xml, gif. jpg, ecc...) all'interno di una tabella SQL. Le specifiche del T-SQL, allo stato attuale, non prevedono istruzioni in grado di facilitare questo compito, non esiste per intenderci una sorta di "INSERT FILE INTO TABLE".
Per trovare soluzione è necessario ingegnarsi, scrivendo codice VB o C# per interfacce e programmi che permettano la copia di files da e verso SQL Server.
Esiste però un'altra strada, Microsoft distribuisce con SQL Server un programma adatto allo scopo e dal nome indicativo: textcopy.exe. In questo articolo vedremo come utilizzarlo al meglio.
Dove si trova textcopy
Textcopy viene distribuito con SQL Server e si trova all'interno della directoy Binn creata durante l'installazione standard. Ecco un'immagine dell'albero delle directories:
Textcopy può essere tranquillamente copiato in altre directory, laddove sia necessario utilizzarlo, oppure distribuito su altri PC, in questo caso ci sono dei requisiti da rispettare:
- Avere le librerie lato client di SQL Server installate sul PC dove viene eseguito textcopy (con il protocollo Named Pipe attivato).
- Avere una login a SQL Server con privilegi sufficienti (minimo dbo), per poter accedere al database utilizzato durante la copia.
Descrizione del programma
Textcopy non è illustrato nei Book OnLine di SQL Server e per avere informazioni su come utilizzarlo dobbiamo digitare la stringa "textcopy /?" dal prompt di DOS. Il risultato restituito è il simile a questo (qui è tradotto sommariamente dall'inglese):
TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]
/S sqlserver SQL Server al quale connettersi.
/UloginLogin da utilizzare per la connessione.
/P password Password per la login.
/D database Il database nel quale effettuare la copia.
/T table La tabella che contiene il tipo text o image.
/C column La colonna di tipo TEXT o IMAGE della tabella.
/W "where clause" Una clausola WHERE completa (es. WHERE pk_id='101').
/F file Il nome del file.
/I Copia testo o immagini dal file a SQL Server.
/O Copia testo o immagini da SQL Server su file.
/K chunksize Dimensione del buffer dati trasferito durante la copia.
/Z Visualizza informazioni di debug durante l'esecuzione.
/? Informazioni di aiuto sull'utilizzo del programma.
Textcopy copia un singolo valore di tipo TEXT o IMAGE da SQL Server su file e viceversa.
Il dati vengono presi da una specifica colonna all'interno della tabella indicata. La copia riguarda esclusivamente la singola riga della tabella, difatti è necessario specificare una clausola WHERE completa, del tipo "WHERE pub_id=9".
La direzione della copia è duplice:
- Input, un file o un'immagine vengono copiati dal filesystem all'interno di una riga di una tabella SQL, tutti i dati esistenti nella riga vengono rimpiazzati durante l'operazione.
- Output, il contenuto di una colonna IMAGE o TEXT di una determinata riga viene copiato su un file, rimpiazzando ogni eventuale file con lo stesso nome.
Alcune considerazioni sono d'obbligo:
- I tipi dati ammessi per le colonne su cui o da cui effettuare la copia sono esclusivamente IMAGE o TEXT.
- La copia dei dati riguarda una singola RIGA della tabella, quindi è obbligatorio esplicitare una clausola WHERE completa che coinvolga la chiave primaria della tabella e garantisca la restituzione di quella determinata riga!
Utilizzare textcopy per importate un'immagine su SQL Server
È possibile usare textcopy per copiare un file, nel nostro caso un'immagine, su una colonna di tipo IMAGE all'interno di una tabella di un database SQL. Allo scopo creiamo nel database tempdb una tabella di prova necessaria a memorizzare i dati dell'esperimento, ecco il codice:
Use tempdb
GO
CREATE TABLE PROVA_UPLOAD
(
[Nome] NVARCHAR(255) PRIMARY KEY,
[Immagine] IMAGE DEFAULT(0x0)
)
Una volta creata la tabella dobbiamo inserire un record a cui abbinare i dati dell'immagine da caricare:
INSERT INTO PROVA_UPLOAD (Nome) VALUES ('immagine.gif')
Questo accorgimento è indispensabile, perché il programma riesce ad inserire i dati sulla riga solo se:
- la riga su cui effettuare la copia dei dati è già presente nella tabella
- il contenuto della colonna [Immagine] per la riga indicata durante la copia è diverso NULL
Per questo motivo durante la creazione della tabella è stato aggiunto un vincolo di DEFAULT sulla colonna [Immagine] che provvede, per ogni inserimento, a valorizzare la riga con dati fittizi (es. 0x0) che in seguito verranno rimpiazzati dai valori veri e propri.
Passiamo ora alla copia dell'immagine, per fare ciò dobbiamo:
- Aprire una sessione DOS da Start > Esegui e digitiamo cmd
- Posizioniamoci in una directory a piacimento, ad esempio C:Wutemp, dove poter effettuare le prove di inserimento
- Copiamo il programma textcopy dalla directory Binn sotto l'installazione standard di SQL Server nella directory di lavoro corrente C:Wutemp
- Prendiamo un'immagine a scelta e copiamola all'interno della C:Wutemp. Sarà l'immagine con cui effettuare il test di inserimento su SQL Server, nel mio "immagine.gif".
Fatto questo, lanciamo textcopy con i parametri opportuni, come nello screenshot sottostante:
I parametri passati sono quelli che abbiamo indicati nel paragrafo precedente e devono essere tutti valorizzati in modo corretto. Una volta lanciato il batch il risultato sarà il seguente:
Ora l'immagine è stata copiata nella tabella T_PROVA_UPLOAD del database tempdb, possiamo controllare dal Query Analyzer (select * from tempdb..prova_upload):
Ora tentiamo l'operazione inversa.
Utilizzare textcopy per esportare un'immagine da SQL Server
Tenendo valido tutto ciò che abbiamo detto prima, ora facciamo l'operazione inversa, copiamo i dati presenti nella colonna [Immagine] in corrispondenza della riga 'Immagine.gif' su un nuovo file che chiameremo 'copia_immagine.gif'.
Passiamo i parametri opportuni, ovviamente qui la direzione sarà /O, cioè output mentre il nome del file cambierà: /Fcopia_immagine.gif, lanciamo il comando:
Il risultato restituito a video sarà simile a questo:
La stessa identica cosa può essere fatta con files PDF, famiglia Office, Xml, etc.
Usare textcopy da una stored procedure
Grazie alla procedura di sistema master..xp_cmdshell, possiamo eseguire sessioni DOS dall'interno di una procedura utente. Diventa semplice a questo punto importare ed esportare immagini direttamente dal Query Analyzer, vediamo il codice:
CREATE PROC dbo.P_IMP_EXP_FILE
(
@runpath VARCHAR(255), -- Percorso di textcopy.exe
@srvr SYSNAME, -- Server SQL
@db SYSNAME, -- DB da utilizzare
@usr SYSNAME, -- LOGIN per connessione
@pwd SYSNAME, -- PASSWORD
@tbl SYSNAME, -- TABELLA
@col SYSNAME, -- COLONNA IMAGE O TEXT
@whr VARCHAR(255), -- CLAUSOLA WHERE
@fil VARCHAR(255), -- NOME E PATH del FILE
@mod CHAR(1) = 'I' -- I per importare, O per esportare
)
AS
DECLARE @CMD NVARCHAR(255)
SET @CMD = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd + ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil + ' /' + @mod
EXEC MASTER..XP_CMDSHELL @CMD
GO
La procedura è un semplice wrapper (guscio) per textcopy, ecco come funziona:
--Per importare l'immagine precedente dal filesystem a SQL Server:
EXEC P_IMP_EXP_FILE 'C:WUTemptextCopy.exe',
'fulcanelli',
'tempdb',
'sa',
'secret',
'dbo.PROVA_UPLOAD',
'Immagine',
'"WHERE Nome = ''immagine.gif''"',
'C:WUTempimmagine.gif',
'I'
--Al contrario per esportare l'immagini sul filesystem:
EXEC P_IMP_EXP_FILE 'C:WUTemptextCopy.exe',
'fulcanelli',
'tempdb',
'sa',
'secret',
'dbo.PROVA_UPLOAD',
'Immagine',
'"WHERE Nome = ''immagine.gif''"',
'C:WUTempcopia_immagine_via_sp.gif',
'O'
Questa procedura deve essere eseguita con privilegi di amministratore in un ambiente totalmente sicuro. Il codice della procedura è allegato all'articolo [download]!
Conclusione
In queste pagine abbiamo parlato di un programma di utilità molto interessante: textcopy, che rappresenta un'arma vincente per il DBA che voglia, in modo rapido e veloce, importare o esportare file o immagini da e verso SQL Server.
Nel prossimo articolo vedremo come effettuare un caricamento di massa di centinaia di immagini su SQL Server (oltre 3000 immagini in meno di 6 minuti), il tutto grazie a textcopy ed alcuni comandi DOS.
