[SQL Server] imageデータをファイルに直接出力する。

SQLで検索したimageデータをファイルに直接出力するにはbcpユーティリティを利用します。

といっても、そう簡単にできるものではありません^^;

bcpユーティリティでファイルを出力する場合にはまず以下のようなフォーマットファイルを用意しなければなりません。

8.0 
1 
1  SQLIMAGE  0  205  ""  1  FILE_IMAGE  ""

このフォーマットファイルではFILE_IMAGEという列をSQLIMAGE型として出力するよう指示しているのですが、問題は３行目の４つめの項目、データサイズです。

データサイズは当然のことながらデータによって異なります。

そのため、出力する各imageデータごとにフォーマットファイルを用意してやらなければなりません。

となると、あらかじめ用意しておくのではなく、動的に作ってやらなければなりません。

imageデータを出力する手順は、まず出力するimageデータのデータサイズを取得し、それを元にフォーマットファイルを出力し、その後、imageデータをファイルに出力するためのbcpコマンドを編集して実行するということになります。

具体的な出力例を以下に示します。

DECLARE
  @sqltext    varchar(1000),
  @cmdtext    varchar(1000),
  @file_id    int,
  @file_name  varchar(100),
  @length     int
BEGIN

  --imageデータを取得するカーソルを定義し、１件ずつ取得する。
  SET @sqltext = 'SELECT FILE_ID, FILE_NAME, DATALENGTH( FILE_IMAGE ) FROM TABLE1'
  EXEC ('DECLARE cur CURSOR FOR ' + @sqltext + ' FOR READ ONLY')
  OPEN cur
  FETCH NEXT FROM cur INTO @file_id, @file_name, @length
  WHILE @@FETCH_STATUS = 0
  BEGIN

    --フォーマットファイルを作成する。
    SET @cmdtext = 'ECHO 8.0 > FILE.FMT'
    EXEC master.dbo.xp_cmdshell @cmdtext
    SET @cmdtext = 'ECHO 1 >> FILE.FMT'
    EXEC master.dbo.xp_cmdshell @cmdtext
    SET @cmdtext = 'ECHO 1  SQLIMAGE  0  ' 
            + CAST(@length AS VARCHAR) 
            + '  ""  1  FILE_IMAGE  "" >> FILE.FMT'
    EXEC master.dbo.xp_cmdshell @cmdtext

    --ファイルを出力する。
    SET @cmdtext = 'BCP "SELECT FILE_IMAGE FROM '+ DB_NAME() + '.dbo.TABLE1 WHERE FILE_ID = ' 
        + CAST(@file_id AS VARCHAR) + '" queryout "' 
        + @file_name + '" -f "FILE.FMT"'
    EXEC master.dbo.xp_cmdshell @cmdtext

    FETCH NEXT FROM cur INTO @file_id, @file_name, @length

  END
  CLOSE cur
  DEALLOCATE cur

END
GO

とまぁ、こんな具合で少々ブサイクなやり方になってしまうのですが、これでimageデータを無理矢理ファイルに出力することができます。

この方法はテキストデータをファイルに出力する時にも応用は可能なので、ニーズに応じていろいろと工夫してみるといいでしょう。

Archive Redo Blog

DBエンジニアのあれこれ備忘録

[SQL Server] imageデータをファイルに直接出力する。