「[[Open棟梁 wiki>https://opentouryo.osscons.jp]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。

-[[戻る>FAQ]]

*目次 [#ceca0ef7]
#contents

*パラメタライズド・クエリの初歩 [#ha6bb666]
-Like検索は標準のパラメタライズド・クエリ、[[動的パラメタライズド・クエリ]]でサポートされています(パラメタにワイルドカードを含めたLikeの検索条件を渡す)。
-パラメタライズド・クエリのパラメタにnullを渡したい場合、DBNullを使用します([[動的パラメタライズド・クエリ]]では、nullは特殊な制御パラメタとして利用されます)。

*方法 [#a3da5b22]
**パラメタライズド・クエリのバインド変数へのバインド方法は? [#mbcbdc8e]
名前バインドにのみ対応しています。このため、どのデータプロバイダであっても、順番バインドをサポートしません。
-ODP.NETのバインドは名前バインド固定になります。
-OLEDB、ODBC、HiRDBのデータプロバイダは~
「?」パラメタの順番バインドにのみ対応しているためフレームワーク内部で名前バインド可能になる様に処理を加えています。~
なお、制限事項として、コメント中に パラメタ名と同じ文字列があると正しく動作しませんのでご注意ください。

**パラメタライズド・クエリのバインド変数への属性指定方法は? [#hbfb130e]
-SetParameter()メソッドで型(、サイズ、方向)を指定可能です。 

-後工程でのPG修正が難しい場合は、SQL定義ファイル中に型キャストを明示すること型指定可能です。~
また、自動生成Daoは型指定するI/Fが無いため、必要に応じて型キャストを使用して対応下さい。

-自動生成DaoのI/Fでは、パラメタへの属性指定ができないので、~
自動生成Daoのテンプレート(DaoTemplate)のSetParametersFromHtメソッドを~
カスタマイズすることで、ユーザコードからパラメタへの属性指定がになります。

--例えばユーザコードから自動生成Daoのプロパティ・プロシージャに以下のクラスを設定する。
  public class GenDaoParam
  {
    public object Value;
    public object DbType;
    public int Size;
    public ParameterDirection Direction;
  }

--そもそも、自動生成ツールで型情報も取得しているので、型込みの生成に対応する~
という話もありますが、実装も複雑になるので、現時点では対応を見送っています。

**ストアド プロシジャ、無名PL/SQLブロックの実行や、戻り値の取得などは可能か? [#c390162a]
ストアド プロシジャでも[[動的パラメタライズド・クエリ]]機能を使用できます。

***ストアド プロシジャの実行方法 [#baebe4d6]
ストアド プロシージャの実行も可能である。

-ポイント
--CommandTypeにStoredProcedureを設定する。

--戻り値用のパラメタを指定する場合、
---Parameter.Directionプロパティに、
---ParameterDirection.ReturnValueを指定。

--アウトプット・パラメタを指定する場合、
---Parameter.Directionプロパティに、
---ParameterDirection.Outputを指定。

--実行には、this.ExecSelect_DR()メソッドを使用する。

--結果セットが複数ある場合
---ループなどを使用して複数の結果セットを取得できる。
---DataTable.Load()メソッドでは、結果セットが自動的に次に送られる。
---DataTable.Load()メソッドを使用しない場合、DataReader.NextResult()メソッドを使用して、次に読む結果セットに進む。

-サンプル~
ストアド プロシージャの実行については下記のコードを参考のこと。
 /// <summary>「sp_help」ストアドプロシージャを実行する。</summary>
 /// <param name="objectName">「sp_help」ストアドプロシージャの「objname」パラメタに指定する文字列</param>
 /// <param name="ds">「sp_help」ストアドプロシージャが返す結果セット(複数返る)</param>
 /// <returns>「sp_help」ストアドプロシージャの戻り値(成功:「」、失敗:「」)</returns>
 public int sp_help(string objectName, out DataSet ds)
 {
   // ストアドプロシージャを指定する。
   this.SetSqlByCommand("sp_help", CommandType.StoredProcedure);
 
   // 引数パラメタを設定する。
   this.SetParameter("objname", objectName);
 
   // 戻り値パラメタを設定する。
   this.SetParameter("ret", null, null, -1, ParameterDirection.ReturnValue);
 
   // ストアドプロシージャを実行し、データリーダを戻す。
   SqlDataReader drd = (SqlDataReader)this.ExecSelect_DR();
 
   // 結果セットを取得する
   ds = new DataSet();
 
   // 結果セットは複数取得可能
   do
   {
     // DataTableを生成
     DataTable dt = new DataTable();
 
     // データリーダから結果をロード
     dt.Load(drd);
 
     // DataTableを格納
     ds.Tables.Add(dt);
 
   } while (!drd.IsClosed);
 
   // データリーダを閉じる。
   drd.Close();
 
   // ストアドプロシージャの戻り値パラメタの値を取得。
   return (int)((DamSqlSvr)this.GetDam()).DamSqlCommand.Parameters["ret"].Value;
 }

***[[CmnDao>https://github.com/OpenTouryoProject/OpenTouryo/blob/develop/root/programs/CS/Frameworks/Infrastructure/Business/Dao/CmnDao.cs]]経由でのストアド実行 [#wcf3155f]
ver 02-30から、汎用Daoを使用したストアド実行が可能になります。

-Open棟梁の汎用Daoを使用したストアド実行サンプル~
https://gist.github.com/daisukenishino2/a97e2017a0c683d2c858e39741b63949

**デッドロック、ロックタイムアウト、「ユニークキーの重複」などの例外をリトライしたい。 [#k470cfdf]
B層[[ベースクラス2]]の例外処理をカスタマイズして、 リトライ対象例外を業務例外に振り替え、P層に正常系の戻り値が戻るようにします。

**SetUserParameter()メソッドの利用ポリシーをどのように考えるべきか? [#qe5e248d]
-SetUserParameter()メソッドは、SQLインジェクションの可能性があるものの、DB部品の(僅かな)制約 / 制限などを解決するAPIでもあります。
-このため、SetUserParameter()メソッドの利用を制限せず、D層の[[ベースクラス2]]に実装するSQLトレースをカスタマイズして~
SQLインジェクションの可能性があるSQLをチェック & Warningログを出力するなどの対応をした方がプロジェクトとしては、スムーズに開発を遂行できると考えます。

**[[D層自動生成ツール]]で楽観排他を、どのように実装するのか? [#e520ecee]
-[[D層自動生成ツール]]でのDao生成時にタイムスタンプ列名を設定し、~
タイムスタンプ必須オプションONにして生成することで楽観排他を実現できます。

-自動生成のDaoクラスを利用しないケースに関しては別途排他に関する実装が必要となりますので、~
更新系は自動生成DAOを必ず利用するような運用とすることをお勧めします。

**OracleのCLOBをODP.NET経由でどう扱うのか? [#i83e9e27]
-以下のADO.NETサンプルを参考に実装できると思います。
-自動生成Daoからだと上手く行かないケースがあるので、その場合は、[[CmnDao>https://github.com/OpenTouryoProject/OpenTouryo/blob/develop/root/programs/CS/Frameworks/Infrastructure/Business/Dao/CmnDao.cs]]を使用して下さい。

***サンプル [#a2aefe32]
-Insert(VB.NET)
 Dim sr As New System.IO.StreamReader("C:\temp\input.txt",System.Text.Encoding.Default)
 Dim ts as String
 ts = sr.ReadToEnd()
 sr.Close()
 
 Dim fs As New System.IO.FileStream("C:\temp\input.gif",System.IO.FileMode.Open,System.IO.FileAccess.Read)
 Dim bs(fs.Length) As Byte
 fs.Read(bs,0,fs.Length)
 
 cmd.CommandText = "INSERT INTO TLOB(NO,TEXT,IMG) VALUES(:v1 ,:v2 ,:v3) "
 cmd.CommandType = CommandType.Text
 
 Dim p1 As New OracleParameter()
 p1.OracleDbType = OracleDbType.Decimal
 p1.Value = 3
 cmd.Parameters.Add(p1)
 
 Dim p2 As New OracleParameter()
 p2.OracleDbType = OracleDbType.Clob
 p2.Value = ts
 cmd.Parameters.Add(p2)
 
 Dim p3 As New OracleParameter()
 p3.OracleDbType = OracleDbType.Blob
 p3.Value = bs
 cmd.Parameters.Add(p3)
 
 cmd.ExecuteNonQuery()
 
 tx.Commit()

-Select(C#)
 cmd.CommandText = "SELECT id, clob_column FROM clob_content WHERE id = 1";
 OracleDataReader reader = cmd.ExecuteReader();
 reader.Read();

-Update(C#)
 cmd.CommandText = "SELECT XXXX FROM YYYY FOR UPDATE";
 reader = cmd.ExecuteReader();
 reader.Read();
 OracleClob clob = reader.GetOracleClob(0);
 clob.Append(ending.ToCharArray(), 0, ending.Length);
 txn.Commit();

***参考 [#tac45af6]
-.NETでラージ・オブジェクトを使用する~
http://www.oracle.com/technetwork/jp/articles/price-dotnet2-099630-ja.html

-How To資料: ODP.NETによるLOB更新のための結果セットのロックの使用~
http://otndnld.oracle.co.jp/tech/windows/odpnet/howto/03_rlock/content.html

-ODP.NET 005 LOB INSERT - jnjnslabの備忘録~
http://jnjnslab.hatenablog.com/entry/2017/03/23/123810

*性能 [#v72fa9e1]
**コマンド タイムアウト値を設定するにはどうすれば良いか? [#r0e2db77]
共通のコマンド タイムアウト値をconfigファイルに設定できます。

**ODP.NET、HiRDBの配列バインドをサポートしているか? [#db704ba5]
-ODP.NET、HiRDBの配列バインドをサポートしています。
-また、配列バインドをサポートしていないデータプロバイダでの~
大量データ処理実装のために、[[バッチクエリ作成支援機能]]を提供しています。

***ポイント [#s72139d0]
ODP.NET配列バインドの場合、

-パラメタ
--ArrayBindCountの指定が必要。
--OracleDbTypeの型指定が必要。

-Dam取得方法
--B層でDamを取得する場合は、((DamOraOdp)this.GetDam())
--「[[自作Daoクラス>D層(Dao)の種類#p684509d]]」でDamを取得する場合は、((DamOraOdp)this._dam)
--「[[Dao集約クラス>D層(Dao)の種類#ra46728e]]」でDamを取得する場合は、((DamOraOdp)this.Dam)

-[[動的パラメタライズド・クエリ分析ツール]]も、「配列バインド」に対応している。

***サンプル [#g6a81f58]
以下、ODP.NETのサンプル([[自作Daoクラス>D層(Dao)の種類#p684509d]])。

 // SQLを設定
 this.SetSqlByCommand("INSERT INTO XXX(AAA, BBB, CCC) VALUES(:P1, :P2, :P3);");
 
 // ODP.NETの配列バインドの場合は、ArrayBindCountを指定
 ((DamOraOdp)this._dam).DamOracleCommand.ArrayBindCount = temp.Length;
 
 // 配列データを作成
 object[] temp1 = new string[] { "aaa", "bbb", "ccc" };
 object[] temp2 = new string[] { "aaa", "bbb", "ccc" };
 object[] temp3 = new string[] { "aaa", "bbb", "ccc" };
 
 // 配列データをバインド(型情報が必要)
 this._dam.SetParameter("P1", temp1, OracleDbType.Varchar2);
 this._dam.SetParameter("P2", temp2, OracleDbType.Varchar2);
 this._dam.SetParameter("P3", temp3, OracleDbType.Varchar2);

**SQLで暗黙の型変換が発生し、検索処理性能が大幅に劣化する。 [#d3619796]

***対策 [#y72939b8]
-SetParameter()メソッドで型、サイズ、方向を指定可能です。 

-後工程でのPG修正が難しい場合は、SQL定義ファイル中に型キャストを明示すること型指定可能です。

***[[参考>データアクセス#e9d7a80e]] [#b07964e5]

**[[動的パラメタライズド・クエリ]]を記述するXMLファイルのサイズなどにより性能が劣化しないか? [#o6536f12]

-1つのXMLファイル中に200-300タグを超えてくると、オンライン処理でも性能が劣化が始まりますので、~
不要なタグは記述しないようにお願いします(XMLファイル サイズについては、問題ないようです)。 

-なお、[[動的パラメタライズド・クエリ分析ツール]]を使用したテストのために記述する PARAMタグ、DIVタグなど~
XMLドキュメント オブジェクトの更新処理を伴わないタグについては、性能劣化の原因になりません。

-参考
--Open棟梁:動的パラメタライズド・クエリの性能測定に使用した、各種リソース情報と、その測定結果。~
https://gist.github.com/daisukenishino2/c2da7b9fadf79c2ba7bcbfe01a73aea4


**大量データ処理(バッチ更新処理)をサポートしているか? [#s95f01f8]
大量データ処理(バッチ更新処理)で
-[[オーバーヘッドのある動的SQL>#o6536f12]]を使用したり、
-1件1件のクエリを発行して多くのラウンドトリップを発生させると、

性能が出ません。

従って、大量データ処理(バッチ更新処理)の場合は、
-配列バインド(ODP.NET、HiRDBでサポート)
-[[バッチクエリ作成支援機能]]

を使用して下さい。

*エラー [#g7fbf41d]

**[[動的パラメタライズド・クエリ]]で、[<]、[>]などの文字が含まれると適切に実行されない。 [#f62b8d45]
XML内部で使用不可能な文字列は、[[HTMLエンコーディング>http://itpro.nikkeibp.co.jp/article/COLUMN/20070409/267808/]]が必要になります。

これらの文字列が含まれる場合は、[[下記の現象>#g5a1aa8a]]が発生します。

**[[動的パラメタライズド・クエリ]]でXMLが編集されずに、そのまま実行される。 [#g5a1aa8a]
-DB部品の最も初めのチェック処理にXmlDocument.Load()メソッドを使用しているため、
--XMLフォーマットとして正しいものは[[動的パラメタライズド・クエリ]]
--不正であるものは[[静的パラメタライズド・クエリ]]

>として実行されるため、この現象は、不正なフォーマットのXMLを投げている事に起因します。

-XMLフォーマットの不正を確認するためには、
--[[動的パラメタライズド・クエリ分析ツール]]を使用するか、
--デバッグ時に、DB部品クラスのIsDPQプロパティ(動的SQLか静的SQLかを識別する)をチェックするか、

>して下さい。

**自動生成Daoの Update処理で「ORA-00972 "識別子が長すぎます"」が発生する。 [#fcffbeab]

-[[D層自動生成ツール]]は、Update時に使用するパラメタ識別子に~
1文字以上のプレフィックス・サフィックスを追加する必要があります。

-この際、パラメタ識別子が30文字を越えてしまい、エラーが発生するケースがあります。

-プレフィックス・サフィックスは、[[D層自動生成ツール]]のコンフィグで設定可能です。

-この再設定と、再生成が面倒な場合は、文字列一括置換ツールなどを使ってパラメタ識別子を~
置換するなどして下さい(DaoとUpdate処理のSQL定義(XML)が置換対象ファイルになります)。

**検索条件のIN句(副問合せ)を動的化したいがエラーとなる。 [#o4b7b07b]
検索条件のIN句(副問合せ)を動的化する場合、[[動的パラメタライズド・クエリ]]のIFタグ ではなく、タグのネストが可能なSUBタグを使用頂けます。

*Daoの使い分け [#m93cb89e]

**更新系処理 [#ibb5dec2]
自動生成Dao

**単一テーブルからのSELECT [#v7b6eb62]
自動生成Dao

**複数テーブルなど複雑なSELECT [#w6260481]
共通Dao([[CmnDao>https://github.com/OpenTouryoProject/OpenTouryo/blob/develop/root/programs/CS/Frameworks/Infrastructure/Business/Dao/CmnDao.cs]]) 

**大量データ処理(バッチ更新処理) [#s95f01f8]
大量データ処理(バッチ更新処理)で
-[[オーバーヘッドのある動的SQL>#o6536f12]]を使用したり、
-1件1件のクエリを発行して多くのラウンドトリップを発生させると、

性能が出ません。

従って、大量データ処理(バッチ更新処理)の場合は、
-配列バインド(ODP.NET、HiRDBでサポート)
-[[バッチクエリ作成支援機能]]

を使用して下さい。

*制限事項 [#s54b0aae]
**[[動的パラメタライズド・クエリ]]のパラメタ名 [#o212ae41]
-[[動的パラメタライズド・クエリ]]のパラメタで、~
XMLタグの編集処理に使用されるパラメタ(タグ内パラメタ)の~
パラメタ名は、(同じ値を使用するパラメタであっても、)~
別々に(1つのXMLタグ毎、ユニークに)定義する必要があります。

-この理由は、以下のような[[アルゴリズム>#vdacfa6a]]に起因します。

***アルゴリズム [#vdacfa6a]
-アルゴリズムとしては、
--テキスト内パラメタや
--タグ内パラメタを

>含むXMLタグを「再帰的」に検索し、

-パラメタ設定されていれば、
--テキスト内パラメタのあるXMLタグを有効化
--タグ内パラメタのあるXMLタグを有効化(その際、パラメタは消す)

>としています。

***結論 [#s9de7ded]
XMLタグの検索方法が再帰的なので、

-タグ内パラメタに同じパラメタがあっても、~
パラメタを消す・消さないの判断がつきません。

-しかし、テキスト内パラメタであれば、~
パラメタを消さないので、同一名称のパラメタも付与できます。
 <?xml version="1.0"?>
 <ROOT>
    SELECT * FROM Shippers
    <WHERE>
      WHERE
        <IF>ShipperID =@P1<ELSE>IS NULL</ELSE></IF>
        <IF>and ShipperID =@P1<ELSE>IS NULL</ELSE></IF>
   </WHERE>
    <PARAM>
      P1, Int32, 1<DIV/>
    </PARAM>
 </ROOT>

>↓↓↓

>
 SELECT * FROM Shippers
   WHERE
     ShipperID =@p1 and ShipperID =@p1

*サポート [#jeee821d]
**Like句を使用した曖昧検索をサポートしているか? [#y8965987]
-サポートしています。
-パラメタライズド・クエリ自体がLike句を使用した曖昧検索をサポートしているため文字列連結やSetUserParameter()メソッドを使用する必要はありません。

**LINQ to SQLやLINQ to Oracle、LINQ to Entities(ADO.NET Entity Framework)をサポートしているか? [#d17aa77f]
-サポートしています。利用制限はしていません(機能提供は無し)。
-D層フレームワーク(静/[[動的パラメタライズド・クエリ]]機能、[[D層自動生成ツール]])の使用を推奨しています。

**任意のDLL(Assembly)からの埋込SQLのロードをサポートしているか? [#v3c9d7c9]
-[[ベースクラス2]]のSetSqlByFile2メソッドを用いてExecutingAssemblyの埋込リソースから SQLをロードすることができますが、~
任意のDLL(Assembly)から埋込リソースをロードを行う場合は、SetSqlByFile2メソッドのカスタマイズが必要になります。

-カスタマイズ方法は、SetSqlByFile2メソッドで、EmbeddedResourceLoader.LoadAsStringメソッドを使用して 直接、埋込SQLをロードし、this.SetSqlByCommandメソッドでSQLを設定するようにします。

-問題は、EmbeddedResourceLoader.LoadAsStringメソッドの引数であるassemblyStringと、loadfileNameに組み込む名前空間情報の情報をどのように渡すかになりますが、この部分はプロジェクト毎に検討ください。

*参考 [#c03f88d3]
-.NET用アプリケーション フレームワーク ”棟梁” 利用ガイド(ベターユース、FAQ編)~
https://github.com/OpenTouryoProject/OpenTouryoDocuments/blob/master/documents/1_User_Guide/ja-JP/7_User_Guide(BetterUse_and_FAQ).doc

--4章:「D層に関するトピック」も参考にして下さい。
--7章:「FAQ」> 7.3節:「D層フレームワーク」も参考にして下さい。


トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS