ナッキーの「Delphiでビジネスアプリ奮闘記」-第2回 CSVファイルをデータベースへ移行する その2

By: Hitoshi Fujii

Abstract: CSVファイルからデータを取り出せたので、データベースに移します。データベースを作成して、コード上でアクセスしてみましょう。

Hide image
nacky_adv_75x83

ナッキー

前回はCSVファイルから、データを1つずつループ文で取り出しました。これをデータベースに移したら、システムの完成に一歩近づけるわ。データベースへのアクセスはちょっと難しいんだよな。


    前回のおさらい

前回はExcelファイルをCSVファイルに変換後、プログラムで読み込んでInterBaseのデータベースに書き込むために項目を切り取る作業に取り組みました。「,(カンマ)」の位置を調べて文字を切り取ることで、データを項目ごとに分けることができたわ。

    データベース作成

データはコード上でデータベースに移すことにしますが、データベースはあらかじめ作っておかなくっちゃ。データを移すためのデータベースを作成します。使用するDBMSはInterBase 2007で、IBConsoleを使ってデータベースを作ります。InterBase 2007を使うには、Delphiとは別にインストールする必要があります。

インストールを終えて環境が整ったら、データベースを作成するために、準備をしましょう。InterBaseサーバーマネージャーを管理者として起動します。Windows Vistaの場合には、起動したとき表示されるメッセージボックスで、[許可(A)]をクリックすると管理者として起動できます。

InterBaseサーバーマネージャーのダイアログボックスが表示されたら、「InterBaseサーバーをWindowsサービスとして起動(R)」のチェックボックスは外しておきます。次にダイアログボックスの[起動]ボタンをクリックします。これで、InterBaseサーバーにアクセスできるようになり、準備完了です。

ノート:

InterBaseのセットアップについては、以前こちらの記事で詳しく紹介しました。


データベースを作成します。IBConsoleを起動して、ローカルサーバーに接続します。[データベース]メニューの[データベースの作成(Y)]をクリックして新たなデータベースを作成します。

Hide image
図1

図01 データベースの作成

ファイルはプロジェクトを作るフォルダに作成すると、コード上でデータベースにアクセスするときファイル名だけ入力すれば接続できます。文字化けを防ぐためにオプション欄の「Default Character Set」で「SJIS_0208」を選択します。「エリアス(A):」に適当な名前を付けて[OK]ボタンをクリックします。

次にテーブルを作成します。「Tables」アイテムを選択して、IBConsole右欄のオブジェクトビューウインドウで、ポップアップメニューを表示して、[作成(C)...]を選択します。フィールド作成のダイアログボックスが表示されます。

Hide image
図2

図02 テーブルの作成

「テーブル名(T)」には「MAIN」、フィールドは「ID、COMPANY、ADDR_1、ADDR_2、TEL、HP」の6つです。IDフィールドは、[データ型を指定]で「INTEGER」に設定し、「Not Null」にチェックを入れます。ほかのフィールドはすべて「データ型を指定(T)」で「VARCHAR」にします。キャラクタセットは「SJIS_0208」を選択。VARCHAR型のフィールドのサイズは用意したCSVファイルのデータが入るように設定します。ここでは、COMPANY:64、ADDR_1:32、ADDR_2:128、TEL:32、HP:128、としています。IDフィールドはプライマリキーとして登録しておきます。フィールドが完成したら、データベースの作成は終わりです。データはプログラムで入力します。

    データベースコンポーネント

データベースが準備できたらIBConsoleを終了して、データを移行するコードをプログラミングします。Delphiを起動して、前回作成したプロジェクトを開きます。まずは、データベースに接続するところからね。今回はデータモジュールを使ってコンポーネントを配置します。

データモジュールはデータベース接続のための非ビジュアルコンポーネントを配置するためのものです。ちょっと見は、フォームのようですが実行しても表示されません。「データモジュール」という名前ですが、非ビジュアルコンポーネントならデータベースに関わりがなくても配置することができるんですって。データモジュールはメニューから作成します。作り方は、「ファイル(F)|新規作成(N)」メニューの「その他(O)...」を選択します。オブジェクトリポジトリが表示されますので「Delphiファイル」カテゴリの「データモジュール」を選択して[OK]ボタンをクリックします。「Unit1」のように末尾に番号のついた新規ユニットが追加されます。

Hide image
図3

図03 データモジュールの作成

追加したデータモジュールに、データベースコンポーネントを配置します。今回使うコンポーネントは「dbExpress」カテゴリの「TSQLConnection、TSQLTable」とDataAccessカテゴリの「TDataSetProvider、TClientDataSet」です。たくさんコンポーネントが必要ですが、データベースサーバーとやり取りする部分と、データを操作する部分が離れているのでトランザクションが短くて済んだり、独自の計算データをメモリに保持することでデータベースサーバーに負担をかけないようにすることができます。配置したコンポーネントにはそれぞれ「SQLConnection1、SQLTable1、DataSetProvider1、ClientDataSet1」という名前がつきます。ここでは、データモジュールのNameプロパティに「dmdTransCSV」、ユニット名は「DMTransCSV」として保存します。もし、前回作成したメインフォームのユニット名に既に「Unit2」のようなデフォルトの名前が付いている場合は、ファイルメニューの「名前を付けて保存(A)...」で「FormTransCSV」と名前を付けます。

はじめにSQLConnection1コンポーネントのプロパティを設定します。SQLConnection1コンポーネントをダブルクリックして「接続の設定」ダイアログを表示します。ツールバーの[接続の追加]ボタンをクリックして「ドライバ名(D)」は「InterBase」、接続名に「CUSTOMERDB」を入力して[OK]ボタンをクリックします。右側の「接続の設定(S)」欄で「Database」に先ほど作ったデータベースのファイル名を入力します。「User_Name」に「SYSDBA」、「Password」に「masterkey」、「ServerCharSet」に「SJIS_0208」を入力して[OK(O)]ボタンをクリックします。

Hide image
図4

図04 接続の設定

できたので[接続のテスト]ボタン(btn)で接続を確認します。

次にSQLTable1コンポーネントは「SQLConnection」プロパティに「SQLConnection1」を設定、「TableName」プロパティに「MAIN」を選択します。DataSetProvider1コンポーネントは「DataSet」プロパティに「SQLTable1」を設定。ClientDataSet1コンポーネントは、「ProviderName」プロパティに「DataSetProvider1」を設定します。

プロパティの設定ができたら、コーディングです。コードはもとのFormTransCSVユニットに記述します。DMTransCSVユニットをuses節に追加して、先ほど配置したコンポーネントを使えるようにしておかなくっちゃね。画面をFormTransCSVユニットに切り替えて[ファイル|ユニットを使う]で、[DMTransCSV]を選択します。

まずはデータベースサーバーに接続します。設計時に接続しておくと、起動中は常に接続した状態になってしまいます。データベースは接続中に障害が起こると深刻な問題になりがちなので、データベースを使用する直前に接続して、不要になったらすぐに接続を解除するようにコーディングしたほうが、安心ですね。設計時のConnectedプロパティは「False」にしておきます。

dmdTransCSV.SQLConnection1.Connected := True;
//データ処理
dmdTransCSV.SQLConnection1.Connected := False;

格納するデータはCSVファイルから読み取りました。ただし、「ID」フィールドはCSVファイルにはありません。どうやって追加したらいいかしら?高橋先生に聞いてみよう!

FROM: ナッキー

TO: 高橋先生

SUBJECT: IDフィールドの採番について

高橋先生、

こんにちは、佐竹です。CSVファイルからデータを取得できましたが、データベースにするためIDフィールドに番号をつけたいんです。どうやって採番したらいいでしょうか?

佐竹


すると、

FROM: 高橋先生

TO: ナッキー

SUBJECT: RE: IDフィールドの採番について

ナッキー、

こんにちは、高橋です。自動的に番号が振られるようなフィールド属性を設定できるデータベースシステムもあるけどInterBaseには無いんだ。もちろんその代わりになる仕組みもあるけど、今回は単純にテーブルの行数の最大値を使えばよいと思うよ。

Takahashi


テーブル行数の最大値かぁ。ヘルプを参照してみます。いいプロパティはないかな?TClientDataSetのプロパティを探していると、TClientDataSetメンバのPublicプロパティにRecordCountプロパティがありました。まさしくこれね。

dmdTransCSV.ClientDataSet1.Open;
FieldData[0]:= dmdTransCSV.ClientDataSet1.RecordCount;

いよいよレコードを追加します。追加するときにデータセットの状態を挿入モードにする必要があります。「Open」メソッドで開いただけだと読み取りができる状態です。TClientDataSetコンポーネントには状態を変化させるメソッドがいくつかあり、追加できるようにするには「Insert」メソッドを使います。

dmdTransCSV.ClientDataSet1.Insert;

次に準備したCSVのデータを1つ1つのフィールドに格納しなくっちゃ。今回もCSVファイルから取り出したときと同じようにfor文を使えばいいわね。

for l := 1 to 5 do
  dmdTransCSV.ClientDataSet1.Fields[l].AsString := Fielddata[l]; 

これだけでは、データベースには反映しません。作業用のエリアにデータを用意した状態です。今度はデータベースにレコードを送ります。TClientDataSetコンポーネントの「ApplyUpdates」メソッドを使います。ApplyUpdatesメソッドはデータを更新するだけでなく、うまくデータが処理できなかったとき、エラーを知らせてくれるんです。パラメータは許容エラー数。「-1」にすると、いくつでもエラーを許可します。エラーが発生したレコードを含めないとすると、「0」にします。戻り値はエラー数なので、成功したときには「0」失敗したときは「1」を返します。だから、戻り値が「0」より大のとき例外を発生させて処理をストップさせればいいわね。エラーメッセージも表示します。

if dmdTransCSV.ClientDataSet1.ApplyUpdates(0) > 0 then
  raise Exception.Create('更新できませんでした');

データベースを扱うときに、いろいろな場所でトラブルはあるかもしれません。トランザクションを設定するとトラブルが発生しても、元に戻してデータに間違いがないようにしてくれます。

データがうまく受け渡せないときの例として、商品とお金のやり取りを考えてみましょう。AさんがB社の商品を買う時、AさんはB社に注文してお金を渡します。B社はお金を確認して、商品をAさんに渡すのが通常です。ところがプログラムの上で、Aさんが商品を注文してお金を渡し、B社がお金を確認する前にトラブルが発生したとします。そうするとB社はお金をもらっていないから商品も渡さない、Aさんはお金を渡したけど商品は渡されない状態になってしまいます。

Hide image
図5

図05 取引の成功と失敗

Aさんが注文してからB社が商品を渡すまでの間を関連する取引としてまとめるのが、トランザクションです。トランザクションを使うと、関連する処理のどこかでトラブルが発生した場合、まとめた処理のすべてが取り消され、なかったことにされます。トラブルが発生しなかったら、注文から商品引き渡しまでのすべての処理が行われます。関連する処理の範囲は任意で決められます。

トランザクションを設定するには、始まりの個所に「BeginTransaction」メソッドを使用します。戻り値は「TDBXTransaction」型です。「DBXCommon」ユニットにある型なので、uses節になければ追加しておきます。

すでに同じ名前のトランザクションが進んでいたら、例外が発生します。そのためにif文で同じ名前のトランザクションが進んでいないかチェックしてから、トランザクションを始めます。トランザクションが始まっているかどうかは「InTransaction」プロパティで調べます。

if not dmdTransCSV.SQLConnection1.InTransaction then
  dbTran := dmdTransCSV.SQLConnection1.BeginTransaction;
//データ処理

終了するときには、成功した場合と失敗した場合と2種類の処理があります。成功した場合は「CommitFreeAndNil」メソッド、失敗した場合は「RollbackFreeAndNil」メソッドを使います。

try
  dbTran := dmdTransCSV.SQLConnection1.BeginTransaction;
  //データ処理
  dmdTransCSV.SQLConnection1.CommitFreeAndNil(dbTran);
Except
  dmdTransCSV.SQLConnection1.RollbackFreeAndNil(dbTran);
end;

更新や追加などのデータは「CommitFreeAndNil」メソッドを使用したときデータベースサーバーに書き込まれます。それまではクライアントに仮のデータとして取っておきます。

最後にどうしてもやらなくちゃならないのが、データベースのCloseとデータベースサーバーの切断です。こういうときはtry..finallyでしたよね。ここまでを前回作成したコードに含めるんですが、CSVファイルを読み込むfor文の中にいっぱい書かなくちゃならなくて、読みにくいコードになりそう。こういうときは、機能で分けてプロシージャを作るとコードがスッキリするんだったわね。ということで、「CSVファイルの読み込み/トランザクションの制御」を行う部分と、「データベースへの書き込み」を行う部分を分けてみます。まずは、CSVファイルを読み込む処理をコーディングします。次に、トランザクションを制御するコードも追加します。uses節に「DBXCommon」ユニットを追加するのを忘れないようにしないとね。

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, StrUtils, DBXCommon, DMTransCSV;
(中略)

procedure TfrmTransCSV.btnStartClick(Sender: TObject);
var
  csvFile : TextFile;
  strBuf : string;
  dbTran : TDBXTransaction;
begin
  AssignFile(csvFile, 'C:\Delphi_App\顧客.csv');
  Reset(csvFile);
  Readln(csvFile, strBuf);

  dmdTransCSV.SQLConnection1.Connected := True;
  if not dmdTransCSV.SQLConnection1.InTransaction then
    dbTran  := dmdTransCSV.SQLConnection1.BeginTransaction;
  try
    try
      while not Eof(csvFile) do
        begin
          Readln(csvFile, strBuf);
          CSVtoDB(strBuf);
        end;
      dmdTransCSV.SQLConnection1.CommitFreeAndNil(dbTran);
    except
      on E : Exception do
      begin
        dmdTransCSV.SQLConnection1.RollbackFreeAndNil(dbTran);
        ShowMessage(E.Message);
        abort;
      end;
    end;
  finally
    CloseFile(csvFile);
    dmdTransCSV.SQLConnection1.Connected := False;
  end;
  MessageBox(0, 'データ変換終了',PChar(Application.Title), MB_OK);
end;

次にデータを移す部分をコーディングします。「CSVtoDB」手続きを宣言部および実装部に記述します。CSVファイルにないIDフィールドはデータベースの件数を調べてセットします。データベースの件数はTClientDataSetの「RecordCount」プロパティで調べることができます。

type
  TfrmTransCSV = class(TForm)
    btnStart: TButton;
    procedure btnStartClick(Sender: TObject);
  private
    { Private declarations }
    procedure CSVtoDB(str : string);
  public
    { Public declarations }
  end;

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.Open;
    FieldData[0]:= IntToStr(dmdTransCSV.ClientDataSet1.RecordCount);

    dmdTransCSV.ClientDataSet1.Insert;
    dmdTransCSV.ClientDataSet1.Fields[0].AsInteger
                                     := StrtoInt(FieldData[0]) ;
    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;

やっと、完成だわ。今回は実行テストするとテーブルを参照して、内容を確認することができます。データベースでは「もしも」のときのことを考えると、大変ですね。でも、重要な部分だから、もしもがあっては困るのよね。がんばって、もう少し機能アップしよう。

Server Response from: ETNASC01