ナッキーの「Delphiでビジネスアプリ奮闘記」-第3回　データベースのレコードを編集する

Abstract: データを追加できたので、今度はデータの書き換えもできるようにしたいな。CSVファイルに変更があったら、データベースのほうも変更してみます。さらにデータベースのユーザーを追加してアクセス制限を設けます。

ナッキー

CSVファイルから無事データベースに移行できたので、今度はデータの書き換えに挑戦してみます。CSVファイルに書き換えがあったときデータベースも更新します。CSVファイルにはキーとなるフィールドがないけれど、どうやって解決しようかな？

ユーザーの追加

これまでデータベースにアクセスするユーザーを「SYSDBA」として、制限なしで使用していました。でも、これじゃあTransCSVのプログラムを使う人すべてがSYSDBAとしてデータベースにアクセスしてしまって、安全性に問題があるわよね。そこで、データベースのユーザーを追加して、プログラムで使うことにします。

InterBaseサーバーマネージャーでInterBaseサーバーを起動して、IBConsoleを使ってユーザーを追加します。IBConsoleの左欄サーバーツリーから「Local Server - gds_db」をダブルクリックして「Users」アイテムを表示します。ショートカットメニューで、[ユーザーの追加(A)...]メニューを選択します。

ダイアログボックスが表示されますので、登録に必要な部分だけ入力します。ここでは「ユーザー名(U):」は「USER1」、「パスワード(P)：」と「パスワードの確認(O):」欄に「user1」を入力したら、[適用(A)]ボタンをクリックします。「追加情報」欄も入力してかまいません。

Hide image
01ユーザ情報

図01 ユーザ情報

ユーザーを作る作業はこれだけでOK。でも使うときには、もう少し作業が必要です。まずはデータベース側にも設定をしなくちゃ。操作は、同じIBConsoleでできます。

IBConsoleのサーバーツリーからCUSTOMER.DBデータベースのアイコンをダブルクリックして「Tables」アイテムを選択します。MAINテーブルのプロパティを表示して、「許可」ページをクリックします。ツールバーの左端の[新しいアイテムを作成します]ボタンをクリックするとユーザーを追加できます。「Grantees:」から「USER1」を選択して[OK]ボタンをクリックします。

Hide image
02許可ページ

図02 許可ページ

「権限付与エディタ」が表示されますので[すべて]ボタンをクリックします。「with grant option」にはチェックをつけません。そのまま[OK(O)]ボタンをクリックして完了です。IBConsoleは終了します。

今度は、プログラムのほうの設定をしますね。Delphiを起動して「TransCSV」プロジェクトを開きます。「DMTransCSV」ユニットの「SQLConnection1」をダブルクリックして、「接続の設定」ダイアログを表示します。「User_Name」に「USER1」、「Password」に「user1」をセットします。

Hide image
03ユーザー変更

図03 接続の設定

接続できるかどうかをチェックして、できたら[OK(O)]ボタンをクリックして閉じます。これで、ユーザーの変更は完了です。

データの編集

次にデータを編集したいんだけど、Excelファイルにはデータを編集したかどうかは、残らないのよね。Excelファイルに任意のフィールドを追加してフラグを残すことはできるけれど、フラグをつけ忘れたり、次回にはフラグを外さなくちゃならなかったり、と運用に不安が残ります。

データベースの方だけど、既存のデータを編集して変更することはできるみたい。データセットをオープンしたあとEditメソッドを使用します。あとは編集したいレコードまでカレント行を移動して値をセット。ApplyUpdatesメソッドで更新を有効にします。

dmdTransCSV.ClientDataSet1.Edit

あとは、どうやって目的のレコードを探すかってことなんだけど、ここはやっぱり高橋先生に聞いてみようかな。

FROM: ナッキー

TO: 高橋先生

SUBJECT: 変更があったレコードを探すには

高橋先生、

こんばんは、佐竹です。

レコードの編集もしてみようかと思っています。でもCSVファイルのどのデータに変更があったか、特定が難しいのです。何かいい対処方法はありませんか？

よろしくお願いいたします。

佐竹

すると、

FROM: 高橋先生

TO: ナッキー

SUBJECT: RE: 変更があったレコードを探すには

ナッキー、

こんにちは、高橋です。

変更があったかどうかの特定が難しいなら、既存のデータをすべて上書きしてしまったらどうかな？もしも、変更がなくても上書きするだけだからデータに変化はない。ただし、既存のデータが少ないことが条件。もしも既存のデータがたくさんあったら処理に時間がかかってしまうかもしれない。

Takahashi

どれが、更新データなのかわからなければ、すべて更新すれば間違いはないわね。高橋先生、ありがとうございます。ここで扱っているデータは1万件を超えるほど、増えることはなさそうです。同じデータがあるかどうか調べて、見つかったら上書きすればいいわね。同じデータを探すのは、いくつかやり方があったはず。SQLのSELECT文で抽出する方法や、テーブルのフィルタ機能を使ってFindFirstメソッドで探します。SQLを使った検索は、テーブルのレコード全件数が多くて、使用したいレコードが少ない場合に向いています。ここで使用するのはすべてのレコードで、全体のレコード数も少ないのでフィルタ機能を使用します。

レコードを検索するときにキーとなるフィールドが必要です。本来はテーブルのプライマリキーがいいのですが、CSVデータには「ID」フィールドのような、キーとなるフィールドがありません。どこかのフィールドをキーとするしかありませんが、顧客データの書き換えって、社名だったり、住所だったり、電話番号だったりと異なるので固定できません。その都度、変更のないキーとなるフィールドを選べると便利ね。ラジオグループでキーとなるフィールドを選択できるようにします。ラジオグループの項目は各フィールドと、「すべて新規」の6つにします。

Hide image
04フォームのデザイン

図04 フォームのデザイン

frmTransCSVユニットをデザインモードで表示します。ラジオグループを置けるように[変換開始]ボタンを少し移動して、フォームの大きさも整えます。フォームにTRadioGroupコンポーネントを追加し、NameをrgpUpdateKey とします。そして、Captionプロパティに「変換キー選択」、Itemsプロパティに「社名、住所1、住所2、電話、HP、すべて新規」を設定します。次に、Columnsプロパティを「2」に設定します。

フィルタ処理でレコード検索

これで、検索のキーを取得できますね。では、さっそくコーディングにはいります。フィルタを使っての検索にはTClientDataSetコンポーネントのFilterプロパティとFilteredプロパティを使います。Filterプロパティには検索の条件式をセットします。Filteredプロパティはフィルタ処理を有効にするかどうかをセットします。Filterプロパティに条件をセット、Filteredプロパティに「True」をセットしてデータセットを開くと、TClientDataSetコンポーネントのレコードがFilterプロパティの条件式に合うものだけに抽出されます。

    dmdTransCSV.ClientDataSet1.Filter := '条件文';
    dmdTransCSV.ClientDataSet1.Filtered := True;
    dmdTransCSV.ClientDataSet1.Open;

フィルタの条件はラジオグループで選択した項目のフィールドと、CSVファイルから抜き取った配列のデータの一致です。ラジオグループのItemsプロパティにはIDフィールドがないので、ラジオグループのItemIndexプロパティに「1」を加えたものと、データセットのFieldsプロパティのインデックスが同じになります。フィールドの名前はFieldNameプロパティで取得します。このとき、静的項目コンポーネントが望ましいので、DMTransCSVユニットのデザイン画面で「ClientDataSet1」のアイコンをダブルクリックして、「すべての項目の追加」をしておきます。

dmdTransCSV.ClientDataSet1.
  Fields[rgpUpdateKey.ItemIndex + 1].FieldName

また、ラジオグループのItemIndexプロパティに「1」を加えたものと、CSVファイルから取り出したFieldData変数の要素番号が等しくなります。FieldData変数の内容をシングルクォーテーションで囲みたいのですが、シングルクォーテーション自身を文字として表現すると、「''」になります。

'Edit1.Caption ''文字列'' Edit2.Caption' // 「Edit1.Caption '文字列' Edit2.Caption」という文字列を表す

さらに、シングルクォーテーション1つを文字列で表すならば、それを囲むためのシングルコーテーションも必要なので「''''」になります。例えばEdit1.Captionに「ABC」という文字が入っていたなら

'''' + Edit1.Caption + ''''  // 「'ABC'」という文字列を表す

のように記述します。ここまでを組み合わせるとこのような記述になります。

dmdTransCSV.ClientDataSet1.
  Fields[rgpUpdateKey.ItemIndex + 1].FieldName
             + ' = ''' + Fielddata[rgpUpdateKey.ItemIndex + 1] + ''''

新規のレコードが混じっているかもしれないので、TClientDataSetコンポーネントのFindFirstプロパティでレコードの有無をチェックしてから書き換えレコードなのか、追加レコードなのかを判断します。追加の場合、IDフィールドが必要なので、フィルタなしの状態で一度開いてレコード数を変数に取っておきます。

dmdTransCSV.ClientDataSet1.Filter := '';
dmdTransCSV.ClientDataSet1.Filtered := False;
dmdTransCSV.ClientDataSet1.Open;
FieldData[0]:= IntToStr(dmdTransCSV.ClientDataSet1.RecordCount);
dmdTransCSV.ClientDataSet1.Close;

dmdTransCSV.ClientDataSet1.Filter :=
  dmdTransCSV.ClientDataSet1.
    Fields[rgpUpdateKey.ItemIndex + 1].FieldName
             + ' = ''' + FieldData[rgpUpdateKey.ItemIndex + 1] + '''';
dmdTransCSV.ClientDataSet1.Filtered := True;
dmdTransCSV.ClientDataSet1.Open;
if dmdTransCSV.ClientDataSet1.FindFirst then
  dmdTransCSV.ClientDataSet1.Edit
else
  begin
    dmdTransCSV.ClientDataSet1.Insert;
    dmdTransCSV.ClientDataSet1.Fields[0].AsInteger := 
                                     StrToInt(FieldData[0]) ;
  end;

注意したいのはフィルタの条件が変化したり、フィルタ処理を利用したり、利用しなかったりすることがある点です。新規のレコードにも対応するため、FilterプロパティやFilteredプロパティをOpenメソッドの前に必ずセットしておきましょう。他の部分も記述します。変更はCSVtoDBプロシージャだけです。

procedure TfrmTransCSV.CSVtoDB(str:string);
var
  i,l : Integer;
  strData : string;
  FieldPos : Integer;
  FieldData : array [0..5] of string;
begin
  strData := str;
  for i := 1 to 4 do
    begin
      FieldPos := AnsiPos(',', strData);
      FieldData[i] := LeftBStr(strData, (FieldPos - 1));
      strData := RightBStr(strData, (Length(strData) - FieldPos));
    end;
  FieldData[5] := strData;

  try
    dmdTransCSV.ClientDataSet1.Filter := '';
    dmdTransCSV.ClientDataSet1.Filtered := False;
    dmdTransCSV.ClientDataSet1.Open;
    FieldData[0]:= IntToStr(dmdTransCSV.ClientDataSet1.RecordCount);
    dmdTransCSV.ClientDataSet1.Close;

    if rgpUpdateKey.ItemIndex = 5 then
     begin
       dmdTransCSV.ClientDataSet1.Filter := '';
       dmdTransCSV.ClientDataSet1.Filtered := False;
       dmdTransCSV.ClientDataSet1.Open;
       dmdTransCSV.ClientDataSet1.Insert;
       dmdTransCSV.ClientDataSet1.Fields[0].AsInteger :=
                                     StrToInt(FieldData[0]) ;
     end
    else
      begin
        dmdTransCSV.ClientDataSet1.Filter :=
          dmdTransCSV.ClientDataSet1.
            Fields[rgpUpdateKey.ItemIndex + 1].FieldName
             + ' = ''' + FieldData[rgpUpdateKey.ItemIndex + 1] + '''';
      dmdTransCSV.ClientDataSet1.Filtered := True;
      dmdTransCSV.ClientDataSet1.Open;
      if dmdTransCSV.ClientDataSet1.FindFirst then
        dmdTransCSV.ClientDataSet1.Edit
      else
        begin
          dmdTransCSV.ClientDataSet1.Insert;
          dmdTransCSV.ClientDataSet1.Fields[0].AsInteger :=
                                     StrToInt(FieldData[0]) ;
        end;
      end;
    for l := 1 to 5 do
      dmdTransCSV.ClientDataSet1.Fields[l].AsString := Fielddata[l];
    if dmdTransCSV.ClientDataSet1.ApplyUpdates(0) > 0 then
      raise Exception.Create('更新できませんでした');
  finally
    dmdTransCSV.ClientDataSet1.Close;
  end;

end;

さあ、追加ができて、更新もOK。次はデータベースを直接編集したあとで、CSVファイルにも反映したいな。だんだん形になってきた気がするわ。もうすこしがんばろうっと。