型付Datasetの%検索について

お久しぶりです。刈谷です。

vb2008にて型付DatasetでSQL SERVER2008 EEから検索を行っています。
このときLike検索で%（前方一致）を行いたいのですが、パラメータで%が含まれていると_（一文字検索）になってしまいます。
多分、インジェクション対策でこのような仕様だと思うのですが、このことについて書かれている文書はありますでしょうか？
最悪、そのカラムの長さになるまで＿で埋めればいいのですが、コードがわかりにくくなるので、できればプロパティ等で%を許すことができればいいのですが・・・

こんにちは。

SQL文のLIKEについていえば、%は１文字以上の文字を表していて、一文字だけの検索になってしまうことはありません。なので前方一致検索も後方一致検索も「%VB」,「VB%」のように簡単にかけます。

どんなコードを書かれていますか？

るきおさん、こんにちは。

DataSetデザイナーを使っているので、コードらしいコードは書いていないんですよね・・・
テーブル構造とSQL文・再現手順を記載してみます。

テーブル名
　TestTable
カラム
　Key1 char(10) NOT NULL
　Value1 char(10) NOT NULL
Primary Key
　Key1

SQL文
　SELECT          Key1, Value1
　FROM            TestTable
　WHERE           (Key1 LIKE @key1)

手順
１）DataSetデザイナーで上記テーブルとクエリー（クエリー名：FillBy）を追加
２）Formに作成されたTestTableをドロップし、DataGridViewで表示
３）Form1_Loadに作成されたコードを修正し、FillByに変更。
Me.TestTableTableAdapter.FillBy(Me.TestDataSet.TestTable, "%")
とすると本来なら、すべてのレコードが表示されると思うのですが、１件も表示されないです。
また、”％”を％１０個にするとすべてのレコードが表示されるので、「％を＿におきかえれいるのでは？」と思った次第です。
（SQL文自体はDatasetデザイナーのクエリービルダで実行したときに期待通りの動作はしています。）


補足
この手に問題にぶつかったときは、プロファイルでとんでるSQLを捕まえて内容を確認しているのですがExprssEditionでは使えませんでした。


P.S.
そういえば、皆さんのところでは台風はどうでした？
台風が近くを通ったはずなのに、雨風はあまり吹かずただの曇りの日でした。

そういえば、％検索を前方一致と書いていましたが、正しくないですね。

るきおさん
＞%は１文字以上の文字を表していて
自分も間違った表現をしていて突っ込むのも何なんですが、０文字以上だと思います。（＾＿＾；）ヾ

> SQL SERVER2008 EE
無暗に略さない方が良いですよ。
Enterprise Edition (最上級のエディション)なのか、それとも
Express Edition (無償のエディション)なのか判断が付きません。
(バージョンによっては、"Embedded Edition" なんてのもあったり…)


> また、”％”を％１０個にするとすべてのレコードが表示されるので
％ ではなく、
% にしなければいけないと思います。

それと、% 2個の場合はどうなりますか?


> WHERE           (Key1 LIKE @key1)
データベースの照合順序は何でしょうか?
また、比較時に照合順序を指定した場合にはどうなるでしょうか?
  WHERE Key1 LIKE @key1 COLLATE Japanese_CI_AS
  WHERE Key1 LIKE @key1 COLLATE Japanese_90_CI_AS
  WHERE Key1 LIKE @key1 COLLATE Japanese_90_CI_AS

http://social.msdn.microsoft.com/forums/ja-JP/sqlserverja/thread/66d195e8-eb03-41ba-8dfe-f53def44c88d/

魔界の仮面弁士さん、ありがとうございます。

>> SQL SERVER2008 EE
>無暗に略さない方が良いですよ。
すみませんでした。
EEと略すと複数のEditionが対象になってしまいますね。

＞% にしなければいけないと思います。
入力ミスで全角になっていました。プログラム上では半角で指定しています。
すみませんでした。

＞データベースの照合順序は何でしょうか?
あまり、データベースには詳しくないのでManagementStudioのGUIで作成しました。
照合順序の指定はしていないので、データベースのプロパティで確認したところJapanese_CI_ASと
なっていました。（確認場所が違ったらご指摘ください）

＞また、比較時に照合順序を指定した場合にはどうなるでしょうか?
＞  WHERE Key1 LIKE @key1 COLLATE Japanese_CI_AS
＞  WHERE Key1 LIKE @key1 COLLATE Japanese_90_CI_AS
＞  WHERE Key1 LIKE @key1 COLLATE Japanese_90_CI_AS

すべて、１件も表示されませんでした。
（２行目と３行目は、同じですよね）


テスト中にchar(10)をvarchar(10)等の可変長に変えたところ、検索されました。
詳細がわかり次第、おって報告したいと思います。

(削除されました)

> EEと略すと複数のEditionが対象になってしまいますね。

で、実際のエディションは?



> 照合順序の指定はしていないので

照合順序の指定は、開発段階から意識しておいた方が良いですよ。

SQL Server のバージョンによっては、幾つかの照合順序バグの存在も知られていますし。

http://sqljp.com/yoshihirokawabata/archive/2005/10/04/14414.aspx



> （２行目と３行目は、同じですよね）

すみません。投稿後に気付いたのですが、修正するまでも無いと思って放置していました。

(BIN や BIN2 など、幾つかの照合順序を列挙するつもりで、編集し損ねていました)



> char(10)をvarchar(10)等の可変長に変えたところ

あぁ！そういえば文字列指定時の型に関して注意点がある事を思い出しました。



今回の場合、@key1 パラメータの型は何に設定されていますか?

（パラメータの Size を 0 か -1 にすれば解決するかもしれません）





DbType = 

    AnsiStringFixedLength

    StringFixedLength

    AnsiString

    String

    その他



Size =

    10

    0

    -1

    その他





たとえば、Size = 10 な文字列系パラメータにしておいた場合、

"%" という文字列は "%         " に変換されて渡される可能性があります。



この場合、『末尾に 9 文字以上の空白を含むデータ』の意味になるため、

「          」や「A         」には一致するのですが

「1234567890」や「XYZ       」には一致しない可能性があります。

魔界の仮面弁士さん、ありがとうございます。

＞＞ EEと略すと複数のEditionが対象になってしまいますね。
＞で、実際のエディションは?

本題のほうが抜けてました。
Express Editionです。


＞照合順序の指定は、開発段階から意識しておいた方が良いですよ。
＞SQL Server のバージョンによっては、幾つかの照合順序バグの存在も知られていますし。

照合順序については、これから勉強したいと思います。
また、バグの情報ありがとうございます。


＞今回の場合、@key1 パラメータの型は何に設定されていますか?
＞（パラメータの Size を 0 か -1 にすれば解決するかもしれません）

DbTypeはAnsiString,ProvierTypeはchar,Sizeは１０にしています。
どうも魔界の仮面弁士さんに教えていただいた内容のような動きをしているので、sizeを変更して試してみたいと思います。

また、この件に関してマイクロソフトにインシデントをあげましたので、何かわかりましたらご報告させていただきます。（インシデントの回答って書き込みしてはまずいですか？）

> （インシデントの回答って書き込みしてはまずいですか？）
「
　『～という現象が発生していましたが、最終的に……という回答を貰って解決しました』
　という内容でコミュニティに投稿したいのですが、構いませんか？
」
などと尋ねてみてください。
よほど秘匿性が高いものでなければ、許可してもらえる事が多いと思いますよ。

魔界の仮面弁士さん、ありがとうございます。

インシデントが解決したときに、マイクロソフトに確認し許可が下りればご報告いたします。

マイクロソフトより回答をいただきましたので、ご報告いたします。
（事前にマイクロソフトにコミュニティーに投稿してもよいとの回答をいただいています）

原因としては、やはり魔界の仮面弁士さんに教えていただいたとおり、パラメータタイプが固定長の場合は文字列の長さがカラムのサイズに満たない場合は、後ろに空白を埋めているためとのことです。
（参考：http://msdn.microsoft.com/ja-jp/library/ms179859.aspx）

回避法としては、
魔界の仮面弁士さんから教えていただいた方法でも可能とのことですが、マイクロソフトとしてはパラメータのProviderTypeをcharからvarcharに変えるほうがいいとのことでした。


回答をいただいた、魔界の仮面弁士さん・るきおさん、ありがとうございました。