Open棟梁 wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

目次

パラメタライズド・クエリの初歩

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

方法

パラメタライズド・クエリのバインド変数へのバインド方法は?

名前バインドにのみ対応しています。このため、どのデータプロバイダであっても、順番バインドをサポートしません。

  • ODP.NETのバインドは名前バインド固定になります。
  • OLEDB、ODBC、HiRDBのデータプロバイダは
    「?」パラメタの順番バインドにのみ対応しているためフレームワーク内部で名前バインド可能になる様に処理を加えています。
    なお、制限事項として、コメント中に パラメタ名と同じ文字列があると正しく動作しませんのでご注意ください。

パラメタライズド・クエリのバインド変数への属性指定方法は?

  • 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ブロックの実行や、戻り値の取得などは可能か?

ストアド プロシジャでも動的パラメタライズド・クエリ機能を使用できます。

ストアド プロシジャの実行方法

ストアド プロシージャの実行も可能である。

  • ポイント
    • 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経由でのストアド実行

デッドロック、ロックタイムアウト、「ユニークキーの重複」などの例外をリトライしたい。

B層ベースクラス2の例外処理をカスタマイズして、 リトライ対象例外を業務例外に振り替え、P層に正常系の戻り値が戻るようにします。

SetUserParameter()メソッドの利用ポリシーをどのように考えるべきか?

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

D層自動生成ツールで楽観排他を、どのように実装するのか?

  • D層自動生成ツールでのDao生成時にタイムスタンプ列名を設定し、
    タイムスタンプ必須オプションONにして生成することで楽観排他を実現できます。
  • 自動生成のDaoクラスを利用しないケースに関しては別途排他に関する実装が必要となりますので、
    更新系は自動生成DAOを必ず利用するような運用とすることをお勧めします。

OracleのCLOBをODP.NET経由でどう扱うのか?

  • 以下のADO.NETサンプルを参考に実装できると思います。
  • 自動生成Daoからだと上手く行かないケースがあるので、その場合は、CmnDaoを使用して下さい。

サンプル

  • 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();

参考

性能

コマンド タイムアウト値を設定するにはどうすれば良いか?

共通のコマンド タイムアウト値をconfigファイルに設定できます。

ODP.NET、HiRDBの配列バインドをサポートしているか?

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

ポイント

ODP.NET配列バインドの場合、

  • パラメタ
    • ArrayBindCount?の指定が必要。
    • OracleDbType?の型指定が必要。
  • Dam取得方法
    • B層でDamを取得する場合は、((DamOraOdp?)this.GetDam())
    • 自作Daoクラス」でDamを取得する場合は、((DamOraOdp?)this._dam)
    • Dao集約クラス」でDamを取得する場合は、((DamOraOdp?)this.Dam)

サンプル

以下、ODP.NETのサンプル(自作Daoクラス)。

// 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で暗黙の型変換が発生し、検索処理性能が大幅に劣化する。

対策

  • SetParameter()メソッドで型、サイズ、方向を指定可能です。
  • 後工程でのPG修正が難しい場合は、SQL定義ファイル中に型キャストを明示すること型指定可能です。

参考

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

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

大量データ処理(バッチ更新処理)をサポートしているか?

大量データ処理(バッチ更新処理)で

性能が出ません。

従って、大量データ処理(バッチ更新処理)の場合は、

を使用して下さい。

エラー

動的パラメタライズド・クエリで、[<]、[>]などの文字が含まれると適切に実行されない。

XML内部で使用不可能な文字列は、HTMLエンコーディングが必要になります。

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

動的パラメタライズド・クエリでXMLが編集されずに、そのまま実行される。

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

して下さい。

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

  • D層自動生成ツールは、Update時に使用するパラメタ識別子に
    1文字以上のプレフィックス・サフィックスを追加する必要があります。
  • この際、パラメタ識別子が30文字を越えてしまい、エラーが発生するケースがあります。
  • この再設定と、再生成が面倒な場合は、文字列一括置換ツールなどを使ってパラメタ識別子を
    置換するなどして下さい(DaoとUpdate処理のSQL定義(XML)が置換対象ファイルになります)。

検索条件のIN句(副問合せ)を動的化したいがエラーとなる。

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

Daoの使い分け

更新系処理

自動生成Dao

単一テーブルからのSELECT

自動生成Dao

複数テーブルなど複雑なSELECT

共通Dao(CmnDao) 

制限事項

動的パラメタライズド・クエリのパラメタ名

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

アルゴリズム

  • アルゴリズムとしては、
    • テキスト内パラメタや
    • タグ内パラメタを

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

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

としています。

結論

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

サポート

Like句を使用した曖昧検索をサポートしているか?

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

LINQ to SQLやLINQ to Oracle、LINQ to Entities(ADO.NET Entity Framework)をサポートしているか?

任意のDLL(Assembly)からの埋込SQLのロードをサポートしているか?

  • ベースクラス2のSetSqlByFile2メソッドを用いてExecutingAssembly?の埋込リソースから SQLをロードすることができますが、
    任意のDLL(Assembly)から埋込リソースをロードを行う場合は、SetSqlByFile2メソッドのカスタマイズが必要になります。
  • 問題は、EmbeddedResourceLoader.LoadAsStringメソッドの引数であるassemblyStringと、loadfileNameに組み込む名前空間情報の情報をどのように渡すかになりますが、この部分はプロジェクト毎に検討ください。

参考

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

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2018-09-01 (土) 16:33:46 (25d)