[SQL Server] imageデータをファイルから直接登録する方法

SQL Serverにはimageデータをファイルから直接登録する方法がいくつかあります。

もっとも、imageデータを直接登録するための専用の方法ではなく、ファイルからデータを直接登録する方法の応用なのですが．．．

１．bcpユーティリティによるファイルのアップロードの応用

"[SQL Server] テキストファイルの入出力方法"で触れたように、bcpユーティリティを使えば、テキストファイルからテーブルにデータをアップロードすることができます。

しかし、アップロードできるデータはテキストデータだけではありません。

フォーマットファイルを工夫することによって、imageデータをアップロードすることもできます。

imageデータをアップロードするときのフォーマットファイルは以下のようになります。

test.fmt

  8.0
  2
  1 SQLIMAGE 0 155845 "" 2 IMAGE_DATA ""

３行目の２つ目の項目にSQLIMAGEというデータ型を指定し、４つ目の項目にimageデータのファイルサイズを指定すれば、imageデータのファイルが丸ごと指定した列にアップロードするという指示になります。

このフォーマットファイルとimageデータのファイルを用意し、以下のようにbcpコマンドを実行すれば、

bcp db1.dbo.TABLE1 in test.jpg -Usa -Psa -SSERVER1 -ftest.fmt

test.jpgがTABLE1のIMAGE_DATA列にアップロードされるというわけです。

ただ、１ファイル＝１レコードの１カラムとなってしまうので、imageデータと他の列のデータを同時にアップロードすることはできません。

そのため、imageデータ以外でDEFAULT値なしのNOT NULL列が存在していると制約違反となってしまったり、アップロードしたimageデータを一意に識別する情報が同時にアップロードできないという問題があります。

後者については以下のように一意に識別する情報はIDENTITY属性を持った列を１列持たせると方法で回避できるのですが．．．

create table TABLE1 (
  ID int identity (1, 1) not null,
  IMAGE_DATA image
)

あと複数レコードのimageデータを同時にアップロードすることもできないのが難点です。

２．BII.EXEによるファイルのアップロード

BII.EXEはリソースキットに付属しているユーティリティで、bcpユーティリティと同じくファイルからデータをアップロードすることができます。

BII.EXEの場合、bcpユーティリティのようにフォーマットファイルを用意する必要はありません。

区切り文字で区切られたデータファイルを用意すればそれでデータのアップロードができます。

imageデータは@ファイル名で表すことにより、外部ファイルを利用できるようになっています。

test.dat

1,あああ,aaa,@a.jpg
2,いいい,bbb,@b.jpg
3,ううう,ccc,@c.jpg

上記のようなデータファイルと@ファイル名で示したimageデータのファイルを用意し、以下のようにBII.EXEを実行すれば、

bii TABLE1 in test.dat -SSERVER1 -Ddb1 -Usa -Psa -TTABLE1 -t, -v

imageデータを含むレコードがアップロードされるというわけです。

bcpユーティリティと違ってimageデータを外部ファイルとして指定できるので、１つのデータファイルで複数のレコードを同時にアップロードできるのが利点ですが、テーブルの全ての列にデータをセットしなければならないのが難点です。

また、SQL Serverの標準の機能ではないため、本格的にアプリケーションに組み入れるような使い方をしていいものかどうかも微妙なところです。

という具合に、上記の２つの方法でimageデータをファイルから直接登録することは可能ですが、いずれも使用方法に制約が多いのが難点です。

それにいずれの方法もデータベース外部のユーティリティを使った方法であるため、トランザクションの内部で実行することができません。

ゆえにimgaeデータのアップロードと他のオペレーションを同一トランザクションとして処理したい場合は、別の方法をとらなくてはなりません。

Archive Redo Blog

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

[SQL Server] imageデータをファイルから直接登録する方法

１．bcpユーティリティによるファイルのアップロードの応用

２．BII.EXEによるファイルのアップロード