Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server
Πιστοποιήσεις:
- Πώς ενεργοποιούμε το Unified Auditing σε Oracle Database - 7 Οκτώβριος 2024
- Τι είναι η PostgreSQL και πως κάνουμε μία πλήρης εγκατάσταση - 2 Σεπτέμβριος 2024
- Πώς ρυθμίζουμε το replication του GoldenGate ώστε να διαβάζει από το Standby του Oracle Data Guard - 2 Αύγουστος 2024
Στον SQL Server έχουμε τη δυνατότητα να μπορούμε να αποθηκεύσουμε μέσα σε ένα πεδίο ενός πίνακα της βάση δεδομένων αρχεία όπως Docx, Xlsx, XML, JSON, JPG, PNG κ.α.. Στο άρθρο αυτό θα αναλύσουμε πως γίνεται αυτό και πως γίνεται αντίστροφα να εξάγουμε τα αρχεία αυτά από την βάση σε κάποιον φάκελο στο file system.
Πώς εισάγουμε τα αρχεία από έναν φάκελο στον πίνακα
Για αρχή θα πρέπει να φτιάξουμε τον πίνακα που θα τα εισάγουμε. Στον πίνακα αυτόν θα ορίσουμε ένα πεδίο ταυτότητας με αύξων αρίθμηση, το όνομα του αρχείου, τον τύπου του αρχείου και τέλος ένα πεδίο τύπου varbinary(max) το οποίο θα περιέχει ολόκληρο το αρχείο σε binary μορφή.
create table mig_db.dbo.docs ( id int identity(1,1), docname nvarchar(200), filetype varchar(100), document varbinary(max) ); alter table mig_db.dbo.docs add constraint pk_docs primary key clustered(id);
Τα αρχεία που θέλουμε να φορτώσουμε βρίσκονται στον παρακάτω φάκελο στον server μας:
Το μόνο που έχουμε να κάνουμε τώρα είναι να φορτώσουμε μέσα στον πίνακα με insert ένα ένα τα αρχεία που θέλουμε. Τo πεδίο που θα περιέχει το αρχείο φορτώνεται ως blob (binary large object) άμεσα με την γνωστή function openrowset, όπως φαίνεται στα παρακάτω παραδείγματα:
insert into mig_db.dbo.docs select N'doc1.docx','.docx',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc1.docx', single_blob) as lob; insert into mig_db.dbo.docs select N'doc2.docx','.docx',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc2.docx', single_blob) as lob; insert into mig_db.dbo.docs select N'doc3.doc','.doc',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc3.doc', single_blob) as lob; insert into mig_db.dbo.docs select N'dp_logo.jpg','.jpg',bulkcolumn from openrowset (bulk N'c:\SQL2019\dp_logo.jpg', single_blob) as lob; insert into mig_db.dbo.docs select N'excel1.xlsx','.xlsx',bulkcolumn from openrowset (bulk N'c:\SQL2019\excel1.xlsx', single_blob) as lob; insert into mig_db.dbo.docs select N'pdfdoc.pdf','.pdf',bulkcolumn from openrowset (bulk N'c:\SQL2019\pdfdoc.pdf', single_blob) as lob;
Αφού ολοκληρώσουμε το φόρτωμα, αν κάνουμε select του πίνακα θα δούμε το αρχεία αυτά σε μορφή binary:
select * from mig_db.dbo.docs;
Πώς εξάγουμε τα αρχεία από τον πίνακα πάλι πίσω σε φάκελο
Για να εξάγουμε τα αρχεία έχουμε δύο τρόπους, θα δούμε πρώτα τον δύσκολο και μετά τον εύκολο.
Hard-way Export (BCP)
Ο δύσκολος τρόπος είναι με την χρήση του BCP και του xp_cmdshell. Η δυσκολία δεν είναι στη χρήση τους αλλά στο ότι τα αρχεία που θα εξάγονται από το BCP θα φαίνονται ως corrupted καθώς θα χρειάζονται μορφοποίηση.
Η διαδικασία είναι η εξής:
- Να ενεργοποιήσουμε το xp_cmdshell αν δεν είναι ήδη
- Με το επόμενο query να δημιουργήσουμε το template με το format που πρέπει να έχουν τα αρχεία (template.fmt).
- Στο αρχείο που θα δημιουργηθεί (template.fmt) να αλλάξουμε όπου έχει το 8 με 0.
- Να τρέξουμε το επόμενο query BCP στο οποίο έχουμε ορίσει ποιο αρχείο θέλουμε από τον πίνακα, σε ποιο file path θέλουμε να το εξάγει και το αρχείο με το format που δημιουργήσαμε στο προηγούμενο βήμα.
--hard way exec sp_configure 'show advanced options', '1' RECONFIGURE exec sp_configure 'xp_cmdshell', '1' RECONFIGURE SET NOCOUNT ON; CREATE TABLE tt(the_file VARBINARY(MAX) NOT NULL); GO SET NOCOUNT ON; DECLARE @bcp_cmd VARCHAR(8000); SET @bcp_cmd='BCP '+QUOTENAME(DB_NAME())+'.dbo.tt format nul -T -N -f "C:\SQL2019\export\template.fmt"'; EXEC master.sys.xp_cmdshell @bcp_cmd; DROP TABLE tt; GO -- Anoigoume to file (template.fmt) pou exei dimiourgei kai allazoume tin timi 8 me 0. SET NOCOUNT ON; DECLARE @bcp_cmd VARCHAR(8000); SET @bcp_cmd='BCP "SELECT document FROM mig_db.dbo.docs where docname =''dp_logo.jpg''" QUERYOUT "C:\SQL2019\export\dp_logo.jpg" -T -fC "C:\SQL2019\export\template.fmt"'; EXEC master..xp_cmdshell @bcp_cmd; GO
Easy-way (Ole Automation Procedures)
Αυτό τό script δεν το έφτιαξα μόνος μου, το βασικό του κομμάτι προήλθε από τον Jitendra στο οποίο έκανα μερικές τροποποιήσεις.
Η διαδικασία με αυτή τη μέθοδο είναι πολύ απλή. Tο μόνο που έχουμε να κάνουμε είναι τα εξής:
- Να ενεργοποιήσουμε τα ‘Ole Automation Procedures’ αν δεν είναι ήδη.
- Να ορίσουμε στη μεταβλητή @outputpath τον φάκελο που θα γίνει το export.
- Να ορίσουμε στον πίνακα-μεταβλητή @Doctable το query με τα δεδομένα που θέλουμε να εξάγουμε (το πεδίο με το όνομα του αρχείου και το πεδίο με το varbinary).
- Τέλος να εκτελέσουμε όλο μαζί το query.
--easy way sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO DECLARE @outPutPath varchar(50) = 'C:\SQL2019\export' ------------ <-------- Export path , @i bigint , @init int , @data varbinary(max) , @fPath varchar(max) , @folderPath varchar(max) --Get Data into temp Table variable so that we can iterate over it DECLARE @Doctable TABLE (id int identity(1,1), [FileName] varchar(100), [Doc_Content] varBinary(max) ) INSERT INTO @Doctable([FileName],[Doc_Content]) select docname,document from dbo.docs; ------------ <---------- Export query --SELECT * FROM @table SELECT @i = COUNT(1) FROM @Doctable WHILE @i >= 1 BEGIN SELECT @data = [Doc_Content], @fPath = @outPutPath + '\' +[FileName] FROM @Doctable WHERE id = @i EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instance created EXEC sp_OASetProperty @init, 'Type', 1; EXEC sp_OAMethod @init, 'Open'; -- Calling a method EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method EXEC sp_OAMethod @init, 'Close'; -- Calling a method EXEC sp_OADestroy @init; -- Closed the resources print 'Document Generated at - '+ @fPath --Reset the variables for next use SELECT @data = NULL , @init = NULL , @fPath = NULL SET @i -= 1 END
Αφού ολοκληρωθεί θα δούμε ότι έχει εξάγει στον φάκελο όλα τα αρχεία που είχαμε στον πίνακα:
Wouldn’t it be better to save the path of the file than the file?