Κλείσιμο
Κλείσιμο
Αρχική » Databases » Microsoft SQL Server » Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server

Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server

Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server
, , , , 1 σχόλιο
Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server
Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

Στον 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 μας:

Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server
1

Το μόνο που έχουμε να κάνουμε τώρα είναι να φορτώσουμε μέσα στον πίνακα με 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;
Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server
2

Πώς εξάγουμε τα αρχεία από τον πίνακα πάλι πίσω σε φάκελο

Για να εξάγουμε τα αρχεία έχουμε δύο τρόπους, θα δούμε πρώτα τον δύσκολο και μετά τον εύκολο.

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

Αφού ολοκληρωθεί θα δούμε ότι έχει εξάγει στον φάκελο όλα τα αρχεία που είχαμε στον πίνακα:

Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server
3

Πηγές:

Μοιράσου το

Το 1 σκέφτηκε το "Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server"

Αφήστε μία απάντηση