SQL Blob Import

Hazel4tw1 · 15 November 2016

Hallo zusammen,

folgendes möchten ich realisieren:

Ich bekomme täglich eine .zip Datei zugeschickt worin sich Audio Dateien befinden. Diese Audio Dateien möchte ich als Blob automatisiert in den SQL Server importieren.

Nun habe ich das Problem das die Audio Dateien immer wechselne Namen hat.

Kann ich die Dateinamen SQL seitig auslesen und im Statement verwenden?

Ich hatte dieses Beispiel gefunden und es klappt auch manuell (Beispiel ist mit einem pdf File):

CREATE TABLE [dbo].[TestBlob](
[tbId] [int] IDENTITY(1,1) NOT NULL,
[tbName] [varchar](50) NULL,
[tbDesc] [varchar](100) NULL,
[tbBin] [varbinary](max) NULL
) ON [PRIMARY]

Insert TestBlob(tbName, tbDesc, tbBin) Select
'81.pdf','PDF file', BulkColumn from Openrowset( Bulk
'C:\blob\udoc\81.pdf', Single_Blob) as tb

Jedoch wüsste ich nicht wie ich den Dateinamen auslesen kann und nicht manuell angeben muss.

Jemand eine Idee?

ukulele · 16 November 2016

Mit xp_cmdshell kannst du auf die Windows-Konsole zugreifen und dir die Ausgabe von dir in eine temporäre Tabelle schreiben. Dann durchläufst du mit dynamischem SQL die Dateinamen und rufst ggf. einen Import-Befehl auf.

Ist schon ein bischen Arbeit und birgt theoretisch auch Sicherheitsrisiken, hab ich aber auch gemacht um Video-Dateien zu indexieren, hab nur den Code nicht hier

Hazel4tw1 · 16 November 2016

Ok vielen Dank für die Antwort.

So in etwa habe ich mir das auch gedacht. Schade das es nicht einfacher geht.

Nur dachte ich mir ich schreibe die Dateinamen über Powershell und einem Bulk Import in eine Helper Tabelle.
Alles andere ist mir glaub ich zu komplex.

ukulele · 16 November 2016

Vielleicht gibt es noch eine elegantere Möglichkeit, ich brauchte den Zugriff auf die CMD sowieso weil ich für jede Video-Datei einen Aufruf an das Tool Mediainfo gemacht habe um Auflösung und Tonspuren und soetwas abzugreifen. Also habe ich xp_cmdshell gleich auch für die Dateinamen verwendet (acuh mit Unterverzeichnissen).

Folgen die Dateinamen einem Muster wie z.B. eine Numerierung?

Hazel4tw1 · 16 November 2016

Leider nicht,

prinzipiell hätte ich einer andere Tabelle eine Spalte die auf jeden Fall jeden Dateinamen beinhaltet (ohne .wav Endung) jedoch sind in dieser Spalte auch Einträge wo es keine Datein zu gibt. (ist wahrscheinlich ein Performance Frage, sind aber pro Tag max. 50 Einträge, wobei 10 Einträge dann tatsächlich auf der Festplatte vorhanden sind.)

Könnt ich mir die Einträge stück für stück wie in einer Art Schleife in den Insert Blob Befehl holen?

ukulele · 16 November 2016

Ja du baust dir den INSERT BLOB-Befehl in einer Schleife immer wieder neu als String zusammen und führst ihn per EXEC() aus, nennt sich dann dynamisches SQL.

Hazel4tw1 · 18 November 2016

Hallo ukulele,

ich habe Online ein Script gefunden welches meine Sache wohl ganz gut machen sollte, leider bekomme ich es nicht richtig zum laufen.

Code:

Code:

-- ——————————–
-- Description:
--this proc imports/syncs imagefiles from a folder in filesystem into table dbo.ReportImages
--If the image already exists in table it will be updated otherwise a new row in table will be generated
-- ——————————–
-- Changes:
--   2012-09-21 JJuelke initial
--—————————–
/*Testcase
EXEC ST_ReportImagesImport @PictureFolder=’D:\TEMP\SUP\types\‘,@IncludeSubFolders=1
*/

CREATE PROC ST_ReportImagesImport
      @PictureFolder nvarchar(2000) = 'D:\xxxxx\temp\Recordings_2016-11-06\' --define input directory here which contains your image-files
     ,@IncludeSubFolders int = 1
     ,@OutputFileList int = 1
     ,@OutputTargetTable int = 1

as

--check if directory exists
    declare @file_results table (file_exists int,file_is_a_directory int,parent_directory_exists int)
    insert into @file_results (file_exists, file_is_a_directory, parent_directory_exists) exec master.dbo.xp_fileexist @PictureFolder
    IF NOT EXISTS (select * from @file_results WHERE file_is_a_directory=1)
    BEGIN
        Print 'WARNING! Directory "'+@PictureFolder+'" does not exist at local file system of sql server! Please check variable @PictureFolder and run script again!'
        RETURN -1
    END ELSE
BEGIN

--create target table if not exists already
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES a where a.TABLE_NAME = 'ReportImages')
BEGIN
    CREATE TABLE [dbo].[ReportImages](
        [ReportImagesID] [int] IDENTITY(1,1) NOT NULL,
        [Filename] nvarchar(2000) NULL,
        [ImageBlob] [image] NOT NULL,
        [MimeType] nvarchar(200) default('Image/jpeg') NULL,
        CreateDT datetime default(getdate()),
        UpdateDT datetime NULL)
END

DECLARE @files table (ID int identity, FileFQN nvarchar(2000), FileName nvarchar(200), mimetype nvarchar(100), depth int, [isFile] int, blob [image] )
DECLARE @filename nvarchar(2000), @mimetype nvarchar(100), @FileFQN nvarchar(2000)

--now get folder content (recursive) and store this information in @files table
    INSERT INTO @files (FileName,depth,isfile)
    EXEC master.dbo.xp_DirTree @PictureFolder,@IncludeSubFolders,1
    DELETE FROM @files WHERE isFile = 0
    UPDATE @files set
        FileFQN   = @PictureFolder + FileName
        , mimetype= CASE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) - CHARINDEX('.',filename)  )))
                                         WHEN 'jpg' THEN 'Image/jpeg'
                                         WHEN 'jpeg' THEN 'Image/jpeg'
                                         WHEN 'bmp' THEN 'Image/bmp'
                                         WHEN 'gif' THEN 'Image/gif'
                                         WHEN 'png' THEN 'Image/png'
                                         ELSE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) - CHARINDEX('.',filename)  )))
                                         END
    --outpuf read filelist                                  
    IF @OutputFileList=1 select * from @files

--now loop/read each file and import
DECLARE @insertString nvarchar(3000)
    DECLARE row_cursor CURSOR FOR SELECT FileFQN, FileName,mimetype FROM @Files
        OPEN row_cursor
        FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
        WHILE @@FETCH_STATUS = 0
            BEGIN
              SET @insertString = N'WITH Source (BulkColumn,Filename,mimetype) as (                              
                                    SELECT BulkColumn,"'+@FileName+'" as FileName, "'+@mimetype+'" as mimetype
                                    FROM OPENROWSET(BULK N"' + @FileFQN + '", SINGLE_BLOB) as tempImg)
                                 
                                    MERGE into dbo.ReportImages t
                                        USING Source s ON s.FileName = t.FileName
                                        WHEN MATCHED
                                        THEN UPDATE SET
                                                 t.ImageBlob = s.BulkColumn
                                                ,t.mimetype = s.mimetype
                                                ,t.UpdateDT = getdate()
                                        WHEN NOT MATCHED by target then
                                            INSERT  (FileName,ImageBlob,mimetype)
                                            VALUES  (s.FileName,s.BulkColumn,s.mimetype);'
                EXEC SP_ExecuteSQL @statement=@insertString
            FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
            END
    CLOSE row_cursor
    DEALLOCATE row_cursor

--output target table
IF @OutputTargetTable=1 select * from dbo.ReportImages

END

Folgendes kommt als Fehler:

https://picload.org/image/rdwowplg/2016-11-1811_22_09-.png

https://picload.org/image/rdwowpld/2016-11-1811_22_21--.png

Entferne ich die "N's" kommt folgendes als Fehler:

https://picload.org/image/rdwowplo/2016-11-1811_24_07-.png

https://picload.org/image/rdwowplc/2016-11-1812_05_30.png

Eine Idee?
Ich hatte mir durchgelesen was dieser "N" Prefix macht, verstehen tu ich das in diesem zusammenhang leider nicht wirklich.

Danke und Gruß

ukulele · 18 November 2016

Ich würde sagen du kannst das N weg lassen und aus dem NVARCHAR() einen VARCHAR() machen, schon probiert? Ich sehe hier nicht die Notwendigkeit für Unicode.

Ich glaube der Fehler der angemeckert wird ist:

Code:

FROM OPENROWSET(BULK N"' + @FileFQN + '", SINGLE_BLOB)

innerhalb des Strings. Da kommt ja dann sowas bei raus:

Code:

OPENROWSET(BULK N"asdf", SINGLE_BLOB)

Hazel4tw1 · 18 November 2016

Ändere ich alle NVARCHAR zu VARCHAR kommt:

Code:

(1 row(s) affected)

(2 row(s) affected)

(0 row(s) affected)

(2 row(s) affected)

(2 row(s) affected)
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Ändere ich nur die Variable FileFQN und filename auf Varchar kommt der gleiche Fehler:

Code:

(1 row(s) affected)

(2 row(s) affected)

(0 row(s) affected)

(2 row(s) affected)

(2 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'D:\xxxxx\temp\Recordings_2016-11-06\134733828.wav'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'D:\xxxxx\temp\Recordings_2016-11-06\134733979.wav'.

(0 row(s) affected)

(1 row(s) affected)

Also der Pfad ist richtig, jedoch erkenn ich nicht warum der Pfad in dem Bulk Import nicht passen sollte

ukulele · 18 November 2016

Wenn du den Typ der Variable von NVARCHAR in VARCHAR änderst musst du natürlich auch den Input ändern und das N' raus nehmen. Nur eins von beiden ist ja Blödsinn.

In dem zusammengebauten Code steht vermutlich

Code:

FROM OPENROWSET(BULK N"D:\xxxxx\temp\Recordings_2016-11-06\134733979.wav", SINGLE_BLOB)

und hier beißt sich das N mit den Anführungszeichen.

Code:

FROM OPENROWSET(BULK "D:\xxxxx\temp\Recordings_2016-11-06\134733979.wav", SINGLE_BLOB)

so könnte es gehen.

Hazel4tw1 · 18 November 2016

Vielen Dank Ukulele

Code funktioniert nun. Hatte glaube ich noch Stress mit den Quatation Marks. und halt der nvarchar variable.

Hier der funktionierende Code:

Code:

-- ——————————–
-- Description:
--this proc imports/syncs imagefiles from a folder in filesystem into table dbo.ReportImages
--If the image already exists in table it will be updated otherwise a new row in table will be generated
-- ——————————–
-- Changes:
--   2012-09-21 JJuelke initial
--—————————–
/*Testcase
EXEC ST_ReportImagesImport @PictureFolder=’D:\TEMP\SUP\types\‘,@IncludeSubFolders=1
*/

CREATE PROC ST_ReportImagesImport
      @PictureFolder nvarchar(2000) = 'D:\MemoCDR\temp\recordings_2016-11-06\' --define input directory here which contains your image-files
     ,@IncludeSubFolders int = 1
     ,@OutputFileList int = 1
     ,@OutputTargetTable int = 1

as

--check if directory exists
    declare @file_results table (file_exists int,file_is_a_directory int,parent_directory_exists int)
    insert into @file_results (file_exists, file_is_a_directory, parent_directory_exists) exec master.dbo.xp_fileexist @PictureFolder
    IF NOT EXISTS (select * from @file_results WHERE file_is_a_directory=1)
    BEGIN
        Print 'WARNING! Directory "'+@PictureFolder+'" does not exist at local file system of sql server! Please check variable @PictureFolder and run script again!'
        RETURN -1
    END ELSE
BEGIN

--create target table if not exists already
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES a where a.TABLE_NAME = 'ReportImages')
BEGIN
    CREATE TABLE [dbo].[ReportImages](
        [ReportImagesID] [int] IDENTITY(1,1) NOT NULL,
        [Filename] varchar(2000) NULL,
        [ImageBlob] [image] NOT NULL,
        [MimeType] nvarchar(200) default('Image/jpeg') NULL,
        CreateDT datetime default(getdate()),
        UpdateDT datetime NULL)
END

DECLARE @files table (ID int identity, FileFQN nvarchar(2000), FileName nvarchar(200), mimetype nvarchar(100), depth int, [isFile] int, blob [image] )
DECLARE @filename nvarchar(2000), @mimetype nvarchar(100), @FileFQN nvarchar(2000)

--now get folder content (recursive) and store this information in @files table
    INSERT INTO @files (FileName,depth,isfile)
    EXEC master.dbo.xp_DirTree @PictureFolder,@IncludeSubFolders,1
    DELETE FROM @files WHERE isFile = 0
    UPDATE @files set
        FileFQN   = @PictureFolder + FileName
        , mimetype= CASE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) - CHARINDEX('.',filename)  )))
                                         WHEN 'jpg' THEN 'Image/jpeg'
                                         WHEN 'jpeg' THEN 'Image/jpeg'
                                         WHEN 'bmp' THEN 'Image/bmp'
                                         WHEN 'gif' THEN 'Image/gif'
                                         WHEN 'png' THEN 'Image/png'
                                         WHEN 'wav' THEN 'Audio/wav'
                                         ELSE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) - CHARINDEX('.',filename)  )))
                                         END
    --outpuf read filelist                                    
    IF @OutputFileList=1 select * from @files

--now loop/read each file and import
DECLARE @insertString nvarchar(3000)
    DECLARE row_cursor CURSOR FOR SELECT FileFQN, FileName,mimetype FROM @Files
        OPEN row_cursor
        FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
        WHILE @@FETCH_STATUS = 0
            BEGIN
              SET @insertString = 'WITH Source (BulkColumn,Filename,mimetype) as (                               
                                    SELECT BulkColumn,' + char(39) + @FileName + char(39) + 'as FileName,' + char(39)+ @mimetype + char(39)+ ' as mimetype
                                    FROM OPENROWSET(BULK ' + char(39)+ @FileFQN + char(39)+', SINGLE_BLOB) as tempImg)
                                   
                                    MERGE into dbo.ReportImages t
                                        USING Source s ON s.FileName = t.FileName
                                        WHEN MATCHED
                                        THEN UPDATE SET
                                                 t.ImageBlob = s.BulkColumn
                                                ,t.mimetype = s.mimetype
                                                ,t.UpdateDT = getdate()
                                        WHEN NOT MATCHED by target then
                                            INSERT  (FileName,ImageBlob,mimetype)
                                            VALUES  (s.FileName,s.BulkColumn,s.mimetype);'
                                                         
                EXEC SP_ExecuteSQL @statement=@insertString
            FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
            END
    CLOSE row_cursor
    DEALLOCATE row_cursor

--output target table
IF @OutputTargetTable=1 select * from dbo.ReportImages

END

SQL Blob Import

Hazel4tw1

Aktiver Benutzer

ukulele

Datenbank-Guru

Hazel4tw1

Aktiver Benutzer

ukulele

Datenbank-Guru

Hazel4tw1

Aktiver Benutzer

ukulele

Datenbank-Guru

Hazel4tw1

Aktiver Benutzer

ukulele

Datenbank-Guru

Hazel4tw1

Aktiver Benutzer

ukulele

Datenbank-Guru

Hazel4tw1

Aktiver Benutzer

Ähnliche Themen