[seasar-dotnet:1638] Re: 外だしSQLについて

kubo [E-MAIL ADDRESS DELETED]
2010年 3月 28日 (日) 17:48:50 JST


久保(jflute)です。

かなりヤマカンなのですが、
http://ml.seasar.org/archives/seasar-dotnet/2010-February/001556.html
この辺の話が関連したりしないかなと。

いずれにせよ、ADO.NET単体での
再現プログラムがあった方が良いかと思います。
(それを使ってDbTypeの変更なども検証できるかと)

2010/3/28 kubo <[E-MAIL ADDRESS DELETED]>:
> 久保(jflute)です。
>
>> ですので、DBのエンコードを変える、NVARCHARに変更
>> することは、現状難しいです。また、ご提案いただいた、テーブルに「レベル」
>> を設ける方法も難しいです。
>> データプロバイダの変更は試してみる価値があると思いますが、
>> まだ検証しておりません。Oracleへの確認もまだ行っておりません。
>
> 大詰めということなので、個人的にはデータプロバイダの変更も
> 現実的ではないですね。原因を掴むという意味で試す価値は大いにありますが。
>
> 回避の手だてですが、やはり原因の場所を特定させるために、
>
>> S2Dao.NETという要因の関係性を明確にするために、
>> 外だしSQLの部分を「ADO.NET直接利用」で試して
>> みると良いです。
>
> まずは、これををやってみた方が良いかと思います。
> (もしやっていたら結果を教えて頂けばと)
> いきなりS2Dao.NETのどこを直すを考えるより、
> ADO.NET直接で再現させてから回避ポイントが探した方が、
> S2Dao.NETでの修正ポイントも明確になりやすいかと。
> (原因が全然検討違いだったら時間がもったいないし)
>
>> そこで、再び確認させていただきたいのですが、外だしSQLのバインド部分の文字エンコードを
>> 指定する設定はSeasar側にあるのでしょうか?
> S2Dao.NETでは、Seasar.Extension.ADO.Types.BaseValueType
> の BindValue() でバインド処理を行っているはずです。
>
> ちなみに、バインド処理でエンコードっていう概念が
> ちょっと思い当たらないのですが...
> http://msdn.microsoft.com/ja-jp/library/system.data.idbdataparameter_members.aspx
> IDbDataParameterのメンバにもエンコーディングを
> 指定するものは見当たらないですね...
> C#上の文字列(String型)はunicodeで扱われてるはずなので、
> Shift_JISに変換掛けるとなると、byte配列でADO.NETに渡すか、
> やはり、ADO.NETに対してエンコーディングを指定する
> ようなやり方にはなると思います。(あるとすれば)
> (ODPのクラスを直接利用すれば指定できたりするとか...!?!?)
>
> 2010/3/28 笹木 悠壱 <[E-MAIL ADDRESS DELETED]>:
>> 久保様
>>
>> 笹木です。お世話になります。
>> すいません、返信が遅くなりました。
>>
>> まず、この件ですが、非常にクリティカルな問題です。
>> 開発でSeasarを使わせて頂いるのですが、現在開発が大詰めを迎えているためです。
>> (もっと早くに気付くべきでした。)
>>
>> ですので、DBのエンコードを変える、NVARCHARに変更
>> することは、現状難しいです。また、ご提案いただいた、テーブルに「レベル」
>> を設ける方法も難しいです。
>> データプロバイダの変更は試してみる価値があると思いますが、
>> まだ検証しておりません。Oracleへの確認もまだ行っておりません。
>>
>> 希望ですが、Seasar側でこの現象を回避できればと思っております。
>> 今回、検証させていただき、原因となっているのは、バインド時に文字エンコードが
>> 変わってしまっていることだと想像できます。そこをSeasar側で何とか出来ないかと検証しております。
>> その後の検証で、外だしSQLのバインド部分をTO_CHARで囲むことで
>> 現象が回避でることがわかりました。下記の様なSQLです。
>>
>> SELECT *
>>  FROM M_LESSON
>> WHERE COURSE >= TO_CHAR(/*pmb.CourseFrom*/)
>>  AND COURSE <= TO_CHAR(/*pmb.CourseTo*/)
>>
>> また、SQLツールで、文字リテラル部分を、N'xxxxx'として、ユニコードに変換するようにSQLを
>> 記述すると、想定外の結果と同じレコード件数になることがわかりました。下記の様なSQLです。
>>
>> SELECT *
>>  FROM M_LESSON
>> WHERE COURSE >= N'T初級'
>>  AND COURSE <= N'T上級'
>>
>> そこで、再び確認させていただきたいのですが、外だしSQLのバインド部分の文字エンコードを
>> 指定する設定はSeasar側にあるのでしょうか?
>>
>> また、設定が無ければ、Seasarのオープンソースに手を加えたいと考えております。
>> バインド処理を行っている箇所を強制的にシフトJISにエンコードする旨の記述を加えます。
>> その箇所をお教えいただけないでしょうか?
>> (一度、ソースを拝見したのでが、それがどこなのかわかりませんでした。)
>>
>> そもそも、見当違いなことを申し上げていたら、何卒ご容赦下さい。
>>
>> 以上、宜しくお願い申し上げます。
>>
>> 笹木
>>
>> ----- Original Message ----- From: "kubo" <[E-MAIL ADDRESS DELETED]>
>> To: <[E-MAIL ADDRESS DELETED]>
>> Sent: Friday, March 19, 2010 10:10 AM
>> Subject: [seasar-dotnet:1614] Re:外だしSQLについて
>>
>>
>>> 久保(jflute)です。
>>>
>>> 検証ありがとうございます。
>>> さらにまとめると:
>>>
>>> .NET-3.5
>>> Oracle11g (on Windows2008)
>>> o NLS_CHARACTERSET=JA16SJISTILDE
>>> o NLS_NCHAR_CHARACTERSET=AL16UTF16
>>>
>>> 「漢字の大なり小なり検索が想定外となる」
>>> 外だしSQL + バインド変数 = x (HITしない)
>>> 外だしSQL + 埋め込み変数 = o
>>> SQL*Plus + バインド変数 = o
>>> SQL*Plus + 埋め込み変数 = o
>>>
>>> S2Dao.NETという要因の関係性を明確にするために、
>>> 外だしSQLの部分を「ADO.NET直接利用」で試して
>>> みると良いです。で、さらに結果が変わらなければ、
>>> データプロバイダを別のものに変えて試してみましょう。
>>> ODPとOLEで結果が同じかどうか!?
>>> (そもそも何をご利用ですか?)
>>> ここまでやるとかなり状況がクリアになるかと思います。
>>>
>>> その後は、ちょっとこの問題のクリティカル性次第なのですが、
>>> もし、業務的に困ってないけど気になって投稿してみた、
>>> というのであれば適当なところで検証を打ち切るのもアリですが、
>>> 重要な問題というのであれば、
>>>
>>> o NVARCHARを利用してみる
>>> o DBのエンコーディングをUTF-8にしてみる(ちょっと大変かな)
>>> などもできるならばやってみると良いかもしれません。
>>> (万が一、回避できる選択肢が見つかればと)
>>>
>>>
>>> 上記の検証結果次第では、ちょっと「コミッタだからわかる」
>>> というレベルを超えちゃっているので、Oracleのサポート、
>>> (もしくはデータプロバイダ)に問い合わせられるなら
>>> 問い合わせてみたほうが良いかもしれません。
>>> あと、Oracleの掲示板(OTNとか)を探して同じような現象が
>>> 報告されていないか、なければそちらで投稿して聞いてみる
>>> とかも良いかもしれません。
>>>
>>>
>>> #
>>> # 個人的には、漢字データに対して厳密な大なり小なりの
>>> # 意識が必要になることはほとんどなかったので、
>>> # この件に関する情報は何も持ってないです。
>>> # また、自分がDB設計するときにはそういう条件が
>>> # (極力)必要にならないようにします。
>>> # (基本的に文字列型で大なり小なり条件が発生しないように)
>>> #
>>> # 例えば、コースは、char(3)のコード(COURSE_CODEとか)
>>> # にして、FK制約でCOURSEマスタテーブルを参照して、
>>> # そちらのテーブルに「レベル」を表現するカラムを追加
>>> # して(数値など)、そのカラムで大なり小なり条件とするなど。
>>> # もし、そういった回避ができるのであれば、それも一つの
>>> # 選択肢かと思います(検証作業が大変だと思うので)。
>>> #
>>>
>>> 2010/3/19 笹木 悠壱 <[E-MAIL ADDRESS DELETED]>:
>>>>
>>>> 藤井様
>>>>
>>>> お世話になっております。笹木です。
>>>>
>>>> 先程、久保様にご報告させていただいた内容で
>>>> 何かお分かりになりましたら、幸いです。
>>>>
>>>> 以上、宜しくお願い申し上げます。
>>>>
>>>> 笹木
>>>>
>>>> ----- Original Message ----- From: "Hiroaki Fujii"
>>>> <[E-MAIL ADDRESS DELETED]>
>>>> To: <[E-MAIL ADDRESS DELETED]>
>>>> Sent: Thursday, March 18, 2010 10:37 PM
>>>> Subject: [seasar-dotnet:1610] Re: 外だしSQLについて
>>>>
>>>>
>>>>> 藤井です。
>>>>>
>>>>> 改めて、Oracle11g、S2Container.NET1.3.17で確認いたしました。
>>>>> テーブルを再度作り直してSQLPlusで実行すると、きちんと2件と
>>>>> ヒットしました。
>>>>> あれ、Oracle10gから11gになって挙動が変わった?何か間違えたかな?
>>>>>
>>>>> その上で、メールの通りコードも記述し、外だしSQL文も同じように
>>>>> 作ってみましたが、きちんとS2DAO.NETは2件、帰ってきました。
>>>>> メールのコピペで作ったわけではありませんが、何が影響してうまく
>>>>> 帰ってこないのか、わからない状況です。
>>>>>
>>>>> さらに、何か情報を提供いただけたら、何かわかるかもしれません。
>>>>>
>>>>>
>>>>>
>>>>> 笹木 悠壱 さんは書きました:
>>>>>>
>>>>>> 藤井様
>>>>>>
>>>>>> お世話になっております。笹木です。
>>>>>>
>>>>>> ご確認いただき、ありがとうございます。
>>>>>> ご指摘いただいた点を踏まえて、再度検証してみました。
>>>>>>
>>>>>> まず、S2Dao.NET用のインターフェイスの書き方に誤りはございませんでした。
>>>>>> また、インターフェイスとテーブルについて、2バイト文字、漢字等を使用せずに
>>>>>> 実行してみましたが、結果は同じでした。
>>>>>> (結果は下記の通りです。)
>>>>>>
>>>>>> それから、SQL文自体はこれで通ります。
>>>>>> WHERE句の中のANDをORに変えて実行した場合、sqlplusで3件帰ってきてしまいま
>>>>>> す。
>>>>>> (結果は下記の通りです。)
>>>>>>
>>>>>>
>>>>>> ■テーブル
>>>>>> ・テーブル名
>>>>>> M_LESSON
>>>>>> ・フィールド
>>>>>> TENPO  NUMBER(3,0) (PK)
>>>>>> DATE VARCHAR2(8) (PK)
>>>>>> START  VARCHAR2(4) (PK)
>>>>>> END   VARCHAR2(4)
>>>>>> COURSE VARCHAR2(6)
>>>>>>
>>>>>> ■上記テーブル内のレコード
>>>>>> 1,"20100301","1000","1100","T初級"
>>>>>> 1,"20100301","1100","1200","T中級"
>>>>>> 1,"20100301","1200","1300","T上級"
>>>>>>
>>>>>> ■実行①(外だしSQLでパラメータコメントを使用)
>>>>>>
>>>>>> SELECT *
>>>>>> FROM M_LESSON
>>>>>> WHERE COURSE >= /*pmb.CourseFrom*/
>>>>>> AND COURSE <= /*pmb.CourseTo*/
>>>>>>
>>>>>> パラメータ値
>>>>>> pmb.CourseFrom→'T初級'
>>>>>> pmb.CourseTo→'T上級'
>>>>>>
>>>>>> ⇒検索結果は、レコードなしとなります。
>>>>>>
>>>>>> (SQLログ)
>>>>>> Seasar.Extension.ADO.Impl.BasicSelectHandler: 2010-03-18 10:04:40,480
>>>>>> [1] DEBUG Seasar.Framework.Log.Logger Debug - SELECT *
>>>>>> FROM M_LESSON
>>>>>> WHERE COURSE >= 'T初級'
>>>>>> AND COURSE <= 'T上級'
>>>>>>
>>>>>> ■実行②(外だしSQLでパラメータコメントを使用をしない)
>>>>>>
>>>>>> SELECT *
>>>>>> FROM M_LESSON
>>>>>> WHERE COURSE >= 'T初級'
>>>>>> AND COURSE <= 'T上級'
>>>>>>
>>>>>> ⇒検索結果は、レコード2件となります。
>>>>>> 1,"20100301","1000","1100","T初級"
>>>>>> 1,"20100301","1200","1300","T上級"
>>>>>>
>>>>>> ■Daoインターフェイス
>>>>>>
>>>>>> [Bean(typeof(CourseInfo))]
>>>>>> public interface ICourseDao
>>>>>> {
>>>>>> IList<CourseInfo> SelectCourse(CoursePmb pmb);
>>>>>> }
>>>>>>
>>>>>> [System.Serializable]
>>>>>> public class CourseInfo
>>>>>> {
>>>>>> string _Course;
>>>>>>
>>>>>> public string Course
>>>>>> {
>>>>>> get { return _Course; }
>>>>>> set { _Course = value; }
>>>>>> }
>>>>>> }
>>>>>>
>>>>>> [System.Serializable]
>>>>>> public class CoursePmb
>>>>>> {
>>>>>> string _CourseFrom;
>>>>>>
>>>>>> public string CourseFrom
>>>>>> {
>>>>>> get { return _CourseFrom; }
>>>>>> set { _CourseFrom = value; }
>>>>>> }
>>>>>>
>>>>>> string _CourseTo;
>>>>>>
>>>>>> public string CourseTo
>>>>>> {
>>>>>> get { return _CourseTo; }
>>>>>> set { _CourseTo = value; }
>>>>>> }
>>>>>> }
>>>>>>
>>>>>> ■sqlplusでの実行結果
>>>>>> SQL> select * from M_LESSON where COURSE >= 'T初級' and COURSE <= 'T上級';
>>>>>>
>>>>>> TENPO DATE STAR END COURSE
>>>>>> ---------- -------- ---- ---- ------
>>>>>> 1 20100301 1000 1100 T初級
>>>>>> 1 20100301 1200 1300 T上級
>>>>>>
>>>>>> SQL> select * from M_LESSON where COURSE >= 'T初級' or COURSE <= 'T上級';
>>>>>>
>>>>>> TENPO DATE STAR END COURSE
>>>>>> ---------- -------- ---- ---- ------
>>>>>> 1 20100301 1000 1100 T初級
>>>>>> 1 20100301 1100 1200 T中級
>>>>>> 1 20100301 1200 1300 T上級
>>>>>>
>>>>>> 以上、宜しくお願い申し上げます。
>>>>>>
>>>>>> 笹木
>>>>>>
>>>>>> ----- Original Message ----- From: "Hiroaki Fujii"
>>>>>> <[E-MAIL ADDRESS DELETED]>
>>>>>> To: <[E-MAIL ADDRESS DELETED]>
>>>>>> Sent: Wednesday, March 17, 2010 10:52 PM
>>>>>> Subject: [seasar-dotnet:1605] Re: 外だしSQLについて
>>>>>>
>>>>>>
>>>>>>> 藤井です。
>>>>>>>
>>>>>>> 完全に一緒ではないのですが、Oracle10gXE、ODP、Quillが1.3.17の
>>>>>>> 環境で実行しましたが、再現しませんでした。
>>>>>>>
>>>>>>> もう少し調べる前に、お願いなのですが、S2Dao.NET用のインターフェイスの
>>>>>>> 書き方は間違えていることはないでしょうか?
>>>>>>> 引数や2バイト文字の漢字や1バイト文字をプロパティなどで混在して
>>>>>>> 使われているようですが、これらを間違えて使っていることはない
>>>>>>> でしょうか?
>>>>>>>
>>>>>>> それと、SQL文もこれで本当に通るか確認をお願いします。
>>>>>>> WHERE句の中のANDをORに変えたらsqlplusで2件帰ってきました。
>>>>>>>
>>>>>>> 確認をお願いいたします。
>>>>>>>
>>>>>>>
>>>>>>> ㈱ネスティ 笹木 悠壱 さんは書きました:
>>>>>>>>
>>>>>>>> はじめまして。
>>>>>>>> 笹木と申します。
>>>>>>>>
>>>>>>>> 外だしSQLについて質問させて下さい。
>>>>>>>> パラメータコメントに全角文字を指定すると、
>>>>>>>> 想定される結果を取得できないことがあります。
>>>>>>>>
>>>>>>>> パラメータコメントを使用せずに、直接書き込んで
>>>>>>>> 実行した場合は、思ったとおりの結果を取得できます。
>>>>>>>>
>>>>>>>> 何か設定が必要なのでしょうか?
>>>>>>>> 以下、実行内容です。
>>>>>>>>
>>>>>>>> ■開発環境
>>>>>>>> OS:WindowsXP
>>>>>>>> VisualStudio2008
>>>>>>>> .NetFrameWork3.5
>>>>>>>> Oracle11g(OS:Windos2008)
>>>>>>>> S2Container.NET 1.3.17.0
>>>>>>>> S2Dao.NET 1.3.17.0
>>>>>>>>
>>>>>>>> ■テーブル
>>>>>>>> ・テーブル名
>>>>>>>> M_レッスン
>>>>>>>> ・フィールド
>>>>>>>> 店舗   NUMBER(3,0) (PK)
>>>>>>>> 日付   VARCHAR2(8) (PK)
>>>>>>>> 開始時刻 VARCHAR2(4) (PK)
>>>>>>>> 終了時刻 VARCHAR2(4)
>>>>>>>> コース  VARCHAR2(6)
>>>>>>>>
>>>>>>>> ■上記テーブル内のレコード
>>>>>>>> 1,"20100301","1000","1100","T初級"
>>>>>>>> 1,"20100301","1100","1200","T中級"
>>>>>>>> 1,"20100301","1200","1300","T上級"
>>>>>>>>
>>>>>>>> ■実行①(外だしSQLでパラメータコメントを使用)
>>>>>>>>
>>>>>>>> SELECT *
>>>>>>>> FROM M_レッスン
>>>>>>>> WHERE コース >= /*pmb.コースFROM*/
>>>>>>>> AND コース <= /*pmb.コースTO*/
>>>>>>>>
>>>>>>>> パラメータ値
>>>>>>>> pmb.コースFROM→'T初級'
>>>>>>>> pmb.コースTO→'T上級'
>>>>>>>>
>>>>>>>> ⇒検索結果は、レコードなしとなります。
>>>>>>>>
>>>>>>>> (SQLログ)
>>>>>>>> Seasar.Extension.ADO.Impl.BasicSelectHandler: 2010-03-17 12:01:30,400
>>>>>>>> [1] DEBUG Seasar.Framework.Log.Logger Debug - SELECT *
>>>>>>>> FROM M_レッスン
>>>>>>>> WHERE コース >= 'T初級'
>>>>>>>> AND コース <= 'T上級'
>>>>>>>>
>>>>>>>> ■実行②(外だしSQLでパラメータコメントを使用をしない)
>>>>>>>>
>>>>>>>> SELECT *
>>>>>>>> FROM M_レッスン
>>>>>>>> WHERE コース >= 'T初級'
>>>>>>>> AND コース <= 'T上級'
>>>>>>>>
>>>>>>>> ⇒検索結果は、レコード2件となります。
>>>>>>>> 1,"20100301","1000","1100","T初級"
>>>>>>>> 1,"20100301","1200","1300","T上級"
>>>>>>>>
>>>>>>>> SQLツールで実行した場合は、結果②と同じです。
>>>>>>>> 実行①で、実行②と同じ結果を取得するためには
>>>>>>>> どういった設定が必要なのでしょうか?
>>>>>>>>
>>>>>>>> 以上、宜しくお願い申し上げます。
>>>>>>>>
>>>>>>>> 笹木
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> ------------------------------------------------------------------------
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> seasar-dotnet mailing list
>>>>>>>> [E-MAIL ADDRESS DELETED]
>>>>>>>> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --------------------------------------------------------------------------------
>>>>>>
>>>>>>
>>>>>>
>>>>>>> _______________________________________________
>>>>>>> seasar-dotnet mailing list
>>>>>>> [E-MAIL ADDRESS DELETED]
>>>>>>> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------
>>>>>>
>>>>>> _______________________________________________
>>>>>> seasar-dotnet mailing list
>>>>>> [E-MAIL ADDRESS DELETED]
>>>>>> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --------------------------------------------------------------------------------
>>>>
>>>>
>>>>> _______________________________________________
>>>>> seasar-dotnet mailing list
>>>>> [E-MAIL ADDRESS DELETED]
>>>>> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> seasar-dotnet mailing list
>>>> [E-MAIL ADDRESS DELETED]
>>>> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>>>>
>>>>
>>> _______________________________________________
>>> seasar-dotnet mailing list
>>> [E-MAIL ADDRESS DELETED]
>>> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>>>
>>
>>
>> _______________________________________________
>> seasar-dotnet mailing list
>> [E-MAIL ADDRESS DELETED]
>> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>>
>


seasar-dotnet メーリングリストの案内