2016年05月18日

結合クエリのCashUpdate更新(サンプルソース付)

結合クエリのCashUpdate方法ポイント

Delphi 10 Seattle
DBアクセス:FireDAC、DatabaseはSQLiteで確認しています。
他のコンポーエントやデータベースでも考え方は同じなので適用できると思います。

----------------------------------------------------------------------------------------------

テーブル(伝票の明細を編集するイメージです)
 伝票明細(Meisai)、商品マスタ(Syouhin) (1:N関連)

CREATE TABLE "MEISAI" (
    `ORDERNO`    INTEGER NOT NULL,
    `MEISAINO`    INTEGER NOT NULL,
    `SYOUHINCODE`    INTEGER NOT NULL,
    `TANKA`    REAL DEFAULT 0,
    `SURYO`    REAL DEFAULT 0,
    `BIKOU`    TEXT DEFAULT '',
    PRIMARY KEY(ORDERNO,MEISAINO)
)
CREATE TABLE "SYOUHIN" (
    `SYOUHINCODE`    INTEGER NOT NULL,
    `SYOUHINNAME`    TEXT,
    `SYOUHINTANKA`    REAL,
    PRIMARY KEY(SYOUHINCODE)
)

----------------------------------------------------------------------------------------------

クエリ
select
 M.ORDERNO
,M.MEISAINO
,M.SYOUHINCODE 
,S.SYOUHINNAME
,S.SYOUHINTANKA
,M.TANKA        /*<=更新対象*/
,M.SURYO        /*<=更新対象*/
,M.BIKOU        /*<=更新対象*/
from
  MEISAI M        /*<=更新するテーブルはこの位置で無いといけない*/
  inner join SYOUHIN S
  on (S.SYOUHINCODE = M.SYOUHINCODE)
where
 M.ORDERNO = :ORDERNO
order by
 M.MEISAINO

----------------------------------------------------------------------------------------------

FDQuery
 上記クエリを設定後、フィールドエディタでフィールドの展開をする必要がある。

FDQueryのdfmファイル内容(関係ない部分は一部削除しています)
  object QryList: TFDQuery
    OnCalcFields = QryListCalcFields
    CachedUpdates = True
    SQL.Strings = (
      'select'
      ' M.ORDERNO'
      ',M.MEISAINO'
      ',M.SYOUHINCODE'
      ',S.SYOUHINNAME'
      ',S.SYOUHINTANKA'
      ',M.TANKA'
      ',M.SURYO'
      ',M.BIKOU'
      'from'
      '  MEISAI M'
      '  inner join SYOUHIN S'
      '  on (S.SYOUHINCODE = M.SYOUHINCODE)'
      'where'
      ' M.ORDERNO = :ORDERNO'
      'order by'
      ' M.MEISAINO')
    object QryListORDERNO: TIntegerField
      FieldName = 'ORDERNO'
      ProviderFlags = [pfInWhere, pfInKey]
    end
    object QryListMEISAINO: TIntegerField
      FieldName = 'MEISAINO'
      ProviderFlags = [pfInWhere, pfInKey]
    end
    object QryListSYOUHINCODE: TIntegerField
      FieldName = 'SYOUHINCODE'
      ProviderFlags = [pfInUpdate]
    end
    object QryListSYOUHINNAME: TWideStringField
      FieldName = 'SYOUHINNAME'
      ProviderFlags = [pfHidden]
      ReadOnly = True
    end
    object QryListSYOUHINTANKA: TFloatField
      FieldName = 'SYOUHINTANKA'
      ProviderFlags = [pfHidden]
      ReadOnly = True
    end
    object QryListTANKA: TFloatField
      FieldName = 'TANKA'
      ProviderFlags = [pfInUpdate]
    end
    object QryListSURYO: TFloatField
      FieldName = 'SURYO'
      ProviderFlags = [pfInUpdate]
    end
    object QryListCalKINGAKU: TIntegerField
      FieldKind = fkCalculated
      FieldName = 'CalKINGAKU'
      Calculated = True
    end
    object QryListBIKOU: TWideStringField
      FieldName = 'BIKOU'
      ProviderFlags = [pfInUpdate]
    end
  end
------------------------------------
ポイントは、ProviderFlagsになります。
更新するフィールドは、pfInUpdateをTrueにする。
更新時の、Where句に相当する部分は、pfInWhereをTrueにする。
join先のテーブルのフィールドは、pfHiddenをTrueにする。
テーブルの主キーのフィールドは、pfInKeyをTrueにする(ふつう勝手にTrueになる)

結合クエリとは関係ないが、BDE=>FireDAC切り替え時、BDEでは設定しなくても
問題なかった、キー項目のProviderFlagsが、未設定だとエラーが出る。
エラー(例外:EFDDBEngineException)が出たらキー項目の下記の確認を。
ProviderFlags = [pfInWhere, pfInKey]
エラー内容は何パターンかあるようだ。
 メッセージ [FireDAC][Phys][IB]-312.〜〜〜〜
 メッセージ [FireDAC][DApt]-400.〜〜〜〜
など。


上記だけではわかりにくいかと思うのでサンプル(ソースのみ)を下記にアップ。データベースも含めています。


(含 LookUpフィールドサンプル。)
明細の追加や削除は考慮して作っていません。上記確認のためなので諸々端折っています。

追記。。。。

今回のケースでは、
--------------------------------
from
  MEISAI M        /*<=更新するテーブルはこの位置で無いといけない*/
  inner join SYOUHIN S
  on (S.SYOUHINCODE = M.SYOUHINCODE)
--------------------------------
の部分は、下記のようなLeft Outer Joinでも使えます
(仕様としては、商品マスタが無い商品を取り扱うというのは、NGですが)
--------------------------------
from
  MEISAI M        /*<=更新するテーブルはこの位置で無いといけない*/
  left outer join SYOUHIN S
  on (S.SYOUHINCODE = M.SYOUHINCODE)
--------------------------------
でもOK。
right outer join や full outer join は、当然ダメ。
ちなみにもともと、SQLiteは、right outer join / full outer join をサポートしていませんが。
他のデータベースをつかうときの注意点です。

ついでに。
たぶんやろうとしないと思うけど、まとめて複数のテーブル(今回では、MEISAIとSYOUHIN)を更新もNGです。


追記その2。。。。
(結合クエリとLookupフィールドについて。私論)

上記の処理では、SYOUHINCODEも変更するということが一般だと思う。
この場合、上記結合テーブルで行うと、SYOUHINCODEが変わったときの処理、コードでゴリゴリ書く必要が出てくる。
そのように考えると、SYOUHINテーブルの引用は、Lookupフィールドを使ったほうが便利だと思う。

ただ、読み込むMEISAIテーブルのデータが少ないケースはそれでよいが、件数が多いケースでは、Lookupフィールドの使用は、格段に処理時間がかかる。そういったケースではコードでゴリゴリ書くことに目をつぶって、結合テーブルを使用することもメリットが出ると思う。


タグ:Delphi FireDAC BDE
posted by しんくそふと at 13:32| Comment(0) | TrackBack(0) | 開発TIPS
この記事へのコメント
コメントを書く
お名前: [必須入力]

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。
※ブログオーナーが承認したコメントのみ表示されます。
この記事へのトラックバックURL
http://blog.sakura.ne.jp/tb/175341763
※ブログオーナーが承認したトラックバックのみ表示されます。

この記事へのトラックバック