- PR -

任意条件設定検索の実装方法について

1
投稿者投稿内容
とら
ベテラン
会議室デビュー日: 2006/10/04
投稿数: 54
投稿日時: 2006-11-28 11:16
任意条件設定検索の実装方法についての質問です。

一覧表示画面などで、検索条件として名称を任意入力させ、入力が有る
場合だけ検索条件を追加するようなケースがあると思います。

たとえば、得意先マスター一覧で、検索条件として得意先名を入力させ、
入力があるときのみ入力値を含む得意先名を持つデータを表示し、入力が
無い場合は、条件にしないようなケースです。

これを、OracleにODPで接続するときには、以下のように実装していました。

・発行するSelect文
Select * From Tokuisaki
Where :001 is null Or TokuisakiMei Like :002 ESCAPE '\\'

・パラメータ設定部分のコード
mySQLCommand.Parameters.Add(new OracleParameter("001",TokuisakiMei));
mySQLCommand.Parameters.Add(new OracleParameter("002",
(TokuisakiMei == null) ?
null :
"%" + TokuisakiMei.Replace("\\","\\\\")
.Replace("_","\\_").Replace("%","\\%") + "%"));


しかし、SQLServerにSQLConnectで接続した場合、同様の実装を行うと、
条件が指定されなかった場合（TokuisakiMeiがNullの場合）に、ExecuteReader
を実行した時点で以下のような例外が発生してしまいます。

・SQLServerの場合に発行するSelect文
Select * From Tokuisaki
Where @001 is null Or TokuisakiMei Like @002 ESCAPE '\\'

・SQLServerの場合のパラメータ設定部分のコード
mySQLCommand.Parameters.Add(new SqlParameter("001",TokuisakiMei));
mySQLCommand.Parameters.Add(new SqlParameter("002",
(TokuisakiMei == null) ?
null :
"%" + TokuisakiMei.Replace("\\","\\\\")
.Replace("_","\\_").Replace("%","\\%") + "%"));

・ExecuteReader発行時に発生する例外

System.Data.SqlClient.SqlException:
パラメータ化クエリ '(@001 nvarchar(4000),@002 nvarchar(4000))Select * From Toku'
にはパラメータ @001 が必要ですが、指定されていません。


どうやら、SQLConnectの場合は、パラメータとしてバインドする値がNullの場合は、
パラメータがバインドされていないとみなしているようです。

JavaからJDBCThinドライバ経由でOracleに接続していたときも、同様の方法で
検索が可能だったのですが、SQLServerの場合は、結果的に発行するSQLのWhere句
に相当する箇所をロジックで動的に変更するしか方法は無いのでしょうか？

何か効果的な対策などお持ちの方がいらっしゃいましたら、ご教示頂けないでしょうか？
よろしくお願いします。
かずくん
ぬし
会議室デビュー日: 2003/01/08
投稿数: 759
お住まい・勤務地: 太陽系第三惑星
投稿日時: 2006-11-28 12:29
DBのバージョン書かれてないから、確実じゃないかもしれない。

case文が使えるのなら、以下のサイトの方法でOK
http://oraclesqlpuzzle.hp.infoseek.co.jp/8-2.html
とら
ベテラン
会議室デビュー日: 2006/10/04
投稿数: 54
投稿日時: 2006-11-28 16:50
情報ありがとうございます。

確かに、環境についてまったく書いていませんでした。
失礼しました。

実際に使用している環境は以下のようなものです。
Windows XP Pro SP2
ローカルに導入したSQLServer 2005
VisualStudio2005（C#）の組み合わせです。
SqlConnectionを使用してSQLServerに接続しています。

> case文が使えるのなら、以下のサイトの方法でOK
> http://oraclesqlpuzzle.hp.infoseek.co.jp/8-2.html
この部分も見せてもらったのですが、私の理解力の問題かもしれませんが、
いまいち私の欲しい情報では無いようです。
勘違いだったら申し訳ありません。

問題としているのは、バインドする変数の値がNullの場合および、
バインド変数にNull値をバインドした場合に例外が発生してしまう部分です。

SqlCommandとかSqlParameterのプロパティに何か仕掛けがあるとかだと
簡単に対策が作れそうなのですが、私が調べてみた限りでは、無理そうです。

発行するSQLの動的変更しか無理なのでしょうか？
とら
ベテラン
会議室デビュー日: 2006/10/04
投稿数: 54
投稿日時: 2006-11-28 18:09
自己完結しました。
SqlParameterにNull値を渡す場合にSQLServerが相手の場合は
明示的にDBNull.Valueを渡す必要があるようです。

パラメータ設定部分を以下のようにすることで期待した結果を得ることができました。

mySQLCommand.Parameters.Add(
new SqlParameter("00001",
(v_TokuisakiMei == null) ?
(object)DBNull.Value :
v_TokuisakiMei));

mySQLCommand.Parameters.Add(
new SqlParameter("000023",
(v_TokuisakiMei == null) ?
(object)DBNull.Value :
"%" + v_TokuisakiMei.Replace("\\", "\\\\")
.Replace("_", "\\_").Replace("%", "\\%") + "%"));

お騒がせいたしました。
（ちなみに￥を入力した箇所が\\になってしまっていますが、回避方法がわからないのでそのままにしています）
かずくん
ぬし
会議室デビュー日: 2003/01/08
投稿数: 759
お住まい・勤務地: 太陽系第三惑星
投稿日時: 2006-11-28 18:09
すまん。問題をはきちがえてた。

引用:

問題としているのは、バインドする変数の値がNullの場合および、
バインド変数にNull値をバインドした場合に例外が発生してしまう部分です。


この掲示板で、DBNullをキーワードに検索すれば、幸せになれるかもしれない。

追加：
あらら、遅かった。もう解決済みなのね。

[ メッセージ編集済み 編集者: かずくん 編集日時 2006-11-28 18:10 ]
1

スキルアップ／キャリアアップ（JOB@IT）