- 追加された行はこの色です。
- 削除された行はこの色です。
「[[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]
-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]
***概要 [#tdca75f6]
１つのXMLファイル中に200-300タグを超えてくると、オンライン処理でも性能が劣化が始まりますので、~
不要なタグは記述しないようにお願いします（XMLファイル サイズについては、問題ないようです）。
***詳細 [#m2b746e7]
-Open棟梁：動的パラメタライズド・クエリの性能測定に使用した、各種リソース情報と、その測定結果。~
https://gist.github.com/daisukenishino2/c2da7b9fadf79c2ba7bcbfe01a73aea4#gistcomment-2667959
***詳細 [#m2b746e7]
上記のgist.github.comの性能測定の結果は、
-すべてのタグをフラットに並べたケースで、~
タグ数が n の場合、計算量は以下のように定義できます。
fact(n)
{
if(n == 0) return 0;
else return n * r + fact(n-1);
}
※ n 回再起して、r は１タグあたりのXMLDocument化のコスト。
-タグがフラットに並んでいないケースでは以下のように計算量が変わってきます。
-タグがフラットに並んでいないケースでは、~
以下のように計算量が変わってきます。
--以下のようなSELECTタグは、
---読込時、4タグのオーバーヘッドはありますが、
---再帰処理回数への影響は、＋１回になります。
<SELECT name="xxx">
<CASE value="A">・・・</CASE>
<CASE value="B">・・・</CASE>
<DEFAULT>・・・</DEFAULT>
</SELECT>
--ネスト可能なタグの処理順は後半になるので影響はありません。~
https://github.com/OpenTouryoProject/OpenTouryo/blob/develop/root/programs/CS/Frameworks/Infrastructure/Public/Db/BaseDam.cs
>
+VAL Tag
+INSCOL Tag
+IF Tag
+Select-Case-Default Tag
+LIST Tag
+Sub-Where Tag
+DELCMA Tag
+Param Tag
-なお、[[動的パラメタライズド・クエリ分析ツール]]を使用した、~
テストのために記述するPARAMタグ、DIVタグなどXMLドキュメント オブジェクトの~
更新処理を伴わないタグについては、再起処理数に影響を与えません。
***性能測定 [#fa6e9f15]
具体的な性能は、[[動的パラメタライズド・クエリ分析ツール]]で測定可能です。
-Demo: dynamic parametrized query analysis tool of Open棟梁. - YouTube~
https://www.youtube.com/watch?v=6_8baBTVcRM
***自作Daoで解決 [#qcdbffe7]
自作Daoでは動的パラメタライズド・クエリ処理後のクエリを再利用できます。
***解決方法 [#h87ce66e]
-[[自作Daoで解決>バッチ処理方式#r8d98838]]
-[[自動生成Daoで解決>バッチ処理方式#r8d98838]]
object obj;
// 動的SQLを読み込む場合。
this.SetSqlByFile2("DaoShippers_D1_Insert.xml");
// この状態では動的SQL（IsDPQプロパティを確認）。
System.Diagnostics.Debug.WriteLine(this.GetDam().IsDPQ.ToString());
// 実行１回目
this.SetParameter("CompanyName", "１回目");
this.SetParameter("Phone", "１回目");
obj = this.ExecInsUpDel_NonQuery();
// 以降は静的SQLとなる（IsDPQプロパティを確認）。
System.Diagnostics.Debug.WriteLine(this.GetDam().IsDPQ.ToString());
// 前回の実行で設定したパラメタ（Command.Parameters）をクリア。
((DamSqlSvr)this.GetDam()).DamSqlCommand.Parameters.Clear();
// 実行２回目
this.SetParameter("CompanyName", "２回目");
this.SetParameter("Phone", "２回目");
obj = this.ExecInsUpDel_NonQuery();
// 前回の実行で設定したパラメタ（Command.Parameters）をクリア。
((DamSqlSvr)this.GetDam()).DamSqlCommand.Parameters.Clear();
// 実行３回目
// ・・・
***自動生成Daoで解決 [#u9c20643]
自動生成Daoに、クエリを再利用機能を追加しました（v02-50から利用可能になります）。
-Open棟梁：Dao自動生成機能の潜在的な性能問題が完全に解決される。 - OSSコンソーシアム~
https://www.osscons.jp/jo1qzb1y1-537/#_537
**大量データ処理（バッチ更新処理）をサポートしているか？ [#s95f01f8]
大量データ処理（バッチ更新処理）で
-[[オーバーヘッドのある動的SQL>#o6536f12]]を使用したり、
-１件１件のクエリを発行して多くのラウンドトリップを発生させると、
性能が出ません。
従って、大量データ処理の場合、[[バッチ処理方式]]を参照し、
-[[配列バインド（ODP.NET、HiRDBでサポート）>バッチ処理方式#v0b8a9bd]]
-[[バッチクエリ作成支援機能>バッチ処理方式#ze851f70]]
-[[動的パラメタライズド・クエリの連続実行方法>バッチ処理方式#y92d7822]]
などの技法を活用して、パフォーマンスを出す必要があります。
*エラー [#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時に使用するパラメタ識別子に~
１文字以上のプレフィックス・サフィックスを追加する必要があります。
-この際、パラメタ識別子が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]])
*制限事項 [#s54b0aae]
**[[動的パラメタライズド・クエリ]]のパラメタ名 [#o212ae41]
-[[動的パラメタライズド・クエリ]]のパラメタで、~
XMLタグの編集処理に使用されるパラメタ（タグ内パラメタ）の~
パラメタ名は、（同じ値を使用するパラメタであっても、）~
別々に（１つの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 や 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層フレームワーク」も参考にして下さい。
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
