バイナリデータをDBに格納

環境/言語:[Vista, VB.Net, Framework3.5]
分類:[.NET]

VB.NetとPostgreSQLにて開発をしています。

サーバにあるファイル(Excel,PDF等)をバイナリデータとしてDBに格納したいのですが、
うまくいきません。
(そもそもファイルをDBに格納すべきでないという事実は調べましたが…)

サンプルコードを下記に記述します。
DB(テーブル)の内容は
　No Char(10)
　File Bytea
です。

Dim path As String = "ファイルパス"
Dim no As String = "no00000001"

Dim con As New OdbcConnection(prvStrConnection)
Dim com As New OdbcCommand()
com.Connection = con

Dim fs As New FileStream(path, FileMode.Open, FileAccess.Read)
Dim bs(fs.Length - 1) As Byte
fs.Read(bs, 0, bs.Length)

Dim query As String = "INSERT INTO テーブル名 (no,file) " & _
"VALUES ('@no','@file');"
com.Parameters.Clear()
com.Parameters.Add("@no", OdbcType.Char).Value = no
com.Parameters.Add("@file", OdbcType.Binary).Value = bs

com.CommandText = query

con.Open()
com.ExecuteNonQuery() ※１
con.Close()

エラー発生箇所は※１で、内容は
"ERROR [22001] ERROR: value too long for type character(10); Error while executing the query"
ERROR [22001] の内容は"文字列データの右側の切り詰め"
でした。

パラメータを追加する際の型が間違えているのかと思い、
「VarChar」なども指定してみたのですが、同じエラーが発生しました。

よろしければご指摘をお願い致します。

> com.Parameters.Clear()
> com.Parameters.Add("@no", OdbcType.Char).Value = no
> com.Parameters.Add("@file", OdbcType.Binary).Value = bs

　　Parametersの使い方が間違っていると思うのですが・・・

　　SQLでは・・・
　　http://support.microsoft.com/kb/316887/ja

　　これをODBC用に書き換えれば使えます。

●　で、これでも無い場合・・・
　　PostgreSQLは使ったことないのですが、そのBLOB列の設定は
　　byteaなんでしょうか？

　　これも違ったら、解りません・・・

以上。

追伸・・・

１回目のＳＱＬ文実行　＝＞　モレが発生する確率大
２回目の実行　　　　　＝＞　モレなし
３回目の実行　　　　　＝＞　モレなし
・
ｎ回目の実行　　　　　＝＞　モレなし

と言うテストを行い。結果的に初回のみであれば、ダミー的に１回
実行した後に再度もう１回行ってよし！とするとか。

そういう回避策を講じた部分もありました。

参考まで・・・

以上。

オショウ様、返信ありがとうございます。
丸１日の放置をお許し下さい。

>Parametersの使い方が間違っていると思うのですが・・・
なんと、間違っていたのですか。
ttp://blog.livedoor.jp/akf0/archives/51431337.html
上記サイトを参考にしたのですが…。

>ttp://support.microsoft.com/kb/316887/ja
>これをODBC用に書き換えれば使えます。
ありがとうございます。
ですが、ODBC用に書き換えてみたところ、コンパイルが通りませんでした。
Dim param As New OdbcParameter("@file", OdbcType.VarBinary, fs.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, fs)
と記述したところ、
縮小変換しないで呼び出されるアクセス可能な'New'が無いため、オーバロードの解決に失敗しました。
とのことでした…。

>PostgreSQLは使ったことないのですが、そのBLOB列の設定は
>byteaなんでしょうか？
はい。PostgreSQLでバイナリデータを格納できるデータ型は'bytea'のみと認識しています。

>１回目のＳＱＬ文実行　＝＞　モレが発生する確率大
>２回目の実行　　　　　＝＞　モレなし
>ｎ回目の実行　　　　　＝＞　モレなし
申し訳ないのですが、ちょっと理解しかねました。
ループで繰り返し実行した場合、１回目のSQL実行が空振りするという事ですか？

> ですが、ODBC用に書き換えてみたところ、コンパイルが通りませんでした。
> Dim param As New OdbcParameter("@file", OdbcType.VarBinary, fs.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, fs)
> と記述したところ、
> 縮小変換しないで呼び出されるアクセス可能な'New'が無いため、オーバロードの解決に失敗しました。
> とのことでした…。

　　こちらでは、問題なく通って実行できました。
　　が、fs って何ですか？バイト配列なら問題ありません。
　　FileStreamなら多分、そこでしょう！

> >１回目のＳＱＬ文実行　＝＞　モレが発生する確率大
> >２回目の実行　　　　　＝＞　モレなし
> >ｎ回目の実行　　　　　＝＞　モレなし
> 申し訳ないのですが、ちょっと理解しかねました。
> ループで繰り返し実行した場合、１回目のSQL実行が空振りするという事ですか？

　　あれ？・・・
　　なんか違うカキコの文字列がついていました・・・

　　これは忘れて下さい。

以上。

たびたび返信ありがとうございます。

>fs って何ですか？バイト配列なら問題ありません。
>FileStreamなら多分、そこでしょう！
申し訳ございません。サンプルコードの記述ミスでした。
まさに「fs(FileStream)」ではなく「bs(Byte())」です。

いろいろ試したところ、私の方法でもオショウ様に紹介頂いたサイトの方法でも
どちらでも動作することを確認しました。

私の方法
com.Parameters.Add("@no", OdbcType.NChar, 10).Value = no
com.Parameters.Add("@file", OdbcType.VarBinary).Value = bs
オショウ様の方法
Dim param1 As New OdbcParameter("@no", OdbcType.NChar, 10, no)
Dim param2 As New OdbcParameter("@file", OdbcType.VarBinary, bs.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, bs)

com.Parameters.Add(param1)
com.Parameters.Add(param2)

上記コードで、「No」の値を直接SQL文に埋め込み、
バイナリデータのみ変数をパラメータに追加というテストを行ったところ
共に問題なく動作しました。

なので、問題は
com.Parameters.Add("@no", OdbcType.NChar, 10).Value = no
or
Dim param1 As New OdbcParameter("@no", OdbcType.NChar, 10, no)
com.Parameters.Add(param1)
こちらのようです。

う〜ん、あとちょっとなんだけどなぁ・・・