SQLのDML文(INSERT、DELETE、 UPDATE)を検知するツールをVBAで現在作成しています。
検知の方法なのですが、最初に対象のテーブルに
SELECT * FROM テーブル名;
を投げその結果を、シートをに格納。
そして、もう一度
SELECT * FROM テーブル名;
を投げ上記とは別にシートに結果を格納。
二つのシートを、主キーを元に文字列比較し、
DML文の検知を行います。
ですが、上記の方法だと主キーのないテーブルには使えません。
そもそも主キーのないテーブルの行を一位に識別するためには
行の値すべて見る必要があるので行全体を比較すれば
テーブルの値の違いだけでも検知できるかもしれませんが、
あまりいいやり方ではない気がします。
VBAに限らず、主キーのないテーブル同士の比較で
似たような事例を知っている方がいれば、ご教授願いたいです。
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
回答4件
0
ベストアンサー
元のテーブルと編集後のテーブルを比較して差分を取り、どのような操作が行われたかを検知したいということでしょうか。
(以下、イメージです。主キーが無いテーブルを想定しています。)
主キー無しテーブルの場合、UpdateはDelete+Insertでも等価な処理ができてしまうので、テーブルの比較だけでどちらが行われたかは厳密には判定できません。
従って、以下のように完全一致以外のものを全て出力させるのが現実的かと思います。
テーブル構成によっては、特定の複数列を主キーと見なしてしまう手もありますが、汎用性は無くなります。
行全体を比較すれば
テーブルの値の違いだけでも検知できるかもしれませんが、
あまりいいやり方ではない気がします。
たしかにスマートではないですが、主キーが無い以上、他に方法は無いですね。
セルを一つずつ取得して比較するのは速度面からあまりよくないですが、一旦範囲全体をVariant型変数に代入してしまえば2次元配列になるので、そこからは高速に処理できます。
上の表を前提にコードサンプルを書いてみました。
※Microsoft Scripting Runtimeへの参照設定が必要です。
lang
1Sub Sample() 2 Dim レコード As String, 行 As Long, 列 As Long 3 4 '高速化の為、元データを配列に転記 5 Dim 元データ As Variant 6 元データ = ThisWorkbook.Sheets(1).Range("B4:E8").Value 7 8 '面倒な配列から、操作しやすいDictionary型に変換 9 Dim 元データディクショナリ As New Dictionary 10 For 行 = LBound(元データ, 1) To UBound(元データ, 1) 11 レコード = "" 12 For 列 = LBound(元データ, 2) To UBound(元データ, 2) 13 レコード = レコード & CStr(元データ(行, 列)) 14 Next 列 15 元データディクショナリ.Add 行, レコード 16 Next 行 17 18 '高速化の為、編集後データを配列に転記 19 Dim 編集後 As Variant 20 編集後 = ThisWorkbook.Sheets(1).Range("G4:J10").Value 21 22 '面倒な配列から、操作しやすいDictionary型に変換 23 Dim 編集後ディクショナリ As New Dictionary 24 For 行 = LBound(編集後, 1) To UBound(編集後, 1) 25 レコード = "" 26 For 列 = LBound(編集後, 2) To UBound(編集後, 2) 27 レコード = レコード & CStr(編集後(行, 列)) 28 Next 列 29 編集後ディクショナリ.Add 行, レコード 30 Next 行 31 32 'ディクショナリ同士の比較でマッチしたものをRemove 33 Dim k1, k2 34 For Each k1 In 元データディクショナリ.Keys 35 For Each k2 In 編集後ディクショナリ.Keys 36 If 元データディクショナリ(k1) = 編集後ディクショナリ(k2) Then 37 元データディクショナリ.Remove (k1) 38 編集後ディクショナリ.Remove (k2) 39 End If 40 Next 41 Next 42 43 'アンマッチデータの出力 44 Debug.Print "元データのアンマッチ" 45 For Each k1 In 元データディクショナリ.Keys 46 If 元データディクショナリ(k1) <> "" Then Debug.Print k1; "行目" 47 Next 48 Debug.Print "編集後のアンマッチ" 49 For Each k2 In 編集後ディクショナリ.Keys 50 If 編集後ディクショナリ(k2) <> "" Then Debug.Print k2; "行目" 51 Next 52End Sub
出力結果はこうなります。
元データのアンマッチ 2 行目 5 行目 編集後のアンマッチ 1 行目 3 行目 4 行目 5 行目
注意点として、レコードにデリミタを付けてないので、例えば1,12と11,2が文字列結合されるとどちらも112になってしまいます。そこらへんは、ご自身で工夫してください。
投稿2016/02/18 17:32
編集2016/02/18 18:35総合スコア686
0
他の方が書かれていないようなので、一点だけ。
主キーがないテーブルに主キーを追加してはいかがですか、他で使わない項目追加は技術的には問題無いです。(”不要なデータを登録して、ディスクを圧迫するな”(実際の増加量が数Mbとしても)など政治的・感情的に反対がおこる可能性はあります。)
オートナンバー型などで項目を追加すればいいです。または、毎秒ごとに行の挿入されるテーブルでなければ、デフォルト値が現在日時の”作成日時”という項目を追加してはいかがでしょう。ミリセカンドまで登録されていればバッティングの可能性はほぼ0です。
これらの方法だと、Insert文を変更する必要がないので、影響はほとんど無いと言っていいです。
投稿2016/02/19 02:44
総合スコア2883
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
0
DBMS次第ですが、「DMLを検知する」のが目的ならトレースログや監査ログなどを見るのが良いと思います。
投稿2016/02/18 15:00
総合スコア925
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
0
一つの質問文の中にお聞きになりたいことが複数ある様ですね。
整理すると以下でしょうか?
1)なにか(SQLテキストファイル?)からDMLに該当するものを検知する方法
2)キーのないテーブル同士のマッチング方法
1については、
直接、DMLに当たるものを見つけ出すのではだめなのでしょう?
自分もVB.netソースからSQL(除くLINQ)を検出して
PrgID(ソースファイル名)毎×テーブル毎にCRUDを書き出すVBAを組んだりしましたが…
2については、
Dictionary Objectを拡張し、Itemに複数(配列やオブジェクト)を格納できるクラスを作成して、
・複合キー(複数の項目を連結したり)で比較
・項目ごとに重要度などで点数付けし、点数合計を比較に使用
などをしました。
テーブル全体の比較であれば、並び替えてからの比較も有効な気がします。
参考になりますでしょうか?
投稿2016/02/18 14:45
総合スコア396
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。