Oracle/SQL ServerのLOBの入出力
データベースでLOB(ラージオブジェクト)を管理するのは面倒くさいとよく言われる。実際に行う場合、入出力は基本的にJavaや.Netなどのプログラムに頼るのを前提に考える場合が多いだろう。ただ、Oracle/SQL Serverではデータベース側で直接扱う方法も提供されている。特にSQL Serverの場合は、うまく使う方法を紹介している例が少ないので、紹介したいと思う。SQL Server 2000までは、text/imageの2タイプしかなく、取り扱いも面倒だったが、SQL Server 2005からはvarchar(MAX)/varbinary(MAX)が導入されたことで、通常のvarchar型/varbinary型とほぼ同様に扱うことができるようになった。この改善は正直大きい。1)ファイルを格納する(Oracle) Oracleの場合、LOBは明らかに通常のデータ型と別物として取り扱われるため、その操作にはロケータを介する必要があり、Oracleが提供しているDBMS_LOBパッケージを使うことになる。一般的なINSERT手順だと以下の通りとなるが、ステップが多くていかにも使いづらい。(なお、あらかじめ対象パスをCREATE DIRECTORYして権限を付与しておくこと) a) 空のBLOBをINSERTして、BLOBロケータを取得 b) BFILENAMEでBFILEロケータを取得 c) DBMS_LOB.FILEOPENでファイルをオープン d) DBMS_LOB.GETLENGTHでファイルサイズを取得 e) BFILEロケータ、BLOBロケータ、ファイルサイズを使ってDBMS_LOB.LOADFROMFILEを実行 f) DBMS_LOB.CLOSEでファイルをクローズ DECLARE v_blob BLOB; v_floc BFILE := BFILENAME('PATH_TMP','Sample.jpg'); v_size PLS_INTEGER; BEGIN INSERT INTO BINARYTABLE(seq,bin) VALUES (1,EMPTY_BLOB()) RETURNING BIN INTO v_blob; DBMS_LOB.FILEOPEN(v_floc); v_size := DBMS_LOB.GETLENGTH(v_floc); DBMS_LOB.LOADFROMFILE(v_blob,v_floc,v_size); DBMS_LOB.CLOSE(v_floc); END なお、SQL*Loaderを使えば、INSERT限定だが一括読み込みを実行することができる。 こんなコントロールファイルを作成すればよい。 複数ファイルでも一括で読み込めるから、単体の作業としては、便利だろう。 LOAD DATA INFILE blob.dat APPEND INTO TABLE BINARYTABLE FIELDS TERMINATED BY "," (seq, filename FILLER char, filedata LOBFILE( filename ) TERMINATED BY EOF)2)ファイルを格納する(SQL Server) SQL Serverの場合は、相当シンプルで基本的には以下の1文で取り込める。 INSERT INTO BINARYTABLE(seq,bin) SELECT 1,x.* FROM OPENROWSET(BULK 'C:\TEMP\Sample.jpg',SINGLE_BLOB) x3)ファイルを出力する(Oracle) Oracleの場合、DBMS_LOBパッケージとUTL_FILEパッケージの両方を使って、32767バイトずつループして書き出すことになる。これも随分プログラム的な操作になる。PL/SQLもプログラミングだなと実感するケースではある。 a) 対象データを検索して、BLOBロケータを取得 b) DBMS_LOB.GETLENGTHでサイズを取得 c) UTL_FILE.FOPENで出力ファイルをオープン d) DBMS_LOB.READで読み出し、UTL_FILE.PUT_RAWでファイルに書きだす(繰り返し) e) UTL_FILE.FFLUSHでバッファをフラッシュする f) UTL_FILE.FCLOSEでファイルを閉じる (長くなるのでソースは省略)4)ファイルを出力する(SQL Server) SQL Serverの場合、ファイルを格納するときのような方法はないが、bcpユーティリティを利用する方法がある。これをTransact-SQLから実行するには、xp_cmdshellを使う。 注意すべきはプレフィックス長の指定で、VARBINARYの場合は8バイトのプレフィックスが出力されてしまうことだ、そのままだと出力できても開けないことが多い。 したがって、プレフィックス長を0に指定するために、フォーマットファイルを利用する。 フォーマットファイルは一旦手動でBCPを実行することにより、生成することができる。 C:\TEMP>bcp "SELECT BIN FROM DB1.dbo.BINARYTABLE WHERE SEQ=1" QUERYOUT "C:\TEMP\Sample.jpg" -T フィールド BIN [varbinary(max)] のファイル ストレージ型を入力してください(Enter) フィールド BIN [8] のプレフィックス長を入力してください0(Enter) フィールド BIN [0] の長さを入力してください(Enter) フィールド ターミネータ [none] を入力してください(Enter) このフォーマットファイル情報をファイルに保存しますか[y/n]y(Enter) ホストファイル名[bcp.fmt]fmt.fmt(Enter) これにより作成されたフォーマットファイル(fmt.fmt)を使ってBCPを実行する。 DECLARE @cmd varchar(300) SET @cmd='BCP "SELECT BIN FROM DB1.dbo.BINARYTABLE WHERE SEQ=1" QUERYOUT "C:\TEMP\Sample.jpg" -Slocalhost -T -fC:\TEMP\fmt.fmt' EXEC xp_cmdshell @cmd, NO_OUTPUT ただし、xp_cmdshellを利用するには、オプションの変更が必要。 sp_configure 'xp_cmdshell',1 GO reconfigure GO